Get the perfect Excel template for speeding up your bank reconciliations.
If your accounting team is like most, you face tight deadlines when closing the books. Often though, your bank reconciliations remain a manual, time-consuming, and error-prone process. It doesn't have to be.
Learn how you can streamline & improve your bank reconciliations using this checklist built by accountants, for accountants. Discover the benefits of consistent, repeatable success every month.
Why Use This Excel Bank Reconciliation Statement Template?
This template streamlines the process of reconciling your bank statements and accounts in Microsoft Excel. This can cut hours or even days off the monthly close, depending on how many accounts you have to reconcile and how active they are. But why is reconciliation so important in the first place?
What is a Bank Reconciliation Statement & why is it important?
A bank reconciliation is the process of comparing account balances against an external source to verify that the balance is correct and catch any mistakes quickly. For example, you might reconcile your cash account balance against your bank statement balance to check for discrepancies. If you find the issue, you can correct it right away, rather than making decisions based on inaccurate numbers. Reconciliations also help identify fraud and bank errors.
Essentially, a bank reconciliation takes place at the end of the month and starts with the ending balance of the account. If the beginning and ending balances on the account and the statement match, everything should be good to go. However, if the balances don’t match, the reconciliation process helps you identify the reason so you know if anything is wrong.
There are several reasons an account may not reconcile right away and not all of them are a problem. However, you need to identify the cause so you can adjust and guarantee there are no problems. For example, a small business owner may have entered a transaction into their bookkeeping system that hasn’t yet cleared the bank. Outstanding checks at the end of the month are common in reconciliations. You’ll need to adjust for this but it’s not actually a mistake or a problem in your books. On the other hand, if fraudulent checks were cashed and not entered into the accounting system, you’ll identify them and be able to take action.
Example of a bank reconciliation
For the best results, you should aim to reconcile all accounts as a part of the monthly close process. In some cases, you may not be able to reconcile every account this frequently due to time constraints. In that case, prioritize the accounts with the highest dollar value and the highest risk if there is an error.
How do you actually do the reconciliation itself? Let’s take a checking account as an example. Let’s say the opening balance of the account in your accounting records is $10,000 and the closing balance is $12,000. You have $4,000 in credits and $6,000 in debits on your books. Enter the beginning and ending balance into your reconciliation worksheet.
Now, look at the bank statement. Let’s say the opening balance is $10,000 and the closing balance is $11,970. We would expect the opening balance to match since they are also the closing balance from last month when we did our last monthly bank reconciliation. In this case, the closing balance doesn’t match and we need to find out why.
Use the worksheet to match each deposit or withdrawal. You may also be able to easily see one transaction that accounts for the discrepancy. In this case, you may have a $30 overdraft fee in the account from a point in the month where you were overdrawn. While this will be recorded in the bank statement, you may not have entered it in your books and may not even have been aware of it. Accounting for that fee, the adjusted balance on your worksheet will now match. Errors may require adjustments to your books in the form of journal entries. If it’s simply a timing issue, you may just need to confirm in your worksheet so you know what caused the discrepancy.
How to do bank reconciliation in Excel
Excel makes reconciling much easier than a manual worksheet. You can use an Excel sheet to pull in numbers from a bank statement or from your accounting system and use formulas to match numbers and identify problems. However, setting up Excel to do this the right way can be time-consuming and complicated if you’re not an expert.
To simplify the process of doing a bank reconciliation in Excel, download the free Excel template above so you don’t need to worry about how to set up your worksheet.
To save even more time on your monthly reconciliations, account reconciliation software lets you automate much of the process while tracking what’s been done for the monthly close, what remains, and who handled what. The end result is a fast, frictionless close. Here’s a handy bank reconciliation example to reference.