Solving Sales Tax Problems for QuickBooks Clients

From the Aug. 2009 Issue

For several years, I’ve been writing about the difficulties of getting
sales tax right in QuickBooks. It seems that virtually every client we look
at has some type of problem with their sales tax data, caused by a combination
of improper setup or inconsistent data entry. On the surface, it seems pretty
easy to set up QuickBooks sales tax codes, items and “rules” for
how to treat sales transactions, but when clients have any significant transaction
volume, it seems that we always find some type of problem when preparing sales
tax returns.

Sales Tax “rules” are relatively simple in some states, but in
many states, the rules get quite complex by requiring businesses to track multiple
taxing locations, rates and agencies. And it gets even harder when there are
special rules for when and how much to tax items, based on factors such as the
“reason for the purchase.”

Truth be told, I have yet to find an accounting program that does a very good
job of handling sales tax. Admittedly, I have pretty high standards for what
I call “good.” To be good, I think the software needs to allow someone
who does his/her own setup and bookkeeping to be able to correctly set up the
sales tax rules, enter the data, pay the taxes, and prepare the tax returns.
And the program must be able to calculate the correct sales tax for any sales
item, in any jurisdiction, and it needs to have always-up-to-date tax rate information.
That’s a VERY tall order because there are so many obscure rules for sales
taxation no matter where you do business. So most accounting low-end applications
get you close, but to make sure the client is fully compliant, expert intervention
is almost always required.

The good news is that add-on developers have developed exactly the solution
I’ve been looking for. There are a couple of products that plug directly
into QuickBooks and most of the popular accounting applications to address the
whole sales tax problem. From the taxation calculation all the way through to
filing the tax returns and providing complete information for a sales tax audit.

The company with the most complete solution in this area is Avalara (www.avalara.com).
I first wrote about Avalara in 2004 when they were just testing their product,
and now with five years of experience in the market, they have gained quite
a presence in the marketplace, serving thousands of clients and millions of
transactions. Another company that has a very similar product is SpeedTax (www.speedtax.com).
Their product is much newer, and I haven’t had the chance to dig deeply
into it yet, but it’s worth checking if you’re looking for the full
solution.

With Avalara’s AvaTax subscription-based SaaS solution, you get an add-on
that dynamically calculates sales tax by looking up the current taxability for
each item on an invoice, based on the ship-to location. In real-time, the product
connects to Avalara’s online tax database, calculates the taxation for
a whole invoice, and returns the correct amount to QuickBooks as the user records
each transaction. AvaTax can also be plugged into web stores such as Prostores
for those businesses who sell products online. This is key because so many businesses
have a mix of sales transactions: Some are created manually in the accounting
application, and some are imported from a web store or POS system. And the sales
taxation needs to be consistent across all sales transactions.

AvaTax also has a sister product called AvaFile, which takes the data directly
to the sales tax return and allows you to efile your return. So if you’re
looking for a full solution to sales tax, I think the best approach is to seek
out a product like AvaTax or SpeedTax.

In the meantime, if you’re trying to help clients who have just the
standard, vanilla QuickBooks, it’s important that you get things set up
correctly and that data entry is done correctly and consistently. To help you
do that, here are several tips for how to set up, track and troubleshoot sales
tax in QuickBooks.

SALES TAX ITEMS
The first thing to know about how QuickBooks tracks sales tax is to understand
that Sales Tax Items are used on sales forms to calculate the amount of sales
tax due on each sale. You set up Sales Tax Items in a list where you define
the name, tax rate and agency to which you pay this tax. Quite often, a given
tax rate is the sum of several component taxes, such as the state sales tax,
the county sales tax, a redevelopment agency tax or a public transportation
tax. Also, some states require you to pay these separate tax amounts directly
to each taxing authority (state, county or other agency) while many states collect
all the taxes and then distribute the money to each of the local governments
and agencies.

