Product Tips

Master Tip: Troubleshooting Sales Tax Payable in QuickBooks 2005 By: Joe Woodard, Certified QuickBooks ProAdvisor® Product: QuickBooks Financial Software Tip Category: Shortcut/Best Practice

Joe Woodard, Certified QuickBooks ProAdvisor and frequent QuickBooks trainer, shares a new Master Tip on dealing with problems in Sales Tax Payable. Joe tells us this area can generate confusion among some clients – and even some practitioners.

Here’s his take on how to troubleshoot problems in Sales Tax Payable.

Situation

In QuickBooks, Sales Tax tracking is much more than a current liability balance on the Balance Sheet. In addition to accruing Sales Tax Payable, QuickBooks also computes and tracks Sales Tax Payable based on:

1. Sales Tax Items – You use Sales Tax Items to track sales tax accruals by taxing authority (e.g. county and/or state). Note: You may have multiple Sales Tax Items (i.e., taxing authorities) per Tax Agency (i.e., sales tax vendor).

2. Sales Tax Codes – Sales Tax Codes cause sales to be taxable or non-taxable and they also track the reason why you charge or do not charge sales tax. Sales Tax Codes are assigned to:

a. Sales Items – For example, you could use a non-taxable sales tax code called “LBR” (Labor) for Service Items as shown below. You can override a Sales Item’s default Sales Tax Code if necessary when you use the Item on a sales form.





b. Customers – For example, you could use a non-taxable Sales Tax Code called “RSR” (Reseller) for resellers. You can override the Customer’s default Sales Tax Code if necessary when creating sales forms.

This multi-level sales tax tracking allows your client to create very detailed sales tax reports like the Sales Tax Liability Report and the Sales Tax Revenue Summary Report to facilitate the preparation of the Company’s sales tax returns. However, due to the complexity of the sales tax tracking, making corrective adjustments to Sales Tax Payable is more involved than simply entering a Journal Entry to correct the balance.

When troubleshooting sales tax tracking in QuickBooks you need to review both the sales tax setup and the Sales Tax Payable ledger. This article includes the following analyses, with detailed, step-by-step instructions:

• Analysis 1 – Review Sales Tax Item Setup Windows (Setup Analysis)
• Analysis 2 – Review the Sales Tax Codes in Sales Item Setup Windows (Setup Analysis)
• Analysis 3 – Review the Sales Tax Codes in Customer Setup Windows (Setup Analysis)
• Analysis 4 – Compare the Balance in Sales Tax Payable on the Balance Sheet to the Company’s Most Recent Sales Tax Return (General Ledger Analysis)

Analysis 1: Review Sales Tax Item Setup Windows

Perform the following steps to review Sales Tax Item setup windows:

• Step 1 – Select the Reports drop down menu, select List, and then select Item Listing to create an Item Listing Report.
• Step 2 – Filter the Item Listing report by Item to include each Sales Tax Item and Sales Tax Group as shown below.

• Step 3 – Remove all columns from the report except: Item, Description, Type, Price, and Tax Agency.
  Note: When viewing Sales Tax and Sales Tax Group Items, the Price column shows the sales tax rate.
• Step 4 – Assign an appropriate report title (as shown below) and then memorize the report for future use.

• Step 5 – Review each Sales Tax Item on the report to make sure the Sales Tax Item is setup correctly. Look for the following information:

  a. Tax Rate – Make sure the tax rate is correct.
  Note: The Sales Tax Item may be part of a Sales Tax Group. In this case you will need to combine the tax rates for all items in the Sales Tax Group to make sure the rate is correct. Use QuickZoom (double-click) if you want to see the details of the Sales Tax Group Item setup.

  b. Tax Agency – Make sure each Sales Tax Item is associated with the correct Tax Agency (vendor record). Consider that the Company may have multiple vendor records for the same Tax Agency. For example, you may find a vendor called “State Board of Equalization,” and another vendor called “St. Board of Equalization.” If you find duplicate vendor records, merge the records together.
  Important: Make sure the Company did not intentionally create the duplicate records. For example, in Georgia you would pay both your sales tax payment and your state withholding payment to the same vendor. If this is the case you should not merge the vendor records. Instead, edit the Sales Tax Item setup windows as necessary to correct the vendor.
  
  

• Step 6 – Changing the tax rate in a Sales Tax Item setup window does not impact existing transactions. If you determine that the client has been under-collecting or over-collecting sales tax you may need to enter a Sales Tax Adjustment (or Journal Entry) to correct the balance. If this is the case, advise the client to make the appropriate adjustment on their next Sales Tax Return

Analysis 2: Review the Sales Tax Codes in Sales Item Setup Windows

