Reconciliation Control Room
Reconcile source-system totals against an Excel reporting layer, isolate exceptions and document a sign-off-ready control process.
Advanced · 75–100 min
Objectives
- Build control totals
- Use XLOOKUP/SUMIFS to compare sources
- Identify unexplained exceptions
- Document reconciliation evidence
- Create a sign-off summary
Project dataset
| Row | A: Txn ID | B: Branch | C: Source Amount | D: Report Amount | E: Status |
|---|---|---|---|---|---|
| 2 | TX001 | Mumbai | 125000 | 125000 | Matched |
| 3 | TX002 | Pune | 98000 | 97000 | Review |
| 4 | TX003 | Nashik | 152000 | 152000 | Matched |
| 5 | TX004 | Mumbai | 74000 | 74000 | Matched |
| 6 | TX005 | Pune | 91000 | 0 | Missing |
| 7 | TX006 | Nashik | 184000 | 185500 | Review |
| 8 | TX007 | Mumbai | 112000 | 112000 | Matched |
| 9 | TX008 | Pune | 68000 | 68000 | Matched |
Guided checkpoints
Establish source control totals
Calculate total Source Amount and total Report Amount before investigating any individual exception.
Create a top-level reconciliation control.
Calculate row variance
Add Report Amount minus Source Amount for each transaction.
Make exceptions measurable.
Classify exceptions
Use IF to label zero variance as Matched and any non-zero variance as Review.
Turn numeric differences into an actionable queue.
Summarize by branch
Use SUMIFS or a PivotTable to compare Source and Report totals by Branch.
Detect concentration of exceptions.
Investigate missing records
Use XLOOKUP or MATCH to confirm whether a transaction exists in both populations.
Separate missing records from amount mismatches.
Create sign-off evidence
Prepare a summary with control totals, exception count, unresolved variance and reviewer comment.
Make the reconciliation auditable.
Download sample project .xlsx · All portfolio projects
Interactive practice runs in your browser.