What-if analysis: data tables, Goal Seek, scenarios and Solver

In depth

Building a model you can question

Good models share a layout: inputs grouped at the top (or on their own sheet) and labelled with their units, calculations below, and outputs where the reader looks first. Ana’s sheet keeps the event in B3:B5, the quote sheet in B7:B11, her cost estimates in B13:B18 and the results in B20:B28. Nothing in the results area is typed: every cell there is a formula.

• Label every input with its unit and its source (the quote sheet; Ana’s estimate).
• Mark input cells so readers can tell them from formulas: a fill colour plus a note, since colour alone is easy to miss.
• Keep one assumption per cell. If the wage changes in January, one edit should update every job.
• Test the model on a case you can work by hand, as above, and at the edges: 40 guests should need one barista and 41 should need two.

Sensitivity analysis by hand

Sensitivity analysis asks which assumptions matter. The simplest version raises one input at a time by the same proportion, say 10%, and records how far the output moves. The table does this for the breakfast, whose contribution is $583.

Two things stand out. The price per guest matters far more than any cost, so a small discount to win a job is expensive. And more guests lower the contribution: 132 guests need a fourth barista, whose four paid hours ($92) cost more than the extra 12 guests bring in ($68.40 after coffee). A model with steps in it can surprise you, which is why you test changes rather than assume their direction.

One-at-a-time changes have two limits. They ignore what happens when inputs move together, and a 10% change may cross a step (like the fourth barista) that a 5% change would not. Data tables and scenarios deal with both.

Data tables

A data table recalculates the model for a list of values of one input, or for every combination of values of two inputs, and lays the results out as a grid. You build it on the sheet. For a one-variable table with its values down a column, put a formula that refers to the output one row above and one column to the right of the first value. For a two-variable table, put that formula in the top-left corner, one input’s values down the column below it and the other’s across the row to its right. Select the whole block and choose Data › What-If Analysis › Data Table.

Excel’s names for the input cells trip people up. The Row input cell is the cell into which the values in the table’s top row are substituted; the Column input cell takes the values in its left column. So a one-variable table with its values down a column uses only the Column input cell. A data table handles at most two input variables, though each can take as many values as you like.

Each time the guests pass a multiple of 40, Ana sends another barista for four paid hours and the contribution drops by $86.30: 41 guests earn less than 40. (At 20 guests the quote is $280 and the $300 minimum applies.) The rule “one barista per 40 guests” is a business decision written into the model, and the table shows what it costs at the edges.

Read along a row and the hourly charge shows its weakness: each extra hour adds $30 to the invoice and $23 for every barista on the job. With one barista an hour earns $7; with two it loses $16, with three $39 and with four $62. Long events with many guests are the ones Ana under-prices, a pattern the challenging example turns into a pricing rule.

A data table recalculates whenever the worksheet does, even if nothing it depends on has changed, so a workbook with several large tables can become slow. Excel can be set to recalculate everything except data tables: in current versions the option is called Partial (Automatic except for data tables in older versions), under File › Options › Formulas or Formulas › Calculation Options. The tables then update only when you ask Excel to recalculate.

Scenarios and Scenario Manager

A scenario, in B01’s sense, is one possible future described by the values of the uncertain inputs. Excel’s Scenario Manager (Data › What-If Analysis › Scenario Manager) saves named sets of values for chosen changing cells, up to 32 of them, and substitutes a set into the sheet when you show it. Its Scenario summary report puts every scenario’s inputs and chosen result cells side by side on a new sheet.

Ana is unsure about her 2027 costs, so she saves three scenarios for the wage, the coffee cost per guest and the van cost, and summarises the breakfast:

Even in the worst case the breakfast keeps a ratio near one half, while the team day, run through the same three scenarios, falls from 32.9% to 22.6%. Scenario Manager has limits worth knowing:

