Scenario Manager in Microsoft Excel

In our previous blog post, we have discussed Goal Seek. In Goal seek, we can define only one variable at a time, However, suppose if we have more than one variable then goal seek would not help us. If there is such a problem, then we use “Scenario Manager”.

Scenario manager is a kind of tool which gives us the result on the changes in many variables. Excited to learn!

Let’s dive in the ocean of Excel and understand how can the scenario manager help us?

Let’s suppose, We have a monthly saving plan data of an employee, who wants to know-

1- How much amount will I have saved if we do some cost-cutting in my expense? (Hint: Good Case)

2- How much amount will I have saved if we do more expense than the scheduled table? (Hint: Bad Case)

The saving amount is based on two variables, Total Income-Total expense. This is the formula we have used to calculate the savings-

The idea is to see, how this final result will change when we change the dependent variables.

As we demonstrated in the earlier blog post, that if we have one variable changing then we can use one-way variables or two variables, However, If we have more than 2 or 3 variables then scenario manager is here to help us-

How do we create a scenario manager? –

1. Go to Data Tab
2. What if analysis group
3. Scenario Manager

4. When we click on scenario manager, we will get this dialogue box-

5. In the scenario manager dialogue box, click on Add button ( Refer the above screenshot )

6. In the add scenario dialogue box, fill the following details-

      a. Scenario Name – Give a name like Good case

      b. Changing cells– Select the range from $B$2: $B$6 (In a different case, we can choose each cell one by one withholding Control Key.

      c. Comment– This option is optional, IF we wish, we can leave a comment, or we leave it blank.

d. Ok

7. Once we click on OK button, we will get scenario values dialogue box (refer the screenshot below)-

Fill all the values. by the way, we have taken a “Good Case” scenario, So make sure we will fill the amount details accordingly. As we can see into the above screenshot, Values are showing with cell address. If we set a name for each cell, cell Name will appear instead of cell address.

How do we apply a name over the cell? –

Here, we are discussing 3 ways to define a Name over the cell. Let’s understand it one by one-

1. Select one cell
2. Choose name box (Besides of Formula Bar)
3. Type a Name like a Salary
4. Follow the same process for each cell

Or

1. Select one cell
2. Right click
3. Define a Name
4. Ok
5. Follow the same process for each cell

Instead of giving Name to the cell individually, we can do it instantly with the shortcut key, How? Follow the process as explained below-

1. Select the cell
2. Press Ctrl+Shift+F3 or Formula Tab->>Create from selection
3. Make a sure mark on the left tab
4. Ok

Once complete the define Naming process, Repeat the process “how do we use scenario manager ” as explained above. When scenario value dialogue box will open, it will look like this-

Change the variables as per our requirement, this is the “Good_Case”, we will change the values so that we can secure good savings. change the values, as explained below-

Salary= No change (Remain same)

Rent= No change (Remain Same)

Food= 3300

Mobile= 500

Other= 400

Then click on Ok (we can add more scenarios by clicking button “Add”)

This is the case for “Good_Case”, Similarly, repeat the same process as explained above to create another scenario. Here we will create multiple scenarios to do the comparison analysis. (For example, “Poor_Case”, ”Regular_Case” etc.)

Once we have completed all the scenarios, to see the preview result just double-click on any scenario. As we double-click on the scenario, the original result value becomes changed based on your given scenario.

Create a summary of all the scenarios at one click-

1. Click on summary
2. We will have two types of report format-
   1. Scenario Summary
   2. Scenario PivotTable report
3. Select the resulting cell in which we want to see the output result. (for example, cell B7 for this case)
4. Ok

Once we click on Ok button, instantly we get a complete scenarios report on a new sheet.

Just look at the above screenshot, we have a different result for different cases.

1. If we go through Plan-1 (Good Case), we would save amount 15800 compare to current value 13000. 😊
2. If we go through Plan-2 (Poor Case), we would save amount 11700 compare to current value 13000. 😞
3. If we go through Plan-3 (Regular Case), we would save amount 13000 compare to current value 13000. 😐

Isn’t it Cool?

Scenario Manager is a great tool when we want to do a sensitivity analysis of our data based on different case. Simply create the scenario and click on the summary, it will give us an overview of all comparative data.

Download the practice file and try it by yourself.

Once you stuck anywhere or if you have any query, feel free to write down in the comment box or get in touch with www.nurturetechacademy .in. We love to hear your queries and ready to help you 24X7.

Stay connected with www.nurturetechacademy.in

Happy Learning 😊