Since QuickBooks allows you to create several individual tax items (with each
portion of the total tax) for each tax agency, many people question whether
they should just create one item for the full tax amount (say 8 percent), or
should they create separate items for each of the small taxes, and then create
a “Sales Tax Group” that combines those smaller items into one total
tax.

The key to deciding whether to create individual items plus a group, or whether
you should create just one item with the total of the combined tax, is to find
out if you need to pay the individual taxes to each agency. If you do, you’ll
need to create individual tax items for each tax agency. If the state collects
and distributes the taxes (i.e., you pay it all to a single agency), then all
you need to do in QuickBooks is create one single tax item for each county (or
other dividing line) in which you collect tax.

SALES TAX CODES
Sales Tax Codes are an additional classification for calculating and reporting
sales tax. A Sales Tax Code is assigned to each product or service item, as
well as to each customer.

Sales Tax Codes serve two purposes. First, they indicate whether a specific
product or service is taxable or non-taxable. Secondly, they categorize revenue
based on the reason you charged or didn’t charge sales tax.

If your sales tax agency requires reporting for different types of exempt
sales, you may wish to create several non-taxable Sales Tax Codes for each type
of non-taxable sale (e.g., RSR for non-taxable resellers).

USING SALES TAX ITEMS & CODES ON SALES FORMS
On sales forms, Sales Tax is calculated at the bottom of the form, separately
from the rest of the Items on the form (see Figure 1).

HOW SALES TAX IS CALCULATED BY QUICKBOOKS
When you properly set up your QuickBooks items, Customers, Sales Tax Codes and
Preferences, QuickBooks automatically calculates and tracks sales tax on each
sale.

As
illustrated in Figure 2 and detailed in the steps above, each line on a sale
shows a separate Item that is taxed according to the combination of how the
Item, Tax Code, and Customer is set up. When you set up Items, you indicate
which Sales Tax Code normally applies to that item. In addition, when you set
up a customer record, you indicate the Sales Tax Item and Sales Tax Code to
be used for that customer.

Then, when you create a sale (Invoice or Sales Receipt), the Customer Tax
Code and the Sales Tax Item are taken from the customer’s record and filled
into the Customer Tax Code and Tax fields on the form. The Customer Tax Code
overrides the Tax Code on each line item. If necessary, you can override the
Tax Code on each line of the sales form or at the bottom of the form. The Tax
Item, which can also be overridden, determines the rate to charge on the sum
of all taxable line items on the sale.

THE SALES TAX LIABILITY REPORT
The Sales Tax Liability report provides you with the information you need to
prepare your sales tax return, including a breakdown of sales and sales tax
collected by county and sales tax agency.

The Sales Tax Liability report shows total sales in each county (e.g., Sales
Tax Items Contra Costa & Santa Clara), and shows the taxable sales separately
from the nontaxable sales. In addition, you can see the tax rates and tax collected
in each county.

The Tax Collected column shows how much tax you have collected on sales for
the period. If you have made any sales tax payments for this period or owe tax
from prior periods, the Sales Tax Payable column will reflect these activities.

To see the detail of transactions that comprise the Sales Tax Liability report,
double-click on any dollar amount in the report.

The State Board of Equalization – Other line on the report shows any
adjustments to the Sales Tax Payable account that you make using a General Journal
Entry or the Sales Tax Adjustment window. This line is zero because no sales
tax adjustments were recorded for this period.

CATEGORIZING REVENUE BASED ON SALES TAX CODES
QuickBooks provides a breakdown of revenue by Sales Tax Code on the Sales Tax
Revenue Summary report (see Figure 3). To display the report, select Vendors
& Payables from the Reports menu and then select Sales Tax Revenue Summary.

If
your sales tax agency requires a detailed breakdown of tax exempt sales, use
Sales Tax Codes to produce information you need and to categorize your sales
by the reason you charged, or didn’t charge, sales tax.

For example, you can create a separate Sales Tax Code for each type of non-taxable
customer, like government agencies, not-for-profit organizations, or resellers
(see Figure 5).

