01 / Learn the method
Learning outcomes
Clean a small ledger export while preserving every exception. Calculate the result, reconcile it and communicate its limitations.
Method
Keep raw rows intact, then create a normalized layer with explicit parsing and duplicate rules. A repeated transaction ID is not enough to delete a row if its contents differ. Reconcile raw totals, excluded duplicates and accepted totals; keep unmapped or invalid rows visible instead of silently dropping them.
Smaller worked example
Raw amounts 100, 100 and 50 include one exact duplicated 100 record. Raw total 250 less excluded duplicate 100 equals accepted total 150. An unmapped account still belongs in accepted totals until its treatment is resolved.
02 / Put it to work
Business context and rules
Fictional Birch Agency, September 2026, USD. The companion CSV has seven rows: T01 sales 1,200.00; T02 expense (250.00); an exact duplicate T02; T03 sales 800.00; T04 expense -100.00 to unmapped account 6999; T05 expense -50.00 with invalid date 2026-09-31; T06 sales 300.00. Dates use ISO format. Account 4000 maps to Revenue, 6100 to Operating expense; 6999 is unresolved. Parentheses mean negative. Exclude only the exact duplicated T02; retain T04 and T05 in the reconciliation but flag them. Accept the signed amount of T05 for reconciliation without inventing a corrected date. Raw signed control total is 1,650.
Original synthetic inputs
| Input | Value | Unit |
|---|---|---|
| T01 normalized amount | 1200 | USD |
| T02 normalized amount | -250 | USD |
| Duplicate T02 normalized amount | -250 | USD |
| T03 normalized amount | 800 | USD |
| T04 unmapped amount | -100 | USD |
| T05 invalid-date amount | -50 | USD |
| T06 normalized amount | 300 | USD |
| Raw signed control | 1650 | USD |
Required deliverables
Import the raw CSV as text, preserve it, build typed amount / valid-date / account-mapping columns, and provide an exception log for duplicate, invalid date and unmapped account. The normalized Inputs tab is a reconciliation aid; submitting its totals without cleaning the raw CSV does not satisfy the task.
Use formulas for derived amounts and preserve source data. Put narrative deliverables in the workbook response area; expand it as needed. Compare amounts within 0.01 of the stated unit and percentages within 0.1 percentage point. No unsupported balancing plugs.
Required raw dataset
Download XFM-01_raw_v1.csv from the case download panel. It deliberately contains duplicate and invalid records. The workbook Inputs sheet contains numeric control aids, not a substitute for cleaning the raw file.
03 / Review your work
Try the assignment before opening the answer.
Open the worked answer and teaching notes
Worked numerical schedule
| Measure | Value | Unit |
|---|---|---|
| Raw signed total | 1,650.00 | USD |
| Accepted total excluding exact duplicate | 1,900.00 | USD |
| Known revenue total | 2,300.00 | USD |
| Known expense total including date exception | -300.00 | USD |
| Unmapped accepted amount | -100.00 | USD |
| Accepted mapping reconciliation | 0.00 | USD |
| Raw control residual | 0.00 | USD |
Interpretation and recommended actions
There are 7 raw rows and 6 accepted records. Raw signed total 1,650 becomes accepted total 1,900 after excluding the duplicate -250. Revenue is 2,300, known expense -300 and unmapped -100. Keep T04 for mapping review and T05 for source-date correction; neither disappears from the total. Preserve the duplicate row and its exclusion reason in an audit trail. No final clean-date claim is appropriate until T05 is corrected by the source owner.
Scoring rubric - 100 points
| Dimension | Points | Awarding guidance |
|---|---|---|
| Calculation | 40 | Raw import / typed normalization 15; exact duplicate handling 10; totals / exceptions 15. |
| Interpretation | 25 | Correct application of the case rules 10; explain the business decision 10; identify evidence or limitations 5. |
| Controls / audit trail | 20 | Traceable formulas 8; independent reconciliation 8; explicit units and signs 4. |
| Communication | 15 | Decision and numerical headline 5; actions with owners and evidence 5; concise response covering all required deliverables 5. |
Award method credit after an isolated arithmetic error rather than repeatedly deducting for it. Equivalent account labels and well-supported alternative recommendations are acceptable. Numerical tolerance is 0.01 in the stated units; no universal passing score is prescribed.
Common mistakes
Dropping an unmapped amount; converting an invalid date into October without approval; deleting all repeated IDs rather than exact duplicates.
Staged hints
Normalize amount text without changing signs. Compare complete duplicate rows. Reconcile accepted mapped plus unmapped amounts.
Instructor notes
Prerequisites: Basic Excel and CSV imports. Suggested use of the estimated 45 minutes: spend roughly 15% on the lesson and smaller example, 55% on the independent task, 20% on comparing approaches and 10% on the decision discussion. Timing is untested. Ask learners to explain why the numerical check is necessary but not sufficient.
For a simpler class, provide the model structure and work through one driver. For an extension, change one operational assumption and require a new reconciliation and recommendation. Verify the new key before distributing any variant. Open files in the intended spreadsheet application before class. Solutions are learning resources, not secure hiring examinations. Expert review remains pending.
Continue this learning path
XFM-02 · Build an Integrated Three-Statement Model
XFM-03 · Audit a Broken Financial Model
AI-assisted synthetic teaching case. Expert review and native Excel / Sheets testing pending.