Home » Difficult Bank Reconciliations in QuickBooks

Difficult Bank Reconciliations in QuickBooks

UPDATED 08/02/2015, VIDEO ADDED
The most difficult issue I have found in dealing with bank reconciliations in QuickBooks is when the beginning balance is incorrect. Take a look at the following scenario.

Here is a reconciliation report from the 11/14/2015 bank statement in one of the sample files that come with QuickBooks. It shows the ending balance for the reconciled account to be $23,171.79.

Upon opening the reconciliation screen to do the reconciliation for 12/15/2015, the beginning balance shows as $23,246.29. If the beginning balance is incorrect, the reconcile process will never get to the correct ending balance.

How does this happen?

When finding the opening balance for an account in the reconciliation screen, QuickBooks does not have a record of the previous reconciliation’s closing balance. So, it cannot look there to find what that balance should be. Even if it did, it would be a poor strategy. The better solution is the one QuickBooks uses. It adds all the cleared transactions in the account being reconciled, and that is the beginning balance. This way, changes/errors to past transactions will not go unnoticed.

It also means those past changes/errors, when they occasionally crop up, must be dealt with. So, in the above scenario, how does one find the reason the beginning balance is incorrect and what should be done to correct it?

Notice in the screenshot of the reconciliation screen, a button labeled Locate Discrepancies. This button will give access to certain helps in locating discrepancies, including the Discrepancy Report. This report can also be accessed at Reports->Banking->Reconciliation Discrepancy.

In our case, the report shows that, since the last reconciliation, a check has been edited and the amount changed by 74.50. This is the cause of the change in our beginning balance.

For further information, it would be possible to view this transaction in the Audit Trail Report (Reports->Accountant & Taxes->Audit Trail).

This report reveals a wealth of information. We knew from the Reconciliation Discrepancy, check 151 was the culprit. Once we find that check on the Audit Trail, look at all the information available. Originally the check was written for 1080.73, then was changed to 1006.23. The changes to the transaction show in bold characters.

This report also tells what user made the edit, on what date and what time. This is a good reason to create user names and keep passwords from being shared. Since the sample file thinks today’s date is always December 15, 2015, all the changes show as of that date. Also note that the correction has already been made on this report, restoring the check to its previous, correct amount.

Correcting the check was not difficult. Note the two arrows in the screenshot highlighting the exact amounts that changed. Also note the circled areas showing exactly what accounts were used originally. If the account had been changed, QuickBooks would show that as well.

Armed with information from the Discrepancy report and the Audit Trail, corrections can be made and the correct beginning balance restored.

What if a transaction has been deleted?

This complicates the repair work needing to be done. Simply replacing the transaction does not reconcile it again, causing QuickBooks to use it in the calculation of the beginning balance. To QuickBooks, it is simply a new transaction.

In this scenario, the 12/15/2015 reconciliation in the same sample file has been completed. The ending balance was $34,592.98. That, then, is what the beginning balance should be for the January 15, 2016 reconciliation. Since I deleted a check that was cleared in the 12/15 reconciliation; that balance is incorrect.

In order to repair the last reconciliation, I will change the date to 12/15/2015, use the correct ending balance from 12/15/2015 in the Ending Balance field, and click Continue.

Now, when I select only the check that I created to replace the deleted one, the Difference noted in the lower right hand corner is zero. Once the Reconcile Now button is selected, QuickBooks will complete the process. It is now possible to begin the January 15, 2016 reconciliation with a correct beginning balance.

Check out this video: