Resolve accounts receivable and accounts payable balances on a cash basis balance sheet

The Cash Basis Balance Sheet (CBBS) shouldn't show Accounts Receivable (A/R) or Accounts Payable (A/P) balances because these accounts track open (unpaid) invoices and unpaid bills. Many companies use A/R and A/P accounts and report on the cash basis. QuickBooks wasn't designed to be used in this way and reporting anomalies result. The only way to assure that cash basis reports have no anomalies is to use only transactions that don't affect A/R or A/P.

For the discussions below, please be aware that every QuickBooks transaction has one source account and one or more target accounts. You can view the posted accounts in any transaction by running the Transaction Journal report. The first line of the Transaction Journal Report shows the source account. The next lines in the transaction show the target accounts.

A/R Debit Balances

• An open invoice with sales tax if the preference is set to "As of invoice date" (accrual basis).
• A cheque to a customer recorded to the A/R account, but not linked to a credit memo.
• An open Invoice or statement charge with inventory parts.
• A journal entry debiting A/R as the source account and crediting a balance sheet account as a target.
• A journal entry debiting A/R as a target account.
• Transferring a credit from one job for a customer to another job for the same customer before the journal entries are linked to the credit memo for one job and the invoice for the other job.

A/R Credit Balances

• An invoice with an item recording retainage to another current asset account.
• A payment from a customer not linked to an invoice.
• A payment linked to an invoice dated in the future if the report date is before the invoice date.
• A credit memo to a customer not linked to an invoice or a refund check.
• A deposit recorded to the A/R account but not linked to a payment or an invoice.
• A journal entry crediting A/R as the source account and a target is a balance sheet account.
• A journal entry crediting A/R as a target account (e.g., transferring a credit to another job).

A/P Debit Balances

• A Bill from a supplier crediting a retainage payable account to reduce the amount due.
• A check or bill payment check not linked to a bill.
• A bill payment  check linked to a bill with a future date if the report date is between the two dates.
• A journal entry debiting A/P as the source acct and a target is a balance sheet account.
• A journal entry debiting A/P as a target account.
• A credit card charge that relieves A/P that isn't linked to a bill.

A/P Credit Balances

• A bill for an inventory part.
• A Bill offset to a balance sheet account.
• A journal entry crediting A/P as the source account and a target is a balance sheet account.
• A journal entry crediting A/P as a target account.

Sample transactions

Example 1: Open invoices that include Inventory Parts are included in the A/R balance on a CBBS. The amount showing in the A/R account is the amount posting to the Inventory Asset account as a credit. (The Source account is A/R and the target account is Inventory Asset and both are balance sheet accounts.)

Example 2: Open bills that are entered to buy Fixed Assets such as office Equipment will appear on A/P account on a CBBS.(Source account is A/P and the account is Fixed Asset and both are balance sheet accounts.)

Example 3: Open bills entered with Inventory Parts debit the Inventory Asset account credits A/P; (The source account is A/P and the target account is Inventory Asset both are Balance Sheet Accounts.)

Example 4: A General Journal transaction that has an expense account (source) as a debit on the first line and the A/P account (target) on the second line as a credit will show on a CBBS. Note: Reverse the lines by entering A/P account (source) credit on the first line and the expense account (target) as a debit as the second line. The transaction won't show on a CBBS.)

Example 5: A General Journal entry that has an Income account (source) on the first line as a credit and the A/R account (target) as a debit on the second line will show on a CBBS. Reverse the lines by entering the A/R account (source) as a debit on the first line and the Income account (target) credit on the second line the transaction won't show on a CBBS.

Recommended solution

Step 1: Identify the root cause transactions

The following reports should help you find the root cause transactions:

Open Invoices report

1. Select Reports, then select Customers & Receivables.
2. Select Open Invoices.
3. Select the Date drop-down arrow and select the correct date.
4. Select Customize Report, Advanced and then select As of Report Date.
5. The payments that appear on the report (included in the CBBS) have been received but not applied to an invoice.

Unpaid Bills report