• Showing a scenario writes its values into the changing cells, replacing what was there, so save the current values as a Base scenario first.
• Summary reports are not recalculated: if the model changes, create the report again.
• A best or worst case puts every input at its extreme at once, which is rarely likely, and scenarios carry no probabilities. For an expected value, as in B01, you supply the probabilities yourself.
• Scenarios kept in other workbooks or sheets can be merged in, which helps when several people each supply one.

Goal Seek

Goal Seek works backwards. You give it a Set cell that contains a formula, the value you want that cell to show (To value), and one input cell to change (By changing cell), which the formula must depend on. Excel tries values of the input, iterating until the formula reaches the target, and leaves the answer in the input cell. It changes exactly one input; for more, use Solver.

Ana wants a contribution margin ratio of at least 45% on every job, and the team day falls short. Goal Seek with Set cell B28, To value 0.45 and By changing cell B8 returns a price per guest of about $8.95; the simple example checks it by algebra. Goal Seek can fail or mislead in four ways:

• The target can’t be reached. For a 20-guest, two-hour meeting the quote comes to $250, which the $300 minimum lifts to $300. Asking Goal Seek for an invoice of $250 by changing the price per guest fails: up to $9 a guest the invoice stays at $300, and above $9 it rises.
• The output doesn’t respond near the starting value. The same flat stretch can stop Goal Seek even when a solution exists further away; start it from a different value.
• There is more than one answer. Goal Seek returns the one it reaches first from the current value.
• The answer makes no sense in practice. Goal Seek may return 37.6 guests or a negative price. It knows nothing about whole numbers or common sense; you do.

Its answer is also a typed value, not a formula: change the costs and the price Goal Seek found is no longer right. Always check it by putting it back into the model, and where you can, solve the same equation by hand or with a formula that stays live.

Solver and linear programmes

Some questions have many inputs and limits at once. In late November Ana has to decide how many catering events to accept for December and how many Saturday barista workshops to run. Each job earns money but uses her time and the van, and she has only so much of both. Choosing the best combination is an optimisation problem.

An optimisation model has three parts. The decision variables are the quantities chosen: E, the events, and W, the workshops. The objective is the formula to maximise or minimise: total contribution. The constraints are the limits every plan must respect. Ana’s figures are fictional:

• Contribution: about $350 a catering event (her costing model’s figure for a typical December booking) and $950 a workshop (12 places at $125, less $550 for the room, materials and a second trainer).
• Her hours: 4 per event (quoting, planning and starting the day on site) and 16 per workshop (preparing, selling places, the three-hour session and the follow-up). She can give 90 hours in December.
• Van hours: 3 per event and 3 per workshop (taking the training machine to the hired room and back). The van is free for 45 hours.
• Demand and commitments: at most 15 events (December’s enquiries), at most 3 workshops (Saturdays 5, 12 and 19 December), and at least 8 events, which are already confirmed.

This is a linear programme: the objective and every constraint are sums of decision variables multiplied by constants. Linear models are solved exactly by the simplex method, Solver’s Simplex LP option. The figure draws this one. Each constraint is a line, and the plans that satisfy all of them form the shaded feasible region. Without whole numbers, the best plan lies at a corner of the region, here where Ana’s hours and the van hours run out together: 12.5 events and 2.5 workshops, worth $6,750.

Half an event is not a plan. Adding the integer constraint that E and W are whole numbers moves the answer to 13 events and 2 workshops, worth $6,450. Rounding the fractional answer would not have found it. Rounding to the nearest gives 13 events and 3 workshops, which needs 100 of Ana’s 90 hours and 48 of the 45 van hours; rounding down gives 12 and 2, worth only $6,100.

Setting up Solver

Solver comes with desktop Excel but must be switched on once: File › Options › Add-ins, choose Excel Add-ins in the Manage box, select Go and tick Solver Add-in. It then appears on the Data tab in the Analysis group. Lay the model out first, as the second widget does: the decision variables in their own cells (B4:C4), the objective as a formula of them (=SUMPRODUCT(B4:C4,B5:C5) in B6), and each constraint’s left-hand side as a formula beside its limit (=SUMPRODUCT($B$4:$C$4,B9:C9) in D9, with the plan as an absolute reference so it can be filled to D10).

