Introduction
Once a sheet calculates results from inputs, you can ask it questions. How many units must we sell to make a profit of 50,000? What would the monthly payment be at different interest rates? What do the best, expected and worst cases look like? Excel's What-If Analysis tools, on the Data tab, answer these questions without rewriting a single formula, as long as the sheet is built as a proper model.
How it works
A model keeps its inputs (prices, rates, quantities) in clearly labelled cells and calculates its results with formulas that refer to them. All the what-if tools work by changing input cells and watching result cells.
Goal Seek (Data, What-If Analysis, Goal Seek) works backwards. You give it a Set cell (a formula), the value To reach, and the By changing cell (one input). Excel tries values until the formula reaches the target and shows the answer, which you can keep or cancel. It changes only one input, and the input must not hold a formula.
A data table shows a formula's result for many input values at once:
- In a one-variable data table, list input values down a column, put a reference to the result formula in the cell above and to the right of the list, select the whole block and choose Data, What-If Analysis, Data Table, giving the input cell as the Column input cell.
- A two-variable data table puts one input's values down a column and the other's across a row, with the result formula in the corner cell, and uses both a Row input cell and a Column input cell.
Excel fills the table with a special {=TABLE(...)} array: you cannot edit one cell of it, and large data tables can slow recalculation (Formulas, Calculation Options, "Automatic except for data tables" helps).
Scenario Manager stores named sets of values for up to 32 input cells, such as Best case and Worst case. Show switches the model to a scenario, and Summary creates a report comparing the result cells across all scenarios.
When you need the best answer with several inputs and rules, such as the production mix with the highest profit within machine hours, use Solver, a free add-in that you enable in Excel's add-ins settings. It changes many inputs at once while respecting constraints.
Example
A loan model with the amount in B1, the yearly rate in B2 and the years in B3 calculates the monthly payment in B4 with =PMT(B2/12,B3*12,-B1).
| A | B | |
|---|---|---|
| 1 | Loan amount | 500000 |
| 2 | Yearly rate | 0.09 |
| 3 | Years | 10 |
| 4 | Monthly payment | =PMT(B2/12,B3*12,-B1) |
Line by line
- B4 shows the payment for the current inputs.
- Data, What-If Analysis, Goal Seek: Set cell B4, To value 5000, By changing cell B1. Excel finds the largest loan with a payment of 5,000.
- Cancel the Goal Seek to keep the original loan. In D2:D5 list rates 0.07, 0.08, 0.09 and 0.10, put
=B4in E1, select D1:E5 and create a data table with B2 as the Column input cell.
Expected result
B4 shows about 6,333.79 per month. Goal Seek reports a loan of about 394,708 for a payment of 5,000. The data table shows the payment for each rate: about 5,805.42 at 7%, 6,066.38 at 8%, 6,333.79 at 9% and 6,607.54 at 10%.
In the real world
Budgets, pricing decisions, loan and investment plans and business cases are all what-if models. Finance teams present best, expected and worst cases from Scenario Manager or from input switches, and break-even points are a classic Goal Seek question.
Common mistakes
- Inputs typed into formulas, which leaves nothing for the tools to change.
- Goal Seek on a formula that cannot reach the target, which gives an approximate or no answer.
- Trying to edit one cell of a data table, which Excel refuses.
- Forgetting that Goal Seek changes the input cell; press Cancel to keep the original.
Best practices
- Separate inputs, calculations and results, and colour input cells consistently.
- Use data tables to show sensitivity, how much a result changes with each input.
- Save a copy before experimenting, or use scenarios to keep the original values.
Keyboard shortcuts
| Action | Windows | Mac |
|---|---|---|
| Goal Seek by key tips | Alt, A, W, G | — |
| Data Table by key tips | Alt, A, W, T | — |
| Scenario Manager by key tips | Alt, A, W, S | — |
| Recalculate, including data tables | F9 | F9 |
Summary
What-if analysis changes the inputs of a model and watches its results. Goal Seek finds the single input value that reaches a target, data tables show results for many values of one or two inputs, Scenario Manager stores and compares named sets of inputs, and Solver finds the best values of several inputs under constraints.
Quick check
Key takeaways
- Keep inputs in their own cells; what-if tools change inputs and watch results.
- Goal Seek works backwards from one target to one input.
- Data tables show many what-ifs at once; Scenario Manager compares named cases.
My notes
Write down what you want to remember from this lesson. Notes are saved on this device as you type, and appear on your dashboard.