Scenario Manager


Definition
The use of Scenario Manager in Microsoft Office Excel allows you to have a certain set of inputs and outputs for a "scenario", and then lets you view different scenarios of the outputs as the inputs change. Basically, it tells you "if this were to change", "this is the result"; and it allows for this to happen for multiple cases or "scenarios". This can help give people information to make decisions on their business (or whatever it may be used for) based on the different outcomes displayed by Scenario Manager. As the use of Scenario Manager in a model also displays the sensitivity of changes on outcomes, it is also sometimes called Sensitivity Analysis.

It can be found in Excel in the data tab in the data tools group, under What-If Analysis.

“A scenario is a set of values that Microsoft Office Excel saves and can substitute automatically on your worksheet. You can use scenarios to forecast the outcome of a worksheet model. You can create and save different groups of values on a worksheet and then switch to any of these new scenarios to view different results.”
-Microsoft Excel Help

How to Use the Scenario Manager

In Order to use scenario manager, you must first go to Data tab and under the data tools click “what if analysis” then choose Scenario Manager.

Click Add Scenario and name the scenario you would like to put in.

After, select the cells that are going to be changed in order to reflect your scenario. For example if you’re going to change the amount of dollars, you choose the cell that contains the amount of dollars then click OK. You then can change the dollar amount based on your Scenario.

Once you’re done changing the desired inputs for your scenario, click OK and now you will see that scenario in your scenario manager. You can see that Scenario displayed if you click the show button.

You can also make a short cut and have an icon appear at the top left corner. Click the down arrow at the top left corner then click the More Commands option then find the scenario button and click add. You will now be able to view different scenarios quickly.


Using Scenario Manager in Microsoft Excel to Predict the Affect of Seasonal Changes on Yearly Income
Example: Patel's Hotel

Full Download of the Workbook:
**Right click, save link as**
Patel's Hotel Yearly Seasonal Projections.xls
Patel's Hotel Yearly Seasonal Projections.xlsx




This Excel workbook has a total of 7 sheets: one for each season, 2 chart analysis sheets, and a Year-End total sheet.
The seasonal sheets has inputs of: number of rooms, number of employees, and operating expenses. Which in return, has outputs of revenue, operating costs, and the net imcome or loss.

The scenario manager is put in use by having multiple scenarios for each season. Scenarios such as: the potential maximum revenue for the season, shutting down for repairs, and scenarios in between the two situations.

With different scenarios chosen for each season, we can see the affects and changes on the yearly net income of the company (hotel).

As a result, the owner and staff, or other stakeholders, can use this information to review the successes of the company and adjust budgets, costs, wages, and other operational factors accordingly to increase the efficiency of the hotel.


References

Further Reading

External Links

Subscribe to: Posts (Atom)