The ₹3,500 gap in the coaching centre fee register
Meera maintains the fee register for a coaching centre in one workbook, Fees_2024.xlsx. Sheet 'Students' lists 212 students with columns StudentID, Name, Batch (B1 or B2) and MonthlyFee. Sheet 'Payments' holds 1,540 rows: Date, ReceiptNo, StudentID, Amount, Mode. At month end the SUMIF of Amount for Batch B2 returns ₹48,500, but the bank statement for the same period shows credits of ₹52,000. Fourteen VLOOKUP formulas on a summary sheet return #N/A. Meera also notices that receipt R-1042 appears twice, several StudentID cells carry a small green triangle in the corner, one Amount cell reading 1,500 is aligned to the left, and the Batch column was filled by dragging down after a filter had been applied. The owner wants the numbers reconciled tonight and a structure that will not break again next month. Meera has roughly two hours and no budget for new software.
1. Meera has two hours. Set out the checks you would run, in order, to explain the ₹3,500 gap, and state what each check rules in or out.
Read the worked answer
Work from the outside in: prove the bank figure first, then explain the difference item by item, and only then change any cell.
- Duplicate receipts. Sort Payments by ReceiptNo and test with
=COUNTIF($B$2:$B$1541,B2)>1. R-1042 appearing twice is an integrity defect, but a genuine duplicate inflates the register, so it cannot explain a shortfall. Check the counterfoil book: either the same payment was entered twice, or one receipt number was reused for two students. Rule it out as the cause of the gap; keep it on the list of defects. - Amounts stored as text. The left-aligned 1,500 is text, not a number, so SUMIF silently skips it. Confirm with
=SUMPRODUCT(--ISTEXT($D$2:$D$1541)). This alone accounts for ₹1,500 of the shortfall. - Wrong batch on correct rows. Because Batch was dragged down after filtering, some B2 students are tagged B1. Filter rows where StudentID exists in the B2 list of the Students sheet but Batch shows B1; two receipts of ₹1,000 wrongly tagged give a further ₹2,000.
- The fourteen #N/A rows. Compare
=LEN(C2)with=LEN(TRIM(C2))to expose trailing spaces, and check whether IDs are stored as text on one sheet and as numbers on the other. These rows are invisible to VLOOKUP and drop out of any lookup-based summary.
The arithmetic closes the gap exactly: 1,500 + 2,000 = 3,500. Note what this reasoning rules out - the VLOOKUP failures are not the cause of the batch total gap, because SUMIF reads the Batch column directly; they damage a different summary. Reporting them as the cause would send Meera to the wrong fix.
2. Recommend concrete changes to the workbook and justify each one by the specific failure it prevents.
Read the worked answer
Structure first, then validation, then protection.
- Separate the three jobs into three sheets. Students as a master list (one row per student, entered once); Payments as an entry log (one row per receipt, no names, no batches); Reports as lookup-only, containing no typed data. This removes the possibility of the same fact living in two places, which is what produced the batch mis-tagging.
- Never type Batch into Payments. Derive it:
=IFERROR(VLOOKUP(TRIM($C2),Students!$A:$D,3,FALSE),"CHECK ID"). A derived batch cannot drift, and the CHECK ID text turns a silent #N/A into a visible task. - Data validation on StudentID as a list sourced from the Students sheet. Typed IDs with trailing spaces and number/text mismatches become impossible at the point of entry rather than detectable weeks later.
- Data validation on Amount: decimal, between 0 and 100000. This blocks '1,500' and other text entries that SUMIF ignores.
- Custom validation on ReceiptNo:
=COUNTIF($B$2:$B$2000,$B2)=1, with an error message telling the user to check the counterfoil. Blocks the R-1042 problem at source. - Convert both ranges to Excel Tables so ranges expand with new rows instead of leaving row 1,541 outside every formula, and protect the Reports sheet so formulas are not overwritten.
- Add a monthly reconciliation block: register total, bank total, difference, and a note field for the explanation. A visible difference cell is what forces the check to happen before the owner asks.
Each change maps to a specific observed failure; validation without the structural split would still allow the batch column to be edited by hand.
3. The owner says: 'Let us simply be more careful when typing and keep everything on one sheet.' How do you answer, and what is the smallest change you would insist on?
Read the worked answer
Answer plainly: care is not a control. The three defects found tonight - a text amount, a dragged batch column, a reused receipt number - were all produced by careful people doing repetitive work under time pressure. Care cannot be audited, cannot be handed to a new volunteer, and leaves no trace of who changed what. A single sheet also mixes three different kinds of fact (who the student is, what was paid, what the report says), so every edit risks touching a fact that should be immutable.
The smallest change worth insisting on, if the owner refuses the full restructure, is the separation of entry from calculation, plus one validation rule:
- Payments becomes an entry sheet with only Date, ReceiptNo, StudentID, Amount, Mode.
- A second sheet holds Batch and MonthlyFee, keyed by StudentID.
- StudentID on the entry sheet is restricted by a list validation.
- Batch is looked up, never typed.
Rationale: this costs about twenty minutes, removes the two causes that produced the entire ₹3,500 gap, and leaves a file that a second person can check without knowing the history. Everything else - duplicate-receipt blocking, table ranges, the reconciliation block - can follow later without rework.
Be equally clear on what will not be fixed by this: the duplicate receipt and the missing student master remain process problems, so the receipt book must still be reconciled against the register at the end of each day.