How to Use the Scenario Manager in Excel

The sample dataset showcases Sale Units, Price per Unit, and Variable Cost per Unit.

Step 1 – Create a Dataset

Suppose, you are going to sell a book and would like to know how the Sale Units, Price per Unit, and Variable Cost per Unit can affect the final profit. The profit is dependent on Sale Units (C2), Price per Unit (C3), and Variable Cost per Unit (C5).

Enter the formula below in C9.

=C5*C6-C7-C5*C8

Step 2 – Set up a Scenario Manager

In the Data tab, go to:

Data → Forecast → What-If Analysis → Scenario Manager

In the Scenario Manager dialog box, click Add.

Choose a Scenario name: Worst Case.
Add comments in the Comment box or leave it blank.
In Changing cells, enter the reference cells: C2, C3, C5 (references must be separated by commas). You can also press CTRL and select the cells.
Click OK.

In Scenario Values, enter the values that define the worst case.
Click Add to add another scenario.
Click OK, and the Worst Case scenario is created.

Here, another scenario will be created:

Click Add.
In the dialog box, follow the steps described above and create the Best Case scenario. (Excel set changing cells for the worst-case scenario as the default changing cells for the best-case scenario).

Create the Most Likely Case with the following details.

Click OK.

Three scenarios were created and are displayed in a list.

Click Close.

Read More: How to Create Scenarios in Excel

Step 3 – View Different Scenarios

Go to the Data tab and click What-If Analysis in Forecast.
Choose Scenario Manager.

You can view the result of each scenario by double-clicking it.

Double-click Worst-Case: the input values in the Excel worksheet will change and the output value will automatically be calculated based on the formula in C9. You can also click the scenario and click Show.
Click Best Case and click Show. The dataset will change automatically.

Step 4 – Create a Scenario Summary Report in Excel

In Data tab, go to:

Data → Forecast → What-If Analysis → Scenario Manager

In the Scenario Manager dialog box, click Summary.

In the Scenario Summary dialog box, enter the Result cells (C9, here) and choose Scenario summary.
Click OK.

The scenario summary report is created:

How to Use the Scenario Manager in Excel

Suppose you receive scenarios from different people. You can merge all scenarios into one workbook. Open each workbook and click Merge in the Scenario Manager dialog box in the original workbook. Select the workbook containing the scenarios that you want to merge.