Scenario analysis recalculates a result under a few complete sets of assumptions; sensitivity analysis moves one driver at a time. This page builds both in Excel on one product business: a switch cell for base, downside and upside, a probability-weighted view, a two-way data table of price against volume, and a tornado ranking of the four drivers.
Scenario analysis recalculates a result, such as operating profit, under a few complete sets of assumptions, usually base, downside and upside, so you see the range of outcomes rather than one number. On a product business planning $180,000 of operating profit, a downside where volume, price and cost all move against it gives $69,000, and an upside gives $272,000. Sensitivity analysis then moves one driver at a time and shows that price matters most.
A scenario changes several drivers together, to fit one story: a recession case where fewer units sell, at a lower price, while input costs rise. A sensitivity changes one driver and holds the rest, to show how much the result depends on it. What-if analysis is Excel's menu for both. Microsoft's introduction to What-If Analysis names three tools under Data > What-If Analysis: Scenario Manager, Goal Seek and Data Table.
Goal Seek runs the question backwards: it finds the one input that gives a target result, such as the volume that brings profit to zero. It has its own page, Goal Seek in Excel; this one covers scenarios, data tables and sensitivity.
Operating profit = Volume × (Price − Variable cost per unit) − Fixed cost
Four drivers, each in its own input cell, and profit computed from those cells only. The base case:
| Driver | Base |
|---|---|
| Volume (units) | 10,000 |
| Price (USD) | 120 |
| Variable cost per unit (USD) | 72 |
| Fixed cost (USD) | 300,000 |
| Revenue (USD) | 1,200,000 |
| Operating profit (USD) | 180,000 |
10,000 × (120 − 72) − 300,000 = 480,000 − 300,000 = $180,000. The base volume usually comes from a run rate or a forecast; run rate and forecast covers where it should come from. The same driver logic applied to selling capacity is in capacity is arithmetic.
Lay the assumptions out as a block: drivers down the rows, cases across the columns, and one live column that picks the case. Put 1, 2 or 3 in B1 (base, downside, upside), case names in C3:E3, and the drivers in rows 4 to 7:
| Row | B: Driver | C: Base | D: Downside | E: Upside | F: Live |
|---|---|---|---|---|---|
| 4 | Volume | 10,000 | 9,000 | 11,000 | =CHOOSE($B$1,C4,D4,E4) |
| 5 | Price (USD) | 120 | 115 | 124 | =CHOOSE($B$1,C5,D5,E5) |
| 6 | Variable cost (USD) | 72 | 74 | 72 | =CHOOSE($B$1,C6,D6,E6) |
| 7 | Fixed cost (USD) | 300,000 | 300,000 | 300,000 | =CHOOSE($B$1,C7,D7,E7) |
The results read only from the live column:
F8 Revenue =F4*F5
F9 Operating profit =F4*(F5-F6)-F7
If B1 holds a case name instead of a number, use =INDEX(C4:E4,MATCH($B$1,$C$3:$E$3,0)). Every formula in the model points at column F, so changing B1 moves the whole workbook to the other case.
The menu alternative is Scenario Manager: Data > What-If Analysis > Scenario Manager > Add, select the changing cells, and type each case's values. Microsoft's Scenario Manager page notes that a scenario can hold at most 32 values, and that scenario summary reports do not recalculate automatically. The values sit inside a dialog box, where a reviewer cannot see them. The switch block puts all three cases on the sheet, side by side, where they can be checked and printed, which is why it is easier to audit.
| Case | Volume | Price (USD) | Variable cost (USD) | Revenue (USD) | Operating profit (USD) | Weight |
|---|---|---|---|---|---|---|
| Downside | 9,000 | 115 | 74 | 1,035,000 | 69,000 | 25% |
| Base | 10,000 | 120 | 72 | 1,200,000 | 180,000 | 50% |
| Upside | 11,000 | 124 | 72 | 1,364,000 | 272,000 | 25% |
Downside: 9,000 × (115 − 74) − 300,000 = 9,000 × 41 − 300,000 = $69,000. Upside: 11,000 × 52 − 300,000 = $272,000. The downside story is a softer market: fewer units, a discount to hold them, and a supplier increase. The upside is a strong year with costs held.
A probability-weighted view multiplies each result by its weight. With the three profits in F13:F15 and the weights in G13:G15:
=SUMPRODUCT(F13:F15,G13:G15)
0.25 × 69,000 + 0.50 × 180,000 + 0.25 × 272,000 = $175,250. It sits below the base because the downside loses $111,000 against the base while the upside gains only $92,000. Show it beside the three cases, never instead of them: the weights are judgment.
A data table recalculates one formula for every pair of values in a grid. Its input cells must be plain values on the same sheet, so give it two of its own: J2 holds volume (10,000) and J3 holds price (120). In L5, the corner of the grid:
=J2*(J3-$F$6)-$F$7
Volumes go across M5:Q5, prices down L6:L10. Select L5:Q10, choose Data > What-If Analysis > Data Table, set Row input cell to J2 and Column input cell to J3, and click OK. With B1 set to the base case, the grid fills with operating profit in USD:
| Price \ Volume | 8,000 | 9,000 | 10,000 | 11,000 | 12,000 |
|---|---|---|---|---|---|
| 110 | 4,000 | 42,000 | 80,000 | 118,000 | 156,000 |
| 115 | 44,000 | 87,000 | 130,000 | 173,000 | 216,000 |
| 120 | 84,000 | 132,000 | 180,000 | 228,000 | 276,000 |
| 125 | 124,000 | 177,000 | 230,000 | 283,000 | 336,000 |
| 130 | 164,000 | 222,000 | 280,000 | 338,000 | 396,000 |
Read across a row for volume and down a column for price. A $5 price change at 10,000 units moves profit by $50,000; a 1,000-unit volume change at $120 moves it by $48,000. At $110 and 8,000 units the business barely breaks even.
Move each driver 10% up and down from base, one at a time, and record profit:
| Driver | −10% | +10% | Profit at −10% (USD) | Profit at +10% (USD) | Swing (USD) |
|---|---|---|---|---|---|
| Price | 108 | 132 | 60,000 | 300,000 | 240,000 |
| Variable cost | 64.80 | 79.20 | 252,000 | 108,000 | 144,000 |
| Volume | 9,000 | 11,000 | 132,000 | 228,000 | 96,000 |
| Fixed cost | 270,000 | 330,000 | 210,000 | 150,000 | 60,000 |
Swing is the result at +10% minus the result at −10%, taken as a positive number. Price swings profit by $240,000, more than twice volume's $96,000, because a price change drops straight to profit while a volume change carries its variable cost with it. Price is the driver to watch.
To draw it, sort the table by swing, largest first, and plot the low and high profit columns as a clustered bar chart. Set series overlap to 100% and the vertical axis to cross at 180,000, so the bars spread left and right of the base. The widest bar sits on top, which gives the tornado its shape.
Three cells must agree with the plan:
If any of the three disagrees, a formula is reading a hard-coded number or the wrong column.
Scenarios are only as good as the base they start from. Covirage's tools compute the base figures from your ledger and sales files and check them before anything else is built; the external AI model explains the result and never does the arithmetic. See FP&A reporting, and how to read a forecast bridge for explaining the gap between cases line by line. For related builds, see break-even analysis, budget vs forecast and the 13-week cash flow model.
Sensitivity analysis changes one input at a time and measures the effect on the result. Scenario analysis changes several inputs together to describe a coherent situation, such as a recession case. Use sensitivity to find the driver that matters, scenarios to see the range of outcomes.
Use Data > What-If Analysis. Scenario Manager stores named sets of input values, Goal Seek finds the input that gives a target result, and Data Table recalculates a formula for a list or grid of input values. All three need the result to be a formula of the input cells.
Three is the usual set: base, downside and upside. More than five is hard to present and usually means some cases are sensitivities. Each scenario should have a one-line story explaining why its assumptions move together.
Compute the result at the low and high value of each driver, calculate the swing, sort drivers by swing with the largest first, and plot the low and high results as a clustered bar chart with overlapping bars around the base value.