In the Solver Parameters dialog, Set Objective is the objective cell (B6); To is Max, Min or Value Of; and By Changing Variable Cells lists the decision variables, up to 200 of them. Add each constraint as a cell, a relationship (<=, =, >=, int, bin or dif) and a limit, preferably a cell such as E9 rather than a typed number. Tick Make Unconstrained Variables Non-Negative unless a variable may go below zero. Then choose the solving method:

• Simplex LP for linear models like this one. It finds the true optimum; with integer constraints it also searches the whole-number plans.
• GRG Nonlinear for smooth nonlinear models, such as one where a higher price lowers the number of bookings.
• Evolutionary for non-smooth models, with jumps or steps in them, such as ROUNDUP or IF applied to a decision variable.

When Solver stops, choose Keep Solver Solution or Restore Original Values, and pick any reports. Its Integer Optimality (%) option matters for whole-number models: at its default of 1%, Solver may stop at a whole-number plan within 1% of the best possible. Set it to 0 when you need the proven optimum.

Reading Solver’s answer

A binding constraint holds exactly at the optimum: the resource is used up. The others have slack, the limit minus what the plan uses. In Ana’s whole-number plan the van is binding (45 of 45 hours), while she has 6 of her 90 hours spare, 1 Saturday free and 2 enquiries unaccepted. Solver’s Answer Report lists the original and final values of the objective and the decision variables, and each constraint’s final value, status (binding or not) and slack. The Solver panel in the second widget shows the constraints part of this report under each result.

The Sensitivity Report goes further, but only for models without integer or binary constraints. For each binding constraint it gives a shadow price: how much the optimal objective changes per unit increase in the limit, and, in its Allowable Increase and Allowable Decrease columns, how far the limit can rise or fall before that rate changes. In Ana’s model without the integer constraint, her hours and the van hours both have a shadow price of $50: with 91 hours the best contribution rises from $6,750 to $6,800. The $50 holds for up to 6 more of her hours; after that the Saturday limit binds and more hours add nothing.

With whole numbers, value comes in lumps. One or two more van hours change nothing in the whole-number plan, because E + W still can’t exceed 15; three more allow a fourteenth event and add $350. And a constraint with slack can still hold a whole-number plan back: Ana has 6 hours spare, yet two more (92) would let her swap two events for the third workshop, 11 and 3, worth $6,700. Shadow prices describe smooth trade-offs that whole-number plans don’t have, which is why Excel offers no Sensitivity Report once integer constraints are in the model. To see what a change is worth, change the limit and solve again.

Two other outcomes need recognising. If no plan meets every constraint, Solver reports that it could not find a feasible solution: with 16 events already confirmed, Ana’s model has no answer, because only 15 enquiries exist and 45 van hours cover at most 15 jobs. If the objective can grow without limit, Solver reports that the objective cell’s values do not converge, which nearly always means a constraint is missing. Remove her hours, the van and the enquiry limit, and Solver would book events without end.

Checking a model’s answers

A model can be wrong in its formulas, its assumptions or its use, and the tools above will happily optimise a mistake. Three habits catch most problems.

1. Solve it another way. Goal Seek’s price can be checked by algebra. A small linear programme can be checked by listing every plan: with E from 8 to 15 and W from 0 to 3 there are 32 plans, 24 of them feasible; the best is 13 events and 2 workshops, and the next best, 10 events and 3 workshops, earns $100 less.
2. Back-test it. Run the model on cases whose answers you know. Applied to all 76 events of 2026, Ana’s assumptions give 1,082 paid barista hours and $24,886 of wages, against $25,200 of event wages in Lantern’s books (F02), about 1.2% less; and they leave a contribution of 44.9% of the year’s $62,320 of invoices, in line with the 45% contribution margin used in M01. The costing model is close enough to trust for pricing.
3. Question the inputs. The plan is only as good as the $350 per event and the 4 hours per event. A data table or a scenario on those estimates shows how far the plan depends on them.