Free Bank Reconciliation Template in Excel - Free Printable

To solve this bank reconciliation problem, we need to understand why the company’s records and the bank statement don’t match — and then fix it.

---

Step 1: Understand what’s given

We have two starting balances:

- Bank account balance (from bank statement) = 4,619,940
- Company’s cash book balance = 5,150,840

The difference is:
5,150,840 – 4,619,940 = 530,900 → This is the mismatch amount. The company thinks they have more money than the bank says.

---

Step 2: Look at the bank statement transactions

The bank statement shows all transactions that have been processed by the bank. Some of these may not yet be recorded in the company’s books — or vice versa.

Let’s go row by row and see which ones are marked as “Mismatch. Please check” — those are the ones causing the problem.

From the table:

- 29/12/22: Cheque Unpaid (Customer DF) — Withdrawal of 500,000 → Mismatch
- 31/12/22: Bank Charges — Withdrawal of 900 → Mismatch

These two items are on the bank statement but likely NOT yet recorded in the company’s cash book. That’s why the company’s balance is higher — they haven’t subtracted these yet.

---

Step 3: Adjust the company’s cash book balance

The company’s cash book balance is too high because it hasn’t recorded:

1. Cheque unpaid (bounced cheque) — 500,000
→ This means a customer’s cheque was deposited, but the bank rejected it. So the company must subtract this from their books.

2. Bank charges — 900
→ The bank took this fee, but the company hasn’t recorded it yet. Subtract this too.

So total adjustments to reduce company’s balance:
500,000 + 900 = 500,900

Now adjust the company’s balance:
5,150,840 – 500,900 = 4,649,940

Wait — that’s still not matching the bank’s 4,619,940. There’s still a difference of:
4,649,940 – 4,619,940 = 30,000

Hmm… so we’re missing something else.

---

Step 4: Check for other possible mismatches

Look again at the bank statement. Are there any deposits or withdrawals that the company might have recorded differently?

Notice this line:

> 27/12/22 Wages (Cheque No 12348) — 10,950 → marked “Records match”

But wait — let’s double-check the math on the bank statement itself.

Start with opening balance: 5,062,790

Then:

- 8/12: -370,000 → 4,692,790 ✔️
- 12/12: +450,000 → 5,142,790 ✔️
- 16/12: +230,000 → 5,372,790 ✔️
- 18/12: -155,000 → 5,217,790 ✔️
- 22/12: -86,000 → 5,131,790 ✔️
- 27/12: -10,950 → 5,120,840 ✔️
- 29/12: -500,000 → 4,620,840 ✔️
- 31/12: -900 → 4,619,940 ✔️

All bank statement math checks out.

Now, let’s think: maybe the company recorded the wages cheque incorrectly?

The company’s balance is 5,150,840.

If we assume the company has recorded everything EXCEPT the two mismatch items (500,000 and 900), then after subtracting them, we get 4,649,940 — which is 30,000 too high compared to the bank’s 4,619,940.

That suggests the company also made an error in recording one of the transactions — specifically, they probably recorded the wages cheque (10,950) as 40,950 — overstating the withdrawal by 30,000.

Because if they subtracted 40,950 instead of 10,950, that would make their balance 30,000 lower than it should be — but wait, no: if they subtracted MORE than they should have, their balance would be TOO LOW. But here, their balance is TOO HIGH.

Actually, let’s reverse it.

Suppose the company did NOT record the wages cheque at all? Then their balance would be 10,950 too high.

But we already accounted for 500,900 in unrecorded items. After adjusting, we’re still 30,000 off.

Alternative idea: Maybe the company recorded the cheque deposited from Customer BG (230,000) as 260,000? That would make their balance 30,000 too high.

Yes! If the company recorded a deposit of 260,000 instead of 230,000, that would inflate their balance by 30,000.

So now we have three issues to fix in the company’s books:

1. Remove the bounced cheque: -500,000
2. Record bank charges: -900
3. Correct the over-recorded deposit: -30,000 (because they added 30,000 too much)

Total adjustment: 500,000 + 900 + 30,000 = 530,900

Now adjust company’s balance:
5,150,840 – 530,900 = 4,619,940

✔ Matches the bank statement!

---

Final Answer:

The mismatch is caused by three items not properly recorded in the company’s cash book:

- A bounced cheque of 500,000 (must be subtracted)
- Bank charges of 900 (must be subtracted)
- An over-recorded deposit of 30,000 (must be corrected by subtracting 30,000)

After making these adjustments, the company’s cash book balance becomes 4,619,940 — matching the bank statement.

Final Answer:
4619940