Excel’s What-If Analysis tools allow us to change the values of inputs to a calculation and explore different possible results. In the following article we will consider a single, simple calculation and discuss how each of the three What-If Analysis tools: Goal Seek, Scenario Manager and Data Tables, can be applied.
The commands for What-if Analysis in Excel are located on the Data tab, in the Forecast group as shown in Fig. 1.
The What-If Analysis command has three options shown in Fig. 2:
What-If Analysis allows us to explore possibilites with our calculations. We consider a simple, somewhat abstract example shown in Fig. 3. Four values are inputs into a calculation. For our purposes we don’t worry about the details of the calculation, it can be a formula involving basic math or some combination of Excel’s builtin functions.
For the sake of this discussion, we are going to use a formula with basic mathematical operators, in particular, in Fig. 4 cell C4 calculates the formula A-B+C-D/2 , where A, B, C and D are the names of the variables stored in cells A2, B2, C2 and D2 respectively. The formula is entered into cell C4 as shown in the formula bar and the result of the calculation will be displayed in cell C4 when we hit the Enter/Return key.
Note that in Fig. 3, the “Calculate” and “Answer” blocks are combined into the worksheet cell C4 shown in Fig. 4. This is just the structure of Excel, we enter a formula into a cell, and the result of the calculation is displayed in that cell.
Goal Seek allows us to set the output value of the calculation and the input value is adjusted to give that required output. Goal Seek is able to handle a complex calculation, but only one input can be changed to give one output. Selecting the Goal Seek command opens the Goal Seek dialog shown in Fig. 5. “Set cell” is the address of the cell whose value we wish to set to a specific value, in this case C4. The “Set cell” must contain a calculation. Next we set the value of the “Set cell” to the value we want in the “To value:” field. For this example we choose the value 300. Finally we point to the cell that we want Excel to change to give the required output value. In this case we are going to vary cell D2, but we could let Excel vary any one of the four input values to get the desired output.
Having set the values in the Goal Seek dialog, we click “OK” and let Excel do its work. The result for our example is shown in Fig. 6. Excel has changed the value in cell D2 from 176, shown in Fig. 5, to -24, shown in Fig. 6 and this results in the desired value of 300 in cell C4.
We could equally well have allowed Excel to vary one of the other input variables in cells A2, B2 or C2 to result in the calculated result of 300 in C4. Which input value you allow to change will, of course, depend on the meaning of the inputs and the details of the calculations you are performing. The important point here is that Goal Seek can only vary a single input to achieve one desired output result. No matter how complex your calculation, Goal Seek may be able to provide this sort of analysis for you.
More information about Goal Seek, and two more examples, can be found in Denise’s article: Excel’s Goal Seek – Part of “What-if Analysis”.
Scenario Manager is basically a way to save sets of input values – each scenario can have up to 32 values, and Excel can save as many scenarios as you wish. The only practical limits are memory and computer storage, but for most of us that won’t be a big limitation. In our example, each scenario will have 4 values corresponding to the 4 input values, A, B, C and D, as shown in Fig. 3. Table 1 shows 5 example scenarios that we can add to Scenario Manager.
Table 1: 5 Scenarios to save with Scenario Manager
Start the Scenario Manager from the Forecast group on the Excel Data tab. Click on What-If Analysis and select “Scenario Manager…” as shown in Fig. 7.
The Scenario Manager dialog opens. If there are no scenarios yet, the dialog looks like Fig. 8. Click on the “Add…” button to add a scenario.
The Add Scenario dialog opens and we can name our scenarios and let Excel know which cells we are going to be changing – these are the set of input cells. Our first scenario will have the values that we found earlier in the Goal Seek section, we can think of this as our base case. The scenario name can be very flexible and we will actually list the parameters so it is easy to differentiate the different scenarios. Starting with the base case we can, for example, enter a name as shown in Fig. 9.
The name of this scenario is “Base Case: A = 275, B = 5, C = 18, D = 176” – it is useful to provide a name that helps to distinguish the scenarios. The cells to be changed are 4 cells in the range $A$2:$D$2 which are the 4 values in row 2 of Fig. 4. Click on OK and we can enter the values for the scenario.
Click on OK to enter the scenario into the Scenario Manager. Click “Add…” to add another scenario. We continue adding another 4 scenarios, the values from Table 1, so the Scenario Manager looks like Fig. 11.
To examine the results of each scenario, simply click on a scenario name and select “Show” – this will fill the values saved in the scenario into the appropriate cells and update the calculations as shown in Fig. 12.
Note that we don’t have to save all of the input values in Scenario Manager, only the values that we want to change. Scenario Manager is useful if we have a few specific cases that we want to examine and display. As mentioned above, each scenario can save up to 32 values and we can add as many scenarios as computer memory and storage allow. In practice, it becomes very tedious to add more than a few scenarios, so this tool seems most useful for displaying final sets of values determined from other analyses.
Data Tables allow us to change either a single input value, or two input values and examine multiple possible outputs in a table as a single view. The Data Table allows us to view many possible outcomes and choose the best one for our needs.
We start by creating a one-input column data table. We are going to generate a set of input values for the variable “C” in cell C2. Our formula is in cell C4 so our column of “C”-values must start one cell to the left and one cell down from the formula. We will set 10 values in the range B5:B14. Select the range of input values including the formula cell and the output cells as shown in Fig. 13.
Go to the Forecast group on the Data tab and click on “Data Table…” under What-If Analysis. This will open the Data Table input cell dialog, as shown in Fig. 14. This dialog allows us to choose the cell whose values we want to vary. In this case, we vary the value in cell C2 from 25 to 250 in steps of 25.
When we click OK, the ouput cells are populated with the results of the calculation for each of the possible input values as shown in Fig. 15.
The set of input values is in the range B5:B14, the formula is in cell C4, and the output values are in the range C5:C14. The Data Table allows us to easily compare results for a large set of possible input values.
We can create a two-input data table by putting the column input values under the formula cell, and putting the row input values in the row to the right of the formula cell as shown in Fig. 16. Similar to the one-input data table discussed above, the Column input cell is C2 and the set of values to be used in that cell are in the range C5:C14. The Row input cell is D2 and the set of values to be used in that cell are in the range D4:F4, The column input values are highlighted in violet and the row input values are highlighted in blue in Fig. 16.
Clicking the OK button on the Data Table dialog will result in the Data Table being populated with all the possible values as shown in Fig. 17. This updates the Data Table by calculating every possible pair of column and row values using the function in cell C4. The output of the calculations fills the range D5:F14 in this example.
Conditional formatting from the Home tab has been applied, using a Yellow-Green color scale which nicely highlights the pattern in the values, moving from the lowest value in the upper right corner to the highest value in the lower left corner.
The Data Table allows us to easily see how changing one or two of the input parameters affects the result of a calculation. Applying conditional formating can assist us in understanding the structure of the resulting table, particularly when the table gets large. More information about visualization with conditional formatting can be found in Denise’s articles:
Excel’s What-If Analysis tools allow us to vary some input parameters and explore alternative outcomes. These can be very helpful for some analyses, and can be used as a first step to deeper exploration of data sets.