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 ( 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 ( 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.

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 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).

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).

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 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.

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.

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