One- and Two-Way Sensitivity
Valuation models rest on dozens of assumptions: growth rates, margins, capital expenditure, discount rates, and terminal value drivers. If you're wrong about even one, your intrinsic value estimate shifts. Sensitivity analysis quantifies exactly how much. A one-way sensitivity table shows how a single assumption change impacts valuation. A two-way table reveals interactions between two assumptions, producing a matrix of outcomes. Together, they identify which assumptions most influence intrinsic value, directing focus to the most critical estimates.
Quick definition: Sensitivity analysis tests how changes in input assumptions affect valuation output, producing tables that map the range of intrinsic values across varying inputs.
Key takeaways
- One-way sensitivity isolates each assumption, showing valuation at ±10%, ±20%, ±30% changes relative to your base estimate
- Two-way sensitivity reveals assumption interactions, displaying how two variables simultaneously affect the final output
- Critical assumptions are those where small changes cause large valuation swings; focus diligence on these
- Non-material assumptions have minimal impact on value; you can afford to be less precise about them
- Breakeven assumptions identify the threshold values at which a stock transitions from undervalued to overvalued
- Communication tool sensitivity tables make your analysis transparent, showing investors exactly which assumptions drive your thesis
One-way sensitivity tables
A one-way sensitivity table tests a single input across a range while holding all other assumptions constant at base case levels. This isolates the specific variable's impact.
Setup:
- Identify the assumption to test (e.g., WACC, terminal growth rate, Year 1 revenue growth)
- Create a column with test values: typically base case ±10%, ±20%, ±30%, or ±50%
- Reference your valuation output (intrinsic value per share) in each row
- Use formulas to substitute the test value while keeping all other assumptions constant
Example: WACC sensitivity for a DCF model
Base Case WACC: 8.0%
WACC Intrinsic Value
6.0% $18.50
6.5% $16.20
7.0% $14.80
7.5% $13.40
8.0% (Base) $12.00
8.5% $10.90
9.0% $9.95
9.5% $9.15
10.0% $8.45
This table reveals that a 1% change in WACC alters intrinsic value by roughly $1.00–$1.50 per share. For this company, discount rate assumptions matter enormously. If you're uncertain about WACC—whether it's 7% or 9%—your valuation range is $9.95 to $14.80, a ±23% spread around the $12 base case.
Building the formula:
In Excel, create the sensitivity table using an indirect reference that swaps the test assumption into your valuation model:
Setup:
A1: WACC Test Value
B1: Intrinsic Value
A2: 6.0%
A3: 6.5%
... (continue through 10.0%)
B2: =IFERROR(
NPV(A2, Cashflows!B5:B14) + (Terminal_Value / (1 + A2)^10)
/ Shares_Outstanding,
"Error")
Or, if your model structure has a specific cell (e.g., Assumptions!C5) for WACC, use:
B2: =INDEX(
INDIRECT("Base_Case!"&ADDRESS(MATCH(Intrinsic_Value_Cell, ...), ...)),
1)
More practically, create a separate "Sensitivity" worksheet where you duplicate your valuation logic but reference the test values:
[Sensitivity sheet]
C5: =A2 (pulls the test WACC from column A)
D50: [DCF formula referencing C5 for discount rate]
B2: =D50 (returns the sensitivity-test valuation)
Expanding to multiple one-way tables:
Create separate one-way tables for your most uncertain assumptions:
- Terminal growth rate (often highly uncertain): 0%, 0.5%, 1.0%, 1.5%, 2.0%, 2.5%, 3.0%, 3.5%, 4.0%
- Year 1 revenue growth (near-term execution): -5%, 0%, 5%, 10%, 15%, 20%
- Year 5 operating margin (long-term profitability): 12%, 14%, 16%, 18%, 20%, 22%
For each, note the base case value and the valuation at each test level. This helps investors and you identify which assumptions deserve the most attention.
Two-way sensitivity tables
A two-way table tests two assumptions simultaneously, showing how their interaction affects valuation. The matrix format reveals whether assumptions are independent or correlated.
Setup:
- Choose two key assumptions (typically those with highest uncertainty or impact)
- Create row headers for one assumption's range (e.g., terminal growth: 0%–4%)
- Create column headers for the second assumption's range (e.g., WACC: 6%–10%)
- In each cell, calculate intrinsic value using the row and column inputs
Example: WACC vs. Terminal Growth Rate
Intrinsic Value per Share (Base Case with Varied Assumptions)
Terminal Growth → 6% 7% 8% 9% 10%
0.5% $8.20 $7.35 $6.75 $6.25 $5.85
1.0% $9.40 $8.35 $7.60 $6.95 $6.45
1.5% $10.90 $9.55 $8.55 $7.75 $7.10
2.0% $12.80 $11.05 $9.75 $8.75 $7.95
2.5% $15.20 $12.90$11.20 $9.95 $8.95
3.0% $18.50 $15.50$13.20 $11.50 $10.20
Interpreting the matrix:
- Reading vertically (down a column): As terminal growth increases, intrinsic value increases across all WACC levels
- Reading horizontally (across a row): As WACC increases, intrinsic value decreases across all terminal growth levels
- Intersection cells show combined effects: At WACC 8% and terminal growth 2.5%, intrinsic value is $11.20
The table also shows where breakeven levels exist. If the current stock price is $10, several assumption pairs justify that valuation: WACC 8% with terminal growth 2.3%, or WACC 9% with terminal growth 2.8%, etc.
Building the formula in Excel:
- Create the row/column headers as shown above
- In the top-left cell, reference a base valuation:
=Base_Case!D50 - In each data cell (e.g., B2 for WACC 6%, terminal growth 0.5%), use:
=IFERROR(
NPV($A2, Cashflows!B5:B14) +
Terminal_Value_Formula(Column_Header_Row / (1 + B$1)^10)
/ Shares_Outstanding,
"N/A")
Alternatively, use Excel's Data Table feature:
- Lay out your row/column headers
- In the top-left cell, reference your valuation output
- Select the entire table range
- Go to Data > What-If Analysis > Data Table
- Set "Row input cell" to your WACC assumption cell, "Column input cell" to your terminal growth assumption cell
- Excel automatically fills the matrix with valuations for every combination
Interpreting sensitivity results
High sensitivity (steep slopes in tables): The assumption drives valuation significantly. Small errors in estimation lead to large valuation errors. Example: If WACC sensitivity shows $5 intrinsic value at 10% vs. $18 at 6%, WACC is critical. Spend time validating your WACC estimate using CAPM, peer comparisons, and historical cost of debt.
Low sensitivity (flat slopes in tables): The assumption matters little to final valuation. You can afford less precision. Example: If working capital changes vary valuation by only $0.20 per share, less rigorous estimation is acceptable.
Assumption interactions (non-linear patterns in two-way tables): Some assumption pairs reinforce or offset each other. Example: High revenue growth + low WACC interact positively (rapid growth discounted slowly = high value), while high revenue growth + high WACC interact negatively (growth discounted heavily = moderate value). These interactions can surprise you.
Tornado charts: visualizing sensitivity
A tornado chart ranks assumptions by their impact on valuation, showing which assumptions matter most at a glance.
Build a tornado chart:
- Calculate the valuation impact of each assumption at ±1 standard deviation or ±10% from base case
- For each assumption, compute: High case value − Low case value = Sensitivity range
- Rank assumptions by range size (largest at top)
- Create a horizontal bar chart where each assumption's bar width represents its sensitivity range
- The chart resembles a tornado (wider at top, narrower at bottom)
Example tornado chart:
Assumption Low Value High Value Range
Terminal Growth $10.20 $14.80 $4.60 ← widest bar (highest impact)
Year 1–5 Revenue $9.50 $15.20 $5.70 ← widest bar
WACC $9.95 $15.10 $5.15
Operating Margin $11.40 $13.20 $1.80
Tax Rate $11.85 $12.25 $0.40 ← narrowest bar (lowest impact)
CapEx Intensity $11.95 $12.10 $0.15 ← narrowest bar
The tornado immediately shows that revenue growth and WACC drive valuation, while tax rate and CapEx have minimal impact. This directs your diligence effort: spend time validating revenue, operating margins, and discount rate assumptions. Less scrutiny needed for tax rate (assuming it's stable) and CapEx (assuming it's well-disclosed).
Real-world example: Valuing a mature energy company
Consider an integrated oil & gas company. Base case assumptions:
- WACC: 7.5%
- Terminal growth: 1.0% (mature, dividend-focused)
- Year 1–5 average production growth: 2% annually
- Crude oil price assumption: $60/barrel (conservative long-term)
Build a one-way sensitivity table for crude oil price, the most uncertain assumption:
Crude Oil Price Intrinsic Value Change from Base
$40/bbl $28.50 -52%
$50/bbl $32.10 -32%
$60/bbl (Base) $42.00 0%
$70/bbl $51.90 +24%
$80/bbl $61.80 +47%
$100/bbl $81.60 +94%
This shows that intrinsic value swings from $28.50 to $81.60 depending on oil price—a 187% range. For this company, oil price is the critical assumption. If the stock trades at $45, it's fairly valued if you assume $61/barrel, overvalued if you believe $50/barrel is more likely, and undervalued if you forecast $70+/barrel.
Now create a two-way table: crude oil price vs. production growth:
Intrinsic Value (Mature Energy Company)
Oil Price → 1% Growth 2% Growth 3% Growth 4% Growth
$50/bbl $28.40 $32.10 $36.20 $40.90
$60/bbl (Base) $35.50 $42.00 $49.80 $58.90
$70/bbl $42.60 $51.90 $63.40 $77.00
$80/bbl $49.70 $61.80 $77.00 $95.10
The matrix reveals that oil price matters more than production growth (wider spread horizontally than vertically). At $60/bbl, varying production growth by 3% points (1% to 4%) changes value by $23 per share. At 1% production growth, varying oil price by $20/barrel (from $60 to $80) changes value by $14 per share. This suggests focusing diligence on long-term oil price forecasts rather than optimizing production growth projections.
Common mistakes
Mistake 1: Sensitivity ranges that are unrealistic Testing WACC from 0% to 30% isn't credible. Narrow ranges to plausible scenarios: for a stable company, test WACC ±1.5% from base case (e.g., 6.0%–9.0%). For a high-growth company with execution risk, test ±2–3% (e.g., 6.5%–11.5%). Be realistic about what assumptions are actually uncertain.
Mistake 2: Forgetting to test correlated assumptions In optimistic scenarios, growth and margins typically both expand. Don't test 15% revenue growth with 12% operating margins—that pairing is unlikely. Use two-way tables to test correlated assumptions (e.g., growth + margin, or growth + discount rate).
Mistake 3: Too many sensitivity tables A sensitivity table for every assumption becomes noise. Focus on the 3–5 most uncertain or impactful assumptions. Use one-way tables for these, then create a two-way table for the top two assumptions.
Mistake 4: Not updating sensitivity tables when assumptions change If you've just updated your WACC estimate, regenerate one-way sensitivity tables around the new base case. Stale sensitivity analysis misleads investors about current valuation ranges.
Mistake 5: Ignoring negative or zero values in sensitivity If testing terminal growth produces negative valuations (e.g., terminal growth of -2% or -5%), include those results. They're implausible but demonstrate how sensitive valuation is to that assumption. If the table shows negative values at realistically possible inputs, reconsider your model assumptions.
FAQ
Q: How wide should my sensitivity ranges be? A: Use ±10%, ±20%, ±30% as a standard, or test a range from historical lows to optimistic highs. For WACC, test ±1.5 percentage points. For growth rates, test ±5 percentage points. Adjust ranges based on assumption uncertainty; highly uncertain assumptions warrant wider ranges.
Q: Should I include the base case in the middle of my sensitivity table? A: Yes, always. This anchors the table and makes it clear which test values are above vs. below your estimate. Include the base case explicitly so readers understand the reference point.
Q: How do I choose which two assumptions to pair in a two-way table? A: Pair the two most uncertain or most impactful assumptions from your one-way tables. Alternatively, pair assumptions you suspect are correlated (e.g., revenue growth and operating margin often move together). A two-way table for WACC and terminal growth is nearly always useful; these are typically the highest-impact, hardest-to-estimate inputs.
Q: Can I create a three-way sensitivity table? A: Not effectively in 2D Excel—a 3D table is hard to visualize and interpret. Instead, create two separate two-way tables: one for Assumption A + B, another for Assumption A + C. This is cleaner and more readable.
Q: What if sensitivity testing shows my valuation is extremely sensitive to one assumption? A: That's valuable information. It means you need to be very precise about that assumption, or your valuation is unreliable. Spend diligence time validating it, or state clearly in your report: "This valuation is highly sensitive to terminal growth rate; we estimate 2.5% but note that 2.0% would reduce intrinsic value by 20%."
Q: Should sensitivity tables include best-case and worst-case scenarios? A: Sensitivity tables typically show a range around your base case (±10% to ±50%). Separate scenario analysis tables capture best, base, and worst cases with full narrative coherence. Use sensitivity for assumption precision, scenarios for strategic outcomes.
Related concepts
- Scenario analysis combines multiple assumptions into coherent narratives (pessimistic, base, optimistic). Sensitivity analysis isolates individual assumptions' impacts.
- Tornado charts visualize sensitivity by ranking assumptions by impact, making the most critical drivers obvious at a glance.
- Breakeven analysis identifies the assumption threshold at which a stock transitions from undervalued to overvalued—essentially reading sensitivity tables backward.
- Monte Carlo simulation extends sensitivity analysis by testing thousands of random assumption combinations simultaneously, producing a probability distribution of valuations.
- Value drivers are the assumptions with highest sensitivity impact; identifying them focuses your research and due diligence effort.
- Model auditability improves when sensitivity tables document your assumption ranges and impacts; external reviewers can replicate your work.
Summary
Sensitivity analysis quantifies how changes in assumptions affect valuation. One-way tables test single assumptions in isolation, revealing which have the highest impact. Two-way tables test assumption interactions, showing how pairs of variables combine to drive value. Tornado charts rank assumptions by sensitivity impact, directing diligence effort to the most critical estimates. Together, sensitivity tables make your analysis transparent and help investors understand the valuation range conditional on different assumption combinations.