Scenario and Sensitivity Analysis: How to Stress-Test Any Financial Model

Every financial model is built on assumptions. Revenue will grow at 8%. The margin will hold at 15%. The discount rate is 8.5%. These numbers might be your best estimates — backed by research, comparable data, and sound judgement — but they are still estimates. The future will not match them exactly.

A model that produces a single answer, with no indication of how that answer changes if the assumptions move, is not a finished model. It is a starting point. The professionals who review your work — whether that is a professor marking a case study, a senior analyst in an IB, or a CFO assessing a business plan — will want to know how confident you are in the output and where the key risks sit.

Sensitivity analysis and scenario analysis are the tools that answer those questions. They are not complicated, but they are consistently underused by beginners. Building them into your workflow from the start is one of the clearest signals that you understand how models actually work.

---

Sensitivity Analysis vs Scenario Analysis — What Is the Difference?

These two terms are sometimes used interchangeably, but they address different questions:

Sensitivity analysis asks: “If I change one assumption by a given amount, how much does the output change?” You vary a single input while holding everything else constant. The result is a table or chart that shows the output’s sensitivity to that one variable.

Scenario analysis asks: “If a specific, coherent set of circumstances occurs — a bear case, a bull case, a recession — what does the model look like?” You vary multiple assumptions simultaneously to represent a plausible alternative world. The result is a set of complete model outputs (not just one line) for each scenario.

The two techniques complement each other. Sensitivity analysis tells you which inputs your model is most exposed to. Scenario analysis shows you what happens in specific situations that are internally consistent (for example: a recession scenario would typically combine lower revenue growth, compressed margins, and tighter credit conditions — not just one of these in isolation).

---

Part 1: Sensitivity Analysis in Excel

The one-variable data table

The simplest form of sensitivity analysis is a one-variable (one-way) data table. You pick a single input — say, the revenue growth rate — and see what your key output (say, the implied share price from a DCF) looks like across a range of values.

Here is how to build one using the WidgetCo DCF from our previous guide:

Step 1 — Set up the table structure

In a spare area of your DCF Valuation tab (or a dedicated Sensitivity tab), create a column of input values:

Cell B2 (header):  Revenue Growth Rate
Cell B3:           4%
Cell B4:           5%
Cell B5:           6%
Cell B6:           7%
Cell B7:           8%   ← base case
Cell B8:           9%
Cell B9:           10%
Cell B10:          11%
Cell B11:          12%

In Cell C2, enter a link to your output cell — the implied share price. This must be the actual formula cell, not a hardcoded number.

Step 2 — Run the data table

Select the range B2:C11. Go to Data → What-If Analysis → Data Table.

In the dialog box:

• Leave Row input cell blank (we are only varying one variable)
• In Column input cell, enter the cell reference of the revenue growth assumption in your Assumptions tab (e.g. Assumptions!B5)

Click OK. Excel fills in the table automatically.

Step 3 — Format for clarity

Apply conditional formatting to the output column: green for values above your base case, red for values below. Highlight the base case row. Label the table clearly — whoever reads this needs to understand immediately what they are looking at.

The two-variable data table

A two-variable (two-way) data table varies two inputs simultaneously and shows the output in a matrix. This is the standard format for the sensitivity table in a DCF — rows for WACC, columns for terminal growth rate.

Step 1 — Set up the matrix

In a spare area, create a grid:

          | 1.5% | 2.0% | 2.5% | 3.0% | 3.5%   ← terminal growth rates (columns)
    6.0%  |      |      |      |      |
    6.5%  |      |      |      |      |
    7.0%  |      |      |      |      |
    7.5%  |      |      |      |      |
    8.0%  |      |      |      |      |  ← base case row
    8.5%  |      |      |      |      |
    9.0%  |      |      |      |      |
    9.5%  |      |      |      |      |
   10.0%  |      |      |      |      |

The WACC values go in the leftmost column (starting one row below the header). The terminal growth rate values go in the top row (starting one column right of the header). In the top-left corner cell of the grid (where the header row and header column meet), enter a link to the output cell — the implied share price.

Step 2 — Run the data table

Select the entire grid including the header row and column and the corner cell. Go to Data → What-If Analysis → Data Table.

• Row input cell: the WACC cell in your Assumptions tab
• Column input cell: the terminal growth rate cell in your Assumptions tab

Click OK. The grid fills instantly.

Step 3 — Highlight the base case and add colour

Use conditional formatting to shade the grid: darker green for higher values, darker red for lower. Draw a border around the base case cell. This is the standard “football field” sensitivity table you will see in every investment banking pitchbook.

For WidgetCo, the completed table might look like this (implied share price, £):

WACC \ g	1.5%	2.0%	2.5%	3.0%	3.5%
6.0%	10.82	11.94	13.41	15.52	18.81
7.0%	9.14	9.93	10.91	12.14	13.81
8.0%	7.85	8.47	9.19	10.07	11.17
9.0%	6.83	7.31	7.87	8.52	9.30
10.0%	6.01	6.39	6.83	7.33	7.93

The base case (WACC = 8.0%, g = 2.5%) gives an implied share price of £9.19. The range across plausible assumptions runs from £6.01 to £18.81 — a reminder that a DCF point estimate is always accompanied by enormous uncertainty.

---

Part 2: Scenario Analysis in Excel

Where sensitivity analysis varies one or two inputs in isolation, scenario analysis builds complete alternative versions of the model. Each scenario represents a plausible, internally consistent state of the world.

Defining your scenarios

For a business model or DCF, three scenarios are the minimum:

Bear case: Things go worse than expected. Revenue grows slowly or declines. Margins compress. Working capital requirements increase. The discount rate rises. This is the scenario that tests whether the investment is still viable under adverse conditions.

