18. Using What-If, Scenario Manager, Goal Seek, and Solver
When Dan Bricklin invented VisiCalc in 1979, he was trying to come up with a tool that would let him quickly recalculate his MBA school case studies. Thirty-five years later, spreadsheets are still used for the same functionality.
Newer spreadsheet tools such as Goal Seek and an improved Solver enable you to back directly into the assumptions that lead to a solution. This chapter discusses some of Excel 2016’s features that are helpful when you are trying to find a specific answer.
Using What-If
After you have set up a model in Excel, you can 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 analysis.
What-if analyses are the least formal method in this chapter. You copy the input variables and formulas multiple times. You can then vary the input variables until you reach a suitable solution.
For example, Figure 18.1 shows a worksheet to calculate the monthly payment on a car purchase. 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.
Cells E1:E4 are a self-contained mini-model. You can copy these cells several times over and perform what-if analysis on the car payment model.
Figure 18.2 shows a basic what-if worksheet that you can use to plug in different numbers manually. Each column uses a different combination of price, term, and interest rate.
There is nothing magic about this type of what-if analysis. There are no ribbon commands involved (other than applying Conditional Formatting, Color Scale to highlight the prices). You copy the model and plug in a few numbers. This is how most Excel worksheets use what-if analyses. The remaining topics in this chapter cover the What-If commands on the ribbon.
Creating a Two-Variable What-If Table
The analysis in Figure 18.2 is fairly ad hoc in that 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 to build the table shown in Figure 18.3:
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. These values are substituted in a cell known as the column input cell.
3. Along the top row of the table, enter various values for the other input variable. These values are substituted in a cell that Excel calls the row input cell.
4. Select the entire table.
5. From the Data tab, select Data Tools, What-If Analysis, Data Table.
6. In the Data Table dialog, enter a row input cell and a column input cell.
7. Click OK to complete the table.
You can use the Data Table command to negotiate the 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:E21, fill in various possible values for purchase price.
3. From F4:K4, fill in various possible values for the term of the loan.
4. Select the entire table, E4:K21, as shown in Figure 18.4.
5. Select Data Table from the Data tab to display the Data Table dialog, as shown in Figure 18.4. The dialog 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.
6. Because the values in F4:K4 are loan terms, specify E2 for the row input cell.
7. Similarly, the Column Input Cell field offers to take each value from the left column and replace that value in a particular cell. Because these cells contain vehicle prices, select 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.
9. Select just the interior of the table. You can see that Excel represents the table with the TABLE() array function. Figure 18.5 shows the table after a color scale has been applied.
Figure 18.5 The values in the table are calculated by a single TABLE() array formula. Oddly, you cannot enter the TABLE formula by typing it. You must use the Data Table command.
Note
You can use the Data Table command when only a single variable is changing. Enter values for the variable down the left column and enter a single cell with 1 in the top row. In the Data Table dialog, specify any blank cell as the row input cell. Alternatively, enter the changing values across the top row, and use a blank cell as the column input cell.
Modeling a Random Scenario Using a Data Table
The model shown in Figure 18.6 is known as a Random Walk Down Wall Street. In this simple model, a penny is flipped 25 times. If the penny comes up heads, you win a dollar. If the penny comes up tails, you lose $1. The model uses =IF(RAND()>.5,1,-1) to simulate the coin flip. The chart shows that the stock analyst running this model did well; he was always up and finished by making money (see Figure 18.6).
Note: Credit for this technique goes to Professor Simon Benninga. If you ever had a college class in financial modeling, you likely used Simon’s textbook on the subject.
United States law requires financial firms to add the note that past results are not indicative of future returns. With the current model, press F9 to run the simulation again and you could see very different results. Same model. Same methods. Same guy pressing the F9 key, but the hotshot stock analyst now loses someone’s money—hopefully not yours (see Figure 18.7).
In my simple model, there are 25 coin flips. But it is possible to model 500 or 1,000 iterations with far more data points than a simple coin flip. After each run through the model, you might be interested in tracking the Max, the Min, the Standard Deviation, plus the most important statistic—did you make or lose money at the end?
In Figure 18.8, five formulas track the performance of one run of the model. It is possible to use Data, What-If Analysis, Data Table as shown in this figure. The top-left corner cell is blank. The Row Input Cell field is blank. The Column Input Cell field is pointing to a blank cell completely unrelated to the model.
When you click OK to run the data table, every row returns the results of flipping the coin 25 times. Imagine if you had a model with many random variables and 1,000 iterations. You can use this technique to exercise the model hundreds of times (see Figure 18.9).
Using Scenario Manager
The Data Table command is great for models with two variables that can change. However, sometimes you have models with far more variables that can change. In such a case, you could use the Scenario Manager, which enables you to create multiple scenarios, each changing up to 32 variables. With the added flexibility, it takes longer to create each scenario.
To learn how to use named ranges to your advantage, see “Using Named Ranges to Simplify Formulas,” p. 260.
Tip
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 easier to understand.
Figure 18.10 shows a sales model. Several input cells are highlighted in gray. The Scenario Manager dialog offers various scenarios that someone manually set up. Double-click any scenario to load those values into the model.
You can compare the results of all scenarios using either a regular summary report or a pivot table summary report. Figure 18.11 shows the regular summary report.
Unfortunately, it is a tedious process to set up each scenario. To set up a scenario, use the following steps:
1. Select Data, Data Tools, What-If Analysis, Scenario Manager to display the Scenario Manager dialog. Initially, the Scenario Manager indicates that no scenarios are defined. Click the Add button to add a scenario. The Add Scenario dialog appears.
Note
It is best to add one scenario that represents your starting assumptions. Otherwise, those numbers will be lost.
2. In the Add Scenario dialog, enter a name for this scenario and then 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 (see Figure 18.12).
3. The Scenario Values dialog box appears, which can be used to edit the values for each starting cell (see Figure 18.13). Note that if you had previously named your input cells, the cell names appear in this dialog instead of addresses. Type new values. If you are finished creating scenarios, click OK. If you want to return to the Add Scenario dialog box to define another scenario, click OK.
Creating a Scenario Summary Report
One powerful feature of Excel scenarios is the capability to create a Scenario Summary report. When you click the Summary button on the Scenario Manager dialog, Excel enables 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.
Caution
The Scenario Summary report is a snapshot in time. If you later change scenarios or add new scenarios, you have to re-create and reformat the Scenario Summary report.
Adding Multiple Scenarios
You might want to share a workbook with others and have them add their own scenarios to 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 to display the Merge Scenarios dialog.
6. In the Book drop-down, select the name of the routed workbook.
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. If these scenarios are truly identical to the scenario that you originally sent out, delete them.
Using Goal Seek
On the television show The Price Is Right, one of the games is the Hi-Lo game. A contestant tries to guess the price of an item, and the host tells the player that the actual price is higher or lower. The process of homing 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 finally $1.67. Using the techniques described so far in this chapter, you might play this game with Excel to try to home in on an answer.
You might have an Excel worksheet set up that calculates a final value using several input variables. To solve the formula in reverse, you need to find input variables that generate a certain answer. There are several possible approaches:
One difficult approach is to determine whether another Excel function reverses the calculation. For example, =ARCSIN() performs the opposite of =SIN(), and =NPER(), =RATE(), or =PV() back into a =PMT().
Another approach is to use algebra to attempt to solve for one of the input variables.
Most people simply play the Hi-Lo game by successively plugging in higher and lower answers to the input cell until they narrow in on an input variable that produces the desired result.
If you play the Hi-Lo game, consider using the Goal Seek command. In effect, this command plays 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 $475 monthly payment. You might find a =PV() function that can solve this. However, most people plug in successively higher or lower values for the price in cell E1 (see Figure 18.14).
Excel’s Goal Seek option, on the other hand, enables you to home in quickly on a value. 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 tab, select the What-If Analysis drop-down, and then select Goal Seek. The Goal Seek dialog appears, as shown in Figure 18.14.
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, set cell E4 to the value of 475 by changing cell E1. Excel begins trying to home in on a value. When Excel gets to within a penny of the value, the results are presented. Behind the dialog, the worksheet shows the proposed price of 26272.12 in the worksheet.
4. Either accept this value by clicking OK or revert to the original value by clicking Cancel.
Goal Seek can be applied to other input cells. For example, in Figure 18.15, the Goal Seek in column F sought a $475 monthly payment by changing the term. If you are willing to make payments for 69 months, you can buy the desired car. However, when Goal Seek tried to get a $475 monthly payment by changing the interest rate, the result was the impossible negative interest rate.
Figure 18.15 Three different Goal Seek commands find how to yield a $475 payment by changing either the price, the term, or the rate.
Using Solver
It is possible to design problems that are far too complex for Goal Seek. These problems might have dozens of independent variables and various constraints. In such a case, you can use the Excel Solver add-in.
With Solver, you identify an output formula cell that you want to be maximized, minimized, or set to a particular value. You specify a range of cells that can be changed. You then specify a number of constraints on input cells or other formulas in the model.
The Solver add-in, which is free with Excel, was written by Dan Fylstra and Frontline Systems. History buffs might remember that Dan Fylstra was the president of VisiCorp—the world’s first spreadsheet program. Frontline Systems offers more advanced versions of Solver plus an Excel Data Miner tool at www.Solver.com.
Tip
Solver was improved in Excel 2010. To get the old Solver to work, you had to have a good grasp on linear mathematics. Hence, the old Solver never worked for me. The new Solver offers advanced methodologies that find solutions to far more problems. If you had tried Solver before without success, it is time to try it again.
Installing Solver
To install Solver, follow these steps:
1. Press Alt+T and then press I to display the Add-Ins dialog.
2. 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 also 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 identify the input range, the output cell, and the constraints. You can ask Solver to minimize or maximize the input cell. Alternatively, you can ask Solver to set the output cell to a particular value. Solver uses advanced algorithms to find input variables that meet your goal and fit within the constraints.
This might be easier to understand with a concrete example. Figure 18.16 shows a worksheet used to model the production of widgets. Cell B23 indicates that each worker in your factory can make 40 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 five shifts a week (Monday through Friday, first shift) up to 21 shifts per week. You can sell as many widgets as you can 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 to maximize production? Solver runs circles around Goal Seek in situations that deal with multiple constraints.
To use Solver to find the answer, use the following steps:
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. Your goal is to maximize the number of widgets produced (cell D22) while keeping the average cost per widget in F22 to less than $2.00.
2. Select Solver from the Data tab in the ribbon.
3. Enter D22 as the Objective. Choose to set this to a Max (see Figure 18.17).
4. Define the input cells as B3:B11 in the By Changing Variable Cells box.
5. Use the Add button to add new constraints. One obvious constraint is that F22 must be less than a $2 cost. Cells B3:B5, which represent weekday shifts, cannot be higher than 5 per week because there are only 5 weekday shifts. There are other important constraints. You probably don’t want an answer that says you work 0.329454 of a shift, so add a constraint that says B3:B11 must remain integers. Add a constraint that says those same cells cannot be negative. For the weekend shifts, the only valid values are 0 or 1. Use a constraint of Binary to allow only 0 or 1 as the input cells.
6. Three Solving Methods are available. Always start with GRG Nonlinear. If that doesn’t work, try Evolutionary. If you are a math genius and have built a model with pure linearity, try Simplex LP.
7. Click the Solve button. Solver begins to iterate through possible solutions. If Solver finds a result, it reports success.
8. Click Save Scenario and give the scenario a solution such as SolverSolution. If you are going to define the solutions as a scenario, after doing this step, you can choose Restore Original Values and click OK. You can then use Data, What-If Analysis, Scenario Manager to add a scenario with your original values.
9. Select the Answer Report to have Excel provide a new worksheet that compares the original and final values.
Note
The GRG engine finds a solution that matches the constraints. However, it might not be the best solution. The LP Solver engine finds the best solution but only if you set up the model as a linear problem. The Evolutionary engine uses Monte Carlo to try random choices, hoping to home in on a better solution.
In the answer report, Solver tells you that you can produce 42,400 widgets by operating five day and evening shifts and two night shifts. The remaining shifts are not cost effective to keep the cost per widget in cell F22 less than $2.
With this current solution, the cost per widget is exactly $2.
Tip
Frontline Systems offer premium versions of their Solver products that can handle more input variables than the Solver in Excel. If you find that Solver cannot solve your problem, sign up for a free trial of Premium Solver at http://mrx.cl/solver77.