Why your client's sales amounts in QuickBooks are probably wrong

By Joe Woodard, AccountingWEB QuickBooks columnist

Premise and Introduction

QuickBooks reports are largely based on two different lists – Accounts and Items. QuickBooks uses the Chart of Accounts and the Trial Balance balances to generate financial reports like the Balance Sheet and Profit & Loss. QuickBooks also uses the Chart of Accounts for the General Ledger and other standard financial reports like those found in any accounting software product.

In QuickBooks, Items are used to create sales reports like the Sales by Item Summary report and to calculate taxable and non-taxable sales on sales tax reports. For accounting professionals, the will be on the Chart of Accounts and the Account balances QuickBooks uses to create standard financial reports. However, most small businesses lean heavily on the Item-based reports to manage their businesses and also to file sales tax returns.

Tying Sales Reports to the Income Statement

In QuickBooks, income is tracked in two different areas of the data file: Items and Accounts. As you might expect, the account activity affects the Profit & Loss statement. QuickBooks refers to Item-based income as “Sales.” QuickBooks uses Items to populate reports like Sales by Item Summary and Sales by Customer.
Ideally, all revenue should affect both an Item and an Income account. When revenue does not affect both an account and an item reference, it is important for you to quickly locate the offending transactions and make any necessary adjustments. Perform the following steps to analyze your client’s Income balances.
Step 1: Create a Sales by Item Summary report. Then, filter the report to include only “All Ordinary Income Accounts.”
Step 2: Create a Profit & Loss for the same reporting period as the Sales by Item Summary report.
Step 3: Compare Total Income on the Profit & Loss to Total Sales on the Sales by Item Summary report. The only possible cause for a discrepancy is Debiting or Crediting Income Accounts on transactions other than Sales Forms (Invoices, Credit Memos, and Sales Receipts).
Step 4: To locate non-sales transactions that post to Income, QuickZoom on Total Income on the Profit & Loss report. Then, filter by Transaction Type for all types except Invoices, Sales Receipts and Credit Memos. The most common non-sales transactions that affect Income are:
a.    Deposits (to record payments from customers)
b.    Journal Entries (adjustments usually entered by the Company’s CPA
c.    The Receive Payments window (to record discounts given – contra income).
Note: When you locate the reasons for the discrepancy, you may or may not make changes. The intent here is to reconcile the two reports and make necessary changes to income or sales.If the non-sales form accurately posts to Income, you need only to note the amount so you can reconcile Sales (Items) to Income (Accounts) for the client. In the examples above, the Customer Payment that posts to Discounts Given and the Journal Entry to adjust income are probably accurate.

However, with Deposit transactions, the client may have entered Income on the Invoice and then applied the customer payment to the Invoice. Then, instead of using the customer payment on the Deposit (through Undeposited Funds), the client entered a Deposit that posts directly to Income. This causes an overstatement in Income for both the cash and accrual basis offset to an overstatement in Undeposited Funds. See the section on troubleshooting Undeposited Funds above for more information. If there is no overstatement in Undeposited Funds, the overstatement will probably be in cash. Either way, enter an adjusting entry to adjust Income and Undeposited Funds/Cash.

 
Tip: If filtering the Profit & Loss as described above doesn’t fully reconcile the two reports, filter the Sales by Item Summary report for all accounts except Income accounts. (This is not a generic filter. You will have to select “Multiple Accounts” and then select every account in the Chart of Accounts with a non-income type.)

Usually you will find several sales Items that do not affect income. Some of these may be accurate. For example, the client may use an Item on Invoices or Sales Receipts to receive prepayments from customers. If the Company uses the Accrual Basis, the offset should go to Unearned Income – an Other Current Liability account. For another example, contractors need an Item to show Retainage on Invoices – retainage owed to them by their customer. The Retainage Item should post to Retainage Receivable – an Other Current Asset account.

 
Warning: If you edit the Account field on Sales Items, your change could affect the previous reporting year.

Adjusting Income Accounts

When entering adjustments to the General Ledger, often you will need to also adjust one or more Items on the Item list as well. If you do not, the Account-based reports will not tie to the Item-based reports.
Therefore, the first step is to determine if you can adjust the Trial Balance only using a Journal Entry or if your adjustment should also include an Item from the Item List. There is a simple litmus test for this determination. If the Account you are adjusting is included in the Item setup window for one or more Items, you must adjust both and Item and an Account. Accounts with an Income and/or Cost of Goods Sold Account type are usually linked to one or more Items. Balance Sheet Accounts, Expense Accounts, Other Expense Accounts and Other Income Accounts are usually not associated with an Item. However, it is always prudent to check before you enter any Journal Entries.
Problem: The Journal Entry window does not have an Item column. You cannot use a Journal Entry to adjust both the Account and the associated Item(s).
Solution: Since the Journal Entry window does not include an Item column, use another transaction in QuickBooks that does include an Item column. Since every transaction in QuickBooks posts debits and credits to the General Ledger you can repurpose any transaction in QuickBooks to act as a Journal Entry.
Perform the following steps to determine if an Account is linked to one or more Items and to adjust the Account and Item as necessary.
Step 1: Create an Item Listing report.
Step 2: Filter the report to include both active and inactive items.
Step 3: Filter the report by Account for the Account(s) you need to adjust. If the report shows any Items, you know the Account you are adjusting is linked to the Item list.
Step 4: If the Account is linked to one or more items, you still need to consider the following to determine if you need to adjust the account and associated item:
a)    You have not used any of the Items linked to the Account you want to adjust in either the current fiscal year or in the year that you are adjusting, and
b)    You do not intend to the use the Item(s) in the future, and
c)    You do not intend to link the account to a new item in the future
Since you cannot use a Journal Entry to adjust the Account, you need to post the same debit and credit to the General Ledger that you would have entered on the Journal Entry, but you have to use another form that allows you to adjust one or more Items as well. You will use different forms in different circumstances. As described below.
Note: Adjusting both the Item and the Account requires the repurposing of transactions to serve as Journal Entries and there are certainly more steps involved. However, after you setup the additional Accounts and Items as shown below, the process to adjust both the Item and the Account is not as time consuming.
When you adjust Income, you almost always need to adjust one or more Items. Items are used most often to record detailed sales information on Invoices, Sales Receipts, and Credit Memos. As a result, income Accounts are almost always included in at least one Item on the Item list.
For this example, the CPA intends to make the following adjustment to the General Ledger:

