Scenario Analysis for Management Practitioners with Microsoft Excel (1)

A project is executed in an uncertain environment and many assumptions and constraints are associated while executing a project. Projects (or programs or portfolios), after all, are undertaken by businesses to bring value to the organizations and the stakeholders. 

Why Scenario Analysis?
Before a project is approved and started, many times scenarios are developed for the project to see if the organization can really meet the obligations as demanded by the project. During project execution too, it is possible that scenarios may be developed and analyzed to check if the project should be continued further. 

Let’s take an example.

Say a project has three phases and there will be a number of labor and non-labor resources as well as expenses in the project in all these three phases. Next, let's see some question on these resources (labor, non-labor, others) and associated costs? 

What will happen:

• If the labor cost increases (or decreases) a lot?
• If the resources are not available in one phase and hence it increases the schedule?
• If the expenses are much more (or much reduced) than planned?

Similarly, there can be other questions related to schedule, cost, resources, expectations on quality front etc. All these will impact the objectives of the project, which can be schedule objective, cost objective or scope objective or any other. Do remember that, these scenarios (and hence questions) can be raised even before the project is approved. 

If you are using MS Excel, it’s a good tool to check on these scenarios and find the impact on the associated objectives. This tool comes with the “What-if Analysis” pack, which can be used to create, run, and see the results of as many scenarios as you want. 

While I’ve primarily explained this for project managers, this tool can also be used by portfolio managers, program managers, operation managers, finance managers, among many others. For example, an operation manager would want to check on the scenarios for the inventories in his or her organization. 

We will reuse our first example of a project with three phases and see various scenarios associated with the cost of the project. The example goes as below.

Example Question
In your project, there are three phases – Phase 1 to Phase 3. In each phase, the cost associated with the resources and expenses are noted in the below table. This data is plotted with MS Excel.

• Scenario 1: What will happen if the “Labor Cost” goes by $200,000 for Phase 2 and by $100,000 for Phase 3?

• Scenario 2: What will happen if the “Expenses” are reduced by $100,000 for all phases (i.e., Phase 1, Phase 2 and Phase 3)?

Let’s solve this case. Of course, you can add the sum of all needed cells by changing the values and see the impact one-by-one. But it will be far easier and quicker if you use the scenario analysis tool of MS Excel. It will help you to do a quick comparison among all the scenarios. 

To use this tool, we will follow these steps:

• Create the Scenarios
• Run the Scenarios
• Check the Summary

Create the Scenarios
To create the scenarios, go to the Data tab – Forecast group – Scenario Manager… command.

This in turn will open up the Scenario Manager screen, where we can add/edit/remove the scenarios. 

Next, let’s add our scenarios. We will have the following scenarios. 

• “High Labor Costs” – The labor cost increase scenario.
• For Phase 2 it will be an increment of $200,000 and for Phase 3, it will be $100,000. 

As you create the scenario, it will come as shown below. 

As shown above, our scenario created is “High Labor Costs” and we will be changing the cells “C6” and “C7”. These are as noted in “Changing Cells:”. The cells for “C6” and “C7” are for Phase 1 and Phase 2 labor costs, respectively. This you can cross-check with the first image of this article.

As you press “OK” on the above screen, you will have the following dialog box asking you to enter the needed values for cells “C6” and “C7”. We will change these values. 

• Cell “C6” value is changed from $ 515,000 to $ 715,000. This way we have a cost increment of $200,000.
• Cell “C7” value is changed from $ 655,000 to $ 755,000. This way we have a cost increment of $100,000.

After you change the cell values, it will be shown as depicted below. 

In the screen shown above, we have changed the values for cell “C6” and cell “C7”.  

Similarly, we will add another scenario for “Low Expenses”. For this case, we will change cell “E5”, “E6” and “E7”.  The cells for “E5”, “E6” and “E7” are for Phase 1, Phase 2 and Phase 3 expenses, respectively. You can cross-check this as well with the first image of this post. For the second scenario, we have reduced the expenses for each phase by $100,000. The cells for low expenses scenario is shown below.

After you have added both the scenarios, the Scenario Manager now has two scenarios as depicted in the below figure.

As you can see, there are two scenarios:

• Scenario 1 is for “High Labor Costs”, which impacts cells “C5” and “C6”, representing Phase 1 and Phase 2 labor costs getting increased.
• Scenario 2 is for “Low Expenses”, which impacts cells “E5”, “E6” and “E7”, representing Phase 1, Phase 2 and Phase 3 expenses getting decreased.

The second and concluding part of this post, we will see how to:

1. Run the scenario (just with a click of mouse and respectively fields getting updated)
2. Summary of scenarios, which helps to check all the scenarios along with the original case.

This series Part - 2: 
Scenario Analysis for Management Practitioners with Microsoft Excel (2)