Chapter 31. Using What If, Scenario Manager, Goal Seek, and Solver

In this chapter

Using What-If 864

Using Scenario Manager 867

Using Goal Seek 874

Using Solver 879

When Dan Bricklin invented VisiCalc in 1979, he was trying to come up with a tool that would let him recalculate his MBA school case studies quickly. Almost three decades later, spreadsheets are still used for the same functionality.

Newer spreadsheet tools such as Goal Seek and Solver allow you to back directly into the assumptions that lead to a solution. This chapter discusses some of Excel 2007’s feaures that are helpful when you are tying to find a specific answer.

Using What-If

Once you have set up a model in Excel, it is very easy to make copies of the model side by side and then change the various input variables to test their impact on the final result. Because this type of analysis answers the question of what happens if a change is made, it is known generically as what-if changes.

What-if analyses are the least formal method in this chapter. You simply copy the input variables and formulas multiple times. You can then vary the input variables until you reach a suitable solution.

For example, Figure 31.1 shows a worksheet to calculate the monthly payment on a new car purchase. In Cells E1, E2, and E3 are the known values: the price, term, and interest rate. Cell E4 calculates the monthly payment, using the =PMT() function.

Figure 31.1. You may not like the answer in Cell E4, but Excel makes the answer easy to find.

Cells E1:E4 are a self-contained mini-model. You can easily copy these cells several times over and perform what-if analysis on the car payment model.

Figure 31.2 shows a basic what-if worksheet. You can use this worksheet to manually plug in different numbers. Columns F and G show the effects of changing the number of months. Columns H:J factor in a lower interest rate. Columns K:M show the effects of finding a lower price. Based on a number of options, Column N starts to hone in on a scenario to get to the $695 target payment: Use 63 months, 5% interest, and a price of $38,500.

Figure 31.2. By making multiple copies of the table, you can create a simple What-If model.

There is nothing magic about what-if analyses. There are no ribbon commands involved. You simply copy the model and plug in a few different numbers. The remaining topics in this chapter are more structured with better features.

Creating a Two-Variable What-If Table

The analysis in Figure 31.2 is fairly ad hoc. It basically enables you to try various combinations until you find one that is close to your target payment. If you have two variables to manipulate, you can use Excel’s fairly powerful Data Table command. To use a data table, follow these steps:

1. Enter a formula in the upper-left corner of the table. This formula should point to at least two variable cells.
2. Along the left column of the table, enter various values for one of the input values.
3. Along the top row of the table, enter various values for the other input variable.
4. Select the entire table.
5. From the Data ribbon, select Data Tools, What-If Analysis, Data Table.
6. In the Data Table dialog box, enter a row input cell and a column input cell.
7. Click OK to complete the table.

For example, you can use the Data Table command to try to negotiate on price and term of the loan by following these steps:

1. Use the formula in Cell E4 as the formula in the top-left corner of your table.
2. From E5:E17, fill in various possible values for purchase price.
3. From F5:K5, fill in various possible values for the term of the loan.
4. Select the entire table, E4:K17, as shown in Figure 31.3.

   Figure 31.3. Preparing for a two-variable what-if analysis.

   

5. Select Data Table from the Data ribbon to display the Data Table dialog box, as shown in Figure 31.4. The dialog box asks you for a row input cell and a column input cell. The Row Input Cell field offers to take each value from the top row of the table and plug it into a particular cell.

   Figure 31.4. Setting up the Table dialog.

   

6. Because the values in F5:K5 are loan terms, specify E2 for the row input cell.
7. Similarly, the Column Input Field offers to take each value from the left column and replace that value in a particular cell. Because these cells contain vehicle prices, choose E1 as the column input cell.
8. Click OK. Excel fills in the intersection of each row and column with the monthly payment, based on the price in the left column combined with the loan term in the top row. Figure 31.5 shows the resulting table.

   Figure 31.5. Excel performs 78 what-if analyses in one command.

   

9. Reselect just the interior of the table. You can see that Excel represents the table with the TABLE() array function. Figure 31.6 shows the table with a heat map applied.

Figure 31.6. The values in the table are calculated by a single TABLE() array formula.

→  For more information on data visualizations, see “Creating Heat Maps with Color Scales,” page 158 in Chapter 9.

Using Scenario Manager

The Data Table command is great for models with two variables that can change. Sometimes, however, you have models with far more variables that can change. In such a case, you should use the Scenario Manager, which allows you to create multiple scenarios, each changing up to 32 variables.

With up to 32 variables changing, it is best to use named ranges for all the input variables before you define your first scenario. One of the results of the Scenario Manager is a summary report. Using named ranges for all the input cells makes the report far easier to understand.

