Sensitivity and Scenario Analysis

The model was right until the world moved

A SaaS company modeled European expansion with a positive NPV (net present value, discounted future cash flows minus investment) at a 15% discount rate. The board approved a $6 million go-to-market plan. Six months later, customer acquisition cost (CAC, the average marketing and sales spend to win one paying customer) rose 40% while gross margin held steady. The project was underwater, but nobody had run a disciplined test of which assumptions actually drove the approval. The spreadsheet was not "wrong" in arithmetic. It was fragile: small moves in a few inputs erased the decision.

Sensitivity analysis and scenario analysis are complementary tools for exposing that fragility before capital is committed. Sensitivity asks: "If I change one input while holding others fixed, how much does the output move?" Scenario asks: "If the world follows a coherent story (recession, regulatory shock, share gain), what happens to a bundle of inputs together?" From Lesson 1 on decision trees, you already rollback EMV under stated probabilities. Sensitivity and scenarios answer the follow-up every board should ask: "Which beliefs, if wrong, flip our choice?"

Managers who skip these steps often debate precision in the wrong cells. They refine headcount planning to the single FTE while leaving CAC payback as a hopeful guess. They present one base case as if it were a forecast. They confuse statistical uncertainty (sampling error in a regression from Unit 5) with structural uncertainty (whether the market accepts your pricing). This lesson teaches mechanics and judgment for both.

Sensitivity analysis does not replace decision trees from Lesson 1. It interrogates them. After you rollback EMV and choose Launch Now versus Study First, sensitivity asks whether that choice survives a 10-point move in strong-demand probability or a 20% swing in launch payoff. Scenario analysis asks whether a coherent Downside story makes the optimal branch look reckless even when base-case EMV is positive. Together, trees, sensitivity, and scenarios form the standard toolkit for capital committees: structure (tree), leverage (tornado), and narrative stress (scenarios).

The managerial payoff is focus. A team with forty assumptions in a model cannot research all forty before a vote. A tornado chart that shows three inputs moving NPV by millions while the other twelve move it by thousands tells the CFO where to send analysts this week. Without that ranking, diligence spreads thin and the board debates office lease timing while customer acquisition economics drive the approval.

One-way sensitivity and tornado charts

One-way sensitivity varies a single input across a plausible range while freezing all other assumptions at their base values. Typical inputs include price, unit volume, growth rate, churn, discount rate, wage inflation, and raw material cost. The output might be NPV, IRR (internal rate of return, the discount rate that sets NPV to zero), profit, or EMV from a decision tree.

The managerial output is often a tornado chart: horizontal bars ranked by the swing in output when each input moves from low to high case. The widest bars sit at the top like a tornado funnel. Those inputs deserve deeper research, better data, or contractual hedging. Narrow bars are secondary for decision quality even if they are politically salient (logo redesign cost rarely moves NPV as much as attach rate).

To build one-way sensitivity systematically:

1. Lock a base case with documented inputs.
2. For each input i, define low and high values (often ±10% to ±30%, or p10/p90 from historical data).
3. Recompute output holding all else at base.
4. Record output range; rank inputs by range width.

The tornado chart is not decorative. It is a prioritization device for due diligence. If NPV swings from +$2M to −$1M when churn moves from 3% to 5% monthly, your growth team should spend analyst time on cohort retention, not on refining office rent assumptions.

One-way sensitivity hides interaction effects. Lower price might raise volume (moving two variables together). That is why we also use two-way tables and scenarios. Still, one-way tornado remains the fastest executive summary of leverage.

When linking to decision trees, rerun rollback at low and high values for the single input that ranks first in the tornado. If Study First becomes optimal when strong-demand probability rises from 0.55 to 0.62, you have a decision threshold more actionable than abstract NPV swing. Document that threshold in the memo (preview Lesson 5). Trees supply the decision; sensitivity supplies the belief that would flip it.

Ranges should be defensible, not theatrical. ±50% on price when history shows 8% annual variance invites false drama. Prefer quantile ranges from data (Unit 5 forecasting) or structured expert intervals. Label judgment-based ranges explicitly so the board knows which bars rest on evidence versus hope.

Two-way sensitivity, break-even, and spider charts

Two-way sensitivity tabulates output for combinations of two inputs. Example rows: price; columns: unit volume; cells: Year-3 EBIT (earnings before interest and taxes, operating profit before financing and tax effects). The table reveals nonlinear regions: profit might be positive only in the upper-right quadrant where both price and volume exceed thresholds.

