0 ITEMS
How to Run Scenario and Sensitivity Analysis in Excel
Every financial model produces one number, and that number is almost certainly wrong. Scenario and sensitivity analysis in Excel replaces it with a range you can defend. This guide walks through the three tools that do the job — Scenario Manager, Data Tables, and the tornado chart — with a worked net profit model you can rebuild in under an hour.
Key Takeaways
- Run a tornado chart first: it ranks your assumptions by impact so you focus on the two or three inputs that actually move the result.
- Excel’s Scenario Manager lets you create as many named scenarios as you want, and each one can switch up to 32 input cells in a single step.
- A two-variable Data Table returns a full grid of results from one calculation, so a 5 by 5 table delivers 25 outcomes in seconds.
- Sensitivity analysis changes one input at a time; scenario analysis changes several inputs together. You need both to see risk clearly.
- Data Table output cells hold array formulas, so you cannot type over any cell inside the results grid.
- Every model needs at least three named cases (base, bull, bear) plus a sensitivity grid built on a structured Excel financial model.
What Scenario and Sensitivity Analysis Mean in Excel
Scenario and sensitivity analysis both measure how changes in your assumptions change your answer, but they ask different questions. Sensitivity analysis changes one input at a time and measures that input’s isolated effect on a single output. Scenario analysis changes several inputs together and measures the combined effect on the whole model.
A single-point forecast hides risk. A range shows it. When you know that a 5% move in gross margin changes net profit by 40,000 dollars, you stop arguing about the point estimate and start managing the assumption behind it.
Excel’s Scenario Manager lets you create as many named scenarios as you want, and each one can change up to 32 input cells (Microsoft Support).
A simple way to remember the split: sensitivity analysis answers “by how much”, and scenario analysis answers “what if all of this happens at once”. Most finance teams run both, and Excel ships the three tools you need at no extra cost: Scenario Manager, Data Tables, and a chart you build yourself, the tornado chart.
Method 1: Use Excel Scenario Manager to Compare Named Cases
Scenario Manager is the fastest way to store named sets of input values and move between them. It lives on the Data tab under What-If Analysis, it needs no add-in, and it prints a clean summary table you can drop straight into a board pack.
Step 1: Build a small base model
Keep every input in its own labeled cell so Scenario Manager has something to change.
- B2 Revenue = 500,000
- B3 Cost of goods sold, as a share of revenue = 60%
- B4 Operating expenses = 80,000
- B5 Net profit =
=B2*(1-B3)-B4
At the base assumptions, net profit = 500,000 × (1 – 0.60) – 80,000 = 120,000.
Step 2: Open Scenario Manager
Go to the Data tab, click What-If Analysis, then Scenario Manager.
Step 3: Add each case
Click Add, name the scenario “Base Case”, set the changing cells to B2:B3, and enter the base values. Repeat for “Bull Case” (revenue 650,000, COGS 55%) and “Bear Case” (revenue 380,000, COGS 65%).
Step 4: Generate the summary report
Click Summary, choose Scenario Summary, and set the result cell to B5. Excel writes a formatted table to a new sheet.
| Scenario | Revenue | COGS % | Net profit |
|---|---|---|---|
| Base Case | 500,000 | 60% | 120,000 |
| Bull Case | 650,000 | 55% | 212,500 |
| Bear Case | 380,000 | 65% | 53,000 |
The bull case lifts net profit to 212,500 because more revenue arrives at a lower cost ratio. The bear case cuts it to 53,000. That 159,500 spread is the risk your single forecast was hiding.
Method 2: Build a Data Table for Sensitivity Analysis
A Data Table recalculates one formula across a grid of input values, which makes it the fastest way to produce a sensitivity matrix in Excel. Every version of Excel since 2007 includes it, and you never type a formula into the grid by hand.
One-variable Data Table
A one-variable table varies a single input and shows the output at each value.
- List the input values down a column, for example revenue at 400,000, 450,000, 500,000, 550,000 and 600,000 in D2:D6.
- In E1, enter a reference to the result formula,
=B5. - Select D1:E6.
- Go to Data, What-If Analysis, Data Table, and set the Column input cell to B2.
- Click OK. Excel fills E2:E6 with net profit at each revenue level.
Two-variable Data Table: a worked example
A two-variable Data Table evaluates a single output against two inputs, so a 5 by 5 grid returns 25 results from one calculation (Microsoft Support).
Work through one cell by hand first, so you can trust the grid. At revenue of 600,000 and COGS of 58%:
Net profit = 600,000 × (1 – 0.58) – 80,000 = 600,000 × 0.42 – 80,000 = 252,000 – 80,000 = 172,000.
To build the grid:
- Put the result reference
=B5in the top-left cell of the range you will fill, for example G1. - List revenue values down column G, from 400,000 to 600,000.
- List COGS percentages across row 1, from 55% to 65%.
- Select the whole block, G1:L6.
- Go to Data, What-If Analysis, Data Table, set the Row input cell to B3 and the Column input cell to B2, then click OK.
Colour shading on a two-variable Data Table turns 25 net profit results into an instant heat map of risk.
Excel fills all 25 cells at once. Each cell holds the array formula {=TABLE(B3,B2)}, which returns several results from one formula and cannot be edited cell by cell. Apply a colour scale from the Home tab to see the safe and unsafe corners of the grid immediately.
Method 3: Build a Tornado Chart to Rank Your Drivers
A tornado chart ranks your inputs by how much each one moves the output, with the largest swing at the top. It is the quickest way to show which two or three assumptions deserve your attention, and you can build it from the tables above in about ten minutes.
Step 1: Measure each input’s swing
Hold every other input at base and record the output at a low and a high value.
| Input | Low value | High value | Output at low | Output at high | Swing |
|---|---|---|---|---|---|
| Revenue | 380,000 | 650,000 | 72,000 | 180,000 | 108,000 |
| COGS % | 65% | 55% | 95,000 | 145,000 | 50,000 |
| Operating expenses | 100,000 | 60,000 | 100,000 | 140,000 | 40,000 |
Step 2: Sort and chart
Sort the rows by swing, largest first. Select the two output columns, insert a 2D clustered bar chart, then right-click the category axis, open Format Axis, and tick “Categories in reverse order” so the biggest bar sits at the top.
The tornado shape appears once you sort by swing: wide bars at the top, narrow at the bottom.
Revenue drives the largest swing in net profit at 108,000, COGS drives 50,000, and operating expenses move it by only 40,000. You now know where to spend your forecasting effort, and you can defend that choice in one slide.
Scenario vs. Sensitivity: Which Method Fits Your Question?
Run all three tools together, but pick your starting point from the question you are answering. A tornado chart answers “what should I worry about”, a Data Table answers “what happens if this one number moves”, and Scenario Manager answers “what does a credible story look like”.
| Question | Best tool | Inputs changed | Output shown |
|---|---|---|---|
| Which assumptions matter most? | Tornado chart | One at a time | Ranked swing per input |
| What if this single number moves? | Data Table | One or two | Grid of one output |
| What does a full story look like? | Scenario Manager | Several together | Named cases and a summary |
A practical order of work: run the tornado chart, build a two-variable Data Table on the top two drivers, then save base, bull and bear cases in Scenario Manager for anyone who wants the narrative.
Common Mistakes That Break Excel What-If Analysis
These five errors show up again and again, and each one has a two-minute fix.
1. Typing over a Data Table. Excel overwrites the output range on every recalculation. Fix: treat the whole grid as read-only and change only the input cells.
2. Using relative references in the result cell. If the result cell points at B5 instead of $B$5 and the table sits on another sheet, the grid fills with errors. Fix: anchor the reference with dollar signs.
3. Leaving the changing cells locked. Scenario Manager cannot write values into locked cells on a protected sheet. Fix: unlock the input cells, then protect the sheet.
4. Mixing scenario and sensitivity logic in one bar. If you move revenue and cost together in a tornado chart bar, you are measuring a scenario, not a sensitivity. Fix: move one input at a time and reset the rest to base.
5. Leaving calculation on Manual. Data Tables quietly stop updating when Excel is set to manual calculation. Fix: go to Formulas, Calculation Options, Automatic, or press F9 to force a recalculation. Excel also recalculates every Data Table each time the workbook recalculates, so keep grids lean in large models (Microsoft Support).
If you want a fuller set of checks, EFM’s guide to financial model validation walks through a seven-step accuracy pass that pairs well with these three tools.
Frequently Asked Questions
What is the difference between scenario analysis and sensitivity analysis?
Scenario analysis moves several inputs at once to model a coherent story, such as a downturn where revenue falls 20%, cost of goods rises 5% and hiring pauses. Sensitivity analysis moves one input at a time while holding the others constant, so you can attribute the change in output to a single assumption. In Excel, Scenario Manager handles scenario analysis and Data Tables handle sensitivity analysis. Strong models use both: the Data Table tells you which numbers deserve attention, and the scenario summary shows stakeholders how the plan behaves when the world changes.
How do I create a two-variable Data Table in Excel?
Put the result reference, for example =B5, in the top-left corner of the range you want to fill. List the first input’s values down the left column and the second input’s values across the top row. Select the entire block, including the formula cell, then go to Data, What-If Analysis, Data Table. Enter the row input cell that your top-row values feed and the column input cell that your left-column values feed, and click OK. Excel fills every interior cell with results and stores them as the array formula {=TABLE(row_input,column_input)}.
Why is my Data Table not updating?
The usual cause is calculation mode. Go to Formulas, Calculation Options and select Automatic, then press F9 to force a full recalculation. If the grid still freezes, check that the result cell references your model output rather than a hardcoded number, and confirm the row and column input cells match your model. Very large models with thousands of formulas plus active Data Tables recalculate slowly because Excel rebuilds every table whenever anything on the workbook changes, so move heavy grids to their own workbook if speed matters.
How many scenarios can Excel’s Scenario Manager hold?
Excel’s Scenario Manager lets you create as many named scenarios as you want, and each scenario can change up to 32 input cells at the same time (Microsoft Support). That 32-cell ceiling sits far above what most models need. Three to five named cases, typically base, bull, bear, upside and a stress case, communicate risk better than a long list of near-identical scenarios. If you need more combinations than 32, use a two-variable Data Table, which has no practical limit on the number of result cells it can hold.
What is a tornado chart and when should I use one?
A tornado chart is a horizontal bar chart that ranks inputs by the size of their effect on one output, with the largest swing at the top and the smallest at the bottom. Use it at the start of any sensitivity analysis, before you build a Data Table, because it tells you which inputs are worth testing. If eight assumptions feed your model but the chart shows that margin and volume together drive most of the profit variance, you can build a focused two-variable grid instead of a sprawling eight-way exercise, and you can explain the result to a non-finance audience in a single slide.
Can I run sensitivity analysis on a DCF valuation in Excel?
Yes, and it is one of the most common uses. In a discounted cash flow model, which values a business by discounting future cash flows back to today, the two inputs that usually drive the most variance are the discount rate and the terminal growth rate. Build a two-variable Data Table with discount rates across the top row and terminal growth rates down the left column, and point the result cell at your enterprise value figure. The grid shows a range of valuations instead of a single number, which is exactly what a buyer, a lender or an investor needs to see before they commit.
Conclusion
Scenario and sensitivity analysis turns a static spreadsheet into a decision tool. Rank your drivers with a tornado chart, test the top two in a two-variable Data Table, and keep base, bull and bear cases in Scenario Manager so everyone can see the range. The whole build takes an afternoon on an existing model, and it changes how you defend your numbers.
I recommend starting from a ready-built sensitivity analysis template rather than wiring the Data Tables and scenario switch yourself. A structured template keeps your assumptions in one place, so the next tornado chart takes minutes instead of hours.
