ALL ABOUT OFFICE

EXCEL

HOME

ACCESS

EXCEL

WORD

OUTLOOK

POWERPOINT

Scenarios

Scenarios are used to save different views of your worksheet under different conditions so that you can compare them later.  A simple scenario may involve changing the cost of the petrol and diesel in our worksheet and seeing what the resulting total fuel cost would be.  You need to give each scenario a meaningful name so that you know what it was created to show and can find it again later.  To make sure that your original spreadsheet values are not lost and so that they can be compared with the other scenario values it is a good idea to save your original values as a scenario before you start.

To create a scenario, click on the Tools drop-down menu and choose Scenarios.  As we are going to create a scenario for our original values we will enter the Scenario name “Original Values”.

Even though we are not actually going to change any values in this scenario, Excel must have a value entered in the Changing cells box to create a scenario.  As we are going to change the Petrol cost and Diesel cost cells in our other scenarios (but not this one) we will enter these cells in the Changing cells box.

When you click OK Excel gives you the opportunity to enter new values for the changing cells as shown below .  Notice that we have named the cells PETROL_COST and DIESEL_COST, it is a good idea to do this when creating scenarios as these range names will be used in the summary we will create later. 

As we are creating a scenario to save our original values we do not want to change the values shown in so we can just click OK to create the scenario.  Our new scenario is shown in the Scenario Manager window together with a scenario that was saved previously from some Solver results.  By clicking the Add button we can now add another scenario to hold some different values.

We are going to find out how our spreadsheet would have looked if the cost of petrol was 86p and the cost of diesel was 88p.  We will call this scenario “86p petrol 88p diesel”.

Again, the changing cells box is set to cells B23:B24.  Click on the red icon at the right hand end of the Changing cells box to return to your spreadsheet and select the cells you want to change.  If you want to change values in several cells that are not next to each other you can select them together by holding down your CTRL key while you click on them.  When you have selected the changing cells and click OK you can enter values for these cells as shown below .

Click OK and the new scenario will be created.  To view a scenario, click on the Show button from the Scenario Manager window.  When you have finished looking at your scenarios, make sure you choose the Original Values scenario to return to your working spreadsheet.

When you have created several scenarios it can be useful to compare them side-by-side.  A scenario summary can be created to compare the scenarios you have created.

Click here to return to the Excel index.

webmaster@allaboutoffice.co.uk