A QuickBooks ProAdvisor must be a Data Detective at times.
While 'data corruption' can cause the balance sheet to be out-of-balance, a balance sheet 'in-balance' on the accrual basis, but 'out-of-balance' on the cash basis is most usually the result of improper transactions, or transactions which QuickBooks can not properly 'convert' to cash basis from accrual. This article examines these transactions as opposed to actual data corruption causes.
The first step in the process of identification and correction is to track down the transaction(s) that are causing the problems. This requires a bit of ‘data detective work’; here is a systematic process to find transactions that may result in the out-of-balance situation:
First we will make the assumption that possibly a single transaction is the cause. In this case you should search QuickBooks (using search or find) for a transaction with the exact amount of the ‘out-of-balance’. If you identify such a transaction, delete it and re-enter it.
If you didn’t find a single transaction for the amount in question, then it will be necessary to start tracking down transactions of the type shown below. This is best accomplished by working in an orderly fashion starting with year, then smaller date periods. Since you are dealing with more than a single transaction, you may realize out-of-balance amounts changing as each time period is examined.
- Research each year to identify the corrupted transactions – Create a balance sheet and select ‘All’ as the date range; at the top of the report select ‘Year’. Examine each year to determine in which year(s) the balance sheet is ‘out of balance’ as opposed to those in which it is ‘in-balance’. You will want to begin your ‘monthly’ detective work by examining the oldest year in which the balance sheet is out-of-balance. For example, if all years were in balance through 12/31/2012, but the balance sheet is not out of balance then obviously the problem lies in ‘this year’.
- Once we have determined the ‘year’ in which the error occurs, we begin the same essential process but this time, we are looking at monthly periods. In our example we found that the only year that was out-of-balance was the current year. Create a balance sheet with the period being ‘this year to date’; at the top of the report select the column option for ‘Month’. If every month of this year’s balance sheet is ‘in-balance’ from January through June, but the month of July is out-of-balance then obviously the transactions producing the error are to be found during the month of July.
- We now undertake the detective work for the affected month to identify the actual date(s) of the transactions. In the case of our example, we would want to produce a balance sheet for the month of July, with dates of July 1, 2013 to July 31, 2013. At the top of the report select the option to display columns by ‘Day’; each column represents every day of the month. Examining this balance sheet we see that each day is in balance until July 14, 2013, and every day since is out-of-balance. This means that the earliest a causative transaction can be found is on July 14, 2013. So at this point you are ready to search for specific transactions that can produce the out-of-balance situation on a ‘cash basis’ even though the ‘accrual basis’ balance sheet is in-balance.
Once we have identified when the out-of-balance transaction(s) have occurred, we need to determine the specific transactions that will put a cash basis balance sheet out of balance. We can then attempt to resolve them. Remember to always backup your company-file before attempting any of these resolutions.
An inventory return and discount on an invoice. Look for any transaction(s) like this:
- The sale of one or more inventory items.
- The return of one or more inventory items.
- A discount item.
1. Create one Invoice for the sale and the discount.
2. Create a Credit Memo for the inventory return.
3. Link the Credit Memo to the Invoice when you Receive Payments.
A discount entered at the customer level and applied at the job level in a scenario similar to:
- You have a customer with multiple jobs. You complete all of the jobs and invoice the customer, including all jobs on the invoice.
- You receive a payment for the invoice, but your customer includes a discount for Job A in the payment.
- You record the payment to the invoice and enter the discount on the Discount/Credit Tab.
- Your cash basis balance sheet will be out of balance.
You need to re-enter the payment and split it among the jobs, entering the discount for Job A.
A journal entry has been posted that is linked to a credit memo:
- You have an open Credit Memo, so you enter an offsetting General Journal entry and link it to the Credit Memo.
- Your cash basis balance sheet will be out of balance.
Resolve this issue by:
1. Delete the journal entry and substitute an Invoice.
2. Link the Invoice to the Credit Memo.
A discount is offset to a Balance Sheet account:
- When you are entering customer payments, you can apply discounts with the Discounts & Credits button. In the discount window, you must enter an offset account in which to record the discount. If you enter a balance sheet account, your cash basis balance sheet will be out of balance.
The fix is to change the discount account to an income or an expense account.
You have entered unconventional inventory transactions:
- Any transaction that drives your Quantity on Hand negative,
- Any transaction that changes the Quantity on Hand for previously entered assemblies and cause the assemblies to be marked pending.
- Offsetting transactions (typically Inventory Adjustments) with (+n) and (-n) units of the same item at the same price.
- Transactions that use (data) damaged items, or damaged customers, or damaged vendors.
Correct the improper inventory transaction.