Sensitivity Analysis in Excel: Data Tables, Scenarios, and Goal Seek

Sensitivity analysis is the difference between a model that looks impressive in a board room and one that actually informs decision-making. Every assumption in your financial forecast carries uncertainty—revenue may grow faster or slower, discount rates may shift, or exit multiples may compress. Sensitivity analysis quantifies this uncertainty by testing how changes in key assumptions ripple through to your valuation, IRR, or other critical outputs. Without it, your model is a black box; with it, you understand exactly which levers drive value and where to focus risk mitigation.

Sensitivity analysis transforms a static forecast into a strategic tool. Whether you're valuing a company, evaluating a capital project, or stress-testing a budget, sensitivity analysis reveals the range of possible outcomes and the assumptions that matter most. In this guide, you'll learn five practical techniques for implementing sensitivity analysis in Excel: one-variable data tables, two-variable data tables, scenario toggles, Goal Seek, and tornado charts. Each has a distinct use case, and together they form a complete toolkit for rigorous financial analysis.

Sensitivity Analysis Workflow

One-Variable Data Tables

A one-variable data table is the simplest and most intuitive sensitivity tool. It shows how a single assumption affects a single output across a range of values. Use this when you want to isolate the impact of one driver—for example, how changes in the discount rate affect your DCF valuation.

Worked Example: Discount Rate Sensitivity

Assume your base case DCF model produces an NPV of £50M using a 10% discount rate. You want to test NPV across a range of discount rates from 8% to 12%.

Step 1: Structure the Table

Create a simple two-column layout:

Discount Rate | NPV
8% | [Formula]
9% | [Formula]
10% | [Formula]
11% | [Formula]
12% | [Formula]

The left column lists your input values; the right column will reference your model's output (NPV cell).

Step 2: Set Up the Formula

In the first data cell (to the right of 8%), enter a formula that references your NPV calculation:

=NPV_Cell

Where NPV_Cell is the cell in your model that calculates NPV.

Step 3: Create the Data Table

Select the entire range (including headers and all input values), then:

1. Go to Data > What-If Analysis > Data Table
2. In the Column Input Cell field, specify the cell in your model that holds the discount rate assumption (e.g., Assumptions!B10)
3. Leave the Row Input Cell blank (you're only varying one dimension)
4. Click OK

Excel will automatically populate the NPV column with results.

Key Insight: As the discount rate increases from 8% to 12%, NPV falls by over £28M (54% decline). This reveals that valuation is highly sensitive to the discount rate—a critical risk factor to discuss with stakeholders.

Pro Tip: Format data tables with conditional formatting (Data > Conditional Formatting > Color Scales) to visually highlight high and low sensitivity regions.

Two-Variable Data Tables

When two assumptions have independent effects on your output, a two-variable data table captures both in a single grid. This is particularly powerful for valuation models, where, for example, both exit multiple and revenue growth affect IRR.

Worked Example: WACC × Terminal Growth Rate → NPV

In a DCF model, terminal value is often the largest component of total value. It depends on two key assumptions: the Weighted Average Cost of Capital (WACC) used to discount cash flows, and the long-term growth rate. A two-variable table reveals how changes in both jointly affect NPV.

Table Structure:

Arrange your table with one assumption down the left (rows) and the other across the top (columns):

                | 2.0% | 2.5% | 3.0% | 3.5% |
5.0%            |      |      |      |      |
6.0%            |      |      |      |      |
7.0%            |      |      |      |      |
8.0%            |      |      |      |      |

Where rows are WACC and columns are Terminal Growth Rate.

Step 1: Set Up the Input Area

The first cell in your table (top-left corner) should reference your output (NPV):

=NPV_Cell

Step 2: Populate Input Values

Fill the row headers (Terminal Growth: 2.0%, 2.5%, 3.0%, 3.5%) and column headers (WACC: 5.0%, 6.0%, 7.0%, 8.0%).

Step 3: Create the Two-Variable Data Table

Select the entire range (including the NPV formula cell, all headers, and the empty grid), then:

1. Go to Data > What-If Analysis > Data Table
2. In the Row Input Cell field, specify the cell for Terminal Growth Rate (e.g., Assumptions!B20)
3. In the Column Input Cell field, specify the cell for WACC (e.g., Assumptions!B15)
4. Click OK

Excel fills the grid automatically.

Resulting NPV Grid (in £M):

Key Insight: Even small changes in terminal assumptions drive outsized valuation swings. Moving from 7% WACC + 2.5% TGR to 6% WACC + 3.0% TGR increases NPV from £142M to £245M—a 72% jump. This highlights both upside potential and downside risk depending on interest rate and growth forecasts.

Scenario Toggles: CHOOSE and INDEX/MATCH

When you have discrete, named scenarios (Base, Bull, Bear), hard-coding each scenario separately wastes time and introduces errors. Instead, use a single-cell toggle that switches between predefined assumption sets.

The CHOOSE Function Approach

CHOOSE is ideal for 2-5 scenarios:

// In the Assumptions sheet, create a Scenario Selector:
// Cell A1: "Scenario"
// Cell B1: 1 (or use a dropdown: 1=Base, 2=Bull, 3=Bear)

// For each assumption, use CHOOSE to pull from different cells:
// Revenue Growth (under Base, Bull, Bear in columns C, D, E):
= CHOOSE(B1, C2, D2, E2)

// WACC:
= CHOOSE(B1, C3, D3, E3)

// Terminal Growth:
= CHOOSE(B1, C4, D4, E4)

When you change B1 from 1 to 2, all formulas referencing these CHOOSE functions instantly switch to the Bull case assumptions. This makes scenario switching frictionless.

Example Setup:

Your model's Revenue Growth cell would contain:

=CHOOSE($B$1, C2, D2, E2)

Where $B$1 is the scenario selector.

The INDEX/MATCH Approach (More Scalable)

For 5+ scenarios or when scenario names change frequently, use INDEX/MATCH with a lookup table:

// Assumptions sheet has a lookup table:
// Column A: Assumption Name
// Column B: Base
// Column C: Bull
// Column D: Bear
// Row 2: Revenue Growth | 5% | 8% | 2%
// Row 3: OpEx % Revenue | 25% | 22% | 28%
// Row 4: WACC | 7.5% | 6.5% | 9.0%

// In your model's Revenue Growth cell:
= INDEX(Assumptions!2:2, MATCH($A$1, Assumptions!A:A, 0))

// Where $A$1 contains the selected scenario name ("Base", "Bull", or "Bear")
// This looks up "Revenue Growth" in column A, then returns the value from the selected scenario column

This approach is more maintainable: you can add scenarios without modifying formulas, and scenario names are self-documenting.

Comparison: Base vs. Bull Scenario

Once your toggle is in place, you can quickly generate scenario outputs:

Goal Seek

Goal Seek solves the reverse problem: instead of asking "what output results from this input?", you ask "what input is required to achieve this output?"

Use cases:

• Find the revenue CAGR needed to achieve a target valuation
• Find the pricing level required to break even on a capital project
• Find the interest rate at which debt serviceability becomes unviable

Worked Example: Required Revenue CAGR for £1B Valuation

Assume your model has:

• Year 1 Revenue: £50M
• Revenue CAGR (assumption cell): 15%
• Enterprise Value Output: £800M (calculated)

You want to find the CAGR needed to reach £1B in enterprise value.

Step 1: Identify Three Cells

1. Formula Cell: The output you want to reach (Enterprise Value = £800M currently)
2. Variable Cell: The assumption you want to change (Revenue CAGR)
3. Target Value: The goal (£1B)

Step 2: Run Goal Seek

1. Go to Data > What-If Analysis > Goal Seek
2. Set Formula Cell: to your EV calculation cell
3. Set To Value: to 1000000000 (£1B)
4. Set By Changing Cell: to your Revenue CAGR cell
5. Click OK

Excel iterates and finds the solution:

Result: Revenue CAGR = 18.7%

Interpretation: To reach a £1B valuation, the company must achieve a 18.7% revenue CAGR—an increase of 3.7 percentage points from your base case of 15%. This reveals the growth premium embedded in your target valuation and whether it is realistic given the market and competitive position.

Pro Tip: Use Goal Seek strategically. If it returns an unrealistic or impossible value (e.g., 200% growth), your target may not be achievable, signaling risk or the need to revisit assumptions.

Tornado Charts: Ranking Assumptions by Impact

Tornado charts rank all assumptions by their sensitivity, answering: "Which assumptions drive the most value?" This is essential for risk mitigation—focus your effort on validating the assumptions that matter.

How to Build a Tornado Chart

Step 1: Select Key Assumptions

Choose 8-12 assumptions that might affect your key output (e.g., NPV or IRR):

• Revenue growth
• WACC
• Terminal growth
• COGS %
• OpEx %
• Exit multiple
• Tax rate
• Working capital days

Step 2: Calculate Base Case Output

Calculate your output under base case assumptions (e.g., NPV = £500M).

Step 3: Shock Each Assumption

For each assumption, calculate the output under two scenarios:

• Upside: Assumption increased by a fixed percentage (e.g., +20%)
• Downside: Assumption decreased by a fixed percentage (e.g., -20%)

For example:

Step 4: Calculate the Delta

For each assumption, calculate the absolute change from base case:

Step 5: Chart the Results

Create a horizontal bar chart with assumptions on the y-axis and impact on the x-axis. The chart resembles a tornado (wide at the top, narrow at the bottom), hence the name.

Key Insight: Revenue growth has the largest impact (£220M swing), followed by terminal growth (£190M). WACC comes third (£130M). This tells you:

1. Focus risk mitigation on validating revenue forecast and market position
2. Terminal value assumptions are critical; stress-test them heavily
3. WACC is important but slightly less sensitive than growth
4. OpEx and COGS, while important to manage, have less impact on total value

Monte Carlo Simulation (Brief Introduction)

While one-variable, two-variable, and tornado analyses test discrete scenarios, Monte Carlo simulation tests thousands of random combinations of assumptions simultaneously. This is powerful when uncertainty spans multiple drivers and you want to understand the full probability distribution of outcomes.

How It Works

1. Define distributions for each assumption: Instead of point estimates, specify ranges (e.g., revenue growth: mean 5%, std dev 2%).
2. Generate random samples: Use RAND() or add-ins to draw random values from each distribution.
3. Calculate output for each iteration: Run your model 10,000 times with different random assumptions.
4. Analyze the distribution of results: Plot NPV, IRR, or other outputs as a histogram to see the range of outcomes and probabilities.

Excel-Based Approach:

While native Excel lacks a Monte Carlo engine, you can build one:

// Column A: Iteration number (1 to 10,000)
// Column B: Random Revenue Growth = NORMINV(RAND(), 0.05, 0.02)
// Column C: Random WACC = NORMINV(RAND(), 0.07, 0.01)
// Column D: Output (NPV) = formula referencing B and C

// Then use Data > Subtotals or a pivot table to analyze the distribution

Better Approach: Use Python, R, or dedicated add-ins like @RISK or Crystal Ball, which handle large-scale simulations efficiently.

Best Practices for Sensitivity Analysis

1. Start with a baseline: Ensure your base case model is correct before running sensitivity. A flawed base case invalidates all downstream analysis.

2. Choose the right shock magnitude: When shocking assumptions (±10%, ±20%), use a magnitude that reflects realistic uncertainty in that assumption. Revenue growth might realistically vary ±2 percentage points; tax rates vary ±1-2 percentage points. Don't shock all assumptions by the same percentage—it's not realistic.

3. Separate business risk from valuation risk: A two-variable table of revenue growth and exit multiple isolates business risk (how big will the company be?) from valuation risk (at what multiple will it be valued?). This distinction is crucial for M&A discussions.

4. Link sensitivity back to drivers: If sensitivity analysis reveals that revenue growth is critical, drill deeper: what sub-drivers affect revenue? Market size, market share, pricing? Build a "sensitivity of sensitivities" to isolate which underlying business lever matters most.

5. Use scenario toggles for presentations: When presenting to boards or stakeholders, scenario toggles (CHOOSE or INDEX/MATCH) are far more compelling than raw data tables. A single button-press that shows "here's Base, Bull, and Bear" is clearer than a 20-row table of numbers.

6. Always document assumptions and shocks: Add a legend to your sensitivity tables and tornado charts explaining the shock magnitude (e.g., "±20% change in assumption") and the rationale (e.g., "Historical WACC range: 6.0% to 8.5%").

7. Test correlation: Many assumptions are not independent. Revenue growth and COGS % may move together (economies of scale). Discount rates and terminal growth may move together (rate environment). Data tables assume independence; Monte Carlo can model correlation. Be aware of this simplification.

8. Compare to historical and peer benchmarks: If your sensitivity analysis shows that NPV is highly sensitive to a 2% change in WACC, benchmark this against historical WACC volatility and peer group ranges. If historical WACC has only varied 0.5%, the 2% shock may be overly pessimistic.

For a comprehensive guide to financial modelling best practices, see our article on Excel Financial Modeling Best Practices.