Category Archives: Modeling and Data Analytics

Modeling in Excel‒ Scenario Manager

Hello Friends,

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:

  1. I have forecasted demand values of a product in number of units—High (6000), Medium (4000) and Low (2000).
  2. I also know that under these scenarios the variable cost of producing the product would vary as—High ($ 90), Medium ($ 95) and Low ($ 100).
  3.  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. Continue reading