How to Run a Sensitivity Analysis in Excel
Learn how to run sensitivity analysis in Excel, build one- and two-variable data tables, and spot which assumptions could change a financial decision.
- Use a one-variable table to isolate volume and a two-variable table to test price and volume together.
- Check the center and a corner against the model.
- At $100, the example breaks even at 875 units.
If your budget shows $5,000 in operating profit, how much room do you have to cut prices or miss your sales target? Sensitivity analysis answers that question by recalculating the same model with different assumptions. In this guide, you’ll build one- and two-variable tables in Excel, check the results against the base case, and find the sales volume at which the business breaks even.
What Is Sensitivity Analysis?
Sensitivity analysis measures how a model’s output changes when you vary selected inputs. Start with a base case, change the inputs you want to test, and hold the remaining assumptions constant. The difference in output shows how exposed the result is to the changes you tested.
For example, a budget might assume a $100 selling price and sales of 1,000 units. Price and volume are the inputs, and operating profit is the output. A one-variable test shows what happens as sales volume changes at $100 per unit. A two-variable test shows what happens across combinations of price and volume.
The same approach applies to other financial models. A cash forecast might test how slower collections affect the month-end cash balance. A valuation model might test how different discount rates affect net present value. In each case, the range of inputs should reflect the decision the model supports.
Sensitivity Analysis vs Scenario Analysis
Use sensitivity analysis to see how much an output changes across a range of prices or sales volumes. Use scenario analysis to evaluate a set of assumptions that could occur together. For example, a promotion scenario might pair a lower price with higher expected volume and extra marketing spend. A grid of price and volume combinations helps isolate their effects. A promotion scenario helps evaluate the whole plan.
If the quarter has already ended, variance analysis helps explain why actual profit differed from budget. If you already know the result you need, Excel’s Goal Seek works backward from that target to a single input. For instance, it can estimate the volume required for $0 operating profit at a fixed price.
How to Choose Sensitivity Analysis Inputs and Ranges
Start with the decision and the output that could change it. If the decision is whether to approve a discount, operating profit and the break-even sales volume are useful starting points. If the concern is liquidity, a minimum cash balance may matter more.
Record the base value, the low and high values to test, the source for that range, and the person who can confirm it. A price range could come from recent quotes or an approved discount policy. A volume range could come from comparable periods, orders in hand, or the sales forecast. Keep the period and units consistent. For rate inputs, specify whether a change is relative or in percentage points. Adding two percentage points to a 10% rate gives 12%. Increasing that rate by 2% gives 10.2%.
In the illustrative budget below, you’ll test a price range of $95 to $105 and quarterly sales of 800 to 1,200 units. Costs stay fixed at the base assumptions throughout the exercise. Before using that approach for a real budget, check whether the sales range crosses a capacity limit or a supplier pricing tier that would also change costs.
How to Run Sensitivity Analysis in Excel
Use a single worksheet in desktop Excel. Enter these assumptions and the profit formula to create the base model.
| Cell | Model item | Value or formula |
|---|---|---|
B2 | Selling price per unit | 100 |
B3 | Quarterly units sold | 1000 |
B4 | Variable cost per unit | 60 |
B5 | Quarterly fixed costs | 35000 |
B6 | Quarterly operating profit | =(B2-B4)*B3-B5 |
The base case in B6 should show $5,000. Use that cell as the output reference in both tables.
Build a One-Variable Data Table
Start with volume and leave the selling price at $100.
- Enter
=$B$6inE3. Enter800,1000, and1200inD4:D6, one value per row. - Select
D3:E6and open Data → What-If Analysis → Data Table. - Set Column input cell to
$B$3, leave Row input cell blank, and select OK.
Excel fills E4:E6 with −$3,000, $5,000, and $13,000. The 1,000-unit result should match the model’s $5,000 base case. At this price, every additional unit contributes $40 toward fixed costs and profit.
Build a Two-Variable Data Table
Add price across the top row and keep volume down the left column.
- Enter
=$B$6inH3. Enter95,100, and105inI3:K3. Enter800,1000, and1200inH4:H6. - Select
H3:K6and open Data → What-If Analysis → Data Table. - Set Row input cell to
$B$2for the prices across the top. Set Column input cell to$B$3for the volumes down the left. Select OK.
Quarterly operating profit by price and sales volume.
| Quarterly units sold | $95 per unit | $100 per unit | $105 per unit |
|---|---|---|---|
| 800 | −$7,000 | −$3,000 | $1,000 |
| 1,000 | $0 | $5,000 | $10,000 |
| 1,200 | $7,000 | $13,000 | $19,000 |
Check the center cell against B6. Then verify a corner by hand. At $95 and 800 units, each unit contributes $35. That produces $28,000 before fixed costs and −$7,000 after fixed costs. Finally, check that the $100 column matches the one-variable table. Those three checks help catch a wrong output reference, swapped input cells, or stale results.
How to Interpret a Sensitivity Table
Start with the values closest to the decision. At 1,000 units, a price reduction from $100 to $95 uses up the entire $5,000 of base-case profit. With price held at $100, volume can fall to 875 units before profit reaches zero. You can calculate that threshold directly by dividing $35,000 in fixed costs by the $40 contribution per unit. At $95, contribution drops to $35 and break-even rises to 1,000 units.
The table shows the outcome for each listed combination. If a price cut is expected to lift demand, check what supports that expectation and whether the team can handle the added volume. For example, at $95 the team would need to sell at least 1,143 units to preserve the original $5,000 profit under these cost assumptions. A promotion scenario can then include any incremental costs and a sales estimate grounded in past results.
A management brief could state the decision this way.
At $95 per unit, quarterly profit falls from $5,000 to $0 if sales stay at 1,000 units. Preserving the original $5,000 requires at least 1,143 units under the current cost assumptions. Before approving the discount, review whether past promotions support that volume and whether early orders are tracking toward it.
Keep the workbook version and the source for each tested range with the brief. Use Kuse to turn a reviewed sensitivity table into a decision brief with the checked matrix, assumption notes, and supporting files. Ask for the base case, the threshold that changes the decision, and the evidence still needed. Have the model owner verify the numbers and wording against Excel, then reuse the brief format for the next forecast.
FAQ
Why does my Excel sensitivity table show the same value in every cell?
Change one model input manually and see whether the linked output moves, then restore the base assumption. If the output changes but the grid stays flat, check the table’s output reference and confirm that the dialog points to the model’s actual input cells. If Excel is set to skip automatic data table recalculation, select the table formulas and recalculate them.
Why does the sensitivity table’s base case differ from my model?
Check whether the table headers use the exact underlying base values. A rounded header can display the same number as the model while passing Excel a different input. Also check the output reference, recalculate the grid, and confirm that inputs outside the test still match the base case.
How can I check which input cells an existing data table uses?
Select a calculated cell inside the grid and look at the formula bar. Desktop Excel displays the data table formula, which can show its row and column input references in that order. Trace those cells to their labeled assumptions and confirm that they feed the linked output.
When should I use a two-variable data table instead of Goal Seek?
Use a two-variable data table when you want to see outputs for combinations of two assumptions, such as profit across prices and sales volumes. Use Goal Seek when you have a target output and want Excel to adjust one input to reach it, such as finding the sales volume that gives $0 profit at a fixed price.
How do I choose realistic inputs for sensitivity analysis?
Start with the approved or current base case, then use evidence such as historical ranges, contract terms, supplier quotes, or forecast assumptions to set the endpoints. Include values close to any threshold that could change the decision. Label exploratory values so reviewers can tell which ranges have stronger support.
How do I use a sensitivity table to find the break-even point?
Look for a cell with zero profit or adjacent cells where profit changes sign. If break-even falls between the values shown, solve for the threshold with the model formula or use Goal Seek with one input. For a volume threshold, round up to a whole unit when needed and check the resulting profit in the model.