Building Your First DCF Model in Excel
Walk through creating a complete DCF spreadsheet from scratch, including revenue projections, working capital assumptions, and free cash flow calculations.
Sensitivity Analysis and Scenario Testing
See how changing your key assumptions affects valuation. This guide shows you how to build sensitivity tables and scenario analyses that help you understand the range of possible outcomes.
6 min read
Advanced
July 2026
DCF Mastery Editorial Team
Editorial Team
Written by the DCF Mastery editorial team, focused on practical, tested guidance for building financial models.
Why Sensitivity Analysis Matters
You've built your DCF model. Revenue projections, WACC calculations, terminal value — it's all there. But here's the thing: your assumptions aren't guaranteed. What happens if revenue grows 2% slower than expected? What if interest rates spike and your discount rate jumps? That's where sensitivity analysis comes in.
It's not about being pessimistic. It's about being realistic. Your model should show what happens across a range of scenarios, not just one "best guess" number. When you present a valuation to stakeholders, they'll ask these questions. You'll want to have answers.
Understanding Sensitivity Tables
A sensitivity table shows you enterprise value across different assumptions. The most common version is a two-way table: one variable changes along the rows, another changes along the columns. You'll see the output (usually enterprise value or valuation multiples) change as assumptions shift.
Here's what makes this practical: if you're changing revenue growth and WACC, you've got maybe 25-30 different scenarios in one table. Your reader can see at a glance how sensitive the valuation is to each variable. Are the numbers relatively stable across the table, or do they swing wildly? That tells you something important about your model.
Key insight: Most valuations are far more sensitive to WACC and terminal growth rate than to near-term revenue assumptions. You'll see this pattern repeat across almost every sensitivity table you build.
Building Your First Sensitivity Table
The mechanics are straightforward. You'll use a two-input data table in Excel — though Google Sheets works too, it's a bit clunky for this.
1
Set up your row and column headers
Pick two variables you want to test. Let's say revenue growth (rows: 0%, 2%, 4%, 6%, 8%) and WACC (columns: 6%, 7%, 8%, 9%, 10%). Leave the top-left cell empty or label it.
2
Reference your base case formula
In the cell where your headers meet (usually top-left), reference your enterprise value calculation from the main model. This is what'll change as assumptions vary.
3
Use Data Table feature
Select the entire range (headers + formula). Go to Data menu, choose Table (or Pivot Table in some versions). Set row input as your first variable, column input as your second. Excel recalculates across all combinations.
4
Format for readability
Use conditional formatting — green for higher values, red for lower. Highlight your base case. Round to millions or thousands depending on your company size. Make it scannable.
The data table feature recalculates your model for each combination. Don't manually change values — that defeats the purpose and introduces errors. Let Excel do the work.
Important Note on Model Limitations
This guide is educational material focused on the mechanics of building sensitivity analyses and scenario tables. Sensitivity analysis is a tool for understanding how assumptions drive valuations — it's not a prediction tool. Real financial analysis requires professional judgment, market research, and often consultation with financial advisors or analysts who understand your specific situation. Always validate your assumptions with actual data and industry benchmarks.
Beyond Sensitivity Tables: Scenario Analysis
Sensitivity tables are one-variable-at-a-time testing. Scenario analysis goes deeper. You're building three or four complete stories: a base case, a bear case, and a bull case. Sometimes an upside scenario too.
Each scenario changes multiple assumptions together in a coherent way. In your bull case, revenue grows faster AND margins improve AND you maintain lower debt. In your bear case, growth slows, margins compress, and interest rates rise. These feel like plausible futures, not just random number combinations.
You'll build separate DCF models for each scenario — or better yet, structure your main model so you can toggle between scenarios with a dropdown or simple cell reference. Then compare the valuations side by side.
Pro tip: Don't make your bear case unrealistically pessimistic. If nobody could believe it, it won't be useful. Anchor it to actual market conditions or historical precedent.
Continue Learning
Deepen your DCF skills with these related guides
Understanding WACC and Discount Rates
Learn how to calculate weighted average cost of capital and why it matters for your valuation. We cover both the theory and practical calculation steps.
Terminal Value Calculation Methods
Terminal value often represents most of your enterprise value. We cover both the perpetuity growth and exit multiple methods with practical examples.
Making Sensitivity Analysis Work for You
Building sensitivity tables and scenarios isn't extra work — it's essential work. When you show a DCF valuation to someone who knows what they're looking at, they'll immediately ask how sensitive the numbers are. You'll have the answer ready.
Start simple: build a two-way sensitivity table on your main assumption (usually WACC and terminal growth). See how the valuation moves. Then add scenarios — bull, base, bear. Format them clearly. You're not just showing numbers, you're showing that you've thought through the range of possibilities.
That's what separates a credible analysis from a one-number guess. And in financial modeling, credibility is everything.
Ready to build your own models? Start with our guide on creating a complete DCF spreadsheet.
Read the DCF Building Guide