Excel Scenario Manager
Would you like to know results of several scenarios for your calculations with one click? Or lets say you are about to buy a car for your company and want to calculate wich option has the highest return value. Scenario Manager is here for you.
Scenario manager is a part of Excel’s What-if Analysis Tools. You can set up a model and create several scenarios to try with this model and compare results. You can open scenario manager from Data Ribbon – > What-if Analysis menu.
Lets see how does this work with an example:
Case: We are planning to buy a new software for our company. We have 3 options to choose from, also we can expand licence of the software that is currently being used in our company. We need a cost comparison to decide on which software is more wallet friendly.
Here is the calculation form for implimantation cost of our software options (I strongly advice to name your changing cells (in column B). It will be much easier and less confusing to work with named cells):
And here we have the cost information about our options:
Now we need to setup our scenarios.
When you open scenario manager for the first time, you will see an empty list of scenarios since there is non defined so far. Click Add button on the right side to start. An Edit Scenario window will oepn. Here you can name your scenario, set changing cells and comment (change log with user info by default). Fill it like below and hit ok.
After you hit ok, scenario values window will open. You can set your set of values for your options from here. Fill it with the values for related scenario and hit ok.
You have one scenario set for your project now. Continue setting up remaining 3 options. Each time you finish setting up a scenario, you will see it added to your scenarios window. Finished state should look similar to this:
Now you can select any scenario and click show to see values filled into calculation form and cost calculated. If you need a report with comparison click on Summary Button. Scenario Summary prompt will be displayed. It will ask for the result cell, fill it with appropriate cell reference and click ok (you can select scenario summary report or scenario pivot table report depending on your needs).
Here is your Scenario Summary Report ready to serve 🙂
You can download example workbook for this tutorial and inspect finished product yourself.