First: Personalize
- SAVE an original copy.
- Look at the bottom tabs.
- Click on the second blue tab named “Jan”.
- Click on the merged cells with “Name” typed in – Row #1.
- Type in your business’s name-(All you “set up” on this first sheet will auto fill in the rest of the month’s worksheets).
- Type in the name of the report- Row #2-(This is a Bank Reconciliation Report, but you can name it whatever you want. Auto filled rest of months).
- Type in the year in cell B5. Will auto-fill remaining months.
- Type in your business’s bank and account number (can just include last 3 – 4 numbers) – Row #4- (will auto-fill rest of months).
- Notice the blue boxes in Jan. (these are the only places you will be putting in information…all the rest are formulas…will tell you later how to get rid of shading).
- Put in beginning balance on the General Ledger side-Row #8 Cell E8 (Notice: You will have to put last month’s ending balance from your general ledger even if it is not January because all the rest of the months it will automatically carry forward).
- SAVE one more copy! This one will be your original to use every year. Can delete original one not personalized now.
Second: Begin to use!
- Go to month you want to reconcile. (Your GL balance should have carried forward and be in cell E8 and also in the Book balance cell E12).
- Don’t worry if you accidently delete a formula. Just click the “Go Back Button”.
- On the GL side…put in TOTAL deposits in cell E9 from the month you need to reconcile in your GL with your current bank statement.
- On the GL side…put in the TOTAL expenses in cell E10 from the month you need to reconcile in your GL with your current bank statement.
- On the Bank Statement side…put in the date cell G8 and ending balance from your statement in cell I8.
- On the Bank Statement side…put the total checks from your statement in cell H33.
- On the Bank Statement side…put the total deposits from your statement in cell H37.
- Put in all the outstanding checks and deposits that has not cleared the bank, but is in your GL’s reconciling month.
*Notice: Your book balance and bank balance (row 12) should match each other and also match ending balance in the GL’s reconciling month. Your checks and deposits should match. (Bottom of report)
**Attention: To get rid of the shading in Jan. Simply click on the cell with the shading…look up in your toolbar and find the Fill Color (icon that looks like a paint can spilling out paint…next to the font color) and click “no fill”.
***NOTE: This is my most aggravating, make-me-want-to-throw-my-computer-out-the-window report. I’ve had several times it didn’t match and I about pulled all my hair out trying to figure out why.
Some of the reasons were:
- Reversed cents in one entry…had to compare each check with each entry for that one.
- Check from a couple of months back had not cleared.
- A check that was dated for the month before was moved back into a reconciled month. Whoever fills out the GL…tell them NOT TO EVER move anything back into a reconciled month without consulting you first.
****Recommendation: Print out the bank reconciliation report each month and keep in a binder along with your business’s bank statement.
Also, I would recommend backing up your data once or twice a month and storing in a separate location from your original.