Break-even analysis solves for the input value where a target output crosses zero (or a hurdle). Common forms:

• Break-even volume: units where contribution margin covers fixed costs.
• Break-even churn: maximum monthly churn for unit economics to work.
• Break-even CAC payback months: longest acceptable recovery of acquisition spend.

Break-even turns abstract NPV into operational thresholds operators can monitor. "We approved this plan if monthly churn stays below 4.2%" is auditable each month. Without break-even, teams discover failure late.

Spider charts plot output as each input sweeps from low to high (one line per input). They show curvature and monotonicity. Tornado charts rank total impact at a fixed comparison point. For executive prioritization, tornado usually wins because it answers "where first?" Spider helps analysts see shape.

When presenting two-way tables, highlight cliff edges: regions where small joint moves trigger infeasibility (capacity bind, covenant breach). Optimization lessons in Unit 7 formalize feasible regions; here we stress that cliff edges often appear as kinks in sensitivity tables.

Two-way analysis is essential when inputs move together. A Downside recession story that cuts volume 15% and price 10% simultaneously often produces worse NPV than either one-way low case alone because fixed costs do not flex. Build at least one 3×3 or 5×5 table for the two tornado leaders before major capital release. The extra spreadsheet work prevents the classic error of reporting "volume alone can make NPV negative" while the realistic Downside is far worse.

Break-even algebra should appear in the workbook with a check line. If break-even churn is 7.2% and base NPV at 5% churn is +$500k, plugging 7.2% should yield NPV ≈ $0 within rounding. If not, the search routine or formula is wrong. Managers trust thresholds only when the arithmetic reconciles.

Scenario analysis: coherent stories, not random rows

A scenario is a named future with internally consistent assumptions. Good scenarios read like brief memos, not isolated cells. If "Recession" lowers revenue growth, it usually also raises churn, widens sales cycles, and tightens credit. If "Regulatory shock" adds compliance cost, it may also delay launch and reduce pricing power. Inconsistent scenarios (recession with rising luxury spend and falling churn) confuse stress tests.

A practical set for many business cases:

Assign probabilities if you want expected scenario NPV, but scenarios are valuable even without formal probabilities because they test resilience. Boards often ask: "What happens in Downside?" not "What is the weighted average?"

Link scenarios to decisions: if Downside NPV is negative but Base is positive, maybe stage investment, add milestones, or buy optional capacity instead of committing all $6M Day 1. Scenario analysis should change structure, not only labels.

From Unit 5 regression, recall that extrapolating far outside historical data ranges makes coefficients unreliable. Scenarios that push inputs beyond observed history should be labeled structural stress, not "95% confidence."

Scenario owners improve quality. Assign a name and function to each narrative: CFO owns Downside macro, CMO owns Upside conversion, General Counsel owns Regulatory. Owners defend internal consistency in the meeting instead of the analyst defending every cell alone. The scenario table becomes a governance artifact referenced in quarterly reviews when actuals diverge from Base.

Stress testing, survival, and linkage to decision trees

Stress testing pushes inputs to extreme but plausible values to test survival: 30% revenue drop, key supplier failure, interest rate spike, or loss of top customer. Banks stress capital; startups should stress runway and debt covenants. The question is not average profitability but "Do we remain solvent and compliant?"

Stress tests differ from scenarios in intensity and purpose. Scenarios explore coherent moderate futures; stress tests ask about tails that threaten continuity. Pair stress with decision trees (Lesson 1): a branch with small EMV but catastrophic tail may fail a stress test even when EMV looks acceptable.

Document triggers tied to sensitivity outputs: "If CAC payback exceeds 14 months for two consecutive quarters, pause paid social spend." Triggers convert analysis into governance.

For communication, show sign reversal: which inputs move NPV from positive to negative? The smallest change in that input is a implicit break-even. Rank sign-reversal inputs; they are strategic risks.

Sensitivity also supports negotiation. If a deal works only when supplier price stays below $4.20 per unit, you know the walk-away threshold in talks.

Stress tests should name a survival metric: cash months remaining, debt covenant headroom, or minimum liquidity buffer. NPV can stay positive while cash runs out because timing differs from value. Walk cash month by month in Downside and Regulatory scenarios when the investment is front-loaded. Unit 4 probability tools help estimate tail frequency, but stress testing is often deterministic: assume the bad event happens and ask whether the firm survives.

