Using Scenarios

"-->

Calc's scenarios tool is one of the coolest features any spreadsheet application could provide. In its simplest form, it lets you create a drop-down list of values to select from for a given cell . In its fullest form, it lets you enclose a set group of cells whose contents change when you select a different item from the drop-down list.

For example, you could set up a scenarios list that lets you choose among different percentage rates for a cell. That cell, in turn , is used in formulas elsewhere in the spreadsheet; so when you select a different percentage rate from the drop-down list, the values in the spreadsheet adjust automatically.

In a more involved example, you could set up a scenarios list that lets you choose among names of different home equity lenders. As you select a different lender, you get different values for things like annual percentage rate, the percentage of equity you can borrow against, and the number of loan years . The cells containing these values, in turn, can be referenced in formulas elsewhere in your spreadsheet to calculate things like the total amount of money you can borrow from that lender, the total amount of interest you'll end up paying, and how much of your credit card debt you'll be able to pay off with the loan amount.

In the latter example, each home equity lender you set up would be a single scenario. A single drop-down list contains multiple scenarios.

Figure 22-6 shows three examples of scenarios.

Figure 22-6. Examples of scenarios

Creating Scenarios

Before you jump in and start creating scenarios, set up your spreadsheet, and try to group your scenario variables in a single area of the spreadsheet. In particular, set up row or column labels next to the values that will be used in the scenarios.

1. Select the cells you want to include in your scenarios.

   You must select at least two cells to create a scenario. If you only have one cell you want to create a drop-down list for, just leave the second cell blank.

   You can select cells vertically and horizontally. All selected cells will be included in your scenarios. You can also select non-adjacent cells. Each non-adjacent cell will have its own drop-down list, but you can switch among scenarios from any of the non-adjacent drop-down lists to change all non-adjacent cell values.

2. Choose Tools > Scenarios.

3. In the Create Scenarios window, type a name for the scenario, add comments about the scenario, and mark the settings you want.

4. Click OK.

5. To add another scenario to the drop-down list, select all the cells in the scenarios area, choose Tools > Scenarios, create the new scenario, and click OK.

   The name of the new scenario is displayed in the scenarios list title bar. Change all appropriate cell values in the scenario area of the spreadsheet to reflect the scenario you just added.

6. Rinse and repeat. (Sorry. Just a little clean humor.)

Scenarios are stored in individual sheets, not for the entire document. So you can have duplicate scenario names from sheet to sheet.

You can also apply conditional formatting to scenario cells to display different scenario values using different cell formats.

You cannot switch between scenarios if cell protection is turned on.

Modifying Scenarios

There are two different aspects of modifying scenarios: by modifying the values in each scenario, or by modifying the properties of each scenario (name, comments, or border color ).

Changing Scenario Values

1. Select the sheet containing the scenario(s) you want to modify.

2. In the scenarios drop-down list, select the scenario containing the values you want to modify.

3. Change the scenario's values directly in the spreadsheet.

You might be tempted to apply cell protection to scenarios so they can't be easily changed. Resist that temptation , because with cell protection turned on you can't switch among scenarios.

Changing Scenario Properties

After you've created scenarios, you can rename them, modify their comments, and change the border colors of the scenarios area.

1. Select the sheet containing the scenario(s) you want to modify.

2. Press the F5 key to display Navigator.

3. In the Navigator window, click the Scenarios button to display the list of scenarios.

4. Right-click the name of the scenario you want to modify, and choose Properties.

   

5. In the Edit Scenarios window (Figure 22-7 on page 607), change the properties for the scenario.

   Figure 22-7. Creating a scenario

   

6. Click OK.

Deleting Scenarios

When you delete a scenario, you also delete the spreadsheet values associated with itunless the scenario you're deleting is the currently selected scenario in the spreadsheet. In that case, the values remain in the spreadsheet, but the scenario title bar says "(empty)". When you switch to another scenario, the data in the (empty) scenario is replaced by data for the newly selected scenario.

1. Select the sheet containing the scenario(s) you want to modify.

2. Press the F5 key to display Navigator.

3. In the Navigator window, click the Scenarios button to display the list of scenarios.

4. Right-click the name of the scenario you want to delete, and choose Delete.

5. Click Yes in the confirmation window.