21.6 Bank Reconciliation
The ask. Zoya closes the month for a fictional Kolhapur store. The cash book and the bank statement do not show the same balance. She must match the entries by reference, list what is missing on each side, and prove that the two balances agree.
Opening balance on both sides: ₹1,50,000. Sheet Book (A: Ref, B: Narration, C: Amount) and sheet Bank (A: Ref, B: Amount). Payments are negative. Fictional data:
Book
| Ref | Narration | Amount (₹) |
|---|---|---|
| CHQ-101 | Supplier payment | -25,000 |
| CHQ-102 | Rent | -60,000 |
| DEP-201 | Cash deposit | 85,000 |
| CHQ-103 | Packaging vendor | -12,000 |
| DEP-202 | Card settlement | 40,000 |
Bank
| Ref | Amount (₹) |
|---|---|
| CHQ-101 | -25,000 |
| CHQ-102 | -60,000 |
| DEP-201 | 85,000 |
| DEP-202 | 40,000 |
| CHG-01 | -590 |
What to deliver (one under the other):
- In Book, add In bank? with XLOOKUP on the Bank refs; show "Not in bank" when missing.
- In Bank, add In book? the same way; show "Not in book" when missing.
- Calculate both closing balances: opening + SUM of amounts.
- List cheques issued but not presented, and bank-only items (charges).
- Build the reconciliation: bank balance − cheques not presented + deposits not credited − bank-only items = book balance.
- Pass a journal entry in the book for the bank charges.
Key formulas (row 2; copy down or let the Table fill them):
Book!D2: =XLOOKUP(A2,Bank!$A$2:$A$6,Bank!$B$2:$B$6,"Not in bank")
Bank!C2: =XLOOKUP(A2,Book!$A$2:$A$6,Book!$C$2:$C$6,"Not in book")
Book balance: =150000+SUM(Book!C2:C6)
Bank balance: =150000+SUM(Bank!B2:B6)
Not presented: =SUMIFS(Book!C2:C6,Book!D2:D6,"Not in bank")
Expected result (check your numbers):
| Line | Amount (₹) |
|---|---|
| Balance as per bank | 1,89,410 |
| Less: cheque CHQ-103 not presented | -12,000 |
| Add back: CHG-01 bank charges not in book | 590 |
| Balance as per book | 1,78,000 |
Check: 1,89,410 − 12,000 + 590 = 1,78,000, which equals the book balance 1,78,000. After the book records the ₹590 charge, the adjusted book balance is 1,77,410.
Checks before you send it:
- Both lookups use exact match on Ref.
- Every "Not in bank" / "Not in book" line has a reason written next to it.
- The reconciliation ends on exactly the book balance.
- The bank charge is entered in the book before closing.
Ravindra Bagale's Tip
Match on a reference, never on amount alone. Two payments of ₹12,000 in a month will match the wrong way.
Ravindra Bagale's Tip – मराठी
नेहमी reference वर match करा, फक्त amount वर कधीच नाही. महिन्यात ₹12,000 चे दोन payments असले की चुकीचे match होतात.
Ravindra Bagale's Tip – हिंदी
हमेशा reference पर match करें, सिर्फ़ amount पर कभी नहीं. महीने में ₹12,000 के दो payments हों तो गलत match हो जाते हैं.
Loading practice tasks...
- Total tenure months (Easy): Add up Tenure months in D2:D11.
- Average loan amount (Easy): Average of E2:E11, rounded to 2 decimals.
- Rows in Home (Easy): How many rows have Loan type "Home"?
- Largest tenure months (Easy): The largest value in D2:D11.
- Tenure months for Home (Medium): Total of D2:D11 where B is "Home".
- Home in Chennai (Medium): Total of D2:D11 where B is "Home" and C is "Chennai".
- Best in Home (Medium): Largest E2:E11 value where B is "Home".
- Value of row 2 (Medium): D2 times E2.
- Look up Arjun Mehta (Medium): The Tenure months for "Arjun Mehta" (use a lookup on column A).
- Total value (Hard): Sum of D times E for all rows.
- Share of Home (Hard): Share of D2:D11 that is "Home", rounded to 3 decimals.
- Rank of row 2 (Hard): Rank of D2 among D2:D11 (largest = 1).
- Above average? (Hard): "Above" if E2 is above the average of E2:E11, else "Below".