→  To learn how to use named ranges to your advantage, see “Using Named Ranges to Simplify Formulas,” page 846, in Chapter 30.

Generally, Scenario Manager allows you to set up named scenarios such as “Best Case,” “Worst Case,” and “Most likely.” In each scenario you can specify values for up to 32 variables.

You would then have a model that calculates results based on the 32 input variables. For example, you might have a business plan that projects sales for the next 120 months using growth rates in the input variable section of the spreadsheet. The important distinction is that while you may only have 32 input variables, you might base millions of formulas on these 32 input variables.

Use the Scenario Manager dialog box to quickly switch to a different set of input variables. The worksheet can quickly be calculated using Best Case and Worst Case scenarios.

For a specific example, Figure 31.7 shows a sales forecasting model. All the highlighted cells are variables that can change. The model calculates a total forecast in Cell B16 and a ratio in Cell B18. To set up and use scenarios, follow these steps:

1. Select Data, Data Tools, What-If Analysis, Scenario Manager to display the Scenario Manager dialog. Initially, the Scenario Manager indicates that there are no scenarios defined, as shown in Figure 31.8.

   Figure 31.7. This forecast model is based on nine variable cells.

   

   Figure 31.8. The Scenario Manager dialog before you add the first scenario.

   

2. Click the Add button to add the first scenario. The Edit Scenario dialog appears.

   Tip From

   

   It is best to add one scenario that represents your starting assumptions. Otherwise, those numbers will be lost.

3. In the Edit Scenario dialog, choose which cells will be changing. Because the variable cells are not adjacent, select the first contiguous range and then Ctrl+click to add additional ranges, as shown in Figure 31.9.

   Figure 31.9. You Ctrl+click to select all the changing cells.

   

   As shown in Figure 31.10, the Scenario Values dialog box appears, in which you can edit the values for each starting cell. It is a little annoying that this dialog can show only five values at a time. If your model contains the maximum of 32 values that can change, you have to scroll several times to see all the values in this dialog.

   Figure 31.10. You use the Scenario Values dialog to edit values for a scenario.

   

4. Edit any values in the Scenario Values dialog. If you have additional scenarios to add, click the Add button. When you are done assigning scenarios, click OK.
5. Try switching between scenarios in the Scenario Manager by either double-clicking a scenario or clicking the scenario and clicking Show, as shown in Figure 31.11. If you are going to add a new scenario similar to one existing scenario, show that scenario before clicking Add.

Figure 31.11. The new scenario is shown behind the dialog box after you double-click a scenario name.

Creating a Scenario Summary Report

One powerful feature of Excel scenarios is the ability to create a scenario summary report. When you click the Summary button on the Scenario Manager dialog, Excel allows you to choose either a scenario summary report or a pivot table report. In either case, you should select one or more cells that represent the results of the model. For example, Figure 31.12 shows OurCo Book Sales and Conversion Rate selected.

Figure 31.12. You can hold down Ctrl to select more than one result cell.

After specifying the results cells, the scenario summary report is added on a new worksheet in the workbook. This is an amazingly useful report that has a number of useful features.

After the initial creation, the summary is as shown in Figure 31.13. Notice that there are group and outline buttons along both the rows and the columns of the report. The plus sign next to Cell A3 indicates that the comments in Row 4 are currently hidden.

Figure 31.13. The default scenario summary report.

You can use word wrapping in a summary report. For example, Figure 31.13 shows that word wrapping was used to make the headings in Row 3 appear on two lines and that the column widths were adjusted. You will probably always have to make these adjustments to make your summary reports look better.

Figure 31.14. You can adjust word wrapping and column widths.

If you click the minus sign next to Row 5, you can hide the assumption cells and just show the results, as shown in Figure 31.15.

Figure 31.15. If your manager’s eyes glaze over with a table of numbers, you can show just the results.

As you create each scenario, the Scenario Manager adds a comment with your name and the date and time. You can also add your own comments. As shown in Figure 31.16, you can click the plus sign next to Row 3 to reveal the comments in the report.

Figure 31.16. You can show comments in a report.

Adding Multiple Scenarios

You might want to share a workbook with others and have them add their own scenarios so that you can get opinions from people in other areas of your company, such as sales, marketing, engineering, and manufacturing. To do this, follow these steps:

1. Save the workbook with just the starting scenario.
2. Route the workbook to each person. In a hidden field, Excel keeps track of who adds each scenario.
3. When you get the routed workbook back, open both the original workbook and the routed workbook.
4. Display the Scenario Manager in the original workbook.
5. Click the Merge button in order to display the Merge Scenarios dialog, as shown in Figure 31.17.

   Figure 31.17. Merging scenarios from a workbook routed to others.

   