Base case: Your best estimate of what will actually happen. Not optimistic, not pessimistic — calibrated to available evidence.

Bull case: Things go better than expected. Revenue growth exceeds forecasts. Margins expand with operating leverage. Capital requirements moderate. This is the scenario that represents the upside thesis.

For WidgetCo, the scenario assumptions might look like this:

Assumption	Bear	Base	Bull
Revenue growth (Yr 2–5)	3%	8%	14%
EBIT margin	12%	15%	18%
CapEx % of revenue	8%	6%	5%
ΔNWC % of revenue increment	15%	10%	8%
WACC	10%	8%	7%
Terminal growth rate	1.5%	2.5%	3.0%

Notice that a bear case does not just mean “lower growth” — it also means lower margins, higher capital requirements, and a higher discount rate, because adverse conditions typically come together. This is what makes scenarios more realistic than simple sensitivity tables.

Building scenarios in Excel — Method 1: Scenario Manager

Excel’s built-in Scenario Manager (under Data → What-If Analysis → Scenario Manager) lets you save named sets of assumption values and switch between them with one click.

Step 1: In your Assumptions tab, identify the input cells you want to vary (revenue growth rate, EBIT margin, CapEx %, WACC, terminal growth rate).

Step 2: Open Data → What-If Analysis → Scenario Manager → Add.

Step 3: Name the scenario “Bear Case”. Select the input cells as the “Changing cells”. Enter the bear case values.

Step 4: Repeat for Base Case and Bull Case.

Step 5: Click Summary. Excel produces a summary sheet showing the value of each output cell (implied share price, enterprise value, equity value) across all three scenarios.

Building scenarios in Excel — Method 2: Manual Switch (more transparent)

For a model you will share or present, a manual scenario switch is often cleaner than the built-in Scenario Manager:

1. Create a Scenarios tab with a column for Bear, Base, and Bull assumptions.
2. Add a Scenario dropdown in your Assumptions tab using Data Validation.
3. Use INDEX/MATCH or CHOOSE formulas to pull the correct assumption into the working cell based on the dropdown selection.

=CHOOSE(ScenarioSelector, BearValue, BaseValue, BullValue)

Where ScenarioSelector is a cell that returns 1, 2, or 3 depending on the dropdown selection. This structure makes the model transparent and easy to audit — anyone can see exactly what is changing between scenarios.

---

Part 3: Which Variables Should You Stress-Test?

A common question from students is: which assumptions should I vary? The answer is the ones that matter most — and you find those by running sensitivity analysis first.

As a rule of thumb, for a DCF model, the variables that move the needle most are:

1. WACC — small changes in the discount rate compound across many years
2. Terminal growth rate — the perpetuity formula amplifies small changes dramatically
3. Revenue growth rate — drives the entire FCF projection
4. EBIT margin — directly multiplied through to NOPAT and FCF
5. CapEx intensity — often overlooked but significant for capital-heavy businesses

For a budget or P&L model, the critical drivers are typically:

1. Volume / units sold (top-line driver)
2. Price / average selling price (especially for businesses with pricing power)
3. COGS as % of revenue (margin sensitivity)
4. Headcount and salary assumptions (largest cost for most businesses)

Build sensitivity tables for the top two or three, then group the rest into scenarios.

---

A Note on Presenting Sensitivity and Scenario Output

The purpose of sensitivity and scenario analysis is not to hedge your model with so many caveats that it becomes meaningless — it is to be honest about uncertainty and to focus attention on the assumptions that matter.

When presenting your analysis:

• Name your scenarios clearly. “Bear / Base / Bull” is fine; “Scenario 1 / 2 / 3” tells the reader nothing.
• Explain the story behind each scenario. What would have to be true for the bear case to materialise? A recession? Loss of a key customer? Cost inflation?
• Present ranges, not just point estimates. “The equity value is £8.47 per share” is less useful than “the equity value ranges from £6.83 to £11.17 across the sensitivity table.”
• Highlight where the model is most sensitive. If a 1% change in WACC moves the valuation by 20%, say so.

These are the communication habits that distinguish a junior analyst who can operate Excel from one who genuinely understands the model.

---

Step-by-Step Practice Exercise

1. Open the WidgetCo DCF from the previous guide (or download the template below).
2. Add a two-way sensitivity table showing implied share price for WACC ranging from 6% to 10% and terminal growth rate from 1.5% to 3.5%.
3. Apply conditional formatting: a green-to-white-to-red colour scale.
4. Add a scenario analysis tab with Bear / Base / Bull inputs as shown in the table above.
5. Add a summary section showing EV, Equity Value, and Implied Share Price for each scenario.

The entire exercise should take 30–40 minutes on a completed DCF. It is also a highly credible thing to walk through in a first-round interview.

---

Key Takeaways

• Sensitivity analysis changes one variable at a time and shows you which inputs the model is most exposed to.
• Scenario analysis changes multiple inputs simultaneously to represent coherent alternative states of the world.
• Every financial model presented professionally should include both — a single-point estimate with no sensitivity analysis is a red flag to experienced reviewers.
• In Excel, use data tables for sensitivity analysis and either Scenario Manager or a manual CHOOSE/INDEX switch for scenario analysis.
• When building scenarios, make them internally consistent: a bear case is lower growth and lower margins and higher discount rates — not just one of these.

---

Previous in series → How to Build a DCF Model: A Beginner’s Step-by-Step Guide

Next in series → The Operational Model: Driver-Based Forecasting for Finance Teams

Related → Monte Carlo DCF: Adding Probability to Your Valuation