Bank Recs Done Right

Share this content

The first thing I learned back in accounting 101 was the formula for a bank reconciliation.  Bank balance plus deposits-in-transit less outstanding checks equals the book balance.  Many accounting software packages such as QuickBooks have a bank reconciliation module within the software itself so there's no use for an Excel spreadsheet to do a bank reconciliation.  There are, however situations where a spreadsheet works better.  In practice, the hardest item to track in a bank reconciliation for me has been the outstanding checks.  Here's a couple of methods to track outstanding checks that I have found useful.

Situation one is when checks are cleared in the general ledger software, but there is no bank reconciliation module, or a spreadsheet for some reason is still more efficient to use.  Create a pivot table that links to the table in the general ledger software where the checks reside.  Then filter the pivot for the correct bank account "Bank of America" and the correct status "O" for open.

Next, link the  total of the pivot table into the appropriate line on the bank reconciliation.

Situation two is when the checking account detail does not reside in the accounting software.  A journal entry is used to record the bank activity.  Such is the case with my payroll account.  This account has a high number of checks and it is not efficient to cancel them individually in the accounting system.  Instead keep a running total of the outstanding checks, add the total current period activity, use a VLOOKUP to link to the bank activity for the same period, finally delete the matches.

Delete the matches in the Amount and the VLOOKUP columns, and the remainder should be the new outstanding checklist that will fit into the cash reconciliation schedule.

 Mastering a new function is only half of the secret.  The real trick when learning a formula like GETPIVOTDATA or VLOOKUP is not just the syntax, rather it is the application.  How can these and other functions be applied to individual needs and situations.


Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.