Using “what if” scenarios

OpenOffice.org 3 Calc Guide

Chapter 9 Data Analysis

< Previous Section

Next Section >

• Consolidating data
• Creating subtotals
• Using scenarios
• Multiple operations
• Goal seek
• Solver

< Previous Page

Next Page >

Scenarios are a tool to test “what-if” questions. Use Tools > Scenarios to enter variable contents—scenarios—in the same cell. Each scenario is named, and can be edited and formatted separately, and chosen from a drop-down list in the Navigator and the title bar of the scenario. When you print the spreadsheet, only the contents of the currently active scenario is printed.

By adding a scenario, you can quickly change the arguments of a formula and view the new results. For example, if you wanted to calculate different interest rates on an investment, you could add a scenario for each interest rate, and quickly view the results. If you had another formula that calculated your yearly income and included the result of the interest rate formula as an argument, it would also be updated. If all your sources of incomes used scenarios, you could efficiently build a complex model of your possible income.

Creating scenarios

To create a scenario, select all the cells that provide the data for the scenario.

1. Select the cells that contain the values that will change between scenarios. To select multiple cells, hold down the Ctrl key as you click each cell.
2. Choose Tools > Scenarios.
3. On the Create Scenario dialog, enter a name for the new scenario. It’s best to use a name that clearly identifies the scenario, not the default name as shown in the illustration. This name is displayed in the Navigator and on the title bar of the scenario on the sheet itself.


Creating a scenario
4. Optionally add some information to the Comment box. The example shows the default comment. This information is displayed in the Navigator when you click the Scenarios icon and select the desired scenario.
5. Optionally select or deselect the options in the Settings section. See below for more information about these options.
6. Click OK to close the dialog. The new scenario is automatically activated.

Settings

The lower portion of the Create Scenario dialog contains several options. In most cases the default settings (shown selected in the example) are suitable.

Display border

Highlights the scenario in your table with a border. The color for the border is specified in the field to the right of this option. The border has a title bar displaying the name of the last scenario. The button on the right of the scenario border offers you an overview of all the scenarios in this area, if several have been defined. You can choose any of the scenarios from this list without restrictions.

Copy back

Copies the values of cells that you change into the active scenario. If you do not select this option, the scenario is not changed when you change cell values. The behavior of the Copy back setting depends on the cell protection, the sheet protection, and the Prevent changes settings.

Copy entire sheet

Copies the entire sheet into an additional scenario sheet.

Prevent changes

Prevents changes to the active scenario.

• You can only change the scenario properties if the Prevent changes option is not selected and if the sheet is not protected.
• You can only edit cell values if the Prevent changes option is selected, if the Copy back option is not selected, and if the cells are not protected.
• You can only change scenario cell values and write them back into the scenario if the Prevent changes option is not selected, if the Copy back option is selected, and if the cells are not protected.

Working with scenarios using the Navigator

After scenarios are added to a spreadsheet, you can jump to a particular scenario by using the Navigator, then selecting a scenario from the list. You can also color code scenarios to make them easier to distinguish from one another.

To select a scenario in the Navigator, click the Scenarios icon in the Navigator. The defined scenarios are listed, with the comments that were entered when the scenarios were created.

Scenarios in the Navigator

Double-click a scenario name in the Navigator to apply that scenario to the current sheet.

To delete a scenario, right-click the name in the Navigator and choose Delete.

To edit a scenario, including its name and comments, right-click the name in the Navigator and choose Properties. The Edit Properties dialog is the same as the Create Scenario dialog.

To learn which values in the scenario affect other values, choose Tools > Detective > Trace Dependents. Arrows point to the cells that are directly dependent on the current cell.