Excel 2010 Bible

Chapter 36: Performing Spreadsheet What-If Analysis

747

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:

l Manual what-if analysis: Plug in new values and observe the effects on formula cells.

l (^) 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.
l (^) Scenario Manager: Create named scenarios and generate reports that use outlines or
pivot tables.
I discuss each of these types of what-if analysis in the rest of this chapter.

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 see what happens to the formula cells. You may want to print the results or save

each scenario to a new workbook. The term scenario refers to a specific set of values in one or more

input cells.

Manual what-if analysis is very common, and people often use this technique without even realiz-

ing that they’re doing a type of what-if analysis. This method of performing what-if analysis cer-

tainly has nothing wrong with it, but you should be aware of some other techniques.

Tip
If your input cells are not located near the formula cells, consider using a Watch Window to monitor the for-
mula results in a movable window. I discuss this feature in Chapter 3. n

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 data table

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.