 Good morning. Good afternoon. Good evening and welcome to another financial modeling webinar series brought to you by D Brown consulting Today we're going to be talking about scenario analysis for financial modeling. How can you master building them? Right, so you're going to master scenario analysis for financial modeling So that's what we're going to talk about today Our webinars are sponsored by D Brown consulting net and we do them every third Thursday of the month from 11 a.m To 11 45 a.m Central African time My sponsors are again D Brown consulting. We also have office training hub.com sponsoring an office training hub.com Delivers online content online training on financial modeling power bi Excel office 365 and it has lots of certified training from the CPD certification service So let's jump right in and your Presenter for today. My name is David Brown I'm the managing partner of D Brown consulting and I'm also an international consultant to the World Bank I do quite a bit of modeling stuff for the World Bank as well and I'm a Microsoft MVP on the data platform You can reach me on Twitter at D Brown analyst You can also reach me on YouTube watch some of my videos on YouTube at bit.ly D Brown videos you can check me out on LinkedIn and also on the office training hub platform where I have some recorded courses You can also check us out at D Brown consulting.net All right, so see you guys online. So let's jump straight into it. So what are we talking about? How do you incorporate a financial model scenario tool or Scenario financial modeling tool for scenario analysis into a completed model. So here we're looking at a completed model So the completed model has your inputs. So this are your typical inputs This are just general PNL assumptions balance sheet assumptions financing assumptions typical input right for a detailed and good Financial model with all the styles and everything you have a calculation sheets Calculation sheet all the diverse calculations that make up Those figures or the schedules different schedules of calculations You have your property plans and machinery retain earnings and all the numbers are all there These calculations obviously fit into the financials. You have your PNL So this is your typical profit and loss account and these are your figures for profit and loss You have your balance sheet. So this is your balance sheets. You can see it's reconciling to zero So it's reconciled. You have your assets equity liabilities and stuff. So typical balance sheet This is our forecast period here. All right. Let me reduce it a bit. So you see everything, right? You can close this selection pane Right, then you have your cash flow. This is your cash flow statements your cash flow from operations cash flow from investing cash flow from financing and then analysis of changing cash and Yes, everything looks reasonable. Yeah, then you have some other blanks here So this is nice. So why would we want to kind of what's the point of having a scenario in here? Well, the thing is when you build a model there's lots of uncertainty So typically when you want to build scenarios when I say scenario for take for example sales see sales currently We're projecting that sales will grow at 10% flat for five years Is that realistic? Well Is it going to sales going to grow exactly at 10% for five years most likely not? It's extremely difficult to predict such things so Wouldn't it be better to have various scenarios so you have your maybe best case worst case likely case scenario and Then you can allow people to pick from those scenarios, right? So so sales growth will obviously be a candidate for that because we can't really predict It's not it's not thing that is pretty difficult to predict. So this is a good candidate for having scenarios Another candidate could even be cost of sales, right cost of sales Historically they seem kind of flat But what about if there's a big downturn of something or maybe it's cost lots more money to buy the raw materials That they need to do this production. So the cost of sales figures could go up, right? So this could be another candidate for scenario analysis, right and You have selling general and admin so this quite quite a few of these are good candidates for I think this three are definitely good candidates for Changes in scenarios, right in scenarios giving them different scenarios then you have dividend payout rates also a good candidate So we could say that all these figures are base figures, right? And then we need various other scenarios. There are various ways of building in scenarios, right? I'll show you two. I'm sure you one common way and then I'm going to show you a more versatile way So the common way is to say hey, okay, this I'm just going to do scenarios for one item Let's just say I'm going to do scenarios just for you on your sales growth. Let me just reduce this columns for you So I'm just going to do some scenarios for year-on-year sales growth So I want to have three scenarios for sales growth, but I don't want to mess up my model So I want to do it in a way that my model has no clue that I'm messing around with its inputs So my model stays balanced, right? The key thing is typically best practice build your scenario schedules before you build your model build everything your Scenario structure is built But I mean real life things don't happen like that, right? So you have a completed model and someone says hey build in the scenario So how many of you have had that that issue where you have a completed model and then they want you to change the structure of the model How do you where do you start because yeah, your model is nice and correct. You don't want to mess it up How many of you have had that issue type in the chat type in the chat Tell me if you've had an issue where everything seems really nice is working and then someone says hey Build something else and I need to see best case worst case likely case for everything and you're like, but but I don't want to mess up my model So let me show you one method it will mess up your model. It works pretty nicely. Let's say we're going to do three scenarios for this The technique is this Build insert your rose I'm going to insert three rows, right? I'm inserting three rows and I'm going to put my scenarios for sales growth in here sales growth scenarios in these three rows Let's just say best case for example best case. Yeah scenario for sales growth No, let's let's say this should be best. I'm just control D. That's what I just did here Let's actually call this first one base case right base case base case And then the next one. Let's call it worst case worst case. You see worst worst worst case And then the last one best case Let's say best case and worst case good things with base best and worst. Why not what should be last? right So once you type this in You have to note something This is what is plugged into your model these these numbers are what is plugged into your model How do I know that I could click on this you and come to my formula bar up here And there's something called trace dependence if you click on trace dependence You see that what depends on this it shows a line if I double click this line and double click what it shows You will see that this Revenue for year one Depends on that percentage if you look at the formula in the cell see that formula This is the pen so K previous sales multiplied by one plus input L 11 what's input L 11 come to the input sheet. That's this input L 11, right? So obviously this is what plugs into your model and we're not going to mess things up I'm gonna leave it like that. We're just going to do some we're gonna build some other assumptions here and then Kind of trick the model into Knowing thinking that nothing has changed when something has changed So first thing you do list out your scenarios that you want for whatever line item it is you're working on Then just put some scenarios there. So look at what I'm going to them I'm just gonna highlight this copy and I'm going to paste them here, right? So I've pasted all of this here, and then I'll put my values. I said, okay This is my base case my best case probably is 13% flat growth Maybe we'll say 10% 11% then I don't know 9% and then 9% and then yeah So then my best case is 13% 14% 12% 12% and maybe another 13.5 I'm just typing anything worst case is always just grew at 2% 1% 1% and maybe 3% and 3% Right, so what I've just done here is I've just entered some list of scenarios base case best case and worst case But but this is not plugged into my model. Nothing depends on this if I click on this and say formulas What depends trace? dependents Nothing the trace dependent command found no formulas that refer to the active cell perfect Well, not perfect, but yeah, so how do you now make the whole model depend on on this? Yeah, you can run all scenarios at the same time you have to run one at a time So the technique is this and just take note of the technique to plug this scenario into your model There are two things you need to define You know two things you need to define you need to define a list and you need to define a switch Two things you need to define a list and a switch Anytime you want to plug in scenarios into your model Define the list of scenarios and define a switch the switch what it will act on is that switch will switch between each of the Scenarios so define a list and a switch so I highlight this and I'm going to call it a list I'm going to give it a list and so it's going to call it L underscore Maybe SC for scenarios L underscore SC Right, that's what I'm calling this. Why did I say L underscore? That's just my technique for telling myself that that's a list right is like a naming convention So L underscore is my way of saying hey, this is a list then SC is just scenario short for scenario So L underscore SC is my list of scenarios Then I need a switch. I need to be able to switch between any one of these you have to designate a cell for that switch Just designate a cell typically in a full blown model I'll have an input sheet somewhere and I'll Arranges sheet and I'll put all my scenarios the switches and everything there But I'm just going to designate this cell right here. So this cell right here is going to be my Designated switch as I said you need a list and a switch. I'm going to call this one my switch So I'm going to go to in fact, it's going to be an input. So I'm going to go to input and just put implement the input style Input style. So I like following there those rules. So this is my input style and it's going to be my switch So I'm going to call it S underscore SC You know, this one I called it L underscore SC. This I'm going to call it S underscore SC. You can see something's already called that's SC S underscore SC, which is fine Right. So I have my list and my switch. So what has that done for us? Nothing for now, but you see the magic very soon What you're going to do is this my switch is going to switch between scenarios I can type one which means that I'm scenario one I'm going to just do the general format. Shortcut is going to shift tilde Tilde is the button to the left of one on your keyboard if you want to learn that short cut Contrashift one is two decimal places going to shift tilde. All right. So if I put one here It means I want to run the base case scenario So what I want to do is run the base case scenario So when I type one here, I want this to change to base case scenarios I'm going to put a formula in here. So I'm going to kind of use this style So I don't I don't it's not going to be an input style anymore So I'll just click on this just another trick click on home click on the brush paint brush And I'm just going to brush this style over so that this is not an input anymore It's just like a normal cell then I'm going to write a formula here that automatically looks at my switch and Picks what the first item here if I had type two on this switch What I want this to pick is the second item here if I type three What I want this formula to pick is the third item So whatever I pick is going to be based on what is in this switch And the formula that does that is index and it's a very simple syntax Once you've defined your list and defined your switch. This is a trick. You just say equals to index Right highlight all the values you want to analyze. So these are my values. I'm take this away So these are my values right these values. These are these three Is my list of values now those list of values you now put a comma and you type your switch You type your switch sc. So it's s underscore sc So see the formula You just say equals to index you highlight your list of scenarios and you put a comma and you type your switch The name of your switch is s underscore sc once you enter You'll see that this is 2% why because this is the third set of scenarios if I type one This is now what 10% if I type two This will now pick what 13% so I can drag this formula all the way, right best practice highlight and then do control R Control R to copy right. All right. So that's it. So I've dragged right and now this works fine if I change this to one Change this to two changes to three Right. So change it to three. What I can do is is worst case scenario I want to know what scenario we are currently running in the model. So another trick. I'll just say equals to I'm gonna say equals to double quotes Which is parentheses there? Oh, so scenario in use Put a colon the space I put a double code. I can't spell use so use scenario in use This is a double code and then I put an unsigned and I say index tab List which is my l scenarios ls scenarios comma switch So every time you use the index is just basically It goes to index list comma switch here We said it goes to index this list this three comma switch same technique here when I enter now Look at what happens You have a nice definition that scenario in use is worst case if I change this three to one Scenario use is base case change to two change to three But this is nice and if you check your model your model is now broken everything is working I go to my balance sheet is still fine. Okay, look at the zero here This is just some small rounding calculations rounding case nothing to be worried about at all. So if I come to my inputs This changing of one two three really doesn't it's not really professional So once you've built your list and switch technique, right? I can even put this in a box control shift seven It's just we have put in this in a box, right? Then I can actually use something called a combo box I can use a tool that will list out the scenarios and I pick from the tool I pick from the tool and the tool will type one two three for me here So instead of me typing one two three I want something that has a list of all the scenarios when I pick one of the scenarios if I pick the first scenario It'll type one if I pick the second scenario, it will type two if I pick the third scenario It will type three To get that you need to have something called developer you need this tab here developer Now if you don't have this tab you can go to file you can go to options Then under options you can pick a customized ribbon and then down here to the right You will see developer. Maybe it's not ticked you make sure you take it So that's file options, then you go to customize ribbon then you pick developer and you say, okay, right? But I have developer that's it up here So I want to get the combo box something called the combo box That's going to help me automate this whole thing, right? Now what's the combo box if you look at your tabs here you have home all the way to view What I always like to tell people is this Everything to the right of view everything you see to the right of view is not Excel it's really an application that works with Excel Everything to the right of view. This is an application adding the many applications Datastream all those are applications that work with Excel So I'm going to go to developer. It's an application that works with Excel I go to insert this insert button here. See controls. I'm trying to create a control, right? So go to insert. I have two sets of controls form controls and active x controls Active x works mostly With macros, right? But form controls work directly with Excel I'm just going to click on the second one click and then I'm just going to drag and draw a Form so this is a form this one, right? So this is a form and this form what do we do with it? We're going to now plug it into this model. How do you plug it in? This is not Excel Because if something is not in a cell in Excel, it is not Excel This is not in a cell in Excel. It's just floating. So it's not Excel But to work with Excel you must work with a cell So for this to work with Excel, you're going to right click it and go to format control And on the format control you'll see your input range and your cell link These are two key things input range. Just take it that input range is your list of scenarios Cell link is your switch So your input range you just highlight your list of scenarios, but really shouldn't highlight You should actually use the name. It's very important to use the name Because if you don't use the name you can only use this box in this sheet But if you use the name you can use this This box or this combo box in all sheets Anyway, so let's type the name is s. No, it was L underscore sc If you remember and this one is s is the switch s underscore sc So you need to remember the name, right? You need to remember the name L underscore sc s underscore sc input range is your list Cell link is your switch. Just click okay. You're going to get some error messages I have a spill because I'm using the new Excel most people have value error so click out first once you create a combo It always does that click out first to activate it and then come here And you see the list of scenarios click it and click the first one works Click the second one it works click the third one it works Can you see he's typing in here if I change this to two is going to also affect this see Perfect, so this is your first way of doing it and doing it in a completed model I now have my scenarios here and it works fine really nicely No, not bad. Not bad. I can right click this make a copy come to my P&L For example come here and paste control V now I have another version of the box here and I can change it here and you see to affect of Hally's you can affect everything right as I changing it It's affecting My model and it's affecting my model my balance sheet is still balancing fine So that's how you plug a Scenario to into a completed model But this is not what I want to show you this good is very good I'm thinking half of our time what I really want to show you is a scenario section entirely So I'm just going to insert some rows here, right and I'm going to create. I'll see leave that one I have here. I'm going to create a scenario to scenario analysis tool or something like that, right? scenario analysis tool, right? Scenario analysis tool is what I'm going to create and What are we going to do with this tool? Well, we're going to create a tool that is interesting on versatile We're going to choose those line items that give you the most variability Right and we're going to now use that to build a scenario and sensitivity analysis tool So how am I going to do that? Let's start picking some line items. Let's say Yeah, so I pick so let's say I pick Even even sales growth. We've done sales already with these buttons. Let's just leave that Let's pick cost of sales SG&A. Let's just say I'm going to pick cost of sales All right Yes, I'm going to pick cost of sales. I'm going to pick SG and A right, so For this this to work I'm going to pretend that these are my base values all these speakers are my base values And what I want to do is pick all the line items. I think will give me the most variability. So let's say You have maintainable KPEX maintainable KPEX seems good. I'm going to put maintainable KPEX there So let's say these are P&L P and L L P&L sensitivities, let's call these sensitivities right P&L sensitivities. Yeah, I'm going to use style home I'm going to put Formats who's heading to style just to make it look nicer. Which other P&L can we pick? Let's say tax tax rate No dividend payout ratio. That's good. Dividend payout Ratio, right? Right. So these are them. That's good So these this this is fine then what else what else what else what else would I do? Let's say Let's do balance sheets. So let's say some balance sheets. So I come here I'm going to paste maybe some balance sheets sensitivities. Yes balance sheets So what am I calling it sensitivities? You're going to see that very soon balance sheet sensitivities So let's pick which line items of our balance sheet should we play with a bit? Let's say share capital so Share capital ending balance now, let's not play with that. Let's say inventory. So inventory is a percentage of cost of goods sold What about if the inventory balance was a little bit larger than that? What will happen? Let's say whatever this inventory balance is if we had 10% more inventory balance inventory I think it's good. You can use inventory. Let's say inventory. Why don't we say all of them, right? Why not? Don't think we have time. Let's just pick a few. Let's say inventory and trade receivables. Let's do that. So inventory Inventory balance trade receivables balance Let's pick what else we pick Let's pick Trade Pables and other Pables and accruals. Why not? So trade Pables Then all the Pables and accruals All right So this is going to be our scenario section. So to build this out these are We're not following these timelines up here. We're going to build it out in an interesting interesting way The technique I'm going to use is something I use really in a lot of my financial models the real-life models that I built for clients and What we're going to call it I'm just going to put the headings here. So you see You see what it is. I need to insert some rows here. So let me just insert some rows. Let's say Two rows and we need to know what is our live scenario that we're using for our model? What's live and then what are we now picking? So what's my live set of scenarios? I'll I'll use this. Let me use these columns here Yeah, so here I'm going to say I need to know what my Live scenario the units the units here are just going to be percentages, okay? So you're going to be percentages or let's say growth percentages, right? So here we're saying cost of sales if cost of sales is Currently 54% and if you look at my P and L cost of sales going to go to P and L in the P and L We're going to say hey P and L says Sales multiplied by that 50 something percent But what I want to do with this sensitivity is say whatever it is I have chosen as my as my what's it called? This value for cost of sales I want to grow it increase it by maybe five percent or three percent or four percent to get so whatever value I have here as my cost of sales I'm going to increase it by two percent or grow it by two percent or grow it by five percent or reduce it by two percent so it's a growth rate So what what I'm going to have is is like a growth. It's going to be growth growth So it's going to be growth rate right a growth rate that's going to be on top of whatever it is I have including as journey growth could be negative or positive your dividend payout ratio Your dividend payout ratio what I want to do here is I probably do a percentage a completely different percentage, right? So I could I could do something that would overwrite this dividend payout Oh, I could just pay an additional dividend on top of that anyone. I like I could say is a rate Which means that it's not going to use this rate. It's going to use the other rate, but let's just say growth Let's just keep it simple so many options here. You will understand very soon So growth growth growth will come to balance sheet later Let's let's use this for now and then I'm going to come here and say, okay What is the live scenario? I'm using my model right now my live scenario. I Need to know what my life scenario is and then With my life scenario, I also need to do something. I'm going to call an override This is my life scenario. What am I what's my life scenario? I'm using because I am I'm going to also have under life scenario Let me make this bigger. I have it live scenario and I'm then going to also have something I call an Override let me take this to the left. Just let me give me some space So I'm going to leave a space. I'm going to say I need an override You see what override means very soon and then after override I then I can have another space just to make it neat and I'm then going to have all my scenarios. I'm going to have my scenario one scenario two scenario three So I'm just going to type one two three four Five so I could just type as many scenarios as I like. So these are my different sets of scenarios, right? Let's just put that there. So these are my different sets of scenarios. I'm just going to make them Normal right different sets of scenarios. What what do I mean by these scenarios? In fact, I can give them a conditional format so you can see a scenario one scenario two I go to custom This is just a scenario Right, this is for format. I'm doing a format to to never change. What's in a cell? What's really in that cell is is is a number So I'm just making it a format. So you see scenario one. What's still in this cell is one two three Yeah, I've just made it look like it's a scenario one scenario two and what I'm trying to do here is I want to be able to pick any of these scenarios and decide to say, do you know what for this scenario one my growth, whatever base value I had for cost of sales, right? Whatever base value I had for cost of sales I want to grow it by 5% for example, right? So to make this even better I'm going to format this so that it's a percentage, right? And I want to know whether it's positive or negative. So I'm going to put a positive value at the front of this format positive And then I put a Underscore close bracket just a format and then a semicolon and I put a square bracket red square bracket and a negative 0% 0.00% So this just a format right and then if there's nothing there if there's nothing there use with a semicolon and put a dash Right So this is just a format. So I'm going to put 5% all through for now all like this 5% Control R control D, right? And then I decide that look, do you know what in scenario one whatever my base value assumptions I'm going to add a 5% to it then I'm adding I'm adding maybe a 4% to whatever my SG&E Value is in my financials and then whatever my dividend values is in my financials I'm adding 12% to it whatever the dividend value comes to I'm adding 12% to it So this would be my scenario one I could call this maybe the scenario that the MD said we should use this my MD scenario, right? In fact, I could even make this a little bigger to put a kind of a comment as to how did this scenario come about because it's Important to know at what meeting we had that they decided to run the scenario. So this was MD scenario at the board meeting, I guess of I don't know what year 21 March 2020 Okay, something like that, right? So I'm gonna wrap this I need to wrap this I'm gonna go to home and wrap this Right, so we'll make it probably make it a smaller font Right, so guess what I haven't plugged anything to my model. I'm still just planning, right? I'm still just planning things right now and by the time I plug it into my world You see how everything just works like like magic, right? So so this is MD scenario as at this. This is what scenario one is scenario one is MD scenario, right? Maybe I'm gonna put a style under the scenario and I'm just going to Underline it or something. Let me see if I can see I like formatting as I work, right? So it just looks neat and tidy. These are my scenarios I can make this guy a little bit bold and maybe I even Drag this let me open this up See what style can I give this guy What style is nice? Check cell Good neutral, so yeah I don't like any of the styles So I'll just put a border around it. I'll just control shift 7 and then this one also control shift 7 right And then country B. So if these are this my scenario, let's say scenario 2 was Let me just copy. I'm going to do control R for all of this So it has the same sound and the delete and then here I'll say this was best case, right? And then this was a worst case or base worst case. Let's say worst case Yeah This was base case now. What do you think base case will be base case will obviously be zero that means I'm not Changing anything if I put zero means I'm not changing anything and we can leave this for future the future anybody in the future can decide to To use this right and change the scenarios. So let's say we have all these these are our scenarios, right? And my best case scenario that cost cost will obviously be less. I'm going to say cost is minus 2 minus 2% and SG&A was also minus 3% and dividend payout. Maybe I'm a shareholder. So say 10% so I like to pay out dividends, right? So My worst case scenario is when cost of sales is probably like 10% more than we thought, right? And this is probably like 8% more than we thought and dividends the just couldn't pay dividends this year, right? So we're not we're going to say dividend was minus 10% or whatever they were going to pay Right. So once you play with this The question now is okay. So what is entering our model right now? What's entering our model? So I would do the technique for this. So what's override? All right. We're coming to override. Let's leave over right for now What is my live? Scenario so these are my scenarios right now. Which one is my live a scenario? How do I pick my live scenario, right? How do I know which scenario is which? That's the question. So so let's let's let's see We're gonna do a data validation So I have to do have to kind of pick which one is my scenario at the moment, right? What's my scenario and Let's see. So I'm just going to come here and my live scenario is going to be a drop-down in here I'll go to data Data up here somewhere is data validation. Where are you data data validation? Hiding somewhere. Here we go. Data validation and in my data validation I'm picking a list and my list is going to be all the scenarios Yeah, scenario one, scenario two, scenario three, up to scenario whatever. You can you can take this all the way to scenario 20 That's a matter. So if I click okay, and I pick scenario two, right? So scenario two I'm just going to wrap it just make it look nice and Maybe centralize it and then of course I want to put that style that we had you see that That's custom style that we had for scenario. Remember, it's just a format, right? Click okay. So make some nice space. So see so this is my active scenario Let me make this an input and make this an input input style So if this is my active scenario, right? If this is my oh, I didn't do that in the right place I'm gonna cut this cut this and bring it here. It's supposed to be my life scenario here Right. So this is my life scenario. So if I pick scenario one, what I want to see in here is the one I'm going to use in my model, which is I'm just going to say equals to index, right? So index, index, my list of scenarios here all the way to the end These are my list of scenarios, right? So index saying hey index looking at that list, right? Coma, what row do I want? Obviously, I'm in the same row. So I don't it's just row one same row What column do I want? So the column I want is whatever column this scenario gives me So this is scenario one, this is scenario two, this scenario three. I remember is one in here, right? So I'm going to select this cell and lock it F4 and then I close my bracket. What this will give me is This so I can use this format here. I'm just going to paint brush this format and click it here, right? So this is my format. Now if I change this to scenario two See what it gives me that is this figure. If I change it to scenario three That's this figure. So this is what's going to run in my model this, right? So I can just drag this down, right? And this basically is how I pick what enters into my model, right? Scenario four, right now scenario five. There's nothing for scenario five. That's scenario one, right? Scenario one being the best case, base case and all that kind of stuff, right? Right. So in fact, you can even come here and put what is exactly what's the meaning of scenario one So that people know, okay, which scenario are we using? You could do that. I could do that here, right? So I could insert a row here Click on this and say clear formatting and then Let me also remove control So I could I come here and just so that I know the name of what scenario is being run right now I'd say it's called the index right index Same thing index. I highlight this All these names, right? And I put a comma and what row do I want? No row one first row And then whatever this scenario is up here F4, close my bracket, enter and this gives me the name of what scenario I'm running. I'm gonna use this format, right? I'm going to use this format and clean this up, right? So this is the scenario I'm running. If I click this Scenario four, base case three Right now, how do I plug this into my model? But before we do that, let's quickly do for balance sheet So the balance sheet figures, what are we trying to do? Here? We're saying that whatever it is in our piano We are going to kind of grow it by five percent grow it by 12 remember I have scenarios already in the old school way. This is the scenarios for Just sales now this one already has scenarios for three I can build is to have scenarios for every line item. I like it's a really wonderful tool But sometimes right when I do scenario one My MD could say, you know what? I like this scenarios But can we just do one with this at four percent? I don't want to change this because that's what we did on the 21st of March 2020 But I would like to use all my scenarios in scenario one Except for just this one that I want to add four percent and I don't want to create a whole new set of scenarios That's where override comes in so override we're saying in override. I hate override Whatever I put in here is going to override what is here regardless of what whatever we said in my scenario analysis tool So what we say with override is okay? Let me copy this Just put some figures here and delete so if I say two percent that means hey I want to override whatever this was supposed to be I'm going to override it with two percent and if I delete two percent it will go back to what it's supposed to be Do you get that? So this overrides Whatever it is I've picked remember these things are controlled by this right if I do scenario to it controls it But I want this to override it right? This is a value here to override it. How do we do that? So I'm going to leave this value here and here we're going to modify this formula So just going to modify it gonna put in the beginning. We're just going to say an if if for example if This cell is not blank right if this cell is not blank Right or if the cell is blank We'll pick this but if it's not blank will will pick whatever is there so And see it's just like English right so here. I'm going to say if right is Blank right there's a function called is blank if is blank this cell right? So if this cell which is hiding here if that cell is blank So look at that if is blank right then a comma So if if that cell is blank obviously means I don't want to override if it's blank Then give me the formula that I already have But at the end if not put a comma if not then give me that Cell and close my bracket and we enter right So when I delete it and it's blank it doesn't override But when I have something in there it overrides that's how override works perfect, right? It's blank no override. There's value it overrides regardless of what you do here override will always take precedence, right? Why do we need that it's just flexibility so that's overrides I'm going to drag this formula all the way down and now we have a tool that just works perfectly So this is my growth now. What about your inventory? So how do we plug inventory trade receivables same thing just basically the same thing here We're basically saying if you check your calculations for inventory for example if I come down to calculations for inventory Whatever this inventory figures are Right, whatever this inventory figures are we probably we're going to grow them by a certain percent or reduce them by a certain percent Same thing right same thing. So what do I do? I just come back to my input sheet I'm just going to use the same thing. So I'm just going to copy all of this so that we don't waste time Yeah, just going to copy all of this guys, right copy and Paste it here. So all of it is going to be like growth I'm going to highlight control D and then so all the formulas work and I'm going to change this to let's say MD scenario is inventory will go four percent two percent for this Trade Pables we grow by three percent and this will also grow by two percent and the best case is that inventory will be Just flat same as base if my trade receivables will actually go down So I'm going to do minus minus five percent and people are not so hopefully my trade receivables are reducing trade Pables I'm going to also reduce my Pables. I'm going to say is minus five percent and then I'm going to say my other Pables and accruals going to be plus five percent and then my worst-case scenario is when inventory actually goes up by 12% Geez, that's terrible trade receivables goes up by 10% and trade Pables Maybe leave it flat trade Pables, no, let's say trade Pables goes up by like six percent and then other Pables and receivables goes up by five percent. So can you see there's so much variability here if I change this to scenario two You can see that everything is now running scenario two except for an override this three percent is here This ten percent is there this five percent is there except for an override if I delete the override everything now runs scenario two perfectly So once there's an override is scenario two plus override Okay enough of all this how do we plug this now into our model right? How do we plug it for override? Let me make this input so everyone knows that hey they can put overrides as they like is an input as well It's always important to use styles and make sure you use styles very properly put your styles there at all times All right, right, so this is a tool this is far more powerful Than this all right. This is even more tedious, but this is so much more powerful So what I could do for this instead of this drop-down actually I could actually use a combo box So we can pick what scenario MD scenario or whatever, right? So let's say all these names of this scenario. We could actually create a combo for it, right? We could do that now. Let me quickly do that. Why not? So we do the developer we go to insert We go to combo box. We draw the combo box. We right-click it We do format control, but the issue is this I should have named this right? I should have named it, but let's do the bad practice first. We highlight this we create a Cell link if I click on this as the cell link and say okay, let's just see if this works If I click on this drop-down, it doesn't work because unfortunately You need a list this way. You can't have a list this way and it works So you need a list that is down this way So we need to do a transpose of this somewhere and all sorts of stuff before this combo box works Lots of work. Yeah, but yeah, that's what modelers go through, right? Modelers need to be able to build all this stuff. So what I would have done is Is I would have come here and say hey go to maybe one input sheet or something or a menu sheet Or go somewhere somewhere in my model come in here I know they were like six of these values, right? I was at six. How many scenarios did we have one two three four Five six and then I'm going to say equals to transpose Right equals to transpose. I come to inputs and then I highlight these was it six. Oh, I thought it was five It's six. It's not going to work. But anyway, I'll four four close my bracket and do control shift enter because it's transpose Is it six? Let's see one two three four good. I did six so control shift enter. This is my list This is my scenario list. I'm going to do control shift seven and then I'm going to call this my Scenarios Right on this my scenarios. I'm going to give it a name. I'm going to highlight this and I'm going to call it I'm going to call it L mega mega scenarios scenario Something mega scenario, right? I'm mega scenario. I'm going to make this cell my switch You know, I said you need a list on a switch s mega scenario mega scenario and Enter and this my switch. Let me just make it an input input input input input that's there And now that I have a list and I have a switch that's in the format that this can work I'll come to developer come to my insert Click on my combo box draw my combo box, right? Click my combo box go to format control Then go to input range. Then I say what oh, I hope I remember the name L underscore mega scenario. I hope mega Scenario, I hope I got that right and then I hope I spelled the other one right s underscore You need to make sure the spelling is exactly the same mega scenario Enter if I click out and click this I see this and everything should work. Let me make this that so you see Base case, but now this one's are zero and zero, right? So it's not going to know which one I picked Does or some strange reason wow So I knew I picked it. Oh, yeah, of course the second on the list and the whatever on the list So this is my list of scenarios and this is this is the MD Scenario is quite a long thing MD did something something something, right? So I can right-click copy this come to my input sheet and I can now paste this maybe here or something right, so I paste it here and this is now my Scenario pick up Yeah, I can delete this one So now when when when what we can do is that your L scenario? This is actually what it would be. So this cell Should really not be the one controlling one two, three's it should be equal to s Mega scenario That's a trick. So s mega scenario. This is no more on a drop down I'm now using this when I click this it's coming in here scenario two. I click this it's scenario three So let's even call this blank scenario. Let's say blank one and blank two right blank two Right, so now if I come here, I should see blank one and blank two right see blank one And which is scenario five and blank two So this still works fine. It's just that I it's an input is no more an input So I'm just gonna come here paint brush and paint on over this and then maybe come back and just make it look like Scenario two so that it's still happy and nice. So it's scenario and scenario two. Oh, I didn't want the data Validation, so I'm just gonna go to data Data validation and data and clear my validation. So it doesn't have a validation anymore And I just centralized this all this housekeeping stuff, right? So I cannot pick this scenario now. How do I plug all this into my model? That's the final step Finals sense. How do I plug all these wonderful things into my model? So I have these things and this can be a very huge table and This will build so much flexibility into your model is on true imagine doing a sensitivity of the cell That's a different story. Maybe another video So where I'll see all these scenarios all in one and this this empty scenario has changed different variables This changed different variables. But anyway, how do you plug it in? Remember override works if I type 1% here It's overrides this guy see that right? So plug in it in cost of sales best way to plug it in You go to your piano and use to designate a cell here. There's any one of these cells here as your sensitivity cell Okay, so I'm gonna designate maybe this I'm gonna call this sense Sense just gonna call this column sense my column g is gonna be my sense column So here is my sense. All I do is equals to in this my sense and go to my input Oh, no, we didn't do any revenue. Did we we started with cost of sales equals to my sense is equal to This cost of sales, right? This is my sense, right? I'm gonna keep my sense I'm gonna use the same formatting that plus and minus formatting. I think it's nicer and eater So this is my sense. I'm gonna make it a link cell style because it's a link cell So link cell formatting style. Where are we somewhere here link cell? Kind of an ugly format actually think so, but this is it. It's my sense now this sense I guess got a copy It's gonna have another sense here, but I'll do the link is gonna be equal to Where are we? Input my second sense is for this is the s journey sensitivity, right Then I copy this one. I think we did for what else did we do sense for I think that was that Dividends so dividends was the next one. The next sense is for dividend payout Where's dividend in my calculation sheet? So I come to dividends here And for dividends, so where are we dividends ending changing so dividends is my ending balance sense So whatever the dividends is this figure is we're gonna do a sense here. I said we're gonna use column g for sense So I'm gonna call this sense which is sensitivity and sense sense sense So dividends here Obviously you could have sensitivities for nearly all of them So I'm just gonna say equals to and I'm gonna come to input and pick this here So whatever the dividends is, you know, this is not dividends What am I doing this dividend payable? Dividends is up here somewhere. Yeah, this dividends so dividends sense. Whatever dividends we're paying now We're gonna increase it by what? Why is it dividends this so you get it? I'm sure you get it by now, right? So this again Let me just make a format. Let's see Custom format this one with percentage. I'm just gonna put a board around it Let's see. What's that? Okay 10 country shift 7 This is my sense, right? How do I plug it in? All you do is come in here Go to this formula Open your bracket So close whatever is there multiplied by open another bracket one plus this growth rate is a growth rate F4 for this right to lock it Close it enter or you should have and then you control R all the way there When you control R you check your balance sheet everything's your balance is fine, right? And that's how you've plugged this in how do we plug those pianos in the same way? I come to this formula here. Whatever that formula is there, right? Whatever is you can just put a bracket around it just to be sure you're happy with the Analysis and you multiply it by one plus this sense this sensitivity lock it F4 close your bracket enter drag it right control R Same thing for this one. You come to this formula here. You kind of open your bracket You close your bracket you multiply it open your bracket one plus This figure here you lock it you close your bracket and you've now plugged that Sensitivity into your model so you can imagine doing this for every single row. It's so powerful So so powerful. How many have we done? I think we did how many we did three now? We just did those three so you do these ones as well You just do that plug it the same way and Once you plug it now if I click on this worst case now if I take this Let me make a copy of this so you can see the effect if I come to my piano now, right? I even go to my balance sheet. I'm gonna drop this in my balance sheet just paste So you're going to say okay in the worst case scenario This is my this is my combo box for all my other variables, right? Click on this MD scenario, this what happens Best case scenario. This what happens Base case scenario. This what happens so you can see this is far more superior Than building what we did the other time, you know, we built this one. This is typically what you see everywhere, right? This is your typical scenario this one is so much more powerful this So this is a mega sensitivity and scenario analysis I've done a model with this this about hundred rows of this, right? And that is the big big big Every could have two hours far as five hours meetings on this and I say, you know, let's override. Let's just override this I need to override. Oh, we're using now. Let's not use blank. Let's use worst case and in the worst case Let's just saying this is not as bad as we thought this this cost of sales was actually just went up by three percent Everything your model has changed because check go to piano now is three percent is growing by right? Because there's an override and that is it so quite a lot quite a long Winded thing, right? I mean that was Mega yeah, that's why we call it a mega scenario Analysis tool so thanks everybody for watching another webinar from D Brown consulting This is our financial modeling webinar series and Sorry, I couldn't really interact with you in the webinar so much to talk about on this But please type in the chat type in the chat and tell us what you think about this webinar It was a bit technical. I know we're gonna launch He's gonna hopefully be on the YouTube channel very soon and you can watch it and then learn a lot out of it This is your mega scenario analysis tool so mastering scenario analysis for financial modeling This webinar by D Brown consulting every third Thursday of the month. We do this. I hope you enjoyed it I'm David Brown, and I'll see you guys next month. Thank you very much. Bye. Bye