Using Scenarios to compare different situations

Apr 18, 2024 | Excel Hints and Tips

In many cases when we are trying to project what might happen in the future there are many unknowns that need to be considered.  This is when scenarios in Microsoft Excel can help.

Creating many copies of the file or sheet with each showing a different situation is an option but scenarios is often a better choice.  There are 2 main advantages of scenarios.  Firstly having only one source of the original data so if the underlying information changes it is only changed in one place.  Secondly the results of the scenarios can be viewed in a summary side by side with only a couple of clicks once they have been created.

Creating a scenario

The first thing to consider when setting up a scenario is whether you will need to get back to the underlying data.  If you do then the first scenario created must store the figures for any cells that are going to be changed in the other scenarios.

1. From the Data tab select What-if-analysis and then Scenario Manager.

Scenario manager image

2. There will be no scenarios currently available.

3. Click Add to create a new one.

4. The scenario should be given a meaningful name, the cells that are going to be changed as part of this scenario selected and a comment added explaining the concept behind the scenario in more detail.  Consider whether the Changing cells should be consistent through all the scenarios so some of the figures can be changed back to their original values while another avenue is explored.

Add scenario image

5. For each of the Changing Cells a figure needs to be entered. To prevent a figure changing for this specific scenario just leave it as it is.

Scenario values image

6. If another scenario needs to be created click Add and repeat steps 4 and 5 or OK to return to the main scenario screen.

Change the scenario viewed

  1. From the Data tab select What-if-analysis and then Scenario Manager.
  2. There will be a list of scenarios to choose from.  Select the one to view and click Show.
  3. The figures on the spreadsheet related to this scenario will be changed.

Add, Delete and Edit Scenarios

At any time a new scenario can be added or one already created can be deleted or edited.

  1. From the Data tab select What-if-analysis and then Scenario Manager.
  2. To add another scenario click Add.
  3. To delete a scenario no longer required click on the scenario first then click Delete.
  4. To Edit the scenario already created click on the scenario then on Edit.

Merge Scenarios

Scenarios can be created in one file and then copied to another file by merging.

1. Make sure that the file containing the scenario and the file the scenario is to be transferred to are both open.

2. Make sure the file containing the scenario is selected.

3. From the Data tab select What-if-analysis and then Scenario Manager.

4. Click Merge.

Merge scenarios image

5. Choose which open Book and Sheet the scenario merges to.  Obviously, this will only work if the layout of the data on the other file is similar to the one where the scenario was originally created.

Creating a Summary Report

The Summary report enables a comparison between the scenarios to be available at a glance on one sheet.

1. From the Data tab select What-if-analysis and then Scenario Manager.

2. There is a choice between a Scenario summary and a Scenario pivot table report.

3. The Results cells can be changed to include any cells that have figures that would be particularly useful to compare for each scenario.

Scenario summary image

4. Click on OK.

5. Depending on the design choice chosen the results will be viewed in a different layout.  With the Scenario summary the figures for any of the cells that are changing will be shown as well as any additional results cells to be monitored.  With the pivot table it is just the results cells that are shown.

Further Reading

If you’ve enjoyed reading about using Scenarios to compare different situations, there are some other blogs below that you might find useful:

Want to learn more about Microsoft Excel? Then email lara@laramellortraining.co.uk to discuss how I can help or have a look at the Microsoft Excel Courses I run.