How to Do a Sensitivity Analysis in Excel: Step by Step
To do a sensitivity analysis in Excel, put each input of your model in its own cell. Then change those inputs and record how the result moves. Use a one-variable data table for one input, a two-variable data table for two, and Scenario Manager for full cases. Finish with a tornado chart that ranks the inputs by impact.
This guide explains what a sensitivity analysis is, how to set up the model, and how to use each Excel tool. It includes a worked example with real formulas, a faster method with AI, and the mistakes to avoid.
What a sensitivity analysis is
A sensitivity analysis shows how much a result changes when one assumption changes. It answers questions like "What happens to profit if the price drops 10%?"
Most business models rest on guesses. Sales volume, price, costs, and growth rates are all estimates. A sensitivity analysis tests which of those guesses matter most.
Excel groups these tools under What-If Analysis. Microsoft defines it as "the process of changing the values in cells" to see how the results of formulas change.
Microsoft lists three built-in tools: "Scenarios, Goal Seek, and Data Tables." Each has a different job:
- Data tables show one result across many values of one or two inputs.
- Scenario Manager saves full sets of inputs, such as a worst case and a best case.
- Goal Seek works backward. It finds the input needed to reach a target result.
A sensitivity analysis usually uses the first two. Goal Seek helps with a related question, such as the break-even price.
Set up the model before you start
Excel's tools only work if the model is built cleanly. Check these points first.
Put every input in its own cell. Units, price, costs, and rates each need a cell. Don't type numbers inside formulas.
Link the result to the inputs. The result cell, such as profit, must be a formula that refers to the input cells. If it doesn't, nothing will change when Excel swaps in new values.
Keep inputs together. Group them in one block at the top of the sheet. Label each one, and color the input cells so they stand out.
Pick one result to test. A sensitivity analysis is easier to read when it tracks one output, such as profit, net present value, or cash at year end.
Note your base case. Write down the starting values. Every result in the analysis is compared with this base.
Here is a simple profit model to follow along with. It is a made-up bike shop, Northgate Cycles:
| Cell | Label | Value |
|---|---|---|
| B1 | Units sold | 2,400 |
| B2 | Price per unit | $850 |
| B3 | Variable cost per unit | $510 |
| B4 | Fixed costs | $520,000 |
| B5 | Profit | =B1*(B2-B3)-B4 |
At these base values, profit is $296,000.
How to do a one-variable sensitivity analysis with a data table
A one-variable data table tests a range of values for one input. Microsoft says it shows "how different values of one variable in one or more formulas will change the results."
Here is how to test price in the example model:
- In D2 to D6, type the prices to test: $800, $825, $850, $875, and $900.
- In E1, one row above and one column to the right of the first price, type =B5.
- Select D1:E6.
- On the Data tab, select What-If Analysis > Data Table.
- In Column input cell, enter B2, the price cell. Leave Row input cell blank.
- Click OK.
Excel fills E2:E6 with the profit for each price. The formula placement matters. Microsoft says to type it "one row above and one cell to the right of the column of values."
The results for Northgate Cycles:
| Price | Profit |
|---|---|
| $800 | $176,000 |
| $825 | $236,000 |
| $850 | $296,000 |
| $875 | $356,000 |
| $900 | $416,000 |
Every $25 change in price moves profit by $60,000. That is the sensitivity of profit to price.
How to do a two-variable sensitivity analysis
A two-variable data table tests two inputs at once. Microsoft says it shows "how different values of two variables in one formula will change the results of that formula."
Here is how to test price and units together:
- In a blank cell, such as D10, type =B5.
- Below it, in D11 to D13, type the unit volumes: 2,000, 2,400, and 2,800.
- To its right, in E10 to G10, type the prices: $800, $850, and $900.
- Select D10:G13.
- Select What-If Analysis > Data Table.
- In Row input cell, enter B2. In Column input cell, enter B1.
- Click OK.
The result is a grid of profits:
| Units \ Price | $800 | $850 | $900 |
|---|---|---|---|
| 2,000 | $60,000 | $160,000 | $260,000 |
| 2,400 | $176,000 | $296,000 | $416,000 |
| 2,800 | $292,000 | $432,000 | $572,000 |
The grid shows that the shop stays profitable in every combination tested. The weakest corner, 2,000 units at $800, still makes $60,000.
There is one hard limit. Microsoft says "A data table cannot accommodate more than two variables." For more inputs, use Scenario Manager.
Data tables also recalculate whenever the sheet recalculates. In a large workbook, that can slow Excel down. Microsoft suggests setting calculation so data tables recalculate only when you press F9.
How to compare full cases with Scenario Manager
Scenario Manager saves complete sets of inputs and swaps them in. Microsoft describes a scenario as "a set of values that Excel saves and can substitute automatically on your worksheet."
Here is how to set up three cases:
- On the Data tab, select What-If Analysis > Scenario Manager.
- Click Add, name the scenario Base, and enter B1:B4 as the changing cells. Keep the current values.
- Add a Worst case: 2,000 units, $825 price, $530 variable cost, and $540,000 fixed costs.
- Add a Best case: 2,800 units, $875 price, $495 variable cost, and $510,000 fixed costs.
- Select a scenario and click Show to load its values.
- Click Summary and choose B5 as the result cell to build a report.
For Northgate Cycles, the summary shows three profits. The worst case makes $50,000, the base case $296,000, and the best case $554,000.
Microsoft notes a few limits. Each scenario can hold up to 32 changing values. The summary report is not automatic: "Scenario reports are not automatically recalculated." If you change a scenario, create a new summary.
How to find a break-even point with Goal Seek
A sensitivity analysis shows how far the result moves. Goal Seek answers the reverse question: what input value gives a target result?
Microsoft says Goal Seek "takes a result and determines possible input values that produce that result." Its example finds the interest rate needed for a loan payment.
Here is how to find the break-even price for Northgate Cycles:
- On the Data tab, select What-If Analysis > Goal Seek.
- In Set cell, enter B5, the profit cell.
- In To value, enter 0.
- In By changing cell, enter B2, the price cell.
- Click OK.
Excel changes the price until profit hits zero. At 2,400 units and the base costs, the break-even price is about $726.67. That is about 15% below the current $850 price.
Goal Seek has one limit. Microsoft says it "works with only one variable input value." For more than one input, Microsoft points to the Solver add-in.
How to build a tornado chart
A tornado chart ranks inputs by how much they move the result. It is the clearest way to present a sensitivity analysis.
Here is how to build one:
- Pick a test range, such as each input 10% lower and 10% higher.
- Change one input at a time, and record the low and high result. Keep the other inputs at base.
- Put the results in a table with one row per input.
- Sort the rows so the input with the widest range is at the top.
- Insert a bar chart of the low and high results.
For Northgate Cycles, with each input moved 10% up and down:
| Input | Profit at −10% | Profit at +10% | Range |
|---|---|---|---|
| Price | $92,000 | $500,000 | $408,000 |
| Variable cost | $418,400 | $173,600 | $244,800 |
| Units sold | $214,400 | $377,600 | $163,200 |
| Fixed costs | $348,000 | $244,000 | $104,000 |
Price matters most, by a wide margin. A 10% price drop cuts profit from $296,000 to $92,000. Fixed costs matter least. That tells the owner where to spend time checking assumptions.
A bar chart in Excel draws bars from left to right. To get the tornado shape, plot each input's change from the base profit instead of the raw values. Then the low case bars point left and the high case bars point right.
How to do a sensitivity analysis with AI
Building data tables, scenarios, and a tornado chart for a large model takes time. An AI tool can run the same tests from your workbook.
Here is the method in three steps, using Powerdrill Bloom.
Step 1: Upload your model
Start with the spreadsheet that holds your model. The AI Sensitivity Analysis Generator asks you to upload "DCF, business case, operating plan or any spreadsheet model."
Make sure the inputs and the result cell are clearly labeled. The tool needs to know which numbers are assumptions.
Step 2: Choose the sensitivity view in natural language
Describe the test you want. For example: "Show a tornado chart for profit with each input moved 10% up and down. Add a two-way table for price and units sold."
The tool page lists four views: "tornado, two-way, Monte Carlo or break-even." It describes the tornado output as a chart "ranking the inputs that move the answer the most." It also offers a "Sensitivity table over the two assumptions that matter."
Step 3: Check the results and export the analysis
Compare one or two results with your own data table before you share them. The base case should match your model exactly.
Then export. The tool page lists an "Excel workbook, branded PDF or canvas link." For a deck, the Excel to PPT tool turns the same file into slides. The AI Financial Modeling page covers building the model itself.
How to read and present the results
A sensitivity analysis is only useful if people act on it. These habits help.
Lead with the ranking. Open with the tornado chart. It shows in one picture which assumptions drive the result.
Translate ranges into decisions. "Profit falls to $92,000 if price drops 10%" is a fact. "We should test the price before we sign the lease" is a decision.
Show the break-even point. Pair the analysis with the value where the result hits zero. For Northgate Cycles, break-even is about 1,530 units at the base price and costs. Our guide to break-even analysis in Excel covers that calculation.
Use realistic ranges. A 10% move suits stable inputs. A new product's volume may need a much wider range.
State what was held constant. Each one-at-a-time test keeps the other inputs at base. Say so on the slide or in a footnote. Readers then know that a 10% price change assumes the same volume, costs, and fixed spend. This one line prevents most follow-up questions about the method.
Revisit after results come in. Once actual numbers arrive, compare them with your ranges. A budget vs. actual report is a natural follow-up.
Common sensitivity analysis mistakes
These mistakes are common and easy to avoid.
Hard-coded numbers in formulas. If a formula contains 850 instead of B2, the data table can't change it. Keep every input in a cell.
Testing too many inputs at once. A data table handles one or two. Move to Scenario Manager or a tornado chart for more.
Using the same range for every input. Price and volume rarely carry the same uncertainty. Pick ranges that reflect what you actually know.
Ignoring links between inputs. A lower price may raise volume. A one-at-a-time test misses that, so add a scenario that moves both.
Stale scenario summaries. Scenario reports don't update on their own. Rebuild them after any change.
Forgetting the base case. Every table should show the base result, so readers can see the size of each change.
When you are ready to stress-test your next model, try Powerdrill Bloom with the workbook you use for planning.
Frequently asked questions
What is a sensitivity analysis in Excel?
It is a way to test how a result changes when its inputs change. In Excel, you use What-If Analysis tools, mainly data tables and Scenario Manager, to swap in new input values and record the results.
How do I do a one-variable sensitivity analysis in Excel?
List the input values in a column. Put a formula that refers to the result one row above and one column to the right. Select the range, choose What-If Analysis > Data Table, and enter the input cell as the column input cell.
What is the difference between a data table and Scenario Manager?
A data table tests one or two variables across many values and shows all results in one grid. Scenario Manager saves full sets of inputs, up to 32 changing values each, and swaps them in one at a time.
How do I make a tornado chart in Excel?
Move each input up and down by the same amount, one at a time, and record the result. Sort the inputs by the size of the range, then chart each input's change from the base result as horizontal bars.
Can AI do a sensitivity analysis?
Yes. An AI tool can read your spreadsheet model, run tornado and two-way tests, and export the results as a workbook or report. You still need to check the base case and choose realistic ranges.
Sources: Microsoft Support, Introduction to What-If Analysis · Microsoft Support, Calculate multiple results by using a data table · Microsoft Support, Switch between various sets of values by using scenarios.