• Step 1 – Select the Reports menu, select List and then select Item Listing to create an Item Listing Report.
• Step 2 – Modify the Report as follows: a. On the Display tab, remove all of the columns except: Item, Type, Description, and Sales Tax Code.

  b. Filter the report by Item for “All Sales Items.”

  c. Enter an appropriate Report Title as shown in the screenshot below.

  d. Memorize the report for future use.

• Step 3 – Item Types consistently correspond to the Item’s taxable status. Make sure each item has the correct Sales Tax Code. Since Sales Tax Codes make items taxable or non-taxable and also track the reason why you charged or didn’t charge sales tax, incorrect default Sales Tax Codes could

  a. Cause errors in sales tax calculations on sales forms.

  b. Cause errors on the Sales Tax Revenue Summary report.
  For example, if a non-taxable Service Item uses the RSR (Reseller) Sales Tax Code, QuickBooks will calculate sales tax correctly on sales forms, but on the Sales Tax Revenue Summary report the Reseller sales would be overstated and the Labor (LBR) sales would be understated.
  
  

• Step 4 – As necessary, QuickZoom (double-click) on Sales Items to correct the default Sales Tax Code.
• Step 5 – Changing the default Sales Tax Codes does not impact existing transactions. If you determine that the client has been under-collecting or over-collecting sales tax you may need to enter a Sales Tax Adjustment (or Journal Entry) to correct the balance. If this is the case, advise the Company to make the appropriate adjustment on its next Sales Tax Return.

Analysis 3: Review the Default Sales Tax Codes in Customer Setup Windows

Perform the following steps to analysis the default Sales Tax Codes in the Customer Setup windows:

• Step 1 – Select the Reports menu, select List and then select Customer Phone List.
• Step 2 – Remove the Phone column and add the following columns to the report: Sales Tax Code, Sales Tax Item, and Resale Number.
• Step 3 – Enter an appropriate Report Title as shown in the screenshot below.
• Step 4 – Sort the report by Sales Tax Code. Sorting the report by Sales Tax Code – in either ascending or descending order, is necessary to:

  a. Segregate the Customer records on the report from the Job Records. Job Records do not have a Sales Tax Code field. When you enter a Job name on a sales form, QuickBooks refers to the Custom Record to determine the Sales Tax Code to be used.

  b. Segregate the taxable customers from the non-taxable customers. For most Companies, the majority of the customers will either be taxable or non-taxable. A Company that sells directly to its customers will have mostly taxable customers, as shown in the screenshot below. Non-taxable customers would be the exception. A wholesaler will have mostly non-taxable customers. In this case, taxable customers would be the exception. Review the report for the Sales Tax Code exceptions and make sure the exception correctly applies to that Customer.

• Step 5 – In the report above all of the customers are taxable except Glass Works Custom Windows. Glass Works is the only “exception” customer. Use this report to confirm that Glass Works is a reseller and that the customer record includes the correct resale number. Tip: For not-for-profit customers use a Sales Tax Code called “NFP” and enter the sales tax exemption number in the Resale Number field.
• Step 6 – Remove the Sales Tax Code and Resale Number columns and add the City column as shown below.

• Step 7 – Compare the City field to the Sales Tax Item to make sure each customer’s default Sales Tax Item is correct. Consider the following when conducting this analysis:

  a. Unless the Company delivers or ships the product to the customer’s location, the Sales Tax Agency may need to be based on the Company’s location instead of the customers. If this is the case, almost all of the customers would have the same Sales Tax Item. Sort the report by Sales Tax Item and look for the exceptions.
  Note: For our example, Rock Castle Construction delivers all materials to the building/re-construction site.

  b. If the Company ships or delivers its products or if the Company has multiple locations, this analysis could take over an hour (or even several hours) to complete. It may be best to have the Company’s bookkeeper conduct this analysis. This level of work may not be the best use of your client’s consulting dollars.
  
  

• Step 8 – If you determine that the Company has been under-collecting or over-collecting sales tax as the result of incorrect Sales Tax Items, you may need to enter a Sales Tax Adjustment (or Journal Entry) to correct the balance. This is only an issue if the Sales Tax Items have different rates. If you have to adjust the balance in Sales Tax Payable, advise the client to make the appropriate adjustment on their next Sales Tax Return.

Analysis 4: Compare the Balance in Sales Tax Payable on the Balance Sheet to the Company’s Most Recent Sales Tax Return

Every business has an allotted time period between the end of the month or quarter and the due date for the sales tax return and sales tax payment. During this time, the sales tax return should agree to the balance in Sales Tax Payable on the Balance Sheet (dated the same date as the return) – allowing you to confirm the accuracy of the Balance. Perform the following steps to research discrepancies between the return and the Balance Sheet, and to make any necessary adjustments.