Account
Debit
Credit
Checking
2000.00
 
Office Expense
1000.00
 
Other Income
3000.00
 
Materials Income
 
6000.00
 
Perform the following steps to adjust the “Materials” Income Account and the “Counter” Non-Inventory Part Item:
Step 1: Create an Other Current Asset Account called Adjustment Clearing.
Note: You can make the clearing Account any type of Account you wish. It is usually best to use a Balance Sheet Account type so you can see all of the Account details using the Account register. The Account will never carry a balance so in the end the Account type is irrelevant.
Step 2: Create an Other Charge Item called Adjustment Clearing and link this Item to the Adjustment Clearing Account you created in Step 1 above.
Step 3: (Optional) Create a Bank Type Account called Journal Entries.
Note: This Journal Entries Bank Account is required when you adjust Cost of Goods Sold and/or Expense Accounts that are linked to Items. See the section entitled Adjusting Cost of Goods Sold and Expense Accounts on page Error! Bookmark not defined. for more information.
Tip: It is best to include this Journal Entries Account on every adjustment you enter, regardless of the form you use – including the Make General Journal Entry window. See page Error! Bookmark not defined. for more information.
Step 4: (Optional) Create an Other Charge Item called Journal Entries that you link to the Journal Entries Bank Account.
Step 5: Open the Enter Sales Receipt window and enter the Items and Accounts as shown in the figure below. Make sure the total amount of the Sales Receipt is “0.00.”

You can optionally create a Customer called “Adjustment.”

 
Enter the number of the Journal Entry here. If the client uses Sales Receipts make sure to reset the numerical sequence after you save this transaction.
Enter the applicable class as you would do on the Enter Journal Entry Window.

 

Warning: Do not use Inventory Items in the steps above. If you need to adjust an Income Account that is linked to one or more Inventory Items, create an Income Item called “Income Adjustment” and link that Item to the Income Account you need to adjust. If you use an Inventory Item on the Sales Receipt above, QuickBooks will reduce the quantity on hand (even if you don’t enter a quantity in the Quantity column) and QuickBooks will also adjust Inventory Asset and Cost of Goods Sold.
Tip: You can create a new Sales Receipt template and title the printed form “Journal Entry.”
Note: If you need to use more than one class on the Sales Receipt (i.e. Journal Entry), edit the template to include a Class column.
Note: If you need to adjust both income and sales tax payable, you can make the “Counter” Item taxable. Then, increase the amount on the Adjustment Clearing line to zero the Sales Receipt.
Step 6: Save the Sales Receipt. Then, with the Sales Receipt displayed press CTRL+Y to create a Transaction Journal to confirm the correct GL postings and to retain a copy of the adjustment for your files.
Note: The Sales Receipt posts the “Journal Entry” shown below. Notice the line with the credit to Income shows both the “Materials Income” Account and the “Counter” Non-Inventory Part Item. In terms of the impact on QuickBooks reports, the Sales Receipt is a “Journal Entry” with an Item column.

This alternative “Journal Entry” has an Item column and impacts both the Account-based and Item-based reports.

 

 

Step 7: Create a Journal Entry as shown below to clear the balance in the Adjustment Clearing Account and to adjust other GL Accounts that are not linked to Items.

About the author
Joe Woodard is an Advanced Certified QuickBooks ProAdvisor and Intuit Solution Provider who has taught over 20,000 QuickBooks consultants across the country. Joe works with Intuit, state CPA societies, and Atlanta-area CPA firms to present advanced QuickBooks instruction to accounting professionals and software consultants. Joe has earned a unique relationship with Intuit as a trainer, consultant and author. Joe has built two successful accounting software consulting practices: the first in New Orleans, Louisiana and the second in Atlanta, Georgia – Creative Financial Software (CFS). In addition to consulting with small businesses, CFS provides advisory services to CPA firms and other QuickBooks ProAdvisors/Intuit Solution Providers across the country -- helping them to better service their clients who use Intuit products. Joe recently hosted the first annual Scaling New Heights QuickBooks conference in Atlanta.

See all articles by Joe Woodard.

Related articles:


Already a member? log in here.

Editor's Choice