When a decision tree recommends Fight in litigation (Lesson 1 Harbor example), stress the lose branch explicitly even if EMV prefers fight. Sensitivity on lose probability and award size shows how fast EMV shifts toward settle. The tree and stress test together prevent a single EMV headline from overriding covenant policy.

Presenting sensitivity to executives without drowning them

Executives need three layers, not forty tabs. Layer 1: base output and recommendation. Layer 2: tornado of top five drivers with swing dollars. Layer 3: scenario table (Base, Upside, Downside) and two break-even thresholds operators can monitor. Everything else lives in backup for skeptics.

Avoid false precision: reporting NPV to the dollar when churn is rounded to one decimal signals amateur modeling. Round outputs for slides; keep exact cells in the workbook. Pair every tornado with a sentence of managerial implication: "Year-1 ARR swing dominates; delay EU hiring until DACH pilot ARR exceeds $3.2M."

When two inputs interact, show one two-way table instead of two separate tornado bars that overstate independence. A 3×3 table fits on one slide and communicates joint risk better than prose about "correlation."

Pre-register which sensitivity runs occur before approval versus after launch. Before approval, focus on sign reversal and break-even. After launch, focus on triggers tied to operating metrics (CAC payback, logo churn). That cadence connects to Lesson 5 communication and prevents sensitivity from becoming a post-hoc justification machine.

Data tables, goal seek, and spreadsheet mechanics

Excel Data Table (one-way) automates sensitivity: row input cell = churn, column = empty, output cell = NPV. Excel fills a column of NPV values for each churn assumption. Two-way Data Table uses row input (price) and column input (volume) to populate a grid. Data Tables recalculate the whole workbook; turn off heavy macros during runs.

Goal Seek finds break-even inputs: set NPV cell to 0 by changing churn cell. Goal Seek is one equation, one unknown. It complements algebraic break-even when formulas are too tangled for closed form.

Document base case cell in bold with comment "LOCKED 2026-07-01." Analysts who overwrite base while running sensitivity destroy audit trail.

Solver is not required for one-way sensitivity on NPV models; Data Table is faster for ≤5 inputs. Use Solver when optimization inside each scenario (Unit 7) changes the output surface.

When exporting tornado to slides, show low/base/high NPV labels on each bar end. Executives should read magnitude without opening the model.

Linking regression outputs to sensitivity ranges (Unit 5)

Forecasting lessons estimated demand with confidence intervals. Use prediction interval width to set low/high rows for volume in tornado, not arbitrary ±30%. If regression standard error implies ±12% at 95% on units, document that range. Structural scenarios (new geography) may exceed regression support; label those bars "judgment range."

Coefficient instability matters: if two predictors are collinear, shifting one in one-way sensitivity while holding the other fixed misstates risk. Scenario bundles move correlated marketing inputs together.

Monte Carlo and simulation (orientation)

Monte Carlo simulation draws many joint scenarios from input distributions and computes output histogram. It generalizes one-way and scenario analysis when many inputs move together with specified correlation. Full simulation is beyond this lesson's spreadsheet focus, but the managerial role is clear: tornado tells you what to stress; simulation quantifies probability of negative NPV if you assign distributions.

When data support it, compare break-even threshold to simulated P(NPV<0). A project with positive base NPV but 35% simulated loss probability may fail risk policy even before optimization enters.

Decision trees remain superior when discrete choices and information order matter; simulation excels when continuous inputs drive a cash flow model. Many firms use both: tree for launch timing, simulation for operating variance post-launch.

Practice extension: building a tornado from scratch

Suppose base EBIT is $2.0M. Document low/high for price (±8%), volume (±12%), unit cost (±6%). Recompute EBIT each time holding other base values. Rank swings. Write one sentence per top driver tying to diligence action (supplier quote, pricing test, volume forecast review). This exercise takes 45 minutes in Excel and prevents black-box reliance on consultant charts.

CloudLedger, a fictional B2B accounting platform, evaluates EU expansion. Base case NPV = +$3.2M at 15% discount rate over five years. Finance varies six inputs ±20% (except where noted).

Part A: Base case anchors

Part B: One-way sensitivity (NPV outcome)

Ranked tornado top three: Year-1 ARR, CAC payback, gross margin.

Part C: Break-even on CAC payback

