Analysis & Decision Tools

Three recipes that attach to any model with a live output cell: sensitivity tables, scenario manager, Monte Carlo. Shared mechanics in references/build-engine.md. The recurring tension here is native Excel What-If structures (which openpyxl can write but not populate) vs code-computed grids (which are populated but static) — build both where it matters.

---

Sensitivity Tables (sensitivity-tables)

One- and two-variable Data Tables showing how a single output (NPV, IRR, EPS, margin) moves as one or two drivers vary.

When: "sensitivity", "data table", "how does X move if Y changes", "tornado-style range".

Tabs: a Sensitivity tab attached to an existing model, plus a Checks block.

Prerequisite: the output must be a live formula chain back to the input cells. If the output is hardcoded, stop and wire the model first — a Data Table over a constant returns the constant.

One-variable block (inputs down a column):

• Corner B3 =Model!Output; swept input values A4:A10 (blue); results column B4:B10.
• Native: select A3:B10 → Data Table with Column input cell = Model!Input; Excel writes {=TABLE(,Model!Input)}. openpyxl writes the text; values populate only on recalc.
• Code-computed fallback (recommended): for each input value, set the cell in Python, recompute the model logic, write the resulting output as a plain value to a labelled matrix below. Keep both: native proves liveness, computed guarantees numbers.

Two-variable block:

• Corner E3 =Model!Output; row-input values down the left edge E4:E10 (driver 1); column-input values across the top F3:L3 (driver 2).
• Native: Data Table with Row input cell = the across driver, Column input cell = the down driver → {=TABLE(across, down)}. Watch orientation — swapping the two transposes the grid silently.
• Code-computed fallback: double loop over both axes writing a value matrix.

Axis ranges: centre each axis on the base case so the base sits in the middle row/column; use an odd step count (5/7/9) so a single middle cell lines up with the base; uniform increments; sweep rates in absolute points (8/10/12%), not relative.

Checks: corner = Model!Output (TRUE); axes labelled with driver names & cell addresses; centre grid cell = current output (base sits in the middle); computed fallback base cell = native cell at the same inputs (within rounding). Apply a 3-colour scale.

Inputs: source model + exact output cell; the one or two input cells; base values + range & step per axis; units/formats.

Example: Model!B40 NPV driven by discount rate (base 10%) and growth (base 3%); one-var sweeps rate 6–14% by 2%; two-var crosses growth 0–6% × rate 6–14%; centre cell at 3%/10% equals Model!B40. Illustrative.

---

Scenario Manager — Base/Bull/Bear (scenario-manager)

A visible, auditable scenario toggle: a selector drives CHOOSE/INDEX assumption pulls, with a side-by-side comparison of all three cases.

When: "scenario toggle", "Base/Bull/Bear", "show the downside", "switch cases".

Tabs: Cover (selector + summary) · Scenario Inputs · live links into the model · Outputs (comparison) · Checks.

Cover: B2 selector with data-validation list 1,2,3; B3 =CHOOSE(B2,"Base","Bull","Bear"); a small summary reading the live output under the active case.

Scenario Inputs (one row/driver, one column/case): A driver, B/C/D Base/Bull/Bear (blue), E active =CHOOSE(Cover!$B$2,B5,C5,D5) (or =INDEX(B5:D5,Cover!$B$2) — cleaner if cases may grow). Copy the one chosen formula down every driver row.

Links into the model: each model assumption cell points at the active value =ScenarioInputs!E5 (green link). Never let the model hold a typed assumption a scenario should control — value flows selector → active column → model.

Outputs (show all three at once, regardless of selector):

• Parallel calc (preferred): replicate the few output formulas three times, each fed directly by the Base, Bull, or Bear column (...!B5:B12, ...!C5:C12, ...!D5:D12), not the active column. Layout: rows = key metrics, columns = Base/Bull/Bear + Bull−Base and Bear−Base deltas.
• Read-each (large models): capture each case's live output by toggling the selector during recalc; label clearly as captured snapshots vs live.

