Excel - Creating Scenarios

This and additional Hints can be found on our website - Training Materials

A Scenario is a named set of values that are saved and can then be substituted into your worksheet.

You can create and save different groups of values on a worksheet and then switch to any of these Scenarios to view different outcomes.

In this example we will build on our previous solver example.

The previous example we had monthly figures for London region,(“January – March”), which we applied constraints to each of the months so that we got a final value of £140,000.00.
The constraints were as followed.

Now in this example we will add three different scenarios for the “London” region for January.

In the previous example we only defined one set of constraints for each month, then viewed the results on a new worksheet.

To create multiple constraints (scenarios) for January follow the above steps for solver to step 9 then

To add multiple Scenarios repeat the solver steps 2 through to 9, but instead of adding constraints select the desired constraint and select the Change button, enter the new constraint solve as before but select a new name for each scenario.

Viewing saved Scenarios.

In the previous example we created three different scenarios (Worst Case, Most Likely Case & Best Case).

To View saved scenarios.

The below results display the Worst Case & Best Case results for January, given that t he Total Sales for months January – March total must be £140,000.00

It is also possible to view graphically the scenario results by selecting the Summary button from the Scenario Manager dialog box. (see above dialog box).