Bank Reconciliation

Continuing my occasional series on “the basics” this post addresses something I almost skipped, thinking it was perhaps too basic.

Then I worked with an employee who reconciles the district’s clearing account, cafeteria account and student body accounts. I realized that the concept of bank reconciliation might be foreign to younger employees.  Growing up in the era of internet banking they simply look at their account balance on line. They don’t keep a separate worksheet showing their deposits and withdrawals and they don’t compare this to the bank’s totals. Thus, it is no longer a given that clerical employees will come to the job with this concept in mind and often they have little formal training to compensate.

A bank reconciliation merely accounts for timing differences between entries in your ledger and the bank. Additionally it will identify errors (locally and at the bank) and will enforce the proper booking of returned checks, bank fees and interest.

Unless an account has hundreds or thousands of entries per month, a simple Excel spreadsheet can be maintained to do the job efficiently.

Bank Recon

The file illustrated above is a simple worksheet for a student body account. Note the expansion buttons. There is an expansion button that hides rows 2-6.  This is hiding bank and book beginning balance information that only has to be entered if the worksheet is set up after the account is in operation. There are also expansion buttons that hide columns B-C, H-J, and P. These contain calculation fields that the clerk should leave alone.

From this worksheet, pivot tables can be created to show checks issued and cleared, club balances, and bank and book balances. Now, I have been told that pivot tables are “advanced.” I disagree, I suggest that you teach your clerical staff this simple technique and you’ll be delighted how they take to it and find other uses for this powerful tool. Don’t hold them back!

Sure, there are plenty of software solutions such as Quickbooks, that will provide the above functionality (plus a lot more). However a small school or district might find the above is just fine for their purposes.

This worksheet now provides the raw data for a summary reconciliation that shows the book and bank in two columns.  Each column starts with this month’s beginning balance (which must equal last month’s ending balance). Entries recorded in the book column are summaries of deposits and checks issued, as well as already-recorded adjustments. On the bank side, deposits and cleared checks are listed, as well as entries such as returned checks, interest, and fees (to match the bank statement). When these two columns are totaled they are unlikely to match. Underneath these entries, list on the book side entries yet to be made (such as interest and fees). On the bank side list uncleared checks and deposits in transit. Now both columns should be equal. The summary reconciliation, along with back-up documentation including the bank statement, is then signed off monthly by a supervisor.

%d bloggers like this: