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 note.
• An open Invoice or statement charge with stock 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 note 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 note 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 stock 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 Stock 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 Stock Asset account as a credit. (The Source account is A/R and the target account is Stock 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 Stock Parts debit the Stock Asset account credits A/P; (The source account is A/P and the target account is Stock 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 Customise 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 Customise 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 Customise 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 Customise Report.
8. On the Display tab, delete the From date and keep the To date.
9. Select Advanced, select the Open Balance / Ageing 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 Memorise 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 Customise 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 Customise Report.
8. On the Display tab, delete the From date and keep the To date.
9. Select Advanced, select the Open Balance / Ageing 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 Memorise 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-stock 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. Memorise the open transactions and then delete them.
   2. Print your cash basis reports.
   3. Reenter the transactions from the memorised 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 Stock items. If you have a few stock items:

1. Write off your stock.
2. Inactivate your stock items.
3. Re-create them as non-stock 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.