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:
Give the scenario a name
Indicate the range of cells being changed- in this case, the cells where we keep the change in assumptions
Insert any comments
We could also add protection, which we pass on for now in order to show you how to also edit and add more scenarios.
Click OK when done
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.
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 calculations all have to reside in one sheet. To do it across multiple sheets, you would need to develop a macro under the VBA code- an advanced topic.
Another limit is the number of calculations being handled, 32. Two workarounds are either splitting up the scenario calculations, such as one for each product or year, so the number of calculations for each is under 32 or to use the macros.
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