Page 250 - Excel Workbook for Dummies

26_798452 ch18.qxp 3/13/06 7:45 PM Page 233

Chapter 18

Performing

What-If Analysis

In This Chapter
Doing what-if analysis with one- and two-variable data tables
Creating and playing with different scenarios
Performing goal seeking
Creating models with the Solver add-in

sing what-if analysis in the spreadsheet to project possible future outcomes based
Uon different variables is, to put it mildly, one of Excel’s fortes. The program offers
you what-if analysis in the form of its one- and two-variable data tables, goal seeking, and
Scenario Manager. And if this is not enough, it also includes the Solver add-in utility, which
enables you to model more complex problems. In this chapter, you get a chance to practice
performing what-if analysis using all of these tools.

Using Data Tables

In the normal Excel spreadsheet, you see the effect of changing an input value on the result
returned by a formula as soon as you enter that new input: Each time you change this input
value, Excel automatically recalculates the formula and shows you the new result based on
the new value. This method is of limited use, however, when you are performing what-if or
sensitivity analysis and need to be able to see the range of results produced by using a
series of different input values in the same worksheet so that you can compare them to
each other.
To perform this type of what-if analysis, you can use Excel’s Data Table command. When
creating a data table, you enter a series of input values in the worksheet, and Excel then
uses each of them in the formula you specify. When Excel finishes computing the data table,
you see the results produced by each change in the input values in a single range of the
worksheet. You can then save the data table as part of the worksheet if you need to keep a
record of the results of a series of input values.

Creating single-variable data tables

In a one-variable data table, Excel substitutes a series of different values for a single input
value in a formula. To create a one-variable data table, you need to set up the master for-
mula in your worksheet and then, in a different range of the worksheet, enter the series of
different values that you want substituted for a single input value in that formula.