Bank Reconciliation, Step by Step: A Worked Example and Excel Tips

The short version

A bank reconciliation compares the cash balance in a company's books with the balance on the bank statement, and explains every difference. After the reconciliation both sides should agree. It is the first reconciliation most accountants learn.

Why the two balances differ

• Deposits in transit: money banked but not yet shown by the bank
• Outstanding cheques: cheques issued but not yet cleared by the bank
• Bank charges and fees not yet recorded in the books
• Interest or other credits received by the bank but not yet recorded
• Errors: wrong amounts or wrong entries by either side
• Direct debits or credits posted by the bank that the company did not know about

The steps

1. Take the bank statement balance and the cash book balance at the same date.
2. Tick off every item that appears in both the bank statement and the cash book.
3. List the items in the cash book that are not on the statement (deposits in transit, outstanding cheques).
4. List the items on the statement that are not in the cash book (charges, interest, direct debits).
5. Adjust the bank balance for deposits in transit and outstanding cheques.
6. Adjust the book balance for charges, interest and other unrecorded items.
7. Check that the two adjusted balances agree. If not, look for errors.
8. Post journal entries in the books for the items that need recording, and keep the reconciliation as support.

A worked example (illustrative numbers)

Suppose the bank statement shows a balance of ₹50,000 and the cash book shows ₹51,500.

Items found: deposits in transit ₹5,000; outstanding cheques ₹3,000; bank charges ₹200 not yet recorded; interest credited ₹700 not yet recorded.

Adjusted bank balance: 50,000 + 5,000 (deposits in transit) − 3,000 (outstanding cheques) = ₹52,000.

Adjusted book balance: 51,500 − 200 (charges) + 700 (interest) = ₹52,000.

Both adjusted balances equal ₹52,000, so the reconciliation agrees. The company then records the ₹200 charge and the ₹700 interest in its books.

What to do when it does not agree

1. Recheck the totals and the arithmetic.
2. Look for a difference that equals a single transaction.
3. Check for transposed digits, such as 540 and 450.
4. Check for items entered twice or on the wrong side.
5. Compare the opening balances with last month's reconciliation.

Excel tips

• Use lookups to match bank lines to book entries by amount and date
• Use conditional formatting to highlight unmatched amounts
• Use pivot tables to total by category
• Sort by amount to find pairs quickly
• Keep the sheet clean: labels, dates, and a final check cell showing the difference

Next step

Practise with a sample bank statement and cash book. Then register on JobKarle and apply to reconciliation and accounts roles.