In this series of posts, I’m going to illustrate the potential of Excel as a modeling tool. We are going to create simple models using the features that Excel offers.
Excel is a powerful data modeling tool, but one should be aware of the modeling features that it offers to be able to fully utilize its potential. In most cases, modeling involves creation and evaluation of multiple scenarios and comparison of their results.
In this post, I’m going to cover the ‘Scenario Manager’ feature in the ‘What-If Analysis’ option available in the ‘Data’ tab on the ribbon. Please download the tutorial file by clicking here.
The ‘Scenario manager’ becomes a very useful feature for analysts in any industry while modeling different scenarios. It becomes even more useful when a small number of independent variables control the results of the entire model.
For example, in estimating the future profits for three demand scenarios for a product—low, medium and high—the scenario manager allows the modeler to check the results of all scenarios by doing calculations only for a single scenario. So, I only have to perform calculations in Excel for the low demand scenario using some initial values. The scenario manager would then allow me to create the other two scenarios simply by substituting the relevant initial values for those scenarios.
I’ll now try to implement the above example in Excel with the information given below:
- I have forecasted demand values of a product in number of units—High (6000), Medium (4000) and Low (2000).
- I also know that under these scenarios the variable cost of producing the product would vary as—High ($ 90), Medium ($ 95) and Low ($ 100).
- Let us assume that the fixed cost of production in each scenario is $ 100,000 and the selling price is $ 150.
Using this information, I need to calculate the profits for all the three scenarios.
I start-off by making calculations for a single scenario—I subtract the Total Cost (Fixed + Variable cost) from the Total Sales (Selling Price X Demand) to get my Profit. Then I go to the Scenario manager and create a ‘Low Demand’ scenario (shown in the figure below).
- In the ‘Changing Cells’ option I select the cells with values that vary under the three scenarios. In this case, that would be the cells with Variable cost and Product Demand.
- Next, I enter the corresponding values for these cells in the ‘Low demand’ scenario.
Similarly, I create ‘Medium Demand’ and ‘High Demand’ scenarios.
Now, there are two ways to view the result of these scenarios:
- I can select a scenario and click on ‘Show’ option in the Scenario Manager Dialog box to see the results on the sheet. Every cell that is dependent upon the specified changing cells changes according to the selected scenario
- The ‘Summary’ option (see image below), and the more useful option, allows me to create a report which compares all the three scenarios with each other in a separate worksheet. This is really useful if you have to present your results for different scenarios.
You may ask the question that you could have easily used Excel’s autofill feature to do the same thing. In this case, yes, that would have been the easier method. But imagine sheets full of calculations dependent upon only a few independent variables—in that case the Scenario Manager comes to the rescue as we have already seen.
Hope this post will be helpful for all of you. In my next posts, I am going to cover other options in the ‘What-If Analysis’. Till then, happy Excelling :).