1. Select Reports, then select Suppliers & Payables.
2. Select Unpaid Bills Detail.
3. Select the Date drop-down arrow and select the correct date.
4. Select Customize Report, Advanced, and then select As of Report Date.
5. The bill payment checks that appear on the report (included in the CBBS) have been entered but not applied to a bill.

Customer Transaction Detail report

1. Select Reports, then select Company & Financial.
2. Select Balance Sheet Standard.
3. Select Customize Report.
4. On the Display tab, for Report Basis select Cash.
5. Delete the From date, keep the To date, and select OK.
6. To bring up the Transactions by Account report, double-click (QuickZoom) the A/R amount.
7. Select Customize Report.
8. On the Display tab, delete the From date and keep the To date.
9. Select Advanced, select the Open Balance / Aging Report Date option, and select OK.
10. Select Customer in the Total by field.
11. Select the Filters tab:
    1. In the Filter list, select Account.
    2. Select the Account drop-down arrow and select All Accounts Receivable.
    3. Select No for Include Split Detail.
    4. In the Filter list, select Paid Status, select Open, and then select OK.
    5. (Optional) Select Memorize to save this report to use again.
12. Each nonzero customer subtotal affects the A/R balance.

Supplier Transaction Detail report

1. Select Reports, then select Company & Financial.
2. Select Balance Sheet Standard.
3. Select Customize Report.
4. On the Display tab, for Report Basis, select Cash.
5. Delete the From date, keep the To date, and select OK.
6. To bring up the Transactions by Account report, double-click (QuickZoom) the A/P amount.
7. Select Customize Report.
8. On the Display tab, delete the From date and keep the To date.
9. Select Advanced, select the Open Balance / Aging Report Date option, and select OK.
10. In the Total by field, select Supplier.
11. Select the Filters tab:
    1. In the Filter list, select Account.
    2. Select the Account drop-down arrow and select All Accounts Payable.
    3. For Include Split Detail, select No.
    4. In the Filter list, select Paid Status, select Open, and then select OK.
    5. (Optional) Select Memorize to save this report to use again.
12. Each non-zero supplier subtotal affects the A/P balance.

Step 2: Manually fix the root cause transactions

IMPORTANT: Back up the QuickBooks company file before proceeding with these steps.

1. Change your Sales Tax preference to Due on receipt of payment, which is the cash basis option, before running the CBBS.
2. Link transactions in the Receive Payment window (A/R).
3. Link transactions in the Pay Bills window (A/P).
4. Be sure that your service, non-inventory part, and other charge items are linked to income and expense accounts:
   1. Select Lists, then select Item List.
   2. Look for any balance sheet accounts (assets, liability, or equity) listed in the Account column.
   3. Assure any items pointing to asset accounts are set up correctly. Some items (e.g., sales tax, retainers) should point to asset accounts.
5. Temporarily delete the transactions you identify as problems:
   1. Memorize the open transactions and then delete them.
   2. Print your cash basis reports.
   3. Reenter the transactions from the memorized transactions list.
6. Edit your journal entries (JEs):
   • Separate the A/P and A/R portions of journal entries that combine several transactions involving B/S accounts, into separate JEs.
   • Move the A/R portion to the first line (source) of the JE. Those JEs that are offset to an income or expense account will drop off the report. The JEs that are offset to a balance sheet account will remain on the report.

   Note: Press Ctrl+Insert to add one line to the GJE and press Ctrl+Delete to delete one line from the GJE.

Step 3: (Recommended) Make preventive changes to your procedures

NOTE: Stop using Inventory items. If you have a few inventory items:

1. Write off your inventory.
2. Inactivate your inventory items.
3. Re-create them as non-inventory parts.

Use only cash transactions (such as checks, sales receipts, credit card charges).

If you must use invoices and bills, assure that none of the targets point to balance sheet accounts.

Track A/R and A/P in separate data files:

• Enter only cash transactions in the main (or cash) data file.
• Enter only accrual transactions into the secondary (or accrual) data file.
• Delete the open accrual transactions and reenter them as cash transactions when the cash has been paid out or paid in.