What IF Analysis – Scenario Managers

6 minutes
Share the link to this page
You need to purchase the class to view this lesson.
One-time Purchase
List Price:  $139.99
You save:  $40
List Price:  د.إ514.18
You save:  د.إ146.92
List Price:  A$195.22
You save:  A$55.78
List Price:  ৳11,874.82
You save:  ৳3,393.05
List Price:  CA$185.28
You save:  CA$52.94
CHF 91.10
List Price:  CHF 127.54
You save:  CHF 36.44
List Price:  kr884.03
You save:  kr252.59
List Price:  €118.72
You save:  €33.92
List Price:  £107.32
You save:  £30.66
List Price:  HK$1,085.03
You save:  HK$310.03
List Price:  ₹10,472.62
You save:  ₹2,992.39
List Price:  RM587.04
You save:  RM167.74
List Price:  ₦53,336.19
You save:  ₦15,240
List Price:  kr1,250.97
You save:  kr357.44
List Price:  NZ$212.50
You save:  NZ$60.71
List Price:  ₱6,854.21
You save:  ₱1,958.48
List Price:  ₨23,539.31
You save:  ₨6,726
List Price:  S$192
You save:  S$54.86
List Price:  ฿4,349.48
You save:  ฿1,242.80
List Price:  ₺1,020.31
You save:  ₺291.53
List Price:  R2,440.10
You save:  R697.22
Already have an account? Log In


Hello everyone, in this video session we will be talking about scenario manager. Some of you may not have used this before, and this is the case study where we talk about how to use this and one of the applications. Now, on the left hand side, I have price fed in for a particular product and the quantities that are being sold. Now, I can quickly get the revenue by multiplying the rupees 10 with the quantity being sold that 606,000 now, a formula was said in for variable cost, which said 50% 50% of the revenue is going to form the variable cost and the fixed cost has been fixed at 1000 level. So, based on these I can quickly calculate the profit that is equal to revenue, less fixed cost Lex variable costs Perfect. Now, based on this already prepared financial model, a very basic one Rather, I would try to create three scenarios.

Scenario one, which is referring to the base case the current case, that is price 10 rupees and quantity being sold 600 there is a best case scenario where in the potential price can be at the rate of 12 rupees per unit, the quantity being sold can be thousand. The worst case scenario tells me the price is down at eight rupees and quantity being sold at 500. Now, I would want to see the results that is, based on these changes, input sales changes, I would want to find out what is the impact on profit. So, here I go I'm going to create three scenarios which can be replicated multiple number of times in terms of execution. Let me go to data. Let me reach out to what if analysis and the first of the three options let me click on that scenario by Now once I look at the interface, it tells me no scenarios has been defined so far.

Please choose Add to add scenario. So there I go, let me click on the Add button. It tells me how do you add scenario it asks for name. So, I give it a name let's say base base case that is, it asks for changing cells, which means what are the inputs that you are expecting change in I will be referring to the cells containing price that is C five comma and the next cell which refers to the quantity being sold. Now it says comment by created by Simon simply change this and say test case. Okay.

Now since you mentioned two cells, as changing cells, it asks you whether c five as per base case should be 10 you As a for the base case, at least it has to be 10. For the quantity being sold, the base case refers as 600. So I'm keeping it that way. Let me add one more scenario, which says best, the changing cells remain the same. As soon as I choose that particular box, you'll notice it started blinking A while back. I press OK. Now this time, best case, reference price as 12.

So I change that to 12. And the quantity being sold at a C six is at 1000. So I'm just keeping the revised numbers as per the respective cases. Let me add the last scenario. So I'm talking about the worst scenario. Let me click on the box and as you can notice, it says start blinking changing cells.

Let me press OK. So unfortunately, you cannot simply choose the sell which contains the worst case scenario, price or quantity being sold. There is no such Option, you'll have to hard coded. So for example, worst case scenario tells me price being eight, and quantity being sold is 500. So each time you add a scenario, each time you add a changing cells category, you will have to write that many number of times in the changing cells value. So my three scenarios has been built up.

Let me press OK. As you can see, based Best Worst, so if I double click on best, it will change the numbers as per best case scenario, and hence the result changes. If I double click on worst, let me return back to base that's 10 rupees and 600 quantity. Now I want to do a competitive analysis for which let me create a summary report. So based on these three scenarios, let me click on summary. Once I click on summary, a very tiny little box comes in, which tells me scenario summary can be derived based on the fact that results says is C 12. Now you can keep CTL as results cells.

Alternatively, in addition to that, you can also add the revenue cell as result cells, meaning the input changes will reflect into these results cells. Let me show you as soon as I press OK. So there you go, what I have in front of me, the two input cells being changing as per best, best worst scenario, C five and C six these are the cells which are referring to price and quantity being sold respectively. The result says our effort into profits and the revenue now for two three reasons, scenario manager is not so popular. Why is that? So?

One because it gives me cell references instead of saying this was a price or let's say this was quantity being sold. So there you have to make a change. And apart from that, if you make any changes to let's say in this current scenario, base case is six rupees, it doesn't change any numbers in the output or result says so it's a static data. And one drawback I already mentioned in the previous case scenario, that number of times that you add scenarios, you will have to you will have to mention those individual values by hard coding them. So, because of these three reasons, it is rarely used. And this is the output report that gets generated.

So that was a quick overview of scenario manager. Of course, if you want to edit or delete that, you can go back to the same sheet, scenario manager, and then I edit or delete button to edit and delete the scenarios respectively. So we'll talk more on the other options of what if analysis in the upcoming videos that is go seek and data tables and trust me The Goal Seek and data tables even even more interesting see in the next video

Sign Up