• Step 1 – Work with the client to prepare the monthly/quarterly sales tax return (if necessary).
  Tip: Though the Sales Tax Liability and Sales Tax Revenue Summary reports in QuickBooks may contain historical data entry errors, you may be able to use a Sales Tax Liability report and a Sales Tax Revenue Summary report with the date range filtered for the current sales tax reporting month/quarter to help you prepare the return.
• Step 2 – Enter a Sales Tax Adjustment or a Journal Entry for adjustments to sales tax recorded on the return (e.g., timely filing discounts, sales tax paid on resale items, penalties, interest, etc.). Make sure the adjustments have the same date as the Sales Tax Return you prepared in Step 1 above. For our example, the Sales Tax Return shows a credit of $80.60 for sales tax paid on resale products.
  Note: When you enter a Sales Tax Adjustment in QuickBooks as shown below, QuickBooks creates a Journal Entry. There is no Transaction Type for Sales Tax Adjustments in QuickBooks.

• Step 3 – Create a Balance Sheet dated the same date as your sales tax return. Make sure to use the appropriate reporting basis (cash or accrual). If there is no discrepancy, proceed to Step 9 below.

  Even though we entered the credit in Step 2 above, the Balance Sheet below is $1,911.11 higher than the prepared Sales Tax Return. The balance due on the return is $4,506.51 and the balance on the Balance Sheet shown below is $6,417.62.

• Step 4 – If the discrepancy is immaterial, enter a Journal Entry or Sales Tax Adjustment to force the balance in Sales Tax Payable to agree to the Sales Tax Return. Offset the immaterial adjustment to the Profit & Loss (e.g. Sales or Miscellaneous Expense). Then, proceed to Step 9 below.
• Step 5 – If the discrepancy is material, you will need to research the reason for the discrepancy so you can offset your Sales Tax Adjustment to the appropriate account(s). Use QuickZoom (double-click) to see the details behind the Sales Tax Payable balance on the Balance Sheet. QuickBooks will create a Transaction Detail Report for the Sales Tax Payable account. Filter the report as follows:

  a. Determine the most recent reporting date with a correct balance in Sales Tax Payable. (e.g., the last day of the previous fiscal year). Set your date range to begin one day after this date and to end on the date of the Sales Tax Return you prepared in Step 1 above.

b. Filter the report by transaction type to include all transactions except: Invoices, Sales Receipts, Credit Memos and Sales Tax Payments.
Note: Sales Tax Payments are a specific transaction type – not to be confused with Checks or Bill Payments.
Note: Sales Tax Adjustments are not a unique type and as a result we cannot exclude them from the report.

c. Review each Journal Entry transaction on the report to make sure the transaction correctly posts to Sales Tax Payable. For example, Journal Entry transactions used to record timely filing discounts or credits for sales tax paid on resale items are correct (see Step 2 above). Then, reverse any journal entries that post to Sales Tax Payable incorrectly. Date your reversing adjustments on the same date as the Sales Tax Return you prepared in Step 1 above. If the balance in Sales Tax Payable is still incorrect after you enter these adjustments, proceed to letter d below.
Tip: If there are numerous transactions on the report, sort the report by transaction type to group all of the Journal Entries (i.e., Sales Tax Adjustments) together.
Note: On the report below there are three Journal Entries. The entry on 09/30/2007 sets the opening balance for the account. Rock Castle Construction began tracking Sales Tax in QuickBooks on 09/30/2007. The other two entries reflect adjustments recorded on the sales tax return and are therefore correct. No action is required based on the information shown on this report.

d. Review each Check or Bill Payment on the report to make sure the amount of the Check or Bill Payment agrees to the amount due on the Sales Tax Return for that period. If the amount of the check is incorrect, enter an adjusting entry dated as of your most recent sales tax return.
Note: If the QuickBooks user has been paying sales tax correctly, the report should not show any Checks or Bill Payments. Sales Tax Payments are a separate transaction type that we excluded from the report in letter b above.
Important: Journal Entries shown on the Sales Tax Payable transaction detail report may already adjust the account for the incorrect payment amount. Also, consider that overpayments may be applied to future returns as carry-forward credits. In either of these cases you should not adjust the balance in Sales Tax Payable. If the balance in Sales Tax Payable is now correct, proceed to Step 9 below. If the balance in Sales Tax Payable is still incorrect, proceed to Step 6 below.

