Using the Scenario Manager Function in Excel.

The reason we use the scenario manager function is that we are trying to create a comparison between two or more different possible outcomes. In most cases we are looking for the effect on one variable or ‘the bottom line.’ In the case of assignment three we are interested in our profit potential. We have different options when creating scenarios—in that we can select one or more variables to change and then we can save the end result as a ‘scenario.’ For assignment three this task has been simplified to changing one variable and seeing its result on one variable. However, once you obtain comprehension of this process/task you can change the number of variables to any number that meets your specific criteria. Another great feature of the scenario manager is its ability to generate a scenario summary or scenario pivot table to better display the result of the different outcomes that you create. Let’s begin.

To start the scenario manager select ToolsàScenario

Once this has been accomplished the following screen will appear:

Upon viewing this screen select ADD then you’ll see the following screen:

In this case you will want to give the scenario a name. If you have not generated any then you might want to save your current worksheet as a scenario and not change any cells. If you want to create a new scenario—like OPTIMISTIC—fill out the form by indicating what cell you wish to change. In this example I am using the hyperlink.com case from project 3. I wish to increase my total income by reducing the cost of my technical support, so I will be changing the percentage from 28.75% to 20%. To do this I select the little color picture on changing cells and I return to the worksheet. I select the cell that contains my technical support value (in this case $B$24), hit OK and the following screen appears.

It is in this box that I type in .20 (for the 20%) and hit OK. IT then creates the scenario and returns me back to the scenario manager screen.

At this point if I want to see the effect on my current spreadsheet I can select the SHOW option and see the changes. However, I wish to create another alternative—this time a Pessimistic one. To accomplish this task I will repeat the previous task with one change—using 45% as my technical support cost (because while I might want that cost to decrease I should consider the option that it might increase). Once this is completed the scenario manager screen will look like this:

At this point I wish to create a scenario summary that illustrates the effects of each change on my total income. To start this task I select the SUMMARY option. The following screen appears:

Notice by default excel wishes to select the cells for you. In most cases they may not be the correct ones. In this example we want to select the TECHNICAL SUPPORT VALUE CELL, and the TOTAL INCOME CELL. Once I make the changes to reflect the specific cells that I wish to compare, I select OK and the following scenario summary report is generated.

At this point you can choose to utilize the Excel report or make your own personal modifications. In either case it presents a nice opportunity to present a visual report on the end results of your different outcome.