CPA Technology Advisor - QuickBooks and Your Sales Tax Return

The QuickBooks Advisor - QuickBooks And Your Sales Tax Return

(Link to Article) (View pdf)

By Doug Sleeter From the November 2005 Issue

Sales Tax is one of the most complicated areas of any accounting program, mainly due to the fact that there are so many different taxing locations, rates, agencies, and rules for when to tax, and how much. 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."

For example, if the software is targeted at the do-it-yourselfer who does his/her own setup and bookkeeping tasks, then I think it's important that these novice users will be able to correctly set it up, 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. 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 applications in the low end get you close, but to make sure the client is fully compliant, expert intervention is required.

A few solutions are on the horizon (implemented as add-ons), and these solutions more fully address the sales tax problem. I've seen some early prototypes, and one that stands out is Avalara (www.avalara.com). Avalara is developing an add-on that plugs into many accounting applications that will dynamically calculate sales tax by looking up the current tax rates for each item based on the ship-to location as you create sales transactions. What's cool about it is that they actually look up the data on the Internet in real-time, so the calculation is always up to date and accurate. They also allow you to create the sales tax return automatically. Another developer, Sales Tax Pro (www.salestax-pro.com) is developing an add-on for QuickBooks that calculates and prints the California Sales Tax returns from QuickBooks data.

I think we'll start to see some very good progress in the area of sales tax in the coming months and years. But for now, most of us have to deal with sales tax in the standard, vanilla QuickBooks. As your QuickBooks Advisor, I have a few tips on how to track sales tax in QuickBooks and how to validate the data when it's time to prepare the sales tax returns.

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 the creation of 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 if they should 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.

The Sales Tax Liability Report
The Sales Tax Liability report provides most of 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. This report shows the breakdown of sales tax items for each agency by item. 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. See Figure 1.

The Tax Collected column shows how much tax you've 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.

Note: In this example, Sales tax had been paid for January and February, so the Tax Collected and Sales Tax Payable are not the same. If you report and pay on the same schedule, these columns will normally match.

If you want 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 adjustments made to the Sales Tax Payable account using a General Journal Entry or Sales Tax Adjustment. This line is zero because there is no sales tax adjustment recorded for the reporting period.

Using Sales Tax Items 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. To see this, display Invoice 2004-114 (the last Invoice in the sample file). See Figure 2.

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, Sales Tax Codes indicate whether a specific product or service is taxable or non-taxable. Secondly, Sales Tax Codes 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).

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. If your sales tax agency requires a detailed breakdown of tax exempt sales, use Sales Tax Codes to produce information you need. Use Sales Tax Codes 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 4.

Then, when you add additional Sales Tax Codes such as those in Figure 4, and then use 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. See Figure 5. To view each individual sale, double-click on any of the amounts 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. See Figure 5.

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

1. 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. See Figure 6.

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

3. 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, you can 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 effect on Income before the two reports will reconcile. See Figure 7.

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 for 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: 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 to save yourself from headaches down the road.

Mr. Sleeter is the founder of The Sleeter Group, a national provider of expert-level QuickBooks seminars for accountants. He is the author of several books including McGraw Hill's college textbook series, "Introduction to QuickBooks Pro." For more information, call 888-484-5484 or visit www.sleeter.com.