01 / Learn the method
Learning outcomes
Find planted errors, correct the model and challenge an AI explanation. Calculate the result, reconcile it and communicate its limitations.
Method
Audit business logic as well as spreadsheet error messages. A formula can calculate a plausible number while linking to the wrong driver. Trace outputs to source assumptions, compare independent checks and perturb one input at a time. Treat AI-generated explanations as claims that require evidence.
Smaller worked example
If revenue = units × price, a formula units × unit cost may return a valid number with no Excel error. A price change should move revenue; if it does not, the dependency is wrong.
02 / Put it to work
Business context and rules
Fictional Spruce Models. Use the same one-year source assumptions as XFM-02. The student workbook includes a visible Broken model tab with five deliberately planted formula errors. The clean Analysis area is your repair workspace. Simulated AI comment: “The balance sheet balances, so the model is reliable; higher receivables increase cash, and a fixed profit cell improves stability.” This comment is deliberately unreliable and is not a statement from a real system or reviewer. Identify all errors, including any that mask other errors. Do not treat the intentionally broken tab as a clean output.
Original synthetic inputs
| Input | Value | Unit |
|---|---|---|
| Opening cash | 100 | USD |
| Opening AR | 80 | USD |
| Opening inventory | 60 | USD |
| Opening net PPE | 200 | USD |
| Opening AP | 50 | USD |
| Opening debt | 150 | USD |
| Opening equity | 240 | USD |
| Forecast revenue | 500 | USD |
| COGS / revenue | 0.6 | fraction |
| Cash operating expenses | 100 | USD |
| Depreciation | 20 | USD |
| Cash interest | 10 | USD |
| Tax rate | 0.25 | fraction |
| Closing AR assumption | 100 | USD |
| Closing inventory assumption | 70 | USD |
| Closing AP assumption | 60 | USD |
| Cash capex | 40 | USD |
| Debt repayment | 20 | USD |
Required deliverables
Submit an issue log with cell, incorrect formula, consequence, corrected formula and test; a repaired formula-based model; and a 150-word critique of the simulated AI comment. Retain the Broken model tab for audit evidence. Identify all five planted errors, not just the final balance difference.
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.
Deliberate defects
The student workbook includes a Broken model tab with intentional logic errors. They are part of the assignment. The solution keeps that tab labeled for comparison and supplies corrected formulas in Analysis.
Workbook structure
The student workbook intentionally leaves the analysis structure open. Build the schedules required above, retain the source inputs, and add formula-based checks. You may add worksheets. The worked solution is one acceptable structure, not the only correct layout.
03 / Review your work
Try the assignment before opening the answer.
Open the worked answer and notes
Worked numerical schedule
| Measure | Value | Unit |
|---|---|---|
| COGS | 300.00 | USD |
| EBITDA | 100.00 | USD |
| EBIT | 80.00 | USD |
| Pretax income | 70.00 | USD |
| Tax expense and cash paid | 17.50 | USD |
| Net income | 52.50 | USD |
| Operating cash flow | 52.50 | USD |
| Investing cash flow | -40.00 | USD |
| Financing cash flow | -20.00 | USD |
| Closing cash | 92.50 | USD |
| Closing net PPE | 220.00 | USD |
| Closing debt | 130.00 | USD |
| Closing equity | 292.50 | USD |
| Closing assets | 482.50 | USD |
| Closing liabilities and equity | 482.50 | USD |
| Closing balance sheet residual | 0.00 | USD |
| Opening balance sheet residual | 0.00 | USD |
Interpretation and recommended actions
Five errors are planted: COGS uses the tax rate instead of the COGS rate; depreciation is added to EBITDA instead of subtracted; net income is hardcoded to 60; the AR change has the wrong cash-flow sign; and the balance-sheet check is typed as zero. The corrected outputs match XFM-02: profit 52.5, cash 92.5 and assets 482.5, with genuine zero checks. A zero typed into a check proves nothing. An AR increase consumes cash under the case assumptions, and a profit hardcode disconnects the statements.
Scoring rubric - 100 points
| Dimension | Points | Awarding guidance |
|---|---|---|
| Calculation | 40 | Five errors correctly identified 15 (3 each); five justified repairs 15 (3 each); correct final model 10. |
| 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
Fixing only the visible balance difference; accepting a typed zero; deleting the broken model; accepting the simulated AI explanation without tracing formulas.
Staged hints
Trace COGS to its rate. Inspect the EBIT sign. Change revenue and closing AR independently. Inspect whether the check itself is a formula.
Instructor notes
Prerequisites: XFM-02 or equivalent. The total duration of 90 minutes is a planning estimate, not a tested completion time. Allocate class time according to prior knowledge and reserve time for discussion of the decision and evidence.
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.
Decision
Reject the simulated AI assurance and repair all five underlying defects, then test the corrected dependencies.
Evidence and reasoning
A typed zero is not a control. Wrong rate references, depreciation signs, profit hardcodes and AR cash signs can propagate through several outputs even when a displayed check looks healthy.
What would change the conclusion
Only traced formulas, independently recalculated controls and changed-input tests support accepting the correction. Continue reviewing assumptions even after the arithmetic reconciles.
Related learning
Version 1.1. Synthetic teaching case. Completion time is an estimate.