NPV = 0 at CAC payback ≈ 14.8 months (solver/search on model). Base 11 months leaves headroom, but +20% payback (13.2 months) already cuts NPV sharply. Check: base NPV +$3.2M ✓ documented in model header.

Part D: Managerial read

Redirect diligence from precision hiring plans to channel economics and localization impact on churn. Set a board trigger: pause scale spend if CAC payback > 13 months for two quarters.

Two-way follow-up: Finance builds a price × volume table for DACH pilot SKUs. Low price (−10%) with low volume (−15%) yields NPV −$0.6M, worse than either one-way low alone (−$0.1M volume-only at −10% volume in the practice problem spirit). The joint cell justifies stage gate even when base case looks comfortable.

Decision tree link: If Study First EMV from Lesson 1 is within $100k of Launch Now, tornado on Year-1 ARR and CAC payback explains which belief flips the tree. CloudLedger should not treat EU approval as robust when two inputs both rank top three and move together in Downside.

Meridian Retail weighs a private-label launch.

Internal consistency: Downside cuts volume and margin and adds modest cost overrun. Decision: approve with stage gate after pilot region; do not national rollout on Base alone.

Managerial read: Expected NPV with probabilities (0.55 Base, 0.25 Upside, 0.20 Downside) = 0.55(1.4)+0.25(3.6)+0.20(−0.9) = +$1.43M, but Downside survival requires $1M reserve line. Scenario table drives both EMV and risk policy.

Check: probabilities sum 0.55+0.25+0.20 = 1.00 ✓

A project has base NPV +$800k. One-way tests:

• Price −10% → NPV +$200k
• Price +10% → NPV +$1,400k
• Volume −10% → NPV −$100k
• Volume +10% → NPV +$1,500k
• Unit cost −10% → NPV +$1,100k
• Unit cost +10% → NPV +$500k

1. Rank inputs by tornado swing.
2. Which single input can flip NPV negative in the low case shown?
3. If Downside sets price −10% and volume −10% simultaneously, is −$100k a safe lower bound? Explain.

Solution

1. Swings: Volume: 1,500 − (−100) = $1,600k; Price: 1,400 − 200 = $1,200k; Unit cost: 1,100 − 500 = $600k. Rank: Volume, Price, Unit cost.

2. Volume −10% yields −$100k NPV; flips sign alone.

3. Not safe: joint Downside likely worse than volume alone if margins compress when price and volume drop together (fixed cost deleverage). Interaction can push NPV below −$100k; need two-way table or scenario model.

Worked check: Base +$800k documented ✓. Volume low alone −$100k ✓. Price low alone +$200k ✓. Joint Downside estimate: start from base, apply volume shock, then price shock on reduced revenue base, or rebuild full cash flow. Illustrative joint NPV −$350k if 40% of fixed costs do not flex. Conclusion: one-way lows understate Downside; scenario bundle required before approval.

Base churn assumption 5% monthly drives +$500k NPV. Break-even churn is 7.2%. Current measured churn in pilot is 6.8% for three months.

1. Is the project viable on break-even logic?
2. What should the manager monitor next quarter?

Solution

1. Pilot churn 6.8% is below break-even 7.2% but above base 5%. Viable on threshold but not on base; fragility high.

2. Monitor cohort churn trend, acquisition mix changes, and CAC payback (Lesson 1 EMV link). Set trigger at 6.5% rolling average for spend review.

Expanded monitoring plan: Week 1–4: weekly logo churn by cohort; Week 5–8: compare pilot channel mix to base assumption; Month 3: rerun Goal Seek on break-even churn if product shipped two features that affect retention. Document owner (VP Customer Success) in assumption ledger.

Check: break-even 7.2% > pilot 6.8% > base 5% ✓ ordering confirms fragility band.

Key takeaways

• One-way sensitivity ranks which assumptions move outcomes most; tornado charts communicate priority.
• Two-way tables and break-even translate financial models into operational thresholds.
• Scenarios bundle inputs into coherent narratives; stress tests probe survival at extremes.
• Run sensitivity before commitment to focus diligence on high-leverage inputs.
• Pair sensitivity with decision trees and risk policy when tails threaten solvency.

1. Take a model you use today; run one-way ±20% on three inputs and rank swings.
2. Write a Downside scenario with at least three internally consistent input moves.
3. Continue to Lesson 3: Value of Information.