MVP #59: A Business Relevant Practical Use Of Scenario Manager in Excel

Scenario Manager is one of Excel’s decision analysis tool. It allows you compare outcome for different business scenarios.

Below is a practical business use case of the scenario manager. It is taken from our business circumstance and you’ll find it very interesting.

We run a Microsoft Excel and Business Data Analysis business. Our major income streams are consulting for companies on data analysis and business process automations, and Microsoft Excel training. So let’s say we decide to run a special one day Microsoft Excel training. It was specifically my idea. I had stumbled on a training advert on Punch newspaper. A one day training at VCP Hotel and costing N80,000. So I felt we should try it too. But I needed to build a convincing business case for the idea. And in doing this I used scenario manager.

I called up the hotel to get the details of the cost of hosting a full day training in their conference hall. I then went to work on the other costs that would be incurred in putting together the training. And below is a the sheet of the cost details.

And the underlying formulas are:



As you can see, I have gotten every cost item listed; the estimated number of participants and the course fee too. But to build a convincing business case I need to create different scenarios. Maybe three scenarios.

• Scenario 1: The worst that could happen if don’t market the training well and put the course fee enticingly low.
• Scenario 2: The most likely thing to happen if we do our regular marketing and put up a fair course fee.
• Scenario 3: What would happen if everything goes extremely well. Which will be our marketing aim.

So how do you set up these scenarios in Excel? You use Scenario Manager.

But first we need to use Named Range for the most important cells in our scenario. They are the Gross Profit cell, the Number of Participants cell and the Course Fee cell. In our scenarios we want to monitor what the Gross Profit will be for different combinations of Number of Participants and Course Fee.

I hope you remember how to do Named Range. You simply select the cell or range, go to the name box and type in the name you want to name the selection as.


We do same for Course Fee.



And for Gross Profit.


Now, we launch the Scenario Manager.

It is under Data Menu, What-If-Analysis. 






So let’s add the three different scenarios.

I’ll start with the worst. Click on Add and give the Scenario name as Worst. The cells we will vary are the Number of Participants and Course Fee cells.



Click on OK. 
It will ask you to set the number of participants and course fee. So based on experience, I know that if we do no serious marketing and set the price to N45,000 we can get 20 people. And that is the worst that can happen.



Click on OK.

Create a second scenario. Name it “Probable”. It will be what we will most likely achieve. Give the number of participants as 30 and the cost as N70,000. 

Finally, do the last scenario. Name it “Ideal”. It will be our marketing aim if we decide to go ahead with the training idea. Give the number of participants as 40 and the cost as N100,000

Once you are done your Scenario Manager dialog box would look like the one below.



Click on Summary. It will ask you for the Result cell to monitor. That is the Gross Profit cell.


Click on OK.

You will be taken to a new sheet showing the comparison of the different scenarios.



And as you can see, I now have a convincing case to show my partners and make them agree to us organizing the one day training.

That’s how easy and powerful the Scenario Manager is.