Scenario Model Builder

---
name: scenario-model-builder
description: Use when building a what-if scenario model with toggles, side-by-side scenario columns, and a sensitivity table. Triggers on "scenario model", "what-if model", "sensitivity analysis", "pricing scenarios".
---

# Scenario Model Builder

Build a model that answers "what if" without saving 6 copies of the workbook. The discipline: levers in one tab, formula structure shared across scenarios, outputs comparable side-by-side. The two-input data table is the secret weapon nobody uses.

## Required inputs

1. **The decision** — pricing, hiring, market entry, capex, marketing spend
2. **The levers** — 2-5 inputs that vary across scenarios. Each gets a row in Inputs.
3. **The outputs** — 3-5 metrics that matter. Revenue, profit, headcount, runway, customer count.
4. **Scenarios** — usually 3 named (Conservative / Base / Aggressive, or Bull / Base / Bear). Some users want 4-6. Cap at 6 — beyond that, no one compares meaningfully.
5. **Sensitivity dimensions** — pick 2 of the levers for the data table. The two with the most uncertainty + impact.
6. **Time horizon** — usually annual or 3-year. Scenarios over time can get unwieldy; consider summary view.

If the user gives you 8 levers, push back. A scenario model with 8 levers has 256 combinations. Pick 3-5 levers; everything else is in Assumptions and held constant.

## Approach

**openpyxl** with formulas. The architecture is critical: Inputs tab has columns for each scenario (Conservative, Base, Aggressive), Output tab has the same column structure, formulas in Output reference Input by relative column position. Drag the formula across; each column auto-pulls its scenario's inputs.

The **Excel Data Table** (`What-If Analysis → Data Table`) is what builds the 2D sensitivity grid live. openpyxl can write the data-table reference; the user re-triggers calc on open. Alternative: pre-compute the grid in pandas and write static values, sacrificing live interactivity for portability.

## Workbook structure

1. **Cover** — model purpose, scenario names + descriptions, what each lever means, owner, version
2. **Inputs** — levers down rows, scenarios across columns. Each lever has a description and a unit. Assumptions held constant in their own section below.
3. **Build** — the calc tab. Same column structure as Inputs (one column per scenario). Formulas reference `Inputs!B5` for Conservative, `Inputs!C5` for Base, etc. — same row, different column.
4. **Outputs** — the comparison view. Outputs down rows, scenarios across columns. Pulls from Build. Adds a "Delta vs Base" row for each output.
5. **Sensitivity** — a 2D Data Table. One driver across the top, another down the side, the cell value = a chosen output. Conditional format as a heatmap.
6. **Charts** — bar chart of each output across scenarios; the sensitivity heatmap.

## Formula principles

- **Same formula structure across scenario columns.** `=Build!B5*Inputs!B$2` works in column B; copy to column C and it becomes `=Build!C5*Inputs!C$2` automatically. This is why you align column-by-column.
- **Inputs use absolute row references, relative columns** ($-on-row, no-$-on-column).
- **Named ranges still apply** for assumptions held constant — but scenario-varying inputs get column-relative refs by design.
- **Delta vs Base row**: `=Output_Scenario - Output_Base` for each output, % change too. The interpretive layer.
- **Data Table for sensitivity**: pick a single output cell as the formula reference. Excel computes the grid by substituting row/column header values into the input cells. Powerful — most users have never set one up.

## The Inputs tab — getting it right

Layout:
```
                  Conservative   Base   Aggressive
Price             $80            $100   $120
Churn (mo)        7%             5%     4%
CAC               $500           $400   $350
New customers/mo  50             80     120
```

Use blue fill on input cells to signal "this is a lever." Use cell comments to describe each lever. Bold the Base column header.

For the sensitivity table, the user picks 2 levers (e.g., Price and Churn). The data table varies those across a grid. Other levers stay at Base values during sensitivity.

## Common pitfalls

- **Each scenario in its own tab.** You'll diverge formulas. Same tab, columns for scenarios, shared formula structure.
- **Hardcoded values in the Build tab.** A coefficient typed in `=Customers*0.6` for gross margin should be on Inputs and reference the scenario column.
- **Sensitivity table that doesn't recompute.** If you build it as static values, label it static and add the year/version. Otherwise the user will assume it's live.
- **Outputs that don't actually move across scenarios.** If your Aggressive case shows the same EBITDA as Base, your formulas aren't picking up the scenario column — check refs.
- **Too many scenarios.** Five "small variations" is harder to interpret than three meaningful ones. Force the user to declare what's materially different.
- **No "what's the same across all scenarios" tab.** Assumptions held constant should be listed somewhere obvious so the audience doesn't ask "what about market growth — is that flexed too?"

## Common scenarios to model

- **Pricing**: price × volume × churn impact
- **Hiring**: headcount × productivity ramp × revenue per head
- **Marketing spend**: budget × CAC × LTV
- **Market entry**: TAM × share × cost to enter
- **Macro**: revenue growth × cost inflation × headcount freeze

## Output

Generate the .xlsx. Print: scenario list with descriptions, key output deltas (e.g., "Aggressive ARR is 38% higher than Base; Conservative is 22% lower"), and the sensitivity table's two dimensions. End with a one-line callout on which lever has the biggest output swing per unit change — that's the variable to focus negotiation/decision on.