• Step 6 – Create a Vendor QuickReport for vendor(s) to whom you pay sales tax (e.g., State Board of Equalization).

  a. Review the Vendor List to make sure there aren’t multiple vendors with similar names before you create the Vendor QuickReport. For more information see Analysis 1, Step 5.b above for more information.

  b. Filter the Vendor QuickReport as follows:

  1. By date using the same date range as you used in Step 5.a above.

  2. By transaction type for the same transactions you used in Step 5.b above. For this step, exclude General Journal Entries from the report as well.

3. Remove the account filter so the report will include transactions that post to accounts other than Sales Tax Payable.

4. QuickBooks will display the report shown below. Review the list of transactions to see if any single transaction – or grouping of transactions – ties to the overstatement or understatement in Sales Tax Payable. If you cannot tie the transactions in this way, QuickZoom on each transaction to research the postings involved. The most common error is one or more Checks used to pay sales tax that are coded to an account other than Sales Tax Payable (e.g., the check could be coded to Sales Tax Expense).
Tip: Sort the report by Transaction Type to group all of the Checks and Bill Payments together.

5. Double-click the transaction to view the General Ledger accounts to which the transaction posts. QuickBooks will display the transaction entry form as shown below.

6. Tip: If there are numerous Checks and/or Bill Payments you may want to add an Account column and a Split column to the Vendor QuickReport. By doing so you won't have to double-click the transaction to see the General Ledger postings. As with any QuickBooks detail report you can add Debit and Credit Columns as well. By adding these columns, the Vendor QuickReport will be similar to the General Ledger report.

• Step 7 – When you have located the erroneous transaction(s), you can either edit the transaction(s) or enter an adjusting entry. Notice that the Check above posts to Sales Tax Expense – a common data entry error. You could enter Sales Tax Payable in the Expense tab of the Check form, or you could enter an adjusting entry as show below. The adjusting entry corrects the balance in both Sales Tax Expense and Sales Tax Payable.

  Important: When adjusting Sales Tax Payable, make sure the use the correct Sales Tax Vendor. In our case the adjustment is associated with the State Board of Equalization vendor.

• Step 8 – Once the balance in Sales Tax Payable is correct, create a Sales Tax Liability report or open the Pay Sales Tax window to make sure the total amount for each vendor agrees to the Company's most recent sales tax return(s) (or to $0.00 if the Company has already paid sales tax for the most recent sales tax return(s))

• Step 9 – If you need to shift amounts on the Pay Sales Tax window or Sales Tax Liability report from one vendor to another, enter a journal entry as shown below. The entry below debits and credits the same account (Sales Tax Payable) so the entry has no net impact on the Balance Sheet.
  Note: Though the Sales Tax Payable column on the Sales Tax Liability Report will shift from one vendor (row) to another, the adjustment shown below has no impact on the Sales and Tax Collected columns of the Sales Tax Liability Report.
  Note: For our test case we will not enter the adjusting entry shown below.

• Step 10 – QuickBooks creates a separate Sales Tax Payment for each vendor shown on the Pay Sales Tax window (on selected lines only). Notice that the adjustment to Sales Tax Payable for the incorrectly coded check (Step 7 above) shows on a separate line, associated with the State Board of Equalization vendor record. QuickBooks uses this separate line for all transactions that affect Sales Tax Payable without affecting a Sales Tax Items. For example, if we had entered multiple adjustments or if there were Checks/Bill Payments used to pay sales tax, QuickBooks would have netted the adjustments together by vendor.

• Step 11 – Once the balance in Sales Tax Payable and the total for each Sales Tax Vendor are correct, create a Sales Tax Payment to clear erroneous detail from the Pay Sales Tax window.
  Note: If the client has already paid the sales tax for the most recent sales tax return, the Sales Tax Payment you create in this step will have an amount of $0.00. This applies even if the Company used a Check or Bill Payment coded to Sales Tax Payable to pay sales tax for previous reporting periods.
  Note: If the client owes sales tax (as with our test case), this step will generate a separate Sales Tax Payment for each vendor. You have two options in this case:

  a.You can assign a “To be printed” status for the Sales Tax Payment(s) if the Company is not ready to pay sales tax.

  b. You could edit the amounts in the Amt Paid column to force the Sales Tax Payment you create to zero. The Company can then use the Pay Sales Tax window to create a Sales Tax Payment at a later date. If you use this method, make sure to “pay” all adjusting lines in full – so they will not appear again on the Pay Sales Tax window and the Sales Tax Liability Report.
  
  

• Step 12 – Once all of the information is correct, click OK. Then, re-open the Pay Sales Tax window to confirm that QuickBooks cleared all adjustments and erroneous detail from the window – as shown below.
  Note: If the client owes sales tax as of the date in the “Show sales tax due through” field, this window will not be blank. Confirm that the total of the Amt. Due column agrees to the Company’s sales tax return

Last Updated: 11/22/2004

Search Other Product Tips