A spreadsheet usually starts from data and arrives at a result: given a price and a cost, how much margin do I get? Often, though, the real question is the reverse: at what price must I sell to reach a certain margin? Or: how does the result change if the cost varies? What-if analysis tools exist for exactly this. In Excel they are under Data, What-If Analysis; in LibreOffice Calc under the Tools menu and partly in the Data menu.
The starting model
All the tools work on a model in which input cells are kept separate and formulas depend on them. Let us use a simple example:
| Cell | Content | Value |
|---|---|---|
| B1 | Unit price | 25 |
| B2 | Variable unit cost | 14 |
| B3 | Monthly fixed costs | 6,000 |
| B4 | Quantity sold | 800 |
| B5 | Profit | =(B1-B2)*B4-B3 |
With these values the profit is 2,800. Now let us ask some questions.
Goal Seek: finding the input for a result
Question: how many units must I sell to break even, that is, to reach zero profit?
Open Goal Seek (in Calc: Tools, Goal Seek) and fill in:
- Set cell: B5;
- To value: 0;
- By changing cell: B4.
The program tries different values for B4 until B5 reaches the target and proposes the result, in this case about 545.45 units. You can accept it or cancel.
A few practical remarks:
- the cell to change must contain a value, not a formula;
- Goal Seek changes only one input at a time;
- the result is approximate: with non-linear models it may settle on one solution rather than another, or find none. It is worth starting from a reasonable initial value.
For a model this simple the break-even point could also be calculated with a formula (fixed costs divided by unit margin). The strength of Goal Seek shows with more complex models, where inverting the calculation by hand would be long or impossible.
Data tables: seeing many scenarios at once
Goal Seek gives a single answer. A data table instead shows how a result changes as one or two inputs vary, all in one grid.
One-variable table
You want to see the profit for prices from 20 to 30 euros.
- In D2:D12 type the prices to try: 20, 21, …, 30.
- In E1, one row above the first price and one column to the right, type
=B5. - Select D1:E12 and open Data Table (in Calc: Data, Statistics, Multiple Operations).
- Leave the row input cell empty and give B1 as the column input cell.
The program fills E2:E12 with the profit for each price, recalculating the model for each value.
Two-variable table
To see the combined effect of price and quantity:
- Prices in D2:D12, quantities in E1:K1 (500, 600, …, 1,100).
- In the corner cell D1 type
=B5. - Select D1:K12, open Data Table, give B4 as the row input cell and B1 as the column input cell.
You get a grid with the profit for every combination. With colour-scale conditional formatting, the loss and profit zones jump out at once.
Data tables are array formulas: you cannot edit a single cell of the result, only the whole table. On heavy models they can slow recalculation; in Excel you can set calculation to automatic "except for data tables" in the formula options.
Scenario Manager
When you want to compare a few complete hypotheses, for example "cautious", "expected" and "optimistic", each with different values for price, cost and quantity, the Scenario Manager lets you save each combination under a name and switch between them. It can also generate a summary report setting the results side by side. In Calc the feature is under Tools, Scenarios.
Alternatively, many people prefer to build scenarios as columns on a parameters sheet and choose the active one with a drop-down list: it is more transparent and works the same way in every program.
Solver: optimising with constraints
Solver is the most powerful tool. It does not just seek a target value, but the maximum or minimum of a result by changing several cells together, while respecting constraints. In Excel it is an add-in to be enabled in the options; in Calc it is under Tools, Solver.
A typical example is the production mix: two products with different margins, using machine hours and materials in different amounts. You want to know how many units of each to produce to maximise total margin, without exceeding the available machine hours or material stock, and with non-negative quantities.
In Solver you specify:
- the objective cell (total margin) and whether to maximise or minimise it;
- the variable cells (quantities of the two products);
- the constraints (hours used less than or equal to hours available, material used less than or equal to stock, quantities greater than or equal to zero, possibly whole numbers).
For linear problems like this one, the Simplex method finds the optimal solution. The result should always be checked with common sense: a badly set-up model produces solutions that are mathematically correct but practically absurd.
In my work I use data tables to show decision-makers how sensitive a result is to the assumptions: it is often more useful information than the single number.
In short
- Simulation tools need a model with inputs kept separate from formulas.
- Goal Seek finds the value of an input that produces the desired result.
- Data tables show the effect of one or two variables in a grid.
- Scenarios save complete sets of assumptions to compare.
- Solver looks for maximums or minimums by changing several cells within constraints.
