What-if analysis

What-if tools change the inputs of a model and look at what happens to its results. They are in the Forecast and Analysis groups on the Data tab.

Goal Seek and Solver calculate first and show you the answer; nothing on the sheet changes until you accept it, and accepting is one undo step.

Goal Seek

Goal Seek works backwards: it finds the value of one input cell that makes a formula reach the number you want. For example, the price that makes profit reach 10,000.

1. Click the cell with the formula — it fills in the first field.
2. Click Data › Forecast › Goal seek….
3. Fill in the Goal seek dialog:

4. Click Find. The answer appears in the dialog, for example B7 reaches 10000 when B2 is 12.5, found in 9 recalculations.
5. Click Apply to write the answer into the input cell, or change the fields and click Find again. Apply is available only when Goal Seek found an answer.

The status bar confirms the change, for example B2 set to 12.5.

If Goal Seek can’t find an answer, the dialog says why:

Data tables

A data table recalculates one or two formulas for a list of input values and writes the results in a grid — a loan payment for five interest rates, say, or for every combination of rate and term.

Lay out the table

The table is a rectangle on the sheet. Its top row and left column are its edges; the cells inside are filled with results.

The formulas must refer, directly or through other cells, to the input cell the values will be put into. The input cells themselves must be outside the table.

Fill the table

1. Select the whole rectangle, edges included.
2. Click Data › Forecast › Data table….
3. In Data table:
   • Row input cell — the cell the values in the top row are put into. Leave it blank for a table with values down the side.
   • Column input cell — the cell the values in the left column are put into. Leave it blank for a table with values across the top.
4. Click Fill.

For each input value, the input cell is set, the formulas are recalculated and their results written into the table. Afterwards the input cells are put back as they were. The status bar says, for example, Filled 25 cells from two inputs. The results are numbers, not a live table — run it again after changing the model.

Scenarios

A scenario is a named set of values for the same input cells — Best case, Worst case, Budget. You can switch the sheet between scenarios, and write a summary that compares their results side by side.

Add a scenario

1. Type the values for the scenario into the input cells.
2. Select the input cells — up to 32 cells, none holding a formula.
3. Click Data › Forecast › Scenarios…. Once the sheet has scenarios, the button shows how many, such as 3 scenarios.
4. In Scenarios, type a name in the box labelled Add the selection followed by its address and as — for example Best case.
5. Click Add.

The status bar says, for example, “Best case” saved, changing 3 cells. Adding a scenario with a name that already exists replaces it (“Best case” updated.). To add another, close the dialog, change the input values, select the cells and add again.

Show a scenario

1. Click Data › Forecast › Scenarios….
2. Click Show beside the scenario.

Its values are written into its cells and every formula recalculates. The status bar says Showing followed by the scenario’s name. Showing a scenario is one undo step. The list shows each scenario’s cells and values under its name.

To delete a scenario, click the bin beside it (Delete followed by its name).

Summarise the scenarios

1. Click Data › Forecast › Scenarios….
2. In Result cells for the summary, type the cells whose results you want to compare, such as B7 or B7:B9. It starts as the current cell.
3. Click Summary. It is available once the sheet has at least one scenario.

A new sheet called Scenario Summary is added and shown. It has a column for Current values — what the sheet held when you made the report — and a column for each scenario, with rows for the Changing cells: and the Result cells:. A note at the bottom says the report is numbers, not live. The sheet you started from is left as it was. The status bar says Summary written to “Scenario Summary”.

Scenarios are saved in the workbook in the format Excel uses.

Forecast Sheet

Forecast Sheet continues a history of values into the future — next year’s monthly sales from the last three years, say — and draws the forecast with a prediction interval that widens the further ahead it goes.

1. Select the two columns: the timeline on the left (dates or numbers, evenly spaced) and the values beside it. Leave out the headings.
2. Click Data › Forecast › Forecast….
3. Check the fields in Forecast sheet:

4. Click Create.

A new sheet called Forecast is added and shown. It has columns for Timeline, Values, Forecast, Lower bound and Upper bound, and a line chart of all of them titled with the interval, such as 95% prediction interval. The last historical value is repeated in the forecast columns so the lines join.

The forecast uses exponential triple smoothing: the level, the trend and any repeating pattern are fitted to the history. The status bar describes what was found — for example 12 periods forecast on “Forecast”. Found a repeating pattern 12 points long, 2 missing points filled in. MASE 0.41 — better than assuming nothing changes. MASE compares the model’s error with the error of assuming nothing changes from one period to the next; below 1 means the model does better.

Missing values in the history are filled in, and values that share a timestamp are averaged.

The FORECAST.ETS, FORECAST.ETS.CONFINT, FORECAST.ETS.SEASONALITY and FORECAST.ETS.STAT functions use the same model in a formula. See the Function reference.

Solver

Solver finds the values of several input cells that make a formula as large or as small as possible, or reach a target, while keeping to limits you set — the product mix that maximises profit within a budget, say.

1. Click the cell with the formula to optimise — it fills in the first field.
2. Click Data › Analysis › Solver….
3. Fill in the Solver dialog:

4. Click Solve. The result appears in the dialog: a sentence about what was found, and the value Solver chose for each changing cell.
5. Click Keep solution to write those values into the sheet, or change the model and click Solve again. Click Cancel to leave the sheet as it was.

The status bar confirms how many cells were updated.

Constraints

Each constraint row has a range on the left, a relation, and — except for int and bin — a right-hand side.

The right-hand side can be a number (100), a range the same size as the left side (D2:D5, compared cell by cell), or a formula (=SUM(C2:C5)/2). Click Add constraint for another row, and × (Remove this constraint) to delete one. Rows with an empty left side are ignored.

What Solver’s answer means

Solver searches for the best values directly, trying steps in each direction, which suits models full of IF, ROUND and lookups. The answer is the best point the search reached, not a proven optimum, and whole-number constraints are searched rather than proved. The same model gives the same answer each time. The dialog says this in a note under the fields.

Result sentences you may see:

Problems with the model are shown in red: