Sensitivity Analysis in Excel — Data Tables + Free Template (2026)

Every plan rests on assumptions — a price, a volume, a cost — and every one of them will turn out to be wrong by some amount. Sensitivity analysis asks the only honest follow-up: how wrong can they be before the plan breaks? In 40 minutes you’ll learn Excel’s complete what-if toolkit: one- and two-variable Data Tables, a colour-coded sensitivity grid, Goal Seek for break-even inputs, Scenario Manager for named cases, and the tornado chart that ranks which assumptions actually matter. Works in Excel 2010 through Microsoft 365.

Dr Waqas Rafique · Founder & CTO · PhD, Statistical Machine Learning
LinkedIn · About

What is Sensitivity Analysis?

Sensitivity analysis measures how much an output of a model — profit, NPV, cash balance, headcount cost — changes when its inputs change. Instead of presenting one number (“profit will be £180,000”), it presents a landscape: profit at every plausible price, at every plausible volume, and at every combination of the two. A single-point forecast is almost guaranteed to be wrong; a sensitivity analysis tells you where reality can land and still be acceptable.

The technique answers three questions that single numbers cannot. First, which assumptions matter? If a 10% swing in price moves profit by 60% but a 10% swing in fixed costs moves it by 4%, you know where to focus negotiation, research, and monitoring. Second, where is the cliff edge? The break-even frontier — the set of input values where profit crosses zero — tells you exactly how much adverse movement the plan survives. Third, how robust is the decision? If the project stays positive across the entire realistic input range, you can commit with confidence; if it flips sign inside that range, you need contingencies before you start.

Excel is unusually good at this because of a purpose-built but oddly hidden feature: the Data Table (under Data → What-If Analysis). A Data Table re-runs your whole model once per test value — or once per pair of test values — and lays the outputs out in a grid, all without macros, copies of the model, or manual retyping. Around it sit three companions: Goal Seek (solve an input backwards from a target output), Scenario Manager (save and switch named bundles of inputs), and ordinary charts for the famous tornado summary.

Sensitivity analysis is deterministic: you choose the input values to test and get exact outputs back. Its probabilistic sibling, Monte Carlo simulation, draws random inputs from distributions and reports the odds of each outcome. They pair naturally — a Data Table maps the terrain, Monte Carlo estimates how likely you are to land on each part of it — and this guide’s final steps show where one hands off to the other.

Before You Start

Sensitivity tools only work on a model with a particular shape. Get these three things right and everything in this guide is mechanical.

Inputs must live in single, dedicated cells

Each assumption — price, units, unit cost, fixed cost, growth rate — needs its own cell, clearly labelled, ideally grouped in one “Assumptions” block and shaded so everyone knows these are the dials. Hard-coded numbers buried inside formulas (=B5*1.2-4000) cannot be tested; extract every constant into an input cell first.

One formula chain from inputs to output

The output cell (say, Profit) must depend on the input cells through live formulas, with no manual steps, pasted values, or circular references in between. The acid test: change an input by hand and watch the output move instantly. If it does not, fix the chain before going further.

Decide your test ranges up front

Pick the realistic low and high for each input — ±20% around base, or specific values from your team. The analysis is only as honest as these ranges: testing price at ±2% when the market could move 15% produces false comfort. Write the ranges down; you will reuse them in the Data Tables and the tornado chart.

1.Step 1 — Set Up the Model (Inputs → Output)

We’ll use a simple, universal profit model that every later step builds on. Lay it out like this:

With the base values, profit is (10−6)×10,000−25,000 = £15,000. Sanity-check the chain now: type 11 into B2 and profit should jump to £25,000 instantly; put it back. The same structure scales to any model — a 50-row cash flow forecast or a full P&L — as long as assumptions are isolated in cells and one output cell summarises the result you care about.

2.Step 2 — One-Variable Data Table

Question: what is profit at each price from £7 to £13? You could type each price into B2 and write down the result seven times — a Data Table does exactly that, automatically.

1. In D3:D9, list the test prices: 7, 8, 9, 10, 11, 12, 13.
2. In E2 — one cell up and to the right of the first test value — link the output:

3. Select the whole block D2:E9 (test values and the output link).
4. Go to Data → What-If Analysis → Data Table. Leave Row input cell empty; set Column input cell to B2 (because your test values run down a column and they are prices, which live in B2). Click OK.

Excel fills E3:E9 with profit at each price: −£15,000 at £7, £5,000 at £9, £15,000 at £10, up to £45,000 at £13. Behind the scenes it substituted each value into B2, recalculated the workbook, and recorded B7 — seven full what-if runs in one command. The results are live: change the model and the table updates.

3.Step 3 — Two-Variable Data Table

The flagship move: profit at every combination of two inputs — classically price × volume. The layout differs from the one-variable case in one key way: the output link goes in the top-left corner of the grid.

1. In D2, link the output: =B7.
2. Down D3:D9, list prices: 7–13.
3. Across E2:K2, list unit volumes: 4,000 / 6,000 / 8,000 / 10,000 / 12,000 / 14,000 / 16,000.
4. Select D2:K9 — corner, row values, column values, and the empty interior.
5. Data → What-If Analysis → Data Table: Row input cell = B3 (volumes run across the row), Column input cell = B2 (prices run down the column). OK.

