3.6 What-if analysis and simple modelling

What you'll be able to do

Run a model backwards and sideways. Which price reaches the profit target, how the answer moves across a whole grid of assumptions, and which plan survives the pessimistic case. Not only "what is", but "what would it take".

Before you start

Build a five-line model on a blank sheet. Units sold, price, cost per unit and fixed costs are the inputs. Profit is one formula. Every tool below pushes on this small machine.

The work

1. Same either way: a model keeps its inputs, its assumptions and its formulas apart. Inputs sit in labelled cells, and results are worked out from them. A number is never buried inside a formula. These tools work by turning input dials, and a number typed into a formula is a dial they cannot reach.
2. No documented keyboard shortcut for this — use the menu: Goal Seek runs one formula backwards. Go to Data → What-If Analysis → Goal Seek. Set the profit cell, to 100000, by changing the price cell, and press Enter. Excel searches and lands, and the model shows the price that reaches the target. One question with one unknown: break-even points, required marks, needed sales.
3. No documented keyboard shortcut for this — use the menu: a data table answers the same question across a whole range at once. For one variable, put candidate prices down a column with the profit formula referenced at the top. Select the block, go to Data → What-If Analysis → Data Table, and point the column input at the price cell. For two variables, put prices down and unit counts across, and the grid fills with the profit at every combination. That grid is the sensitivity table every business case wants.
4. No documented keyboard shortcut for this — use the menu: Scenario Manager stores named versions of the world. Go to Data → What-If Analysis → Scenario Manager → Add and make three: Optimistic, Expected and Pessimistic. Each holds its own set of input values. Show swaps a whole version in. Summary lays all three side by side on one sheet. Scenarios move several dials together, which the one-dial tools cannot do.
5. No documented keyboard shortcut for this — use the menu: Solver is for "the best result within limits". Turn it on once at File → Options → Add-ins in the ribbon. Then go to Data → Solver and set it up. It maximises profit by changing price and units, within limits such as capacity and a price ceiling. That is the constrained planning problem of real work. Know it exists and what its dialog asks for, and come back when a real problem needs it.
6. Same either way: after any of these runs, say the answer as a sentence with its assumption attached. "₹412 per unit reaches the target, if costs hold." A number without its assumptions is how models mislead people.

Try it

On your five-line model: Goal Seek the break-even price. Build a one-variable table over ten prices, and a two-variable grid of price against units. Make three scenarios and summarise them on one sheet. Read each result aloud with its "if".

Check yourself