Bank Reconciliation Instructions

First: Personalize

• SAVE an original copy.
• Look at the bottom tabs.
• Click on the first blue tab named “Jan”.
• Click on the merged cells with “Name” typed in - Row #2.
• Type in your organization’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 #3-(This is a Bank Reconciliation Report, but you can name it whatever you want. Auto filled rest of months).
• Type in the date unless you are using in 2008- Row #4-(This is not automatic and each month will have to have the date updated.).
• Type in your organization’s bank and account number- Row #5- (Auto filled 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 (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 E9 and also in the Book balance cell E13).
• Don’t worry if you accidently delete a formula. Just click the “Go Back Button”.
• On the GL side…put in TOTAL credits in cell E10 (current deposits-can rename-will autofill) (from the month you need to reconcile in your GL with your current bank statement).
• On the GL side…put in the TOTAL debits in cell E11(current disbursements- can rename-will autofill)) (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 I9.
• On the Bank Statement side…put the total checks from your statement in cell H39.
• On the Bank Statement side…put the total deposits from your statement in cell H43.
• 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 13) 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:

1. Reversed cents in one entry...had to compare each check with each entry for that one.
2. Check from a couple of months back had not cleared.
3. 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 organization’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.

Return to Free Spreadsheets from Bank Reconciliation Instructions