Scenario Tables in Excel
Professional valuations never rest on a single set of assumptions. Markets shift, companies execute poorly or exceed expectations, and macroeconomic conditions change. Scenario analysis quantifies how valuation responds to different futures—pessimistic (downside), realistic (base case), and optimistic (upside). Excel's scenario tools and data tables let you organize and compare these outcomes side by side, turning assumption variations into actionable probability-weighted valuations.
Quick definition: Scenario analysis projects intrinsic value under multiple assumption sets, revealing the valuation range and helping investors understand downside risk and upside potential.
Key takeaways
- Three-scenario modeling (pessimistic, base, optimistic) captures the range of plausible outcomes and quantifies risk asymmetry
- Scenario tables isolate impact by holding most assumptions constant while varying a few key drivers, clarifying cause and effect
- Probability-weighted intrinsic value combines scenarios into a single expected value, useful for portfolio decisions
- Side-by-side comparison reveals how margin assumptions, growth rates, and discount rates affect final valuation differently
- Breakeven analysis identifies the assumption thresholds at which a stock becomes undervalued or overvalued
- Narrative alignment ties each scenario to a coherent market or company story, not arbitrary numbers
Designing your scenario framework
Before building tables, define three distinct scenarios aligned with your investment thesis:
Pessimistic Scenario (Downside)
- Revenue growth slows significantly (e.g., 2–4% annually)
- Operating margins compress due to competition or cost inflation
- WACC increases (higher risk premium reflects uncertainty)
- Terminal growth near 0%, reflecting market-share losses
- Use case: The company faces technological disruption, regulatory headwinds, or execution risks
Base Case (Expected)
- Revenue grows at historical average plus modest adjustments (5–8% annually)
- Margins stabilize near long-term averages or gradual improvement
- WACC reflects current market conditions and company risk profile
- Terminal growth aligns with long-term GDP growth (2–3%)
- Use case: Management guides successfully, market conditions remain stable, competitive position holds
Optimistic Scenario (Upside)
- Revenue growth accelerates (10–15% annually) due to market expansion or market-share gains
- Operating margins expand toward best-in-class levels
- WACC declines as company de-risks through scale and operational improvement
- Terminal growth reflects sustained competitive advantages (3–4%)
- Use case: Product innovation succeeds, market adoption accelerates, management execution exceeds expectations
These scenarios should reflect your honest assessment of the company's probability distribution, not wishful thinking or worst-case extremes. Pessimistic doesn't mean bankruptcy; optimistic doesn't mean doubling revenue forever.
Building separate scenario worksheets
Organize your model with one worksheet per scenario plus a summary sheet:
Worksheets:
├── Summary (comparative metrics)
├── Pessimistic (downside assumptions & DCF)
├── Base_Case (expected assumptions & DCF)
├── Optimistic (upside assumptions & DCF)
└── Inputs (shared reference data)
Each scenario worksheet contains:
- Assumption section: Revenue, margin, CapEx, working capital, discount rate, terminal growth
- Calculation section: Revenue projections, margin walkdowns, free cash flow builds
- Valuation section: DCF calculation, enterprise value, equity value, price per share
Use consistent cell references across scenarios. If pessimistic-case revenue is in A5, base-case revenue should also be in A5 on its worksheet. This consistency simplifies formulas that reference across scenarios.
[Pessimistic sheet]
A5: Base Year Revenue | 100
A6: Year 1 Growth | 2.0%
A7: Year 2 Growth | 3.0%
[Base_Case sheet]
A5: Base Year Revenue | 100
A6: Year 1 Growth | 6.0%
A7: Year 2 Growth | 7.0%
[Optimistic sheet]
A5: Base Year Revenue | 100
A6: Year 1 Growth | 12.0%
A7: Year 2 Growth | 14.0%
Creating a scenario summary table
On a "Summary" worksheet, pull valuation outputs from each scenario into a comparison table:
Scenario Comparison Table
Pessimistic Base Case Optimistic
Revenue Year 5 ($M) 145 245 385
EBITDA Margin Y5 18% 22% 26%
FCF Year 5 ($M) 22 48 78
Terminal Growth Rate 0.5% 2.5% 3.5%
WACC 9.5% 8.0% 7.0%
Enterprise Value ($M) 320 650 1,200
Less: Net Debt ($M) 50 50 50
Equity Value ($M) 270 600 1,150
Shares Outstanding (M) 50 50 50
Intrinsic Value per Share $5.40 $12.00 $23.00
Upside/Downside vs. Price -55% +20% +92%
This table immediately shows investors the range of outcomes. If current stock price is $10:
- Downside scenario (20% probability): $5.40 implies -46% downside
- Base case (50% probability): $12.00 implies +20% upside
- Upside scenario (30% probability): $23.00 implies +130% upside
Expected value = (0.20 × $5.40) + (0.50 × $12.00) + (0.30 × $23.00) = $13.38 per share
Multi-scenario data tables for sensitivity
Excel's Data Table feature allows you to vary two inputs simultaneously across scenarios. This is especially useful for testing how margin and discount-rate assumptions drive valuation across multiple scenarios.
Example: Create a 2-way table showing intrinsic value sensitivity to WACC and terminal growth rate for the base-case scenario:
-
Create a layout:
Input 1 (rows): Terminal Growth Rate (0.5%, 1.5%, 2.5%, 3.5%, 4.5%)
Input 2 (cols): WACC (6%, 7%, 8%, 9%, 10%)
Output: Intrinsic Value per Share -
In the row header cells, enter your WACC values: 6%, 7%, 8%, 9%, 10%
-
In the column header cells, enter terminal growth: 0.5%, 1.5%, 2.5%, 3.5%, 4.5%
-
In the top-left corner, reference the base-case intrinsic value:
=Base_Case!D50 -
Select the entire table range
-
Go to Data > What-If Analysis > Data Table
-
Set Row input cell to the WACC cell in Base_Case, Column input cell to the terminal growth cell
-
Excel fills the table with intrinsic values for every combination
Intrinsic Value Sensitivity Table (Base Case)
6.0% 7.0% 8.0% 9.0% 10.0%
0.5% $9.20 $8.15 $7.45 $6.85 $6.35
1.5% $10.50 $9.20 $8.30 $7.60 $7.00
2.5% $12.00 $10.50 $9.30 $8.50 $7.80
3.5% $14.10 $12.00 $10.60 $9.50 $8.70
4.5% $16.80 $14.10 $12.20 $10.80 $9.70
This table reveals which assumptions matter most. If the stock price is $10, the table shows all assumption combinations that justify that valuation—your "breakeven" assumptions.
Waterfall analysis: from pessimistic to optimistic
Create a visual bridge showing how each scenario driver shifts value from pessimistic to base case to optimistic. Use a waterfall chart in Excel:
-
Calculate the valuation bridge manually:
- Start: Pessimistic value ($5.40)
- Impact of higher revenue growth: +$2.50 → $7.90
- Impact of margin expansion: +$1.80 → $9.70
- Impact of lower WACC: +$2.30 → $12.00 (base case)
- Impact of terminal growth: +$6.00 → $18.00 (toward optimistic)
- End: Optimistic value ($23.00)
-
In Excel, create a column chart with stacked bars and negative values, or use a dedicated waterfall add-in
-
Label each step with the driver (revenue growth, margin, discount rate) and dollar impact
This waterfall is powerful for presentations—it shows exactly which assumptions drive the valuation gap between downside and upside, making the case transparent.
Probability weighting and expected value
Assign probabilities to each scenario based on your conviction and the company's specific risks:
Scenario Probability Intrinsic Value Contribution
Pessimistic 20% $5.40 $1.08
Base Case 50% $12.00 $6.00
Optimistic 30% $23.00 $6.90
Expected Value $14.00 per share
The probabilities must sum to 100% and reflect your actual beliefs, not just equal weighting. If the company has successfully executed its strategy, you might weight base case higher (60%) and pessimistic lower (10%). If execution risk is high, shift weight to pessimistic.
Expected value combines scenarios into a single number useful for portfolio allocation. If the current stock price is $10 and expected value is $14, the expected return is +40%, weighing scenarios by probability.
Real-world example: Valuing a cloud software company
Consider a SaaS company trading at $50/share. You model three scenarios:
Pessimistic: Customer churn accelerates as competition intensifies. Annual recurring revenue (ARR) growth slows to 15% (vs. historical 35%). Gross margin compresses from 80% to 75%. Magic number (ARR growth ÷ sales & marketing efficiency) falls below 0.75, a warning sign. Intrinsic value: $35/share.
Base Case: Churn stabilizes, ARR growth remains 30–35%, margins expand to 82% via operational leverage. Magic number stays healthy at 0.85–0.90. Intrinsic value: $60/share.
Optimistic: Company wins a major enterprise segment, ARR growth accelerates to 45%. Gross margin expands to 85% as platform scale improves. Magic number exceeds 1.0, indicating exceptional efficiency. Intrinsic value: $95/share.
Assigning probabilities:
- Pessimistic (15%): $35/share
- Base Case (60%): $60/share
- Optimistic (25%): $95/share
Expected value = (0.15 × $35) + (0.60 × $60) + (0.25 × $95) = $62.25/share
At $50/share, the stock trades at a 20% discount to expected value. The data table shows which growth or margin assumptions would justify $50, helping you identify the bear case assumptions baked into current pricing.
Common mistakes
Mistake 1: Scenarios that don't align with company narrative Don't arbitrarily assign growth rates. Tie each scenario to a specific market outcome: "If market adoption doubles" (optimistic) or "If a new competitor captures 10% share" (pessimistic). Scenarios should tell a coherent story, not just vary numbers.
Mistake 2: Probability weighting that sums to more than 100% This creates a mathematical error that inflates expected value. Always double-check: Pessimistic + Base + Optimistic = 100%. If you're uncertain about probabilities, use 25%/50%/25% as a neutral starting point.
Mistake 3: Ignoring correlation between assumptions In optimistic scenarios, margin expansion and higher growth often occur together. Don't model 15% revenue growth with 15% gross margins—that combination is unlikely. Build scenarios with internally consistent assumptions.
Mistake 4: Too many scenarios Four or five scenarios create decision paralysis. Stick to three. If you want more granularity, use sensitivity tables within each scenario to test sub-cases (e.g., base case with high margin vs. low margin).
Mistake 5: Forgetting to adjust discount rates across scenarios Pessimistic scenarios should have higher WACC (increased risk), optimistic scenarios lower WACC (de-risking as company grows). Holding WACC constant while varying growth is unrealistic and understates downside risk.
FAQ
Q: How do I decide on scenario probabilities? A: Use your conviction level and risk assessment. If you're highly confident in the base case and see limited downside, weight it 60–70%. If execution risk is high, weight pessimistic case higher (25–30%). Probabilities should reflect honest beliefs, not hope.
Q: Should I include a "catastrophic" worst-case scenario? A: Only if it's plausible (bankruptcy, acquisition, regulatory shutdown). Avoid artificial downside cases with <5% probability—they're usually just noise. Focus on scenarios you genuinely believe could happen.
Q: Can I use scenario analysis for negative value scenarios (e.g., short thesis)? A: Yes. If you're evaluating downside risk, model a scenario where the stock goes to zero (bankruptcy) and assess the probability. This is essential for risk management and short-sale thesis development.
Q: How often should I update scenario probabilities? A: Reassess whenever new information arrives: quarterly earnings, management changes, competitive announcements, or macroeconomic shifts. Rerun expected value with updated probabilities to track how your conviction changes.
Q: Should I present all three scenarios to investors or just expected value? A: Present all three. Institutional investors want to understand your downside, base, and upside cases separately. Expected value without scenario detail can seem like a black box. Transparency builds credibility.
Q: How do I handle scenarios for cyclical industries (e.g., automotive, construction)? A: Model scenarios based on cycle position. Pessimistic: entering downcycle (volumes down 20%, pricing pressure). Base: mid-cycle (stable volumes). Optimistic: upcycle (capacity constraints, pricing power). Adjust margin assumptions accordingly.
Related concepts
- Sensitivity analysis isolates the impact of a single assumption (e.g., WACC) on valuation. Scenario analysis combines multiple assumptions into cohesive stories.
- Monte Carlo simulation extends scenario analysis by testing thousands of assumption combinations, producing a probability distribution of valuations. See Chapter 16 for advanced techniques.
- Risk-adjusted returns weight scenarios by probability to produce an expected return that reflects both upside potential and downside risk.
- Break-even analysis identifies the assumptions required to justify current market prices, revealing what the market is implicitly assuming.
- Stress testing is a more extreme version of scenario analysis, often used in financial institutions to test resilience to severe market shocks.
- Scenario hedging guides portfolio construction by sizing positions to your conviction in each scenario (overweight undervalued pessimistic cases, underweight expensive optimistic cases).
Summary
Scenario analysis grounds valuations in reality by acknowledging uncertainty. Build three narratively coherent scenarios—pessimistic, base, optimistic—with internally consistent assumptions. Create summary tables comparing outputs across scenarios, use data tables for sensitivity testing within scenarios, and weight scenarios probabilistically to calculate expected value. This discipline transforms a single-point estimate into a range, communicating both upside potential and downside risk to stakeholders.