Excel fills the 7×7 interior with 49 profit figures — 49 complete recalculations of your model. At £7 and 4,000 units, profit is −£21,000; at £13 and 16,000 units it is £87,000; and somewhere between, the numbers cross zero. That zero-crossing line is the break-even frontier, and making it visible is the next step. The mapping rule to memorise: values across the top → Row input cell; values down the side → Column input cell — it feels backwards and is the single most common wiring mistake.

4.Step 4 — Format the Grid with Conditional Formatting

A 49-number grid is data; a coloured grid is an answer. Two rules turn the table into an instantly readable map.

Rule 1 — the 3-colour scale

Select the interior result cells (E3:K9), then Home → Conditional Formatting → Color Scales → Red–White–Green (lowest red, midpoint white, highest green). Profit now shades smoothly from deep red losses to deep green gains, and the gradient’s direction shows each input’s pull.

Rule 2 — flag the loss region explicitly

With the same cells selected: Conditional Formatting → Highlight Cells Rules → Less Than… → type 0 → choose a strong red fill (or red border). Now every loss-making combination is unmistakable, and the visual edge where red gives way to green is the break-even frontier.

Finishing touches that cost nothing and read professionally: format the numbers as £#,##0;[Red]-£#,##0 or scale to thousands with £#,##0,"k"; bold the row/column headers; and put a border around the base-case cell (price £10, 10,000 units) so readers can locate “today” on the map. For a deeper treatment of colour-scaled matrices, see the heat map in Excel tutorial — a sensitivity grid is a heat map of what-ifs.

5.Step 5 — Goal Seek for Break-Even Inputs

The grid shows roughly where profit crosses zero; Goal Seek gives the exact value. It works backwards: you fix the output and Excel solves for the input.

Go to Data → What-If Analysis → Goal Seek and fill in:

Excel iterates B2 until B7 hits zero and reports: break-even price = £8.50 (at base volume and costs). Click Cancel to keep your original value, or OK to accept the solved one. Repeat with By changing cell = B3 to get break-even volume (6,250 units), or B4 to find the highest unit cost the plan tolerates (£7.50).

For this simple model you can verify algebraically — break-even units = Fixed costs ÷ (Price − Unit cost) = 25,000 ÷ 4 = 6,250 — but Goal Seek’s value is that it works identically on models far too tangled for algebra: 40-row cash flows, tax schedules, tiered pricing. The full economics of break-even (contribution margin, margin of safety, charts) are covered in the dedicated break-even analysis in Excel tutorial.

6.Step 6 — Scenario Manager

Data Tables vary one or two inputs systematically. Real futures move several inputs at once: in a recession, volume falls and price gets discounted and input costs wobble. Scenario Manager handles these named bundles.

Open Data → What-If Analysis → Scenario Manager → Add. Create three scenarios over changing cells B2:B5:

Click Show on any scenario and Excel writes its values into the input cells, rippling through the whole model — instant switching between futures. Better still, click Summary…, set the Result cell to B7, and Excel generates a Scenario Summary sheet showing every scenario’s inputs and resulting profit side by side — a ready-made exhibit for the board pack.

Note the worst case: it is not apocalyptic — each input moves a plausible 10–20% — yet profit flips to a £7,000 loss. That is the signature insight of scenario analysis: individually survivable movements compound. A plan that looks robust input-by-input can still fail when adversity correlates, which is precisely why you run scenarios as well as single-input sensitivities.

7.Step 7 — Build a Tornado Chart

A tornado chart compresses the whole analysis into one ranked picture: which inputs move profit the most? Build the data first — for each input, hold all others at base, swing it to its low and high, and record profit (a 1-variable Data Table per input, or just type the values in and read B7):

To chart it: sort the rows by Swing descending, then insert a Stacked Bar chart of two series — (Low − Base) and (High − Base) — with the category axis crossing at zero so bars extend left and right of the base case. Make the first series’ fill subtle red and the second green. Sorted largest-swing-first, the bars taper downwards in the classic tornado shape.

Reading it takes five seconds: price dominates (a ±10% move swings profit by £20,000 — 133% of base profit), unit cost is second, fixed costs barely register. Notice the asymmetry of direction too: cost bars point the opposite way to price bars because they hurt when they rise. That ranking is the management agenda: defend price, negotiate unit cost, and stop agonising over the stationery budget.

8.Step 8 — Interpret and Present the Results

Numbers persuade only when translated. Four habits turn a sensitivity workbook into a decision.

Quote elasticities, not cells. “A 10% price cut costs us two-thirds of profit; a 10% volume gain adds about a quarter” lands harder than any screenshot. Compute each as (% change in output) ÷ (% change in input) from your tornado data — price’s elasticity here is 6.7×, volume’s 2.7×.

