Chapter 31: Performing Spreadsheet What-If Analysis
3131
You could use the value 360, for example, for the loan term argument of the PMT function in cell C11 of
Figure 31.1. Using a cell reference has two advantages. First, you’ll have no doubt about the values that
the formula uses. (They aren’t buried in the formula.) Second, you can easily change the value—typing
a new value in the cell is easier than editing the formula.
Using values in formulas may not seem like much of an issue when only one formula is involved, but
just imagine what would happen if this value were hard-coded into several hundred formulas that were
scattered throughout a worksheet.
Exploring Types of What-If Analyses
Not surprisingly, Excel can handle much more sophisticated models than the preceding
example. To perform a what-if analysis using Excel, you have three basic options:Manual what-if analysis Plug in new values and observe the effects on formula cells.
Data tables Create a special type of table that displays the results of selected formula
cells as you systematically change one or two input cells.
Scenario Manager Create named scenarios and generate reports that use outlines or
PivotTables.We discuss each of these types of what-if analysis in the rest of this chapter.Performing manual what-if analysis
A manual what-if analysis doesn’t require too much explanation. In fact, the example that
opens this chapter demonstrates how it’s done. Manual what-if analysis is based on the
idea that you have one or more input cells that affect one or more key formula cells. You
change the value in the input cells and observe the formula calculations. You may want to
print the results or save each scenario to a new workbook. The term scenario refers to a spe-
cific set of values in one or more input cells.Manual what-if analysis is common. People often use this technique without even realizing
that they’re doing a what-if analysis. This method of performing what-if analysis certainly
has nothing wrong with it, but you should be aware of some other techniques.If your input cells are not located near the formula cells, consider using a Watch Window to monitor the
formula results in a movable window. We discuss this feature in Chapter 3, “Performing Basic
Worksheet Operations.”Creating data tables
This section describes one of Excel’s most underutilized features: data tables. A data table
is a dynamic range that summarizes formula cells for varying input cells. You can create a