Saturday, May 16, 2020

Scenario Analysis for Management Practitioners with Microsoft Excel (2)

In the earlier part of this post, I had outlined how scenario analysis helps managers to present their cases for the project,under various situations or scenarios. 

During project execution, too, this analysis will be helpful to communicate with the stakeholders based on various situations or scenarios. 

In the earlier part, we saw how to create the scenarios with MS Excel with a quite powerful pack of "What-if analysis". In this part, we will know how to run the scenarios and check the summary of all scenarios in one sheet.

Run the Scenarios
Next, we will run the scenarios by using the “Show” command in the Scenario Manager screen. This command is highlighted in the previous image. 

Select the first scenario of “High Labor Costs” and click on the “Show” command. This, in turn, will update the values of “C5” and “C6” and also the values in “Total” columns. This is shown below and updated ones are highlighted in blue.

As shown above, the labor costs for Phase 1 and Phase 2 have gone up by $200,000 and the total cost for these phases also have gone up. 

Similarly, when you the second scenario of “Low Expenses”, it will come as shown below. 

As you can see, in this case the expenses and hence total costs for all the phases have gone down by $100,000. The updated cells are again highlighted in blue.

Reverting Back to the Original Scenario
Many times, it’s possible you would like to revert back to the original scenario. To do that simply click on the “Undo” command on the Quick Access Toolbar (QAT) of MS Excel tool.

Summary of Scenarios
Another powerful functionality given with MS Excel is the summary of the scenarios you have created. This way you can check for the impact of all the scenarios along with the original values.

The summary report can be generated by running the “Summary…” command in the Scenario Manager screen.  

The summary report informs on the current or original cases, the “High Labor Costs” scenario and its impact, the “Low Expenses” scenario and its impact. For example:
  • For Phase 1 and Phase 2, the labor costs were $500,000 and $515,000, respectively. If you apply the scenarios, it will become $700,000 and $715,000, respectively.
  • For Phase 1, Phase 2 and Phase 3, the expenses were $275,000, $200,000 and $290,000, respectively. If you apply the scenarios, it will become $175,000, $100,000 and $190,000, respectively.

Final Words
While working as a project manager, it’s likely that you will be asked to check upon various scenarios for your projects. In this case we saw the increase or decrease of cost related items for the project. This can also be for your project schedule, too. MS Excel provides this in-built tool which can help work with various scenarios. 

Scenario analysis is not only needed by project managers, it may also be required if you are working as a portfolio manager or program manager or finance manager. Also, as mentioned earlier, in operational work with inventory, utilities, site related costs, you can be asked to have various scenario analysis.

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

1 comment:

  1. This is really very nice article. You have done very good work.
    I like your blog, it is full of knowledge and gain. keep it up. I hope you have more information Great blog.

    Download Microsoft Excel


Sign- or Log-in and put your name while asking queries in comments. Any comment is welcome - comments, review or criticism. But off-topic, abusive, defamatory comments will be moderated or may be removed.