Using “what if” scenarios

From Apache OpenOffice Wiki
Jump to: navigation, search

Scenarios are a tool to test “what-if” questions. Each scenario is named, and can be edited and formatted separately. When you print the spreadsheet, only the contents of the currently active scenario is printed.

A scenario is essentially a saved set of cell values for your calculations. You can easily switch between these sets using the Navigator or a drop-down list which can be shown beside the changing cells. For example, if you wanted to calculate the effect of different interest rates on an investment, you could add a scenario for each interest rate, and quickly view the results. Formulas that rely on the values changed by your scenario are updated when the scenario is opened. If all your sources of income used scenarios, you could efficiently build a complex model of your possible income.

Creating scenarios

Tools > Scenarios opens a dialog with options for creating a scenario. To create a new scenario:

  1. Select the cells that contain the values that will change between scenarios. To select multiple ranges, hold down the Ctrl key as you click. You must select at least two cells.
  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.
  4. Creating a scenario
  5. 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.
  6. Optionally select or deselect the options in the Settings section. See below for more information about these options.
  7. Click OK to close the dialog. The new scenario is automatically activated.

You can create several scenarios for any given range of cells.


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

Display border

Places a border around the range of cells that your scenario alters. To choose the color of the border, use the field to the right of this option. The border has a title bar displaying the name of the active scenario. Click the arrow button on the right of the scenario name to open a drop-down list of all the scenarios that have been defined for the cells within the border. You can choose any of the scenarios from this list at any time.

Copy back

Copies any changes you make to the values of scenario cells back into the active scenario. If you do not select this option, the saved scenario values are never changed when you make changes. The actual behavior of the Copy back setting depends on the cell protection, the sheet protection, and the Prevent changes settings.

Documentation caution.png If you are viewing a scenario which has Copy back enabled and then create a new scenario by changing the values and selecting Tools > Scenarios, you also inadvertently overwrite the values in the first scenario.

This is easily avoided if you leave the current values alone, create a new scenario with Copy back enabled, and then change the values only when you are viewing the new scenario.

Copy entire sheet

Adds to your document a sheet that permanently displays the new scenario in full. This is in addition to creating the scenario and making it selectable on the original sheet as normal.

Prevent changes

Prevents changes to a Copy back-enabled scenario when the sheet is protected but the cells are not. Also prevents changes to the settings described in this section while the sheet is protected. A fuller explanation of the effect this option has in different situation is given below.

Changing scenarios

Scenarios have two aspects which can be altered independently:

  • Scenario properties (the settings described above)
  • Scenario cell values (the entries within the scenario border)

The extent to which either of these aspects can be changed is dependent upon both the existing properties of the scenario and the current protection state of the sheet and cells.

Changing scenario properties

If the sheet is protected (Tools > Protect Document > Sheet), and Prevent changes is selected then scenario properties cannot be changed.

If the sheet is protected, and Prevent changes is not selected, then all scenario properties can be changed except Prevent changes and Copy entire sheet, which are disabled.

If the sheet is not protected, then Prevent changes does not have any effect, and all scenario properties can be changed.

Changing scenario cell values

The table below summarizes the interaction of various settings in preventing or allowing changes in scenario cell values.

Settings Change allowed
Sheet protection ON

Scenario cell protection OFF
Prevent changes ON
Copy back ON

Scenario cell values cannot be changed.
Sheet protection ON

Scenario cell protection OFF
Prevent changes OFF
Copy back ON

Scenario cell values can be changed, and the scenario is updated.
Sheet protection ON

Scenario cell protection OFF
Prevent changes ON or OFF
Copy back OFF

Scenario cell values can be changed, but the scenario is not updated due to the Copy back setting.
Sheet protection ON

Scenario cell protection ON
Prevent changes ANY SETTING

Scenario cell values cannot be changed.
Sheet protection OFF

Scenario cell protection ANY SETTING
Prevent changes ANY SETTING

Scenario cell values can be changed and the scenario is updated or not, depending on the Copy back setting.

Working with scenarios using the Navigator

After scenarios are added to a spreadsheet, you can jump to a particular scenario by selecting it from the list in the Navigator.

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

Scenarios in the Navigator

To apply a scenario to the current sheet, double-click the scenario name in the Navigator.

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.

Content on this page is licensed under the Creative Common Attribution 3.0 license (CC-BY).
Personal tools