Reconcile two transaction lists, identify missing and mismatched records and build a review sheet that preserves the original data.
A fictional club compares its internal payment log with a bank export. Match by a unique reference after trimming spaces. Do not match by amount alone. The dates are outside this simplified exercise; all amounts are teaching units.
| Source | Reference | Amount |
|---|---|---|
| Ledger | P1 | 100 |
| Ledger | P2 | 150 |
| Ledger | P3 | 80 |
| Bank | P1 | 100 |
| Bank | P2 | 145 |
| Bank | P4 | 60 |
Decide the key, normalization rules, amount tolerance and duplicate policy before calculating. A lookup can silently choose the first of several duplicate keys. Preserve raw data on separate sheets and put normalized values in new columns. In this case the tolerance is exactly zero and each reference should be unique per source.
Create raw sheets and a rule sheet with zero tolerance.
Reconciliation contract
Record your result and evidence here or in a separate file.
Neither source is edited in place and duplicate checks are present.
Count references before using them in a lookup.
Build the union of references from both sources so one-sided items cannot disappear. For each reference, show presence, ledger amount, bank amount and bank-minus-ledger difference when both exist. Keep missing values distinct from a genuine zero amount. The reference calculation is independent of any particular spreadsheet brand.
Create rows for P1, P2, P3 and P4 and classify each.
Matching table
Record your result and evidence here or in a separate file.
P1 matches, P2 differs by -5, P3 is ledger-only and P4 is bank-only.
Do not convert missing entries to zero before classifying presence.
The total difference must be explained by the classified items. A difference bridge is a useful control because row-level labels can otherwise look complete while omitting a transaction. Use a consistent sign convention and write it above the table. Compare both row counts and amounts.
Reconcile ledger total 330 with bank total 305 using the three exceptions.
Difference bridge
Record your result and evidence here or in a separate file.
-5 - 80 + 60 = -25, matching bank minus ledger.
Reverse signs only after defining which source is subtracted from which.
An exception is a request for evidence. Record the likely question, owner and next action, but do not mark a discrepancy resolved because a plausible story exists. A timing difference needs a dated supporting record; a fee needs evidence of the charge. This teaching case supplies no such external evidence.
Prepare an exception log with questions for P2, P3 and P4.
Three-item investigation log
Record your result and evidence here or in a separate file.
All exceptions remain open until the stated evidence is supplied.
Replace assumed explanations with evidence requests.
A reusable workbook detects new rows, duplicate keys and changed totals without manual patches. Test a new matching reference and a duplicate. Explain any formula-range limits. Protecting a sheet can prevent accidental edits, but it does not replace a review of the logic or the evidence behind adjustments.
Add P5 with equal amounts to both sources, then inject a duplicate in a test copy.
Reusable workbook and test note
Record your result and evidence here or in a separate file.
P5 matches and the duplicate produces a visible warning.
Use a defined data range or table and recheck the count reconciliation.
Deliver untouched raw sheets, rules, matching table, difference bridge, exception log and two test cases. State which spreadsheet and functions you actually used; do not claim a macro or automated bank connection.
No unexplained difference is hidden in an adjustment row and missing values remain distinguishable from zero.
| Criterion | Points | Evidence |
|---|---|---|
| Evidence and correctness | 30 | Calculations, source use and results match the supplied case; limitations are honest. |
| Required artifacts | 30 | All five stage outputs and the capstone handoff are present and inspectable. |
| Reasoning and decisions | 20 | Choices, assumptions, alternatives and the case-specific critical condition are explained. |
| Reproducibility | 10 | A reviewer can follow the stated steps and distinguish expected from observed results. |
| Clear communication | 10 | Labels, units, structure and evidence references make the work easy to review. |
Score at least 80/100, supply every required artifact, and satisfy the path-specific critical condition. Rework missing evidence before marking complete.
Ledger total is 100 + 150 + 80 = 330. Bank total is 100 + 145 + 60 = 305. The difference of −25 is explained by P2 (−5), missing bank P3 (−80) and bank-only P4 (+60). The supplied CSVs and reference output can be used in a free spreadsheet.
This is a synthetic reconciliation exercise, not a bank integration or an audited financial record.