In this chapter
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.
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.
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.
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.
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:
For example, you can use the Data Table command to try to negotiate on price and term of the loan by following these steps:
TABLE()
array function. Figure 31.6 shows the table with a heat map applied.TABLE()
array formula.→ For more information on data visualizations, see “Creating Heat Maps with Color Scales,” page 158 in Chapter 9.
Note
Note that the values calculated in the table are live formula values. If you change the numbers along the edge of the table, the TABLE()
array formula recalculates new loan payment amounts.
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:
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.
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.
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.
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.
Tip From
To force word wrapping in the middle of a cell, you position your mouse cursor at the break point and press Alt+Enter.
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.
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.
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 re-format) the scenario summary report.
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:
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).
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:
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.
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.
To install Solver, follow these steps:
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:
You can save each Solver solution as a scenario. All these scenarios later show up in the Scenario Manager.
18.221.89.18