Sensitivity analysis shows how a model’s calculated output changes when selected assumptions change. In Excel, a one-variable data table tests one input; a two-variable data table tests combinations of two inputs.
Use it to understand a model’s dependence on assumptions. The table does not assign probabilities, predict a share-price range or make the underlying forecast reliable.
Start with a small valuation calculation
Use a blank worksheet. The example is a simplified constant-growth perpetuity, not a complete company DCF. Assume the next annual cash flow is 100, the discount rate is 10% and growth thereafter is 2%. The rate and cash flow must use the same annual basis.
| Cell | Meaning | Enter |
|---|---|---|
| B2 | Next annual cash flow | 100 |
| B3 | Annual discount rate | 10% |
| B4 | Annual growth rate | 2% |
| B6 | Calculated value | =B2/(B3-B4) |
The base result is 100 ÷ (10% − 2%) = 1,250. The discount rate must exceed growth for this illustration. A full DCF also needs an explicit forecast where appropriate, consistent cash-flow and discount-rate definitions, timing and any enterprise-to-equity adjustments.
Create a one-variable data table
- Enter 9%, 10% and 11% in D9:D11.
- Enter
=$B$6in E8. Leave D8 empty. - Select D8:E11 and open Data → What-If Analysis → Data Table.
- Set Column input cell to B3. Leave Row input cell empty.
The values run down a column, so they replace the discount-rate input through the Column input cell field. The expected outputs are:
| Discount rate | Calculated value |
|---|---|
| 9% | 1,428.57 |
| 10% | 1,250.00 |
| 11% | 1,111.11 |
Keep the base model linked to the table. Typing expected answers into the results area creates a static comparison, not a native What-If Data Table.
On Mac, consult Microsoft’s Mac data-table instructions for the interface.
Create a two-variable data table
- Enter
=$B$6in D14. - Enter growth assumptions of 1%, 2% and 3% across E14:G14.
- Enter discount rates of 9%, 10% and 11% down D15:D17.
- Select D14:G17 and open Data Table.
- Set Row input cell to B4 and Column input cell to B3.
The row contains growth assumptions, so Row input cell points to growth. The column contains discount rates, so Column input cell points to the discount rate.
| Discount rate / growth | 1% | 2% | 3% |
|---|---|---|---|
| 9% | 1,250.00 | 1,428.57 | 1,666.67 |
| 10% | 1,111.11 | 1,250.00 | 1,428.57 |
| 11% | 1,000.00 | 1,111.11 | 1,250.00 |
In this setup, the next cash flow remains fixed at 100 as growth changes. That convention matters: a different model might calculate the next cash flow from growth, producing different table values.
Check a result manually
For the 11% discount rate and 1% growth combination, the answer is 100 ÷ (11% − 1%) = 1,000. Confirm both that corner and the base case before interpreting the rest of the table.
Higher discount rates reduce value in this example. Higher growth increases value. If your outputs run in the opposite direction, check the inputs, formula and the row/column mapping.
Use Goal Seek for a target output
Goal Seek asks a different question: what single input produces a target result? With cash flow 100 and growth 2%, a target value of 1,000 implies a discount rate of 12%.
Use B6 as the formula cell, 1,000 as the target and B3 as the changing input. Inspect the result and the formula’s valid range. A mathematical solution is not evidence that the implied discount rate is economically appropriate.
Distinguish sensitivity from scenarios
A sensitivity table isolates selected inputs while other assumptions follow the model’s existing logic. A scenario changes a coherent set of business assumptions, such as lower sales, slower collections and a hiring delay.
Be careful with inputs that are related. For example, a risk-free rate can already feed a calculated cost of equity. Varying both independently without a clear design can make the combinations difficult to interpret.
Troubleshoot the table
- Same output everywhere: confirm the output formula depends on the selected input cells.
- Unexpected direction: check which variable runs across the row and which runs down the column.
- Stale results: inspect calculation settings, including whether data tables are excluded from automatic recalculation.
- Invalid values: investigate zero denominators and assumptions outside the model’s valid range.
- Different spreadsheet application: check its support and interface for native What-If Data Tables before sharing.
The cell addresses above describe this example; they do not refer to an attached sensitivity workbook. Menu placement can vary by Excel version. Microsoft documents data-table setup and calculation behavior.
Apply the method to your model
Choose an output that answers the decision, such as closing cash, operating profit or a clearly defined valuation. Choose a sensible input range, test the mapping and explain what the comparison leaves unchanged.
The worked three-statement model provides a separate operating case. The valuation bridge explains the difference between business value and common equity value.