Then, when you use these Sales Tax Codes on sales forms according to why you
charge or do not charge sales tax, QuickBooks will show a separate column for
each Sales Tax Code in the Sales Tax Revenue Summary report. To display the
report, select Vendors & Payables from the Reports menu and then select
Sales Tax Revenue Summary.

To view each individual sale, double-click on any amount in the Sales Tax Revenue
Summary report. This will display a Transaction Detail report showing each transaction
affecting that Sales Tax Code.

SALES TAX PROBLEM SOLVING PROOFING SALES TAX REPORTS
To prepare your sales tax return, you must first validate the numbers on the
Sales Tax Liability and Sales Tax Revenue Summary reports. The following list

of questions will help you confirm the accuracy of information on sales tax
reports and will provide ideas for making corrections.

Does the total of the Sales Tax Payable column on the Sales Tax Liability
report match the Sales Tax Payable figure on the Balance Sheet?

If not, make sure that the reporting basis is the same on both reports. For
example, if you accrue sales tax “Upon Receipt of Payment,” make
sure to use a cash basis Balance Sheet.

Does Total Sales on sales tax reports agree to the total income on the Profit
and Loss? If not there may be several causes:

Items
Used On Sales Forms That Post To Non-Income Accounts
QuickBooks calculates the sales columns on the sales tax reports from the total
of each Invoice, Sales Receipt and Credit Memo (net of sales tax) regardless
of the type of item used. Since some items may point to non-income accounts
(e.g., an item to record prepayments that posts to unearned income), the total
sales on the Sales Tax Liability and Sales Tax Revenue Summary reports will
not agree to total income on the Profit & Loss. To correct this problem,
filter the Sales Tax Liability and Sales Tax Revenue Summary reports by account
for “All Ordinary Income Accounts.” Then memorize the report for
future use.

Discounts Taken on Bills
If you take discounts on bills by clicking Set Discounts in the Pay Bills screen,
QuickBooks increases Income but does not increase sales on the Sales Tax Liability
and Sales Tax Revenue Summary reports. You will need to reduce this amount for
Total Income on the Profit & Loss by the amount of discounts taken before
attempting to reconcile the two reports.

Transactions Other Than Sales Forms Affect Income Accounts
Only sales forms (Invoices, Sales Receipts or Credit Memos) will affect the
sales amounts on the Sales Tax Liability and Sales Tax Revenue Summary reports.
Using the Find command, search by “Transaction Type” for all transactions
other than Invoices, Sales Receipts and Credit Memos, by “Account”
for “All Ordinary Income Accounts”, and by “Date” for
the current fiscal period. If QuickBooks finds any transactions you will need
to reverse their affect on Income before the two reports will reconcile.

Are some lines on the Sales Tax Liability Report missing a Sales Tax Item?
If so there may be several causes:

The Client Probably Used Write Checks or Pay Bills Instead of Pay
Sales Taxes to Create Sales Tax Payments.
To correct this, replace the check (CHK) with a sales tax payment transaction
(TAX PMT).

The list of possible problems goes on, but these are the most common problems
we find with client data. There are many trouble spots you’re likely to
hit when helping clients with Sales Tax in QuickBooks (or any other accounting
software for that matter). It pays to school yourself on the tiny details of
their setup and confirm it works correctly before you release the client to
enter his or her own data.

One thing is for sure, and that is – with sales tax problems, the troubleshooting
and corrections are often very time-consuming, painful, and require lots of
expertise to correct. So make sure you get sales tax set up correctly from the
beginning and save yourself from headaches down the road.

– – – – – – – – – – – – – – – – – – – – – – – – – –

Mr. Sleeter is the founder of The Sleeter Group, a national group of QuickBooks
and accounting software consultants who serve small and medium-sized businesses.
He is the author of several books including the QuickBooks Consultant’s
Reference Guide, and the market leading college textbook “Learning QuickBooks
Step by Step.” For more information, call 888-484-5484 or visit www.sleeter.com.