Lead with the frontier. The single most decision-relevant fact is the distance from base case to break-even: price can fall 15% (to £8.50) or volume 37.5% (to 6,250 units) before losses begin. That margin of safety, read straight off Goal Seek and the coloured grid, tells leadership how much shock the plan absorbs.

One slide: the tornado. One appendix: the grid. Executives need the ranking; analysts need the map. Present the tornado chart with the break-even quotes on it, and attach the conditional-formatted two-variable grid for anyone who wants to explore combinations.

State your ranges. Every conclusion is conditional on the ± ranges you tested. Write them on the exhibit (“inputs varied ±10% around base; base = £10 / 10,000 units / £6 / £25,000”). It makes the analysis reproducible and pre-empts the inevitable “but what if price falls 20%?” — to which your live Data Table can answer in seconds.

Try It: Live Two-Variable Sensitivity Grid

This is Steps 1–4 running live. The four sliders are the model’s input cells; the grid is a two-variable Data Table of profit at price (rows) × volume (columns), spanning ±30% around your base price and volume, recoloured on every change. Red cells are losses, green are profits — the boundary between them is the break-even frontier. Try dragging unit cost up and watch the red region flood across the grid; the outlined cell is your base case.

Advanced Sensitivity Tips

Percentage-of-base grids

Instead of absolute test values, build the row/column headers as multiples of base (=Base*0.8, =Base*0.9…). The grid then re-centres itself automatically whenever the base assumption changes — one less thing to maintain.

Speed: “Automatic except for data tables”

Every Data Table cell triggers a full workbook recalc, so big models with several grids can crawl. Formulas → Calculation Options → Automatic Except for Data Tables defers them; press F9 to refresh on demand. Remember to flip back (or always F9 before reading results) — a stale grid looks exactly like a fresh one.

Sensitivity on NPV and IRR

Nothing changes for financial models: make the discount rate and growth rate input cells, point the corner at the NPV cell, and the same two-variable recipe yields the classic NPV sensitivity matrix (discount rate × terminal growth) used in every valuation deck.

From deterministic to probabilistic

The grid shows what happens if; it cannot say how likely. When stakeholders ask “what’s the chance we lose money?”, graduate to Monte Carlo simulation in Excel — thousands of random scenarios over the same model, summarised as P10/P50/P90 and a loss probability.

Share the analysis without sharing the spreadsheet

Sensitivity workbooks die in inboxes — the version with the coloured grid is never the version people open. Upload the model output to DataHub Pro and share a live, interactive dashboard instead: stakeholders explore the numbers in a browser, and there is exactly one current version.

Worked Example: Pricing a New Product

A small UK manufacturer is launching a product at a planned £10 price, expecting 10,000 units in year one, with a £6 unit cost and £25,000 of fixed launch costs — the exact model from Step 1, base profit £15,000.

The question from the MD: a major retailer will commit to 14,000 units, but only at £8.75. Better or worse than the plan?

The grid answers in one glance. The two-variable table from Step 3 (price 7–13 down, volume 4,000–16,000 across) shows: at £8.75 × 14,000, profit = (8.75−6)×14,000−25,000 = £13,500 — £1,500 worse than base, despite 40% more volume. The colour scale makes the reason visible: at low margins the grid barely greens as you move right; volume cannot rescue a thin price.

Goal Seek sharpens the counter-offer. Setting profit to the £15,000 base by changing price, with units fixed at 14,000: the deal matches the plan at £8.86, and every penny above that is upside. The team counters at £9.00 (profit £17,000), the retailer accepts, and the decision — which had been argued by instinct for a fortnight — closes with a 10-minute spreadsheet exercise.

The tornado seals the lesson. Ranked swings show price’s £20,000 impact dwarfing volume’s £8,000 at equal ±10% moves. The MD’s takeaway, now on a slide: “we are a price-sensitive business; we trade volume for price only above £8.86.” That sentence is the entire sensitivity analysis, operationalised.

Common Errors & Fixes

Every cell in the Data Table shows the same number

Either calculation is set to “Automatic except for data tables” (press F9, or switch to Automatic), or the Row/Column input cell points at a cell the output does not actually depend on. Verify by typing a test value into the input cell manually — the output must move.

“Input cell reference is not valid” / inputs on another sheet

Data Table input cells must live on the same sheet as the table. Mirror the inputs locally or relocate the table to the model sheet.

Cannot edit or delete part of a Data Table

The results are one array — Excel refuses single-cell edits with “Cannot change part of a data table.” Select the entire results range (not the headers) and delete or rebuild it as a block.

Row and column inputs swapped

If the grid looks plausible but wrong (profit falling as price rises), the Row and Column input cells are reversed. Rule: values across the top → Row input cell; values down the side → Column input cell.

Goal Seek “may not have found a solution”

The target may be unreachable in the realistic range (no price makes a loss-making product yield £1m), or the model is discontinuous (IFs, lookups) near the target. Try a starting value closer to the answer, or widen Goal Seek’s iteration limits under File → Options → Formulas.

Conditional formatting colours look random

The colour-scale range probably includes the header row/column or the corner formula, dragging the scale’s min/max. Apply the rules to the interior result cells only.