6. In the Book drop-down, choose the name of the routed workbook. In Figure 31.17, the dialog shows that two scenarios are available on Sheet 1.
7. Excel usually encounters identically named scenarios in the merge process. It differentiates any scenarios with identical names by adding a date or name to the incoming scenarios as shown in Figure 31.18. If these scenarios are truly identical to the scenario that you originally sent out, delete those scenarios.

   Figure 31.18. Merged scenarios are added to the bottom of the list.

   

8. In the Scenario Manager, click Summary. The Scenario Summary dialog appears.
9. In the Scenario Summary dialog, click Scenario PivotTable report. The initial pivot table appears, as shown in Figure 31.19.

   Figure 31.19. The default pivot table shows scenarios from all people on the routing list.

   

10. Drag the field from the Report Filter area to be the first Row Labels field. You can now see scenarios grouped by author. As shown in Figure 31.20, although this is an interesting view, there is not a good way to see the assumptions that each author used to arrive at his or her results.

Figure 31.20. You can move the field from the Report Filter to the first row label in order to compare scenarios by person.

Using Goal Seek

Have you seen the television show The Price Is Right? One of the games on the show is the Hi Lo game. A contestant tries to guess the price of an item, and Bob Barker tells the player that the actual price is higher or lower. The process of honing in on a price of $1.67 might involve guesses of $2, $1, $1.50, $1.75, $1.63, $1.69, $1.66, $1.68, and $1.67. Using the techniques described so far in this chapter, you might find yourself playing this game with Excel to try to narrow in on an answer.

You might have an Excel worksheet set up that calculates a final value using several input variables. How can you solve the formula in reverse? You want to find input variables that will generate a certain answer.

One difficult option is to determine if there is another Excel function that reverses the calculation. For example, =ARCSIN() performs the opposite of =SIN().

Another difficult option is to use algebra to attempt to solve for one of the input variables.

Mostly, though, I find people will simply play the “Hi-Lo” game, successively plugging in higher and lower answers to the input cell until they narrow in to an input variable that produces the desired result.

If you’ve ever found yourself playing the Hi-Lo game, check out the Goal Seek command. This command will, in effect, play the Hi-Lo game at hyperspeed, arriving at an answer within a second.

Consider the car payment example at the beginning of the chapter. You want to find a price that yields a $695 monthly payment. If you do some research, you might find the =PV() function that can solve this. Most people will simply plug in successively higher or lower values for the price in Cell E1 (refer to Figure 31.21).

Figure 31.21. Goal Seek lets you find one value by changing one other cell.

Excel has an option that allows you to quickly hone in on a value. It is called Goal Seek. To use Goal Seek, follow these steps:

1. Select the answer cell. In this example, it would be the payment in Cell E4.
2. From the Data Tools group of the Data ribbon, select the What If Analysis drop-down and then choose Goal Seek. The Goal Seek dialog appears, as shown in Figure 31.21.
3. In the Goal Seek dialog, indicate that you want to set the answer cell to a particular value by changing a particular input cell. In this example, you want to set cell E4 to the value of $695 by changing Cell E1. Excel quickly tries to hone in on a value. When Excel gets to within a penny of the value, the Goal Seek Status dialog appears, as shown in Figure 31.22. This dialog reports that it was trying to reach a target value of $695 and was able to reach that value. Behind the dialog, the worksheet shows the proposed price of $36,828.54 in the worksheet.

   Figure 31.22. You can choose to accept the proposed solution or revert to the previous numbers.

   

4. Either accept this value by clicking OK or revert to the original value by clicking Cancel.

Goal Seek is great for quick calculations. Figure 31.23 shows an example in which two additional Goal Seek operations have been done. One operation set Cell F4 to $695 by changing the term in Cell F2. The next operation set Cell G4 to $695 by changing the interest rate in Cell G3.

Figure 31.23. Three different Goal Seek commands find how to yield a $695 payment by changing either the price, term, or rate.

Using Solver

It is possible to design problems that are far too complex for Goal Seek. These problems may have dozens of independent variables and various constraints. In such a case, you can use the Excel Solver add-in.

Using Solver is a bit tricky. With Solver, you specify a range of cells that can be changed. You are allowed to specify certain constraints on the solution. Finally, you indicate that you want one particular formula cell to either be maximized, minimized, or set to a particular value.

The Solver add-in, which is free with Excel, was written by Frontline Systems. Solver cannot solve some complex modeling systems. In such a case, you can purchase a premium version of Solver that can handle up to 2,000 input variables. To learn more about the premium version of Solver, visit Frontline Systems, at www.Solver.com.

Installing Solver

To install Solver, follow these steps:

