Module 06 — Sensitivity, Scenarios & Robustness

Goal

Stress the model and make it audit-proof.

Concepts featured

• 2-variable sensitivity: lender gross yield across SOFR {3.5%,4.0%,4.5%,5.0%,5.5%} × exit month {24,30,36,42,48}. One correctly anchored formula (B$4, $A5) fills the whole grid.
  • Native Data Table: in Excel you can also build this with Data → What-If Analysis → Data Table (row input = SOFR cell, column input = exit-month cell) wrapping a single XIRR output cell. Data tables recalc on open; the live anchored grid here recalculates everywhere and validates in any engine.
• Scenario switch via INDEX/CHOOSE (Base / Downside / Upside) — a switch cell picks the active row. Prefer INDEX over volatile OFFSET.
• Interest-reserve circularity: the loan funds a reserve whose size depends on the funded balance. Solve it algebraically — L = B / (1 − m·rate/12), reserve = L − B — so there is no circular reference. (The alternative is Excel's iterative calculation toggle: File → Options → Formulas → Enable iterative calculation; the workbook's calc settings already carry that flag for reference.)
• Audit drill: find and fix planted errors — a hardcode, a logic slip, a sign error, and a check that should fail.
• Goal Seek (manual): Data → What-If → Goal Seek to back into an input that hits a target metric.

Common mistakes

• Mixed-anchor errors that make a "data table" drift.
• Leaving a live circular reference without enabling iteration (or, better, solving the algebra).
• "Fixing" a failing check by changing the check instead of the input.

Your assignment

Open 06_Sensitivity_ASSIGNMENT.xlsx: build the 2-var grid and scenario switch, solve the interest reserve, and fix the 4 planted errors. Self-Check grades the grid cells, the reserve, and the corrected audit cells.