Advanced Spreadsheets – Scenarios

Create named scenarios.

A scenario is a tool in Calc which helps in testing ‘what-if’ questions. Anything which depends on a particular condition can be defined using a scenario. Say you need to calculate some amount in different currencies; you can have different scenarios for different currencies and can use them as per the requirement.

To create a scenario in Calc, select the cells which contain the values which are going to change between different scenarios. Like we have in the below given example.

After selecting the cells, click on ‘Tools’ from the main menu bar. From the resulting drop-down, click on ‘Scenarios’. This will open the ‘Create Scenario’ dialog.

It is preferred to not use the default name and use something which is meaningful for the users. Having a name for the scenarios is compulsory. An optional comment can also be entered. Settings contain some simple options which can be selected as per the requirements. The important ones are the ‘Prevent Changes’ and ‘Copy Back’ checkboxes. ‘Copy Back’ copies any changes made to the values of the scenario cells back into the active scenario. ‘Prevent Changes’ prevents changes to a scenario enabled as ‘Copy Back’.

After entering all the details, click on ‘OK’, and the scenario gets created.

We can create multiple scenarios for the same cells and have different cell values for each scenario. Then we just need to select the appropriate scenario from the drop-down and the values for that scenario will get auto populated in the cells.

Show, edit, and delete scenarios.

Click on ‘View’ button on the main menu bar. From the resulting drop-down, click on ‘Navigator’. This opens ‘Navigator’ dialog.

Click on the ‘scenarios’ icon in the navigator and this displays all the scenarios on the page. Double click on any scenario to make that scenario active.

To edit a scenario, right-click on any scenario name. This gives two options, delete and properties. Click on delete to delete a scenario and click on properties to edit it. Properties will open the same ‘Create Scenario’ dialog box where the appropriate changes can be made.