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.


img0
img141
img142
img143
img144
img145
img146
img147
img148
img149
img150
img151
img152
img153
img154
img155
img156
img157
img158
img159
img160
img161


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.
Scenario List 22
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.
Ceate Scenario 23
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.
Scenario Dropdown 24

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.
Modify Scenario 25
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.