Sensitivity Analysis

Sensitivity analysis tests how a financial model’s output—typically intrinsic value or valuation multiples—changes when you adjust individual assumptions. It isolates the impact of each variable on your investment thesis, revealing which drivers matter most and where you need higher conviction. Done right, it prevents surprise valuations and uncovers hidden model dependencies.

What Is Sensitivity Analysis

Sensitivity analysis answers a straightforward question: “If this assumption changes, how much does my valuation change?” By testing one variable at a time—holding all others constant—you measure the elasticity of your model to individual inputs.

The key distinction is between sensitivity analysis and scenario analysis. Sensitivity analysis changes one input to see its isolated impact. Scenario analysis bundles multiple assumptions together to model realistic states of the world: a bull case, base case, and bear case. Both belong in a professional model, but they answer different questions.

Sensitivity is particularly valuable in DCF models, where small changes in discount rates, terminal growth, or revenue assumptions can swing valuations by 20–50%. If your model is highly sensitive to a single assumption, you’ve found your key risk. You either need to strengthen conviction in that assumption through due diligence, or accept the wider valuation range it creates.

One-Way Data Tables

A one-way data table is the foundation of sensitivity analysis in Excel. It shows how one output changes as a single input varies across a range. The setup is simple but must be precise.

How to Build a One-Way Data Table
Step 1: In a blank area, list your input values vertically (e.g., WACC ranging from 6% to 12% in 0.5% increments).
Step 2: In the column to the right, one row above the first input value, reference the output cell you want to test (e.g., =I15, if I15 contains your valuation).
Step 3: Select the entire data range: input cells plus the formula reference.
Step 4: Go to Data → Table (or Data → What-If Analysis → Data Table in older Excel versions).
Step 5: Enter the input cell address (e.g., the WACC assumption cell) in the “Column input cell” field. Leave “Row input cell” blank.
Step 6: Click OK. Excel fills the table, showing your output at each input level.

The resulting table instantly shows you that if WACC moves from 8% to 10%, your valuation drops from $45 to $38 per share. That 200 basis point shift creates a $7 swing—material enough to warrant careful assumption-setting.

Two-Way Data Tables

A two-way data table extends the logic to test two variables simultaneously, displayed as a matrix. This is useful when two variables interact or when you want to stress-test the combination most likely to drive valuation swings.

The most common two-way table in equity research tests WACC on one axis and terminal growth rate on the other, with implied share price as the output. This reveals the valuation sensitivity to both the discount rate and long-term growth assumption—the two most contested assumptions in a DCF.

This format makes it easy to see interaction effects. Notice how the valuation spread widens at higher growth rates. A 1% change in WACC costs you $8 per share at 4.5% terminal growth, but only $6 per share at 2% growth. This visual clarity helps you set appropriate assumption ranges and stress-test your thesis against multiple directions of risk.

Key Variables to Test

Not all assumptions deserve equal testing. Focus on drivers with both uncertainty and impact. Here are the core variables across equity models:

Start by testing the variables that define your investment case. If your thesis rests on margin expansion, test EBITDA margin sensitivity first. If you’re modeling a mature business with heavy CapEx, focus on capital intensity and growth. Use the tornado chart (below) to rank sensitivities objectively.

Tornado Charts

A tornado chart visualizes which variables have the biggest impact on your output. Each bar shows the valuation range produced when that variable moves from its low to high assumption—all other variables held at base case.

The construction is straightforward: for each variable, run a one-way data table with its low and high values. Extract the resulting valuations, then calculate the valuation spread (high minus low). Plot these spreads as horizontal bars, sorted from longest to shortest. The longest bars are your key value drivers; the shortest are noise.

The visual resembles a tornado because bars narrow toward the bottom. This shape makes it immediately clear which risks matter. A variable that produces a $5 spread is not worth revisiting; one that produces a $30 spread is a conversation with management or the key point of differentiation in your thesis.

Tornado charts also expose hidden model issues. If an obscure tax assumption ranks in the top three drivers, your model may be overly engineered in the wrong areas, or that assumption is genuinely critical and needs deeper scrutiny.

Interpreting Results

What High Sensitivity Means for Your Investment Thesis
If your analysis reveals that valuation swings wildly with small assumption changes, you face a choice: strengthen your conviction in those assumptions, or lower your price target to reflect the uncertainty. Sensitivity is not a flaw—it’s a diagnostic tool. A model that shows low sensitivity across the board may be poorly constructed or artificially stable. But if three variables drive 80% of valuation range, and you lack conviction in one of them, your upside is conditional. Price accordingly.

Look for threshold effects too. Your model may show that valuation remains stable across WACC 8–10%, then drops sharply at 11%. That 11% level is the breakeven cost of capital; if market conditions could plausibly push WACC to 11%, you have a discrete risk cliff that base-case numbers alone wouldn’t show.

Compare your sensitivity results to peer volatility and market expectations. If your DCF is highly sensitive to terminal growth rate ±0.5%, but the company operates in a stable, mature industry, that sensitivity may indicate your model is overcomplicating a straightforward business. Conversely, if you’re modeling a cyclical or growth-stage company, high sensitivity to macro variables (WACC, growth rate) is expected and acceptable.

Common Mistakes

Avoid These Pitfalls1. Testing unrealistic ranges: If you set WACC sensitivity from 5% to 15%, you’re not stress-testing—you’re guessing. Use market-based ranges. For a US industrial company, WACC typically spans 7–10% depending on leverage and risk profile. Test that band, not fiction.2. Testing correlated variables independently: In reality, recessions don’t just cut growth—they also widen spreads, raising WACC. Testing growth and WACC independently creates a bull-bear fantasy: high growth + low WACC, or vice versa. Use scenario analysis instead to bundle realistic pairings.3. Ignoring non-linear effects: Small sensitivity tables assume linearity. But many relationships aren’t linear. Leverage effects, tax shields, and terminal value fractions can bend at certain assumption levels. Large moves (±5–10% from base) often exhibit non-linearity; run scenario analysis in parallel to catch these.4. Over-relying on one sensitivity measure: A one-way table shows impact but not probability. A variable might produce a wide range but have low probability of extreme values. Combine sensitivity with scenario analysis and stress testing to weight outcomes by likelihood.5. Forgetting to document assumptions: Sensitivity only matters if readers know what was tested and why. Call out your base case, your range, and your justification for each assumption in the writeup. A tornado chart with no supporting text is a red flag.

Key Takeaways

• Sensitivity analysis isolates the impact of individual assumptions on valuation, revealing which drivers matter most and where you need conviction.
• One-way data tables are the core technique: change one input, measure output impact, repeat across a realistic range.
• Two-way tables add depth, testing variable interactions (e.g., WACC vs. terminal growth) and revealing how risk compounds in multiple dimensions.
• Tornado charts rank sensitivities visually, making it clear which variables are key value drivers and which are immaterial.
• Test realistic ranges grounded in market data, not arbitrary bounds. Test correlated variables together in scenarios, not separately.
• High sensitivity signals risk—either model over-reliance on one or two assumptions, or genuine fundamental uncertainty that warrants cautious positioning or deeper due diligence.

Frequently Asked Questions