# Scenario Analysis in Excel: Skip Scenario Manager, Use a Selector Cell

Scenario Manager hides your assumptions inside a dialog nobody can audit. A scenario table with an INDEX selector does the same job in the open, updates live, and feeds the tornado chart that shows which input actually matters.

---

- **Canonical URL:** https://dothecalculation.com/blog/templates/excel-scenario-analysis-and-tornado-charts
- **Category:** Templates
- **Author:** Do The Calculation Team
- **Published:** 2026-08-03
- **Reading time:** 10 min read
- **Publisher:** Do The Calculation (https://dothecalculation.com)
- **Methodology:** https://dothecalculation.com/methodology

---

## The Problem With Scenario Manager

Excel has a built-in Scenario Manager under Data, What-If Analysis. It stores sets of input values and swaps them into your model on demand. It works, and there are three reasons not to use it.

- The assumptions live inside a dialog box. Nobody reviewing the workbook can see them without clicking through, and they do not print.
- Changing a scenario means reopening the dialog and editing values in a small text field, which is a poor place to maintain anything.
- The summary report is a static snapshot. Change the model and it does not update, so it goes stale without saying so.

A scenario table on the worksheet solves all three, and it is less work to build.

## The scenario table

**One row per assumption, one column per scenario**
| Assumption | Downside | Base | Upside |
| --- | --- | --- | --- |
| Units sold | 5,200 | 6,200 | 7,100 |
| Price | $74.00 | $79.00 | $83.00 |
| Variable cost | $37.20 | $34.50 | $33.00 |
| Fixed costs, excluding marketing | $142,000 | $138,000 | $134,000 |
| Marketing spend | $54,000 | $48,000 | $44,000 |

**The selector**

```
Selector cell: 1, 2, or 3, with data validation    ·    Live input: =INDEX(ScenarioRow, Selector)
```
- Each live input cell pulls from its own row of the table using the same selector.
- The model reads only the live input cells and does not know scenarios exist.
- Add =CHOOSE(Selector, "Downside", "Base", "Upside") beside the selector so the active case is labelled on screen and in print.

> **Colour the scenario table differently from the model** — A consistent convention, such as blue for typed assumptions and black for formulas, makes it immediately clear which cells a reader may change. In a model with a scenario table, add a third colour for the live input cells so nobody types over a formula that should be pulling from the table.

Everything downstream now recalculates when the selector changes. No dialog, no dead summary, and the assumptions are visible on the page where a reviewer can argue with them.

## Showing all three at once

A selector shows one scenario at a time. To display all three side by side without three copies of the model, use a one-variable data table with the selector as the column input.

- Put 1, 2, and 3 down a column.
- One column right and one row up, put references to the outputs you care about: profit, margin, cash, and so on.
- Select the block, Data, What-If Analysis, Data Table, and set the column input cell to the selector.
- Excel runs the whole model three times and fills the grid.

**The result**
| Scenario | Revenue | Operating income | Margin |
| --- | --- | --- | --- |
| Downside | $384,800 | −$4,640 | −1.2% |
| Base | $489,800 | $89,900 | 18.4% |
| Upside | $589,300 | $177,000 | 30.0% |

That is a live summary rather than a snapshot. Change a cost assumption in the table and all three columns move, which is precisely what Scenario Manager's report will not do.

## The tornado chart

Scenarios move every input at once, which tells you the range of outcomes and not which assumption is driving it. A tornado chart answers that by moving one input at a time.

- For each input, record the output at its downside value and at its upside value, holding everything else at base.
- A two-column one-variable data table per input does this, or record the six numbers by hand for a small model.
- Compute the swing as the absolute difference between the two.
- Sort the inputs by swing, largest first.
- Chart low and high as a stacked bar with the low series formatted as no fill, which produces the floating bars.

**Operating income, one input at a time, sorted by swing**
| Input | At downside | At upside | Swing |
| --- | --- | --- | --- |
| Units sold | $45,400 | $129,950 | $84,550 |
| Price | $58,900 | $114,700 | $55,800 |
| Variable cost | $73,160 | $99,200 | $26,040 |
| Marketing spend | $83,900 | $93,900 | $10,000 |
| Fixed costs | $85,900 | $93,900 | $8,000 |

> **What the ranking is actually telling you** — Volume and price together account for far more variance than every cost line combined. That is where the analysis effort belongs: tightening the volume forecast is worth more than refining the fixed cost estimate, however much easier the second is to do.

One caveat on reading a tornado. The swing depends on the range you chose for each input, so an input with a wide assumed range will look important partly because you assumed it was uncertain. Use ranges of comparable confidence, such as each input's 10th and 90th percentile, rather than round numbers.

## Two inputs at once

Where two assumptions interact, a two-variable data table shows the interaction that one-at-a-time analysis misses. Volume down the side, price across the top, operating income in the grid.

Add conditional formatting with a colour scale, or a rule highlighting every negative cell, and the loss-making region of the grid becomes visible as a shape. That communicates a risk far better than a single downside number does.

## Which tool for which question

**Four questions, four tools**
| Question | Tool |
| --- | --- |
| What happens under a coherent set of assumptions? | Scenario table with a selector |
| Which assumption matters most? | Tornado chart |
| What does the answer look like across a range? | One or two-variable data table |
| What input gives exactly this answer? | Goal Seek |
| What is the best combination under constraints? | Solver |
| What is the distribution of outcomes? | Monte Carlo simulation |

Scenarios and sensitivity answer different questions and a good model has both. Scenarios keep the assumptions coherent, since a downside case where volume falls and prices rise is not a real world. Sensitivity breaks that coherence deliberately to isolate one driver.

## What scenario analysis does not do

- Attach probabilities. Three scenarios are three points, not a distribution, and nothing says the downside is as likely as the upside. If you need probabilities, you need a simulation.
- Capture correlation properly. A tornado moves one input at a time while holding the rest at base, which is exactly the wrong assumption where inputs move together.
- Include what you did not think of. Every scenario is a variation on the model you built, and the risks that matter are often outside it.
- Handle non-monotonic responses. If an output rises then falls as an input increases, the two endpoints of a tornado bar can be identical and hide a peak in the middle.
- Tell you which scenario to plan for. That is a risk appetite question, and the model is silent on it.
- Validate the base case. Three scenarios built around a wrong centre are three wrong answers.

**Why not use Excel's Scenario Manager?**

Because the assumptions are hidden in a dialog, editing them is awkward, and the summary report is a static snapshot that goes stale silently. A scenario table on the sheet with an INDEX selector does the same job visibly and updates live.

**How do I switch between scenarios with one cell?**

Lay the scenarios out as columns, put a number in a selector cell with data validation restricting it to the valid range, and make each live input =INDEX(itsRow, Selector). Add a CHOOSE formula beside the selector so the active scenario is named on screen.

**How do I build a tornado chart?**

Record the output at each input's low and high value with everything else at base, compute the swing, sort largest first, then plot low and high as a stacked bar with the low series set to no fill. The floating bars are the tornado.

**What ranges should I use for a tornado?**

Ranges of comparable confidence across every input, such as each one's 10th and 90th percentile. Using round numbers makes the ranking partly an artefact of how wide you happened to make each range, which defeats the purpose of the chart.

**Should scenarios move one input or several?**

Several, and coherently. A downside case usually means lower volume and higher costs together, because the conditions that cause one cause the other. Moving one input at a time is sensitivity analysis, which is a different exercise with a different purpose.

**How many scenarios should a model have?**

Three is the usual set and it is usually enough: a base, a downside, and an upside. More than five and nobody reads them. If you find yourself wanting many, what you actually want is a simulation with a distribution rather than more discrete cases.

---

_Source: [Do The Calculation](https://dothecalculation.com/blog/templates/excel-scenario-analysis-and-tornado-charts). Quote freely with attribution and a link to this page._