Checks: selector in 1–3 and integer; every driver has all three cases (COUNTBLANK=0); active value matches the selector's case. Directional sanity: Bull should not produce a worse headline than Base, Bear not better — a violation means two case columns are swapped.

Trade-off vs Excel's built-in Scenario Manager: the built-in stores values in a hidden dialog, overwrites live inputs, and desyncs easily. A visible toggle keeps every case on the grid, makes the active pull a traceable formula, shows all three outcomes at once, and survives copy/review/version control.

Inputs: model + assumption cell per driver; Base/Bull/Bear value per driver; output metrics + cells; parallel-calc vs snapshots.

Example: growth (4%/8%/1%), gross margin (55%/58%/50%); selector 2 → Bull, E5 =CHOOSE(2,...) = 8% flows to Model!B6; Outputs show NPV 760/1,140/410 with a Bull−Base delta 380, all visible at once. Illustrative.

---

Monte Carlo (no add-in) (monte-carlo)

Simulate thousands of trials of an uncertain output and summarize the distribution — no Crystal Ball, no @RISK.

When: "Monte Carlo", "distribution of outcomes", "probability of", "risk simulation".

Tabs: Cover (run settings + summary) · Assumptions (each uncertain input + distribution) · Simulation (trial engine) · Results (trials, percentiles, histogram) · Checks.

Assumptions (one row/uncertain input, blue parameters):

• Normal: mean, sd → =NORM.INV(RAND(),mean,sd).
• Uniform: min, max → =min+(max-min)*RAND().
• Triangular: min, mode, max → with u=RAND(), Fc=(mode-min)/(max-min): =IF(u<Fc, min+SQRT(u*(max-min)*(mode-min)), max-SQRT((1-u)*(max-min)*(max-mode))).
• Correlation caveat: independent RAND() draws assume uncorrelated inputs; if drivers move together (price & volume, rate & default) independent sampling understates tail risk — flag it and drive correlated inputs from a shared factor.

Simulation — native path: live draw cells (one per input) feed the model; the output =Model!Output is the corner of a one-variable Data Table whose Column input cell points at an unused scratch cell. Each trial row forces a recalc, reseeding RAND(), so captured outputs differ per row → {=TABLE(,scratch)}. Populates only after a real-engine recalc.

Simulation/Results — numpy path (recommended via code): sample each distribution N times with numpy (np.random.normal/triangular/uniform), evaluate the output vectorized across all N trials, write the N outputs as plain values. Deterministic, avoids volatile RAND() churn. Seed with np.random.default_rng(seed) and note the seed on Cover.

Results: percentiles =PERCENTILE.INC(trials,0.05) (P5/P10/P50/P90/P95); =AVERAGE mean, =STDEV.S sd; threshold probability =COUNTIF(trials,"<"&hurdle)/COUNT(trials); histogram via FREQUENCY (or numpy counts) → BarChart.

Trial count: percentile standard error shrinks ~with √N, so 1,000→10,000 roughly halves the noise; lean to 10,000 for tail percentiles and small threshold probabilities; state the count on Cover.

Checks: trial count stated & matches (COUNT(trials)=Cover!B3); all summary stats present; percentiles monotonic (P5≤P10≤P50≤P90≤P95); histogram present. Distribution sanity: confirm sampled means/spreads match parameters (a triangular mode outside min/max, or negative sd, is a common error); for zero-bounded inputs (volumes, prices) prefer triangular or a truncated draw over a wide normal that can sample negatives. Note: sampled trial outputs are written as values, not formulas; percentile/probability cells stay as live formulas over the trial range.

Inputs: model + output cell + each uncertain input cell; a distribution & parameters per input; trial count + any correlation; threshold(s) for the probability metric.

Example: NPV with growth ~Normal(4%,2%) and gross margin ~Triangular(50%,55%,58%); 10,000 trials → P5 210, P50 760, P95 1,430, mean 770, sd 380, P(NPV<0) 6.5%; right-skewed histogram, monotonic percentiles. Illustrative.