It seems that balancing a checkbook is a personal task most folks dread doing (although here’s 7 reasons to do so), which means it’s likely not much different for businesses. I, on the other hand, would say that bank reconciliations are my favorite thing! To me there’s nothing better than a big ol’ detail-ridden, large-volume-of-transaction laden recon. I’m sure part of my joy comes from the process that I’ve nailed down in my numerous years of corporate accounting and today I’d like to share my secret weapon: a simple excel template.
This template will look very different from what you may be used to in QuickBooks, and will probably look completely foreign if you’re new to financial reconciliations in general. I will break it out piece by piece so that you, too, can find joy – or at least less pain – in reconciling your own bank.
Template for Reference:
- STEP 1: First, we start by entering our ending bank balance from the statement into item (A), which in this case is $1,100 on 3/31/17. Then we enter the ending general ledger balance as of the same date into item (B), which is $800.
- STEP 2: With this template, notice that at the very bottom at item (H) it will indicate how much of a variance between the bank and general ledger we have left to identify. After completing step 1 above, the variance will show $300. ($1,100 – $800).
- STEP 3: The next step is to start matching transactions between the bank statement and the general ledger. After you’ve matched all that you can, you may be left with any of the three following types of discrepancies:
Uncleared checks: these are checks you have recorded on the general ledger that have not cleared the bank. Enter these under item (C). These will be negative amounts since the bank balance will be reduced once they clear.
Deposits in transit: these are deposits made and recorded in the general ledger but have not posted to the bank yet. Enter these under item (D). These will be positive amounts since the bank balance will increase once they post.
Items to be posted: these are items on your bank statement that did not get posted in the general ledger by the cut-off date. Enter these under item (E). These can be positive or negative amounts, depending on what the item is (it should match what it shows on the bank statement). See examples in the reference template above. Be sure to post these! Otherwise they will stay on the reconciliation until you do.
- As you enter your identified discrepancies, your unidentified variance will change. Once it reaches zero, your reconciliation is complete. Warning: it’s still important to confirm that items of the same amount are correctly matched. If you have recurring or multiple transactions of the same amount, simply matching amounts could lead to a discrepancy later on that will be more difficult to research.
- Also at this point, your “adjusted” bank and general ledger balances will be the same. This indicates matching balances after all discrepancies have been resolved.
I hope that this simple template will get you on your way to less-stressful bank reconciling. If you would like further training or assistance creating a more complex template, I would be happy to help! Just shoot me an email at firstname.lastname@example.org to get started.
Find out other reasons I use Excel in my previous blog “The Power of Excel.”