Scenario manager functionality can be used to simulate and  compare different scenario for example the worst, the most probable and the best scenario.

Let’s use the data we had for the previous blog.

This is the most probable or normal scenario.

Volume                48

Price                     32

Var cost               18

Fix cost                700

The best scenario would be

Volume                55

Price                     33

Var cost               17

Fix cost                680

The worst scenario would be

Volume                46

Price                     30

Var cost               18,5

Fix cost                715

Of course, we can make three different cost volume profit analyses, but we can also use scenario manager functionality.

Select data – what if analysis – scenario manager

Press add.

Fill accordingly. Note, that in changing cells we are entering a range B2:B5. Separate cells could be entered, too.

This is the normal scenario. In the input box the cell addresses are visible. You need to map in your mind that volume is B2, price is B3 and so on.

Press again add to create the best scenario.

Fill the best scenario values.

Press ok.

Press add once more.

Create the worst scenario.

Fill the values for the worst scenario.

Select a scenario and double click it. When I double clicked the best scenario, the values were updated into B2:B5. Also the results were updated.

This way you can update the scenarios into Excel model.

If you press summary.

In the summary we are comparing the margin and the result. Press ok.

Excel created a Scenario Summary view. Here we can see that current values are the same as the best scenario. The values are identified by cells not by the real names in one column left.

The changing cells are

B2: Sales volume

B3: Sales price

B4: Variable cost per unit

B5: Fixed costs

Results:

F4: Margin

F6: Result

The best scenario is generating the best values as all the changing cells have the best values. The result is clearly positive. Unlike the worst scenario, which have all the lowest sales values and highest cost values. Therefore the result is clearly negative.

With scenario manager you can compare how different scenarios affect your calculations. First, you need to key in the scenarios. After that you switch from scenario to another by clicking each scenario and see how the different scenarios affect your calculations.