If reconciling your business banking accounts is a monthly chore you dread, read on. We’re about to make your life easier. Simply download this bank reconciliation template and follow these five easy steps:
Step 1: Enter dates and balances.
Enter the bank statement date and ending bank balance along with the ending book balance in the designated cells as illustrated here. Note: The book balance is the balance in your accounting records such as Quickbooks or other records.
Step 2: Enter all outstanding checks.
Enter checks that have been written and recorded but did not clear your bank account in the designated cells as shown below:
The easiest way to identify your outstanding checks is to look at the check sequence summary on the bank statement to see which check numbers may have been skipped. Typically, your outstanding checks will be those issued towards the end of the month – start there and work backwards to speed this process.
Step 3: Enter any outstanding deposits in the designated areas.
Typically, you would not record cash deposits in your accounting system unless they have been deposited in the bank. However, if you did record a deposit on your accounting books that you have not yet taken to the bank, you will need to account for that timing variance.
Step 4: Enter any other outstanding adjustments.
These adjustments may include any accounting entries that are recorded in your accounting records in the cash account that are not reflected on the bank statement.
Step 5: Balance.
The bank reconciliation template we have included in this blog is designed to balance all of the outstanding checks, deposits, and adjustments. If you have entered all of the outstanding items, a zero amount should appear in the variance cell (F18) and cell F20 will state “BALANCED”. If there is a variance, cell F20 will show “OUT OF BALANCE”.
If you are out of balance and unable to identify the variance, it is best to utilize what I call “Tick and Tie”. Simply go line by line through your bank statement and your check register (accounting record) simultaneously and check off all the items one by one. You should be able to identify any differences this way. Check the actual dollar amounts in your accounting record against the bank statement as sometimes even the bank will make a mistake.
Things to remember
To balance your records in future months, simply right click on the tab label at the bottom of the bank reconciliation workbook (as shown below) and select “Move or Copy…”, check the “Create a copy” box and you can copy the sheet into the same workbook or into a new workbook. I recommend that you copy the sheet into the same workbook. You can re-label the worksheet by left clicking inside the tab label. I like to label the sheets by month and keep an entire year of sheets in the same workbook. Save the file as “BankReconFiles.2012” or something similar.
For the new month, clear out only the cells highlighted in grey with blue font, except for the outstanding checks. Start by reviewing the checks that were labeled as outstanding from the prior month. This ensures that no checks remain outstanding longer than 30 days. If outstanding checks stay on your books for longer than 30 days, it’s suggested that you contact the payee to confirm that they have received payment.
Also, if you need more rows for outstanding items, add them by selecting any cell on row 14 or 15 and hitting the short cut key sequence, of: Alt, I, R (for Insert Rows). Just make sure you are inserting your rows within the formula ranges for the total cells (currently row 18).
The process will become easier each month as you learn the routine. Get your accounts in balance and then turn your focus back to the important stuff: earning more money.