Ms Office Tune Up

In what-if analysis, a particular set of input values that you plug into a model is called a sce­nario. Because most what-if worksheets can take a wide range of input values, you usually end up with a large number of scenarios to examine. Instead of going through the tedious chore of inserting all these values into the appropriate cells, Excel has a Scenario Manager feature that can handle the process for you.

As you’ve seen in this chapter, Excel has powerful features that enable you to build sophis­ticated models that can answer complex questions. The problem, though, isn’t in answering questions, but in asking them. For example, Figure 1 shows a worksheet model that ana­lyzes a mortgage. You use this model to decide how much of a down payment to make, how long the term should be, and whether to include an extra principal paydown every month. The Results section compares the monthly payment and total paid for the regular mortgage and for the mortgage with a paydown. It also shows the savings and reduced term that result from the paydown.

Figure 1 A mortgage analysis worksheet.

Here are some possible questions to ask in this model:

• How much will I save over the term of the mortgage if I use a shorter term and a larger down payment and include a monthly paydown?
• How much more will I end up paying if I extend the term, reduce the down payment, and forego the paydown?

These are examples of scenarios that you can plug into the appropriate cells in the model. Excel’s Scenario Manager helps by letting you define a scenario separately from the worksheet. You can save specific values for any or all of the model’s input cells, give the sce­nario a name, and then recall the name (and all the input values it contains) from a list.

Before creating a scenario, you need to decide which cells in your model will be the input cells. These will be the worksheet variables-the cells that, when you change them, change the results of the model. (Not surprisingly, Excel calls these the changing cells.) You can have as many as 32 changing cells in a scenario. For best results, follow these guidelines when setting up your worksheet for scenarios:

• The changing cells should be constants. Formulas can be affected by other cells, and that can throw off the entire scenario.
• To make it easier to set up each scenario and to make your worksheet easier to under­stand, group the changing cells and label them (see Figure 1).
• For even greater clarity, assign a range name to each changing cell.

To work with scenarios, use Excel’s Scenario Manager tool. This feature enables you to add, edit, display, and delete scenarios, as well as create summary scenario reports.

When your worksheet is set up the way you want it, you can add a scenario to the sheet by following these steps:

1. Choose Data, What-If Analysis, Scenario Manager. Excel displays the Scenario Manager dialog box.
2. Click Add. The Add Scenario dialog box appears. Figure 2 shows a completed ver­sion of this dialog box.


Figure 2 Use the Add Scenario dialog box to define a scenario.

3. Use the Scenario Name text box to enter a name for the scenario.
4. Use the Changing Cells box to enter references to your worksheet’s changing cells. You can type in the references (be sure to separate noncontiguous cells with commas) or select the cells directly on the worksheet.
5. Use the Comment box to enter a description for the scenario. This displays in the Comment section of the Scenario Manager dialog box.
6. Click OK. Excel displays the Scenario Values dialog box, shown in Figure 3.


Figure 3 Use the Scenario Values dialog box to enter the values you want to use for the scenario’s chang­ing cells.

7. Use the text boxes to enter values for the changing cells.

NOTE

Notice in Figure 3 that Excel displays the range name for each changing cell, which makes it easier to enter your numbers correctly. If your changing cells aren’t named, Excel just displays the cell addresses instead.

8. To add more scenarios, click Add to return to the Add Scenario dialog box and repeat steps 3-7. Otherwise, click OK to return to the Scenario Manager dialog box.
9. Click Close to return to the worksheet.

After you define a scenario, you can enter its values into the changing cells by displaying the scenario from the Scenario Manager dialog box. The following steps give you the details:

1. Choose Data, What-If Analysis, Scenario Manager.
2. In the Scenarios list, click the scenario you want to display.
3. Click Show. Excel enters the scenario values into the changing cells. Figure 4 shows an example.
4. Repeat steps 2 and 3 to display other scenarios.
5. Click Close to return to the worksheet.

Figure 4 When you click Show, Excel enters the values for the highlighted sce­nario into the changing cells.

Technorati Tags: plugging multiple input, formula excel 2007

Popularity: 3% [?]