Here are bank reconciliation exercises and answers in printable PDF format and in Excel.
These are free to use by individuals, teachers and students, small business owners and bookkeepers, and anyone else who is interested.
Use these exercises to practice working on bank reconciliation problems and solutions.
These are based on manual bookkeeping, not on bookkeeping software.
The first bank reconciliation exercise is a step-by-step tutorial - see what to do at each step.
It brings to light common problems and the solutions to fix them.
You will learn:
All names of people and businesses in these exercises are fictitious and made up from my imagination. They do not depict real names, businesses or places known by me.
The Rose and Flower is a new floral shop started by Rose Green on April 1st.
Rose opened a business account with a bank called A Major Bank (AMB). She gets the following with her bank account:
Rose also has a business Visa card with AMB.
The Rose and Flower shop operated throughout April.
Rose occasionally updated her Cashbook for April.
The cashbook is a pre-printed book from the stationery shop. Rose just writes down her transactions into it when she remembers.
Here is an image of the April page in the Cashbook at the end of April.
The closing balance is left blank until Rose checks the Bank Statement.
At the beginning of May, Rose received a Bank Statement from AMB. Here it is: -
Now Rose is ready to reconcile the bank statement and cashbook.
She lays them side by side on her table.
She has a ruler, a pen and a highlighter.
Here are the ticked and highlighted documents - Bank Statement first then the Cashbook
Here is the final list:
Why is $350 entered twice?
The receipt from Office Supplies shows her that the Cash Machine (also known as a Cash Till) was $300 and the Petty Cash Box was $50.
One of the $350 entries must be reversed ('removed' from the Cashbook).
Why are the kitchen supplies $55 not on the bank statement?
The Cashbook Reference column indicates ‘Cash’ was used. Rose looks in the Petty Cash box and finds the receipts in there. The items were brought with money from the Petty cash box and so they should not be in the Cashbook for the bank account.
This entry must be ‘removed’ from the bank Cashbook page, and a new record must be started for the transactions in and out of the Petty Cash box. This can be done with a Petty Cash Log.
Why is BT payment of $102.50 split in the Cashbook?
It is split because two different types of expenses were purchased and so they needed to go to different accounts (vehicle costs and telephone)
The split in the Cashbook is fine. Leave as is.
Why is there an OD fee of $10?
The bank account went into overdraft. Rose does not have an arranged overdraft facility with the bank, so they charged her $10.
Enter it into the Cashbook.
Why is $90 to glass cleaners not on the Bank Statement?
Cash was used to pay for this. Rose checked the Petty Cash box but it was not taken from there. She remembers using her own cash to pay.
Keep receipt and give to Accountant to enter into the bookkeeping system using a journal.
Why has the bank done a deposit correction of $50.
Rose checks the original deposit paperwork and calculations and sees that her assistant Alice did not add up the receipts correctly. The bank is correct to make this adjustment.
Include the adjustment in the Cashbook.
Why is Ch 0004 not on Bank Statement?
Rose checks the check/cheque book and she looks at her bills. She knows she posted the check to the sign writers and so they probably did not get it deposited before the month end.
This will be adjusted using the Bank Reconciliation Worksheet.
Why are the travel costs of $210 not on the Bank Statement?
The Cashbook reference is ‘Bank Card’. Rose remembers that she used the business Visa Card, not the bank card. However, she is still waiting for the Visa card statement which the bank will only send out in the middle of May.
‘Remove’ this transaction from the bank account Cashbook and start a separate Cashbook page for the Visa card transactions.
Why are none of the bank fees not in the Cashbook?
Rose wasn’t aware of them until she got the bank statement, although if she had checked her bank account online more closely she would have seen them.
Enter them into the Cashbook.
What is the $110 AMB Loans?
Rose checks her loan paperwork and sees that this is the regular monthly repayment. It is for $90 principal and $20 interest.
Enter to the Cashbook.
$200 withdrawal must be split to two Accounts
Rose only discovered this after checking the deposit to the Petty Cash Log sheet against the entry in the Cash Book.
Show that the Cash Box amount was $80, and the Cash Machine amount was $120.
Here is the list of Rose with her answers
Rose works through her list of solutions to adjust the Cashbook at the bottom.
If she did not have enough room at the bottom, she would simply turn the page and do it there because she is using a pre-printed Cashbook which has lots of lined pages.
If she was using lined pages printed off her computer she would just print more pages as she needs them.
The first thing Rose does is to enter into the Cashbook the transactions from the Bank Statement that were missing from the Cashbook, which were:-
These are all straight forward entries.
She dates them at the date showing on the Bank Statement even though entering them after the transactions dated April 30th in the Cashbook – a mixed date order within the same month is not a huge problem.
The second thing Rose does is to enter adjusting entries for the transactions that were wrong in the Cashbook, which were:
Difference No. 1 – The double entered $350.00.
How: The adjustment is entered to the Money In column in the Cashbook to put the Cashbook back up to a correct balance. Look at the balance before the entry was made and look at it after this entry was made.
Difference No. 2 – The kitchen supplies entry of $55
How: This adjustment is also entered to the Money In column, same reason as difference no. 1
Difference No. 3 – The Split purchase of $102.50
How: Nothing is done here because the split amount still adds up to $102.50 and so the Cashbook bank balance is correct.
Difference No. 5 – The $90 for the window cleaners.
How: This adjustment is also entered to the Money In column, same reason as difference no. 1 and 2 above.
Difference No. 8 – The hotel payment of $210.
How: This adjustment is also entered to the Money In column, same reason as difference no. 1,2 and 5 above.
Difference No. 11 - Split the Cash Withdrawal to two accounts.
How: Enter $120 into the Money In Column, which leaves $80 against the Cash Box account. Then do a second entry of $120 into the Money Out column against the Cash Machine account.
Rose dates them all at April 30th (except for No 6. Cash withdrawal) but indicates in the Details column the date of the original transaction entry in the Cashbook.
At this stage Rose could also go back to the Bank Statement and tick off the highlighted transactions to show that they have now all been dealt with.
There are always two main steps to get the bank account and cashbook balanced to each other if they are unbalanced.
First Step: Adjust the cashbook
Second Step: Use a bank reconciliation worksheet template
Sometimes it will only be necessary to process just the first step, or just the second step, depending on what transactions are missing or wrong.
If you deal with purely modern online banking and do not issue or receive checks/cheques, you will not have to use a Bank Reconciliation Template.
This list will help you decide if you should adjust the Cashbook or use a Bank Reconciliation Worksheet or to do both.
Here is the example of Rose’s reconciliation template which shows you how to get the bank reconciliation format correct.
Unpresented checks/cheques and unpresented deposits must all be calculated into the mix using a Bank Reconciliation Worksheet (not entered as adjustments in the Cashbook as previously mentioned).
The check/cheque number 0004 dated 27th April is not on the Bank Statement so Rose writes it down in the Unpresented Checks/Cheques box of the Bank Reconciliation Worksheet.
Rose then enters the amounts into the Reconciliation section of the worksheet.
The Expected Bank Statement Balance of $2,441.50 is the closing Bank Statement balance so it is good and correct.
If it did not come to that on the Bank Reconciliation Worksheet, it would mean either:
...and would have to go back to check them all.
The Cashbook closing balance for the last day of April will remain as $2,091.50.
There are no further adjustments to be made.
The information from the Bank Reconciliation Worksheet is not transferred into the Cashbook because the un-presented check/cheque will be on next month’s Bank Statement.
So next month the Cashbook balance and Bank Statement balance will align with each other without having to do another Reconciliation form (unless of course next month there are new unpresented checks/cheques or deposits).
At the end of the day Rose will file the April Bank Reconciliation Worksheet with the April Bank Statement so that if some months later someone were to look back at April they will see from the Reconciliation worksheet why the Cashbook balance was not the same as the Bank Statement balance.
Here is the image of the Cashbook for the Visa Credit Card which Rose started half way through her pre-printed Cashbook (rather than paying for a whole separate Cashbook from the stationers).
She will match and reconcile this in the same way she matched and reconciled the Main Bank Account, once she receives her Visa Credit Card statement.
Here is the image of the Petty Cash Log.
This is based on my petty cash log form, but there is no reason not to use the Cashbook layout.
For example, Rose could start a Cashbook for the cash box at the back of her Main Cashbook (just like she started the Visa Cashbook half way through the Main Cashbook pre-printed book.
After this exercise Rose decides she must check her bank account online more often, every day even, to make sure the Cashbook is as close to agreement as possible with what it says at the bank so that there are less adjustments to make at the end of the month.