Excel 2010 Bible

Chapter 36: Performing Spreadsheet What-If Analysis

753

3. Select the range E4:M14 and choose Data ➪ Data Tools ➪ What-If Analysis ➪
   Data Table.


4. In the Data Table dialog box, specify B5 as the Row input cell (the response rate)
   and cell B4 as the Column input (the number mailed).


5. Click OK. Excel fills in the data table.

Figure 36.9 shows the result. As you see, quite a few of the combinations of response rate and

quantity mailed result in a loss rather than a profit.

As with the one-input data table, this data table is dynamic. You can change the formula in cell E4

to refer to another cell (such a gross profit). Or, you can enter some different values for Response

Rate and Number Mailed.

FIGURE 36.9

The result of the two-input data table.

Using Scenario Manager

Data tables are useful, but they have a few limitations:

l You can vary only one or two input cells at a time.

l (^) Setting up a data table is not very intuitive.
l A two-input table shows the results of only one formula cell although you can create addi-
tional tables for more formulas.
l In many situations, you’re interested in a few select combinations, not an entire table that
shows all possible combinations of two input cells.
The Scenario Manager feature makes automating your what-if models easy. You can store different
sets of input values (called changing cells in the terminology of Scenario Manager) for any number
of variables and give a name to each set. You can then select a set of values by name, and Excel dis-
plays the worksheet by using those values. You can also generate a summary report that shows the