Understanding QuickBooks Cash Basis Reports - Excerpt
The following is an excerpt from the QuickBooks 2011 Consultant's Reference Guide.
One of the best features of QuickBooks is that it does not lock you in to the Cash or Accrual Basis. This means you can use accrual basis reports throughout the year for management information, and use cash basis reports for preparing your taxes. This doesn’t mean QuickBooks keeps two sets of books; it just means that, in order to present a cash basis financial statement, it does its best to convert from Accrual to Cash Basis. However, you may find that the cash basis balance sheet has any number of troubling numbers on it, so it’s important to understand exactly what QuickBooks does and doesn’t do when it converts from Accrual to cash.
In general, QuickBooks removes unreceived income and unpaid expenses from your reports. It also adds income and expenses from last year that were received or paid during the current year. However, as discussed below, there are several nuances that usually cause the cash basis reports to be inaccurate. This section starts with the basics of how to create QuickBooks cash basis reports, and then discuss several areas where you may have trouble. If you want to skip to the problem areas, here is a list of them:
- How Cash Basis Reports are calculated.
- Limitations of QuickBooks Cash Basis Reports.
- Troubleshooting Out-of-Balance Cash Basis Balance Sheets.
- Proofing A/R and A/P on Cash Basis Reports.
- Tricks for Completing the Cash Basis Conversion for Financial Statement Presentation.
- How Partially Paid Invoices and Bills affect Cash Basis Reports.
Reporting Preferences for Cash Basis
You can use the preferences to change the default basis for QuickBooks reports. To change the preferences, follow these steps:
- Select the Edit menu and then select Preferences.
- Scroll down and click on Reports & Graphs. Then click the Company Preferences tab.
Figure 1 Set the default for summary reports to Cash or Accrual.
How Cash Basis Reports are Calculated
On the cash basis P&L report, QuickBooks omits from income the total of all open invoices using Items associated with income (or expense) accounts and it adds to income the total receipts against invoices dated in prior years.
Similarly, on a Cash Basis Balance Sheet, QuickBooks reduces Accounts Receivable by the total of all Open Invoices that use Items associated with income (or expense) accounts.
The same thing happens with unpaid bills except that expenses and Accounts Payable are involved. That is, all unpaid bills will be removed from the cash basis Balance Sheet and P&L as long as the coding on the bill is to an expense (or income) account.
Normally, you wouldn’t expect to find a balance in A/R or A/P on a cash basis balance sheet. However, if you do find a balance in A/R or A/P on a Cash Basis Balance Sheet, it’s probably due to one of the following situations.
If the cash basis Balance sheet shows balances in A/R or A/P, determining the cause of the balance as described below may not solve the problem. You may need to manually adjust the balance in A/R or A/P. See Completing the Cash Basis Conversion on page 7 for more information.
- Check the open invoices and unapplied credit memos. Do they include an item that points to a balance sheet account? When an invoice or credit memo includes an Item that is associated with a balance sheet account, that transaction will affect A/R on both the accrual and cash basis balance sheet (i.e. A/R will have a balance even on the cash basis). To fix this, see the section on completing the cash basis conversion on page 7.
- Check the openinvoices and unapplied credit memos. Do they include an Inventory Part? If so, the “average cost” of the inventory item is left as a debit in A/R, with an offsetting credit to Inventory Asset.
- If A/R has a negative balance, it is probably due to unapplied payments. An unapplied payment is an open transaction that involves both A/R and another balance sheet account (usually Undeposited funds), so this transaction will not be removed from the cash basis A/R balance. Create an Open Invoices report and look for negative numbers. If you find both positive and negative numbers on this report, use the Receive Payments screen to apply the payments to your open invoices. If there are no Invoices to which you can appropriately apply the payments, see the section on completing the cash basis conversion on page 7.
- Check the unpaid bills and bill credits. If they include an item that is associated with a balance sheet account, they won’t be removed on the cash basis reports. For example, if the client enters a bill for a loan at the bank or a credit card that is tracked as a liability account in QuickBooks, the bill will credit A/P and debit the liability account. Since the bill is connected to a balance sheet account, the transaction affects both A/P and the offsetting balance sheet account (loan payable or credit card payable) on the cash basis Balance Sheet.
- If A/P has a negative balance, it is probably due to a Check coded to Accounts Payable (e.g., a prepayment to a vendor coded to Accounts Payable) or to an overpayment made to a vendor using the Pay Bills screen. Create an Unpaid Bills report and look for negative numbers. If you find negative numbers on this report, use the Pay Bills screen to apply the prepayments (Checks) or overpayments (Bill Payments) to your unpaid bills. If there are no bills to which you can appropriately apply these debits to A/P, see the section on completing the cash basis conversion on page 7.
- Check for journal entries that hit A/R or A/P accounts. If Accounts Receivable or Accounts Payable is on the top line of the Journal Entry and the offset is to one or more income or expense accounts, QuickBooks removes (i.e. ignores) the journal entry on the cash basis Balance Sheet. To quickly solve this problem, enter the “Journal Entries” bank account on the top line of the journal entry as described on page Error! Bookmark not defined..
Limitations of QuickBooks Cash Basis Reports
There are several things that QuickBooks does not do when calculating the cash basis Balance Sheet.
- Unpaid Payroll Taxes – There is no removal of unpaid payroll taxes from the cash basis Balance Sheet, or from the cash basis P&L.
- Prepaids – QuickBooks does not consider any balance sheet accounts for prepaid expenses, customer prepayments, retainers, etc. when calculating the cash basis Balance Sheet.
- Credit Card Liabilities – If bills are entered for the Credit Card liabilities, the corresponding Accounts Payable amount will remain on the cash basis Balance Sheet until the bill is paid.
- Sales Tax Payable – QuickBooks uses the settings in the Sales Tax Preferences screen (Edit>Preferences>Sales Tax, Company Preferences) to determine the amount of Sales Tax payable on the Sales Tax Liability reports. However, on the cash basis Balance Sheet, QuickBooks reduces the balance in sales tax payable for unreceived sales tax (i.e. Sales Tax on open invoices).
Figure 2 Sales Tax Preferences
Troubleshooting Out-of-Balance Balance Sheets
If the cash basis Balance Sheet is out of balance (i.e., if total Assets does not equal total Liabilities and Equity, the problem could be caused by using customer discounts instead of credit memos. There is a bug in QuickBooks (all editions, all years) that causes the cash basis Balance Sheet to be out of balance if discount transactions are coded to balance sheet accounts.
For example, if an invoice was paid, or partially paid using a discount transaction and the other side (the Debit) was coded to a balance sheet account (e.g. Customer Prepayments or Retainers), it will cause the cash basis Balance Sheet to be out of balance.
Unfortunately, QuickBooks doesn’t provide an easy way to search for discount transactions, so you’ll have to use the Find command, filtered for all Payment transactions. The resulting find report will show the transactions “Type” column, and you can look for Discount transactions in the report. If you see any coded to balance sheet accounts, those are the problem transactions.
Figure 3 Find Report showing customer discount transactions
To fix the problem, delete the discount transaction(s) and create customer Credit Memo(s) instead.
Proofing A/R and A/P on the Cash Basis Balance Sheet
To proof the balances of A/R and A/P on the Cash Basis Balance Sheet, use QuickZoom to display the transactions behind the numbers on the Balance Sheet.
- Create a Cash Basis Balance Sheet. Select the Reports menu, choose Company & Financial, and then choose Balance Sheet Standard.
If your preferences are set to default to Accrual Basis reports, click Modify Report on the Balance Sheet report and select Cash Basis. Then click OK.
Figure 4 A Cash-Basis Balance Sheet with A/R
- The Cash Basis Balance Sheet above has a balance in Accounts Receivable. To see why, double-click on the A/R balance to see the transactions behind it.
- Set the date range on the Transactions by Account Report to clear the From date and leave the To date set to the end of the period you are evaluating.
Figure 5 Transactions behind the A/R account from the Cash-Basis Balance Sheet
- The Transactions by Account report shows all transactions dated through the To date that use Accounts Receivable. However, we only want to see the open transactions, and we only want to see those transactions that were open as of the report date.
- Click Modify Report and then click the advanced button. Then select “Report Date” in the Open Balance/Aging section and click OK. This setting tells QuickBooks to only consider invoices that were open as of the date on the report, as opposed to just looking for whether they were ever paid (possibly after the report date).
Figure 6 Setting the Open Balance/Aging option to "Report Date"
- Click the Filters tab.
- Filter the Transactions by Account report to only include transactions with a Paid Status of Open.
Figure 7 Filter the transactions report to include only “open transactions.”
Now, the Transactions by Account Report shows that a single transaction is causing the Cash Basis Balance Sheet to show a balance in A/R. In this case, it’s an open invoice with an Inventory sale, so the average cost of that inventory remains in A/R. The offsetting amount is a credit in the Inventory Asset account representing the reduction in inventory for the items on the invoice.
As you can see, all open transactions in the A/R account that use other balance sheet accounts, remain on the cash basis Balance Sheet.
Figure 8 The filtered transaction report. Includes a single transaction with a paid status of “open.”
Completing the Cash Basis Conversion
Although QuickBooks does a great job of removing invoices and bills when you create a cash basis report, it does not reverse any accruals you make for prepaid expenses or unearned income. As shown in Figure 4 it also does not reverse any A/R or A/P amounts that offset to balance sheet accounts other than Sales Tax Payable. In order to create a true cash basis Balance Sheet, you may need to enter journal entries to reverse accruals for prepaid income and expenses. There are two options:
Option 1: When There is No Need to Preserve Accrual Basis Year-End Reports
Using the totals from the filtered “Transactions by Account” report as shown in Figure 8, create a journal entry to adjust the balance in A/R. In the example above, the balance of $600 in Accounts Receivable came from an Invoice with a $600 reduction in Inventory for the sale of an Inventory Part Item. To complete the cash basis conversion, create a Journal Entry like the one shown in Figure 9.
Figure 9 Journal Entry to complete the cash basis conversion
- Do the same for A/P as necessary, using a separate Journal Entry.
You must not use Accounts Receivable or Accounts Payable on the top line of these journal entries when the offset to AR or AP involves one or more income or expense accounts. If you do, the transaction will be considered “open” and therefore it will not change the cash basis reports. Instead, use the workaround of putting the Journal Entries bank account on the top line of the journal entry (see Using a Bank Account to Track Journal Entries on page Error! Bookmark not defined.).
Also, you must enter a customer name in the name field on the A/R line of the journal entry, and you must use a vendor name on the A/P line. To keep things simple, create separate names for these adjustments. For example, use “Accounting Adjustments – C” for A/R adjustments and “Accounting Adjustments – V” for A/P adjustments.
- If necessary, create additional journal entries to debit accrued income or credit accrued expenses that are not associated with A/R or A/P.
- Enter reversing entries as of the first day of the following reporting period. Depending on how many journal entries you created, it might be fastest to memorize each entry from steps 1-3, and reverse the debits and credits.
Figure 10 Entry to reverse the cash basis conversion
QuickBooks 2002 and above (Premier and Enterprise) include a “Reverse” button on the journal entry screen. The reverse button creates a separate journal entry transaction with the debits and credits inverted. The default date for the reversing entry is the first day of the month after the date of the original journal entry. For example, if you enter a journal entry dated July 8, 2002 and click the “Reverse” button, QuickBooks creates a reversing entry dated August 1, 2002. You have the option of viewing and making changes to the reversing entry before saving.
- Lock the file. If it won’t cause too many restrictions, lock the file as of the date of your reversing entries (i.e. the first day of the next reporting period). In any case, lock the file as of the last day of the prior reporting period to preserve your cash basis conversion. For more information on locking the data file, see page Error! Bookmark not defined..
Option 2: Preserves Accrual Basis Year-End Reports
Some accountants prefer to complete the cash basis conversion outside of QuickBooks (e.g. using a spreadsheet) to preserve Accrual Basis Year-End Reports. Be careful with this approach, as it does not create auditable “entries” in the books. Document everything you change and keep your notes. If you want a complete, auditable cash basis file in QuickBooks but want to leave the accrual basis reports intact, use the following method.
- Create a backup of the data file.
- Restore the backup file you just created and give the restored file a slightly different name (e.g. Academy Glass – 200X Cash). Make sure to store this file in a separate folder on your client’s hard drive so that your client will not accidentally use this file to process transactions. Make sure the file name refers to the financial reporting year. The cash basis file will be for reporting purposes only.
- Using the total from the filtered “Transactions by Account” report as shown in Figure 8, create a journal entry to adjust the balance in the A/R account. Do the same for A/P as necessary, using a separate journal entry.
You must not use Accounts Receivable or Accounts Payable on the top line of these journal entries if the offset to AR or AP involves on ore more incomes or expense accounts See the note on page 8 for more information.
- If necessary, create additional journal entries to debit accrued income or credit accrued expenses that are not associated with A/R or A/P.
- Lock the file as of the last day of the prior reporting period.
Cash Basis Reporting for Partially Paid Invoices and Bills
If your client’s data file includes partially paid invoices or bills, QuickBooks applies part of the income to cash basis reports by allocating the payment evenly across all split detail. For example, the Invoice in Figure 11 increases income in two different income accounts.
Figure 11 Invoice for Bob Mason that increases two different income accounts
The Part Items (Window and 104-Slider) credit an income account called “Product Sales” in the amount of $560 and the Service Items (Design and Labor) credit an income account called “Services” in the amount of $100. See Figure 12.
Figure 12 Profit & Loss showing only activity from Invoice 2002-108
If Bob Mason pays half the balance of the Invoice, the Cash basis Profit & Loss will show an increase to Product Sales for $280 ($560/2) and an increase to Services for $50 ($100/2). See Figure 13.
Figure 13 Cash basis Profit & Loss showing only activity from Invoice 2002-108
When you set up a new QuickBooks data file for your client, you need to enter each open Invoice into the file separately. Many of these open Invoices may be partially paid. To properly setup the data file you will need to enter each partially paid Invoice using its original detail and totals and then enter a payment from the customer for the partial payment. Code the payment to Undeposited Funds and then enter a zero-dollar deposit into the Journal Entries account. Select the transaction in the Payments to Deposit screen (which clears undeposited funds), and enter an offsetting negative amount coded to Opening Balance Equity. If you enter just the unpaid balance of the Invoice, QuickBooks will not be able to accurately calculate the Cash Basis Profit & Loss or Cash or Cash basis Sales reports. The same applies to partially paid bills. When you create a Bill Payment for the partial payment of the bill, use the Journal Entries bank account to pay the bill. Then enter a Journal Entry to debit Journal Entries (to clear the balance) and credit Opening Balance Equity.