Scenario and Sensitivity Analysis Techniques

Why Single-Point Estimates Are Dangerous (The Range Problem)

A DCF model produces a precise number. Your model says the stock is worth $47.32. But that precision is false. You assumed 7% revenue growth, 12% operating margin, and 9% WACC. Change any of those inputs by a small amount and the output moves significantly.

The point is: A 1% change in discount rate can alter valuation by 10-15%. Sensitivity analysis reveals how much your conclusion depends on uncertain assumptions.

Terminal value typically contributes 70-80% of total DCF valuation. If your terminal value rests on assumptions about perpetual growth rates five years from now, small changes ripple through the entire model.

Two Approaches: Sensitivity vs. Scenario Analysis

Sensitivity analysis varies one or two inputs while holding everything else constant. You ask: "What happens to my valuation if WACC moves from 8% to 10%?"

Scenario analysis changes multiple inputs simultaneously to reflect a coherent future state. You ask: "What happens if we enter a recession (lower growth, compressed margins, higher risk premiums)?"

Both are essential. Sensitivity shows you which variables matter most. Scenarios show you what the world might look like and what the stock would be worth in each state.

One-Variable Sensitivity: The Basic Data Table

Start by sensitizing the most impactful variables one at a time.

In Excel:

1. Set up your DCF model with key inputs in named cells
2. Choose the variable to test (e.g., WACC)
3. Create a column of test values (7%, 8%, 9%, 10%, 11%)
4. Link the adjacent cell to your valuation output
5. Select the range, go to Data > What-If Analysis > Data Table
6. For a column input, enter the cell reference of your WACC assumption

The result shows how valuation changes as WACC moves through your test range.

Sample output:

The durable lesson: This table immediately reveals sensitivity. A 4% swing in WACC produces a 67% swing in value. If you are not confident in your WACC estimate, your valuation range is wide.

Two-Variable Sensitivity: The Matrix

The most common two-variable analysis pairs WACC and terminal growth rate because both drive terminal value, which dominates DCF models.

In Excel:

1. Put WACC values across the top row
2. Put terminal growth values down the first column
3. Link the corner cell to your enterprise value output
4. Select the entire range
5. Data > What-If Analysis > Data Table
6. Row input: WACC cell; Column input: growth rate cell

Sample output:

The point is: The corners show extreme outcomes. The center shows your base case. This matrix communicates uncertainty better than any single number.

Key Variables to Sensitize

Not all assumptions deserve equal attention. Focus on variables that:

• Have wide uncertainty ranges
• Significantly impact valuation
• You cannot observe directly

High-impact variables (always test these):

• Discount rate (WACC or cost of equity)
• Terminal growth rate
• Revenue growth rate in key forecast years
• Operating margin trajectory
• Exit multiple (if using multiple-based terminal value)

Medium-impact variables (test if relevant):

• Tax rate assumptions
• Working capital as percent of revenue
• Capital expenditure intensity

Low-impact variables (usually skip):

• Depreciation method
• Interest income on cash

The durable lesson: Terminal value and discount rate assumptions almost always dominate. If your model is not sensitive to revenue growth, something may be wrong with your structure.

Tornado Charts: Visual Prioritization

A tornado chart ranks variables by impact, showing which assumptions matter most.

How to build one:

1. Identify 6-8 key assumptions
2. For each, define a "low" and "high" value (e.g., +/- 1 standard deviation or reasonable bounds)
3. Calculate valuation at each extreme while holding other variables at base case
4. Plot the range (low to high) as a horizontal bar
5. Sort bars from widest (most impactful) to narrowest (least impactful)

Visual interpretation:

The chart looks like a tornado: wide at the top, narrow at the bottom. The variables at the top of the chart deserve the most analytical attention.

Why this matters: If revenue growth drives more value variation than margin assumptions, spend more time researching the sales pipeline than cost structure.

Scenario Analysis: Coherent Futures

Scenarios tell a story. Instead of varying one input arbitrarily, you construct a plausible future and adjust all related inputs accordingly.

Standard scenarios:

The point is: In a recession, multiple things go wrong simultaneously. Revenue falls AND margins compress AND risk premiums rise. Testing one variable at a time misses this.

Building a coherent bear case:

Do not just lower revenue growth by 5%. Ask:

• Why would revenue fall? (market share loss, industry decline, macro weakness)
• What happens to margins if revenue falls? (operating leverage works in reverse)
• What happens to cost of capital if earnings decline? (beta may increase, credit spreads widen)

The durable lesson: Good scenarios tell consistent stories. Bad scenarios mix inputs that would not occur together.

Probability-Weighted Valuation

After building scenarios, assign probabilities and calculate expected value:

Example:

This weighted average provides a single number while acknowledging uncertainty.

Why this matters: If the stock trades at $52 and your expected value is $47.75, the market is pricing in a more optimistic scenario mix than you believe.

Common Errors in Sensitivity and Scenario Analysis

Error 1: Using unrealistic ranges

Varying WACC from 5% to 15% produces dramatic results but is not useful if the realistic range is 8% to 11%. Ground your ranges in historical data and peer comparisons.

Error 2: Ignoring correlations

In reality, revenue growth and margin are often correlated. High growth sometimes comes with lower margins (investment phase) or higher margins (operating leverage). Sensitivity tables that treat them as independent can mislead.

Error 3: Not documenting assumptions

Scenarios must be explained and defended. What macro environment produces the bear case? What company-specific event triggers the bull case? Without documentation, scenarios become arbitrary.

The point is: Sensitivity analysis without context is just math. Scenarios without narrative are just numbers.

Error 4: Varying only one input at a time

Pure sensitivity analysis misses interaction effects. Scenario analysis addresses this by changing multiple variables together, but you must ensure the combinations are internally consistent.

Communicating Results

When presenting valuation work, show the range, not just the point estimate.

Effective presentation:

• "Our base case valuation is $50/share, with a range of $35-65 depending on growth and margin outcomes."
• "The stock trades at $55, pricing in our bull case. If base case materializes, there is 10% downside."
• "The tornado chart shows that WACC and terminal growth together explain 80% of valuation variability."

Avoid:

• "The stock is worth exactly $47.32." (False precision)
• "We ran sensitivity on all 15 inputs." (Information overload)
• "Our bull case assumes everything goes right." (Not a coherent scenario)

Next Steps

1. Open an existing DCF model (or build a simple one) and identify the 5 most impactful input assumptions.

2. Build a two-way data table in Excel with WACC and terminal growth rate. See how wide the valuation range becomes.

3. Construct a tornado chart for your model. Rank which variables drive the most uncertainty.

4. Write out three scenarios (bull, base, bear) with coherent narratives. What macro or company-specific events produce each state?

5. Calculate a probability-weighted expected value. Compare to the current stock price and interpret what the market is pricing in.