Scenario Manager

Last week we looked at data tables, which are handy for changing 1 or 2 variables.

But what if you want to change more variables or if you want to show multiple calculations based on 2 variables?

That is where the Scenario Manager can be useful.

The exercise is in the Scenario Manager file.  The result is in the Scenario Manager Result file.

Here is a worksheet where we forecast current year margins.

Suppose we want to check out different assumptions for the unit volume, sales price and cost changes.  We can do this via the Scenario Manager.

From the Tools menu, select Scenarios.

Then click Add.

In this section we:

Click OK once again.  Now we see the listing of our scenarios.

To add another scenario, here is a shortcut, which is why we let the protection be unchecked.  Click Close on the Scenario Manager dialog box.

Then in the worksheet, add the new values in the assumption cells.

Then follow the same steps above to start the new scenario.

Click OK to verify the values.

Then click OK.   You then see two scenarios listed.

We then continue and add a third scenario.  This time we will do it the alternate way.  Click Add.

Click OK.  Now we have to adjust the values manually.

Now we have our 3 scenarios.  Time to generate some output.

Click Summary

You get two choices, either as a report or a pivot table.  Check out the report.

Here is one tip.  We created names for the Changing Cells (assumptions) and Result Cells so that the names appeared in this report.  Otherwise, it will just give the cell references, which might not be as meaningful.

Note it also doubles up by showing the current values which is also a scenario.  Hide that column like we have here.

Alternatively, here is the pivot table view after we have reformatted it.

Each view has its place.  Generally we like the summary form.

A couple comments on scenarios:

The Scenario Manager allows you to use more than two variables.  It also produces a nice summary report or pivot table.  This is a great tool to use.

Return To Excel Tips Index