Sensitivity Analysis

Definition

Sensitivity analysis is a technique for measuring how changes in individual input variables affect a financial model's output, holding all other variables constant. It answers the question "what if this one assumption is wrong?" by systematically varying inputs across a defined range and observing the resulting change in key outputs. The two primary tools are data tables (one-way and two-way grids) and tornado charts (bar charts ranking variables by impact). [src1]

Key Properties

• One-way data table: Varies one input across a range, displays output for each value [src1]
• Two-way data table: Varies two inputs simultaneously creating an output matrix — standard DCF sensitivity [src2]
• Tornado chart: Horizontal bars ranking variables by impact, sorted most-to-least [src3]
• Break-even analysis: Finds the input value where output crosses a threshold (NPV=0, IRR=hurdle) [src1]
• Excel tools: DATA TABLE function, Goal Seek for break-even [src2]

Constraints

• One-at-a-time limitation: Cannot reveal interaction effects or correlated changes — use scenario analysis or Monte Carlo for that. [src1]
• No probability information: Shows what happens if input changes, not how likely that change is. [src2]
• Range selection is subjective: Ranges must reflect realistic uncertainty. [src3]
• Two-variable limit in data tables: Excel data tables max at two variables per table. [src2]
• Assumes linearity: Non-linear models need the full curve, not just endpoints. [src3]

Framework Selection Decision Tree

START — User needs to test model sensitivity
├── How many variables to test?
│   ├── 1 variable → One-way data table or spider chart
│   ├── 2 variables → Two-way data table (standard DCF)
│   ├── 5-10 ranked by impact → Tornado chart
│   └── Many with correlations → Monte Carlo Simulation
├── Need probability information?
│   ├── YES → Monte Carlo
│   └── NO → Sensitivity Analysis (this unit)
├── Variables change independently or together?
│   ├── Independently → Sensitivity Analysis
│   └── Together as scenarios → Scenario Analysis
└── Output format?
    ├── Table/matrix → Data table
    ├── Ranked bars → Tornado chart
    └── Distribution curve → Monte Carlo

Application Checklist

Step 1: Identify key input variables

• Inputs needed: Financial model, list of assumption inputs
• Output: Prioritized list of 5-10 inputs likely to drive output variance
• Constraint: Focus on inputs with both high sensitivity AND high uncertainty [src1]

Step 2: Define realistic ranges

• Inputs needed: Historical data, industry benchmarks, expert estimates
• Output: Min/max range for each input
• Constraint: Ground ranges in data — arbitrary ±50% produces misleading results [src3]

Step 3: Build data tables and tornado charts

• Inputs needed: Model, variables, ranges
• Output: One-way tables, two-way table for top 2 inputs, tornado chart
• Constraint: Use Excel DATA TABLE function for accurate recalculation [src2]

Step 4: Identify break-even points and present

• Inputs needed: Data tables, threshold values
• Output: Break-even values, visual summary
• Constraint: Never show tornado chart without base case reference [src4]

Anti-Patterns

Wrong: Testing only upside sensitivity

Showing only how NPV improves with higher growth and lower WACC creates false confidence. [src1]

Correct: Testing symmetric ranges

Vary each input both above and below base case by the same amount. Show both positive and negative bars. [src3]

Wrong: Using arbitrary ranges for all variables

Applying ±50% to all inputs regardless of actual uncertainty makes stable inputs look as uncertain as speculative ones. [src2]

Correct: Calibrating ranges to actual uncertainty

Use historical standard deviations for market variables, management confidence for operational variables. Each gets its own justified range. [src3]

Wrong: Confusing sensitivity analysis with scenario analysis

Changing revenue, margins, and growth simultaneously and calling it "sensitivity analysis." [src1]

Correct: Keeping variables independent

Change one variable while holding all others at base case. If multiple change together, call it scenario analysis. [src1]

Common Misconceptions

Misconception: The widest bar on a tornado chart is the biggest risk.
Reality: Tornado charts show sensitivity (impact of change), not risk (likelihood of change). A highly sensitive but near-certain variable is low risk. [src3]

Misconception: Two-way data tables show all important interactions.
Reality: They show interaction between exactly two variables. Real models have 10+ uncertain inputs. Monte Carlo captures the full interaction space. [src2]

Misconception: Sensitivity analysis validates a model.
Reality: It tests how outputs change with inputs — not whether the model structure or base assumptions are correct. A flawed model produces consistent but wrong sensitivity results. [src1]

Comparison with Similar Concepts

When This Matters

Fetch this when a user asks about building sensitivity tables, creating tornado charts, what-if analysis, identifying key value drivers, or building data tables for DCF output.