1. Choose the Windows Icon menu, and then click Excel Options. The Options dialog appears.
2. Choose Add-Ins from the menu on the left of the Options dialog.
3. At the bottom of the Add-Ins page that appears, choose Excel Add-ins from the Manage drop-down. The Add-Ins dialog appears.
4. Click Go.
5. In the Add-Ins dialog, make sure that Solver is checked.

Solving a Model Using Solver

To use Solver, your worksheet should contain one or more input variables. The worksheet should contain one or more formulas that result in a solution within a single cell.

For each input variable, there might be certain constraints. For example, you might want to assume that a certain variable must be positive or that it should be in a certain range of values.

When using Solver, you will identify the input range, the output cell, and the constraints. You can ask Solver to minimize or maximize the input cell. Or, you can ask Solver to set the output cell to a particular value.

Solver will rapidly loop through many different input variables, trying to find a combination that meets your goal.

This might be easier to understand with a concrete example. Figure 31.28 shows a worksheet used to model production of widgets. For the sake of this example, say you have a factory that is capable of making widgets. Cell B23 indicates that each worker can make five widgets per hour. Workers who work evenings, nights, or weekends are paid a shift differential. You can choose to keep your factory running for anywhere from 5 shifts a week (Monday through Friday, first shift) up to 21 shifts per week. You can basically sell as many widgets as you produce, provided that the overall cost is less than $2 per widget. You have a skilled workforce of 100 workers available for first shift, 82 workers for second shift, and 75 workers for third shift. How many shifts should the plant be open in order to maximize production? Solver runs circles around Goal Seek in situations that deal with multiple constraints. To find the answer, use Solver as follows:

Figure 31.28. A worksheet to model widget production.

1. Note that Cells B3 through B11 define how many shifts the factory will be open. All the remaining cells in the model calculate the total number of widgets produced and the average cost per widget.
2. As with Goal Seek, you start out by telling Solver that you want to set a target cell equal to a maximum, minimum, or certain value by changing other cells. For example, the Solver Parameters dialog shown in Figure 31.29 indicates that the goal is to maximize widget production by altering the number of shifts.

   Figure 31.29. Maximizing widget production.

   

3. Enter the first constraint, that the market will only bear a manufacturing cost of $2 per widget. To specify this constraint, click the Add button in the Solver Parameters dialog.
4. As shown in Figure 31.30, tell Solver that the manufacturing cost must be less than or equal to $2 per widget.

   Figure 31.30. Building the cost constraint.

   

5. Tell Solver that there cannot be a negative number of shifts. To do so, you need to add a constraint to indicate for each shift that the shift count must be greater than or equal to zero, as shown in Figure 31.31.

   Figure 31.31. Although it is obvious to you, Solver must be told that it cannot have negative numbers of shifts.

   

6. Specify that Cells B3:B5 must be less than or equal to five because you can have only five of each shift during the week.
7. In this model, it is not valid to work 0.32 shifts; only integer values can be used in a given range. Therefore, select the value int for the comparison operator to tell Solver that a certain range can accept only integers (see Figure 31.32).

   Figure 31.32. You use the integer constraint to prevent fractional answers.

   

8. In this case, the Saturday and Sunday shifts are a special case: The company is either open or not. The only two possible values for each cell is 0 or 1. This is a special constraint called a binary constraint. Select the bin value in the comparison operator to specify that Cells B6 through B11 are limited to binary values.
9. After you have entered all the constraints, click OK to return to the Solver Parameters dialog.
10. Click the Options button on the Solver Parameters dialog to open the Solver Options dialog. Figure 31.33 shows that by default, Solver works for no more than 100 seconds. This is designed to prevent Solver from trying for a long time to find a solution.

    Figure 31.33. You use the Solver Options dialog to fine-tune the processes used.

    

11. When you have entered all the constraints and parameters, click OK to close the Solver Options dialog and return to the Solver Parameters dialog.
12. Click the Solve Model button on the Solver Parameters dialog. Solver begins to iterate through possible solutions. If Solver finds a result, it reports success, as shown in Figure 31.34.

    Figure 31.34. Solver reports success.

    

13. In the Solver Results dialog, choose the Answer to have Excel provide a new worksheet that compares the original and final values. As shown in Figure 31.35, the answer report is added as a new worksheet. In the answer report, Solver tells you that you can produce 54,600 widgets by operating five of each shift during the week and one Saturday shift. The remaining shifts are not cost-effective to keep the cost per widget in Cell F22 under $2. With this current solution, the cost per widget is $1.97.

Figure 31.35. The day shift workers will be picking up some overtime on Saturdays, thanks to Solver.

You can save each Solver solution as a scenario. All these scenarios later show up in the Scenario Manager.