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

*Alex Tapio · 2026-04-29 · 9 min · Excel Techniques*

Canonical: https://finamodel.com/blog/sensitivity-analysis-excel

Master sensitivity analysis techniques in Excel: one-variable data tables, two-variable data tables, scenario toggles, Goal Seek, and tornado charts. Includes worked examples and best practices for financial modeling.

**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.

```mermaid
flowchart TD
    A[Base Case Model] --> B[Which Analysis]
    B -->|Single Assumption| C[One-Variable Data Table]
    B -->|Two Assumptions| D[Two-Variable Data Table]
    B -->|Discrete Scenarios| E[CHOOSE Toggle]
    B -->|Target Seeking| F[Goal Seek]
    B -->|Ranking by Impact| G[Tornado Chart]
    C --> H[Output Range of NPV and IRR]
    D --> H
    E --> H
    F --> I[Output Required Assumption Value]
    G --> J[Output Ranked Sensitivity Chart]
```

*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:

```excel
=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.

| Discount Rate | NPV |
| :--- | :--- |
| 8% | £65,240,000 |
| 9% | £57,580,000 |
| 10% | £50,000,000 |
| 11% | £43,220,000 |
| 12% | £37,100,000 |

**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.

---


<!-- template:dcf -->

## 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):

```excel
=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):**

| WACC / TGR | 2.0% | 2.5% | 3.0% | 3.5% |
| :--- | :--- | :--- | :--- | :--- |
| 5.0% | £280 | £320 | £370 | £435 |
| 6.0% | £185 | £210 | £245 | £290 |
| 7.0% | £125 | £142 | £165 | £195 |
| 8.0% | £85 | £98 | £115 | £138 |

**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:

```excel
// 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:**

| | Base | Bull | Bear |
| :--- | :--- | :--- | :--- |
| Revenue Growth | 5% | 8% | 2% |
| OpEx % Revenue | 25% | 22% | 28% |
| WACC | 7.5% | 6.5% | 9.0% |
| Terminal Growth | 2.5% | 3.5% | 1.5% |

Your model's Revenue Growth cell would contain:

```excel
=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:

```excel
// 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:

| Metric | Base | Bull | Bear | Upside | Downside |
| :--- | :--- | :--- | :--- | :--- | :--- |
| Year 5 Revenue | £250M | £350M | £150M | 40% | -40% |
| Year 5 EBITDA Margin | 28% | 32% | 22% | +400 bps | -600 bps |
| Enterprise Value | £1,200M | £1,800M | £600M | 50% | -50% |
| IRR | 18% | 26% | 10% | +8 pts | -8 pts |

---

## 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:

| Assumption | Base | Downside (-20%) | Upside (+20%) | Delta |
| :--- | :--- | :--- | :--- | :--- |
| Revenue Growth | 5% | 4% | 6% | NPV: £400M → £620M |
| WACC | 7% | 5.6% | 8.4% | NPV: £580M → £450M |
| Terminal Growth | 2.5% | 2.0% | 3.0% | NPV: £420M → £610M |
| COGS % | 40% | 48% | 32% | NPV: £450M → £560M |
| OpEx % | 25% | 30% | 20% | NPV: £480M → £530M |

**Step 4: Calculate the Delta**

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

| Assumption | Downside NPV | Upside NPV | Impact (Upside - Downside) |
| :--- | :--- | :--- | :--- |
| Revenue Growth | £400M | £620M | £220M |
| WACC | £580M | £450M | £130M |
| Terminal Growth | £420M | £610M | £190M |
| COGS % | £450M | £560M | £110M |
| OpEx % | £480M | £530M | £50M |

**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:

```excel
// 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](/blog/excel-financial-modeling-best-practices).

---

## Key Takeaways

**Sensitivity analysis is not optional - it is the bridge between a static forecast and actionable insight.** A 3-statement model or DCF that lacks sensitivity analysis is incomplete and risky to rely upon.

- **One-variable data tables** isolate the impact of a single assumption and reveal linear relationships.
- **Two-variable data tables** capture the joint effect of two assumptions, essential for valuation models where WACC and terminal growth interact.
- **Scenario toggles** (CHOOSE or INDEX/MATCH) enable rapid switching between named scenarios and are ideal for executive presentations.
- **Goal Seek** reverses the direction of analysis: find the assumption required to achieve a target (e.g., revenue CAGR to reach a valuation goal).
- **Tornado charts** rank assumptions by impact, answering the critical strategic question: "Which drivers matter most?"
- **Monte Carlo simulation** extends single-variable and two-variable analysis to handle uncertainty across all assumptions simultaneously, providing a full distribution of outcomes.

The choice of technique depends on your question: Are you exploring the impact of one assumption? Use a one-variable table. Two? Use a two-variable table. Do you need to present discrete scenarios? Use a scenario toggle. Do you want to reverse-engineer an assumption to meet a target? Use Goal Seek. Want to rank what matters? Build a tornado chart. Each tool has a purpose, and mastering all of them makes you a more rigorous, credible analyst.

For a deeper dive into DCF models and terminal value assumptions, see [DCF Model Excel Tutorial](/blog/dcf-model-excel-tutorial). For the foundational skills needed to build sensitivity-ready models, review [Excel Financial Modeling Best Practices](/blog/excel-financial-modeling-best-practices).



## Frequently asked questions

### What is sensitivity analysis and why is it critical for financial models?

Sensitivity analysis tests how changes in key assumptions (like revenue growth, discount rates, or terminal multiples) affect your model's outputs (NPV, IRR, valuation). It is critical because no forecast is certain. By quantifying which assumptions drive the most value, you can focus risk mitigation on what actually matters and communicate the range of outcomes to stakeholders with confidence.

### What is the difference between a one-variable and two-variable data table?

A one-variable data table shows how a single assumption (e.g., discount rate) affects a single output (e.g., NPV) across a range of values. A two-variable data table shows how two assumptions simultaneously affect one output, creating a grid of results. Two-variable tables are more powerful but more resource-intensive and harder to interpret at a glance.

### When should I use a scenario toggle versus a data table?

Use scenario toggles (CHOOSE or INDEX/MATCH) when you have 3-5 discrete, named scenarios (e.g., 'Base', 'Bull', 'Bear') with different sets of assumptions across multiple drivers. Use data tables when you want to isolate one or two assumptions and explore a continuous range of values. Scenario toggles are clearer for presentations; data tables are better for sensitivity exploration.

### What does Goal Seek do and when should I use it?

Goal Seek finds the value of a single assumption that produces a target output. For example, it can find the revenue CAGR needed to achieve a $1B valuation. Use Goal Seek when you have a target (e.g., a deal breakeven) and want to reverse-engineer the required assumption. It's a one-way, single-variable tool; use Solver for multi-variable optimization.

### What is a tornado chart and how do I build one?

A tornado chart ranks assumptions by their impact on a key metric (NPV, IRR, valuation). Each assumption is shocked up and down by the same percentage (e.g., ±20%), and the resulting change in the output is plotted as a horizontal bar. Longer bars = more sensitive. Build one by calculating base case output, then running one-variable data tables for each assumption, extracting the high/low deltas, and charting the results.

### Is Monte Carlo simulation available in Excel?

Native Excel has no built-in Monte Carlo tools, but you can build one by creating a large table of random assumptions (using RAND() or RANDBETWEEN()), calculating the output for each iteration, and analyzing the distribution of results. For faster, more robust simulations, use add-ins like @RISK, Crystal Ball, or Python libraries like NumPy. Monte Carlo is essential when uncertainty spans multiple assumptions simultaneously.