 Hello everybody and good morning. I think it's the morning. Yes Let's see People are walking into the room and I have a poll up for you guys want to know how many modellers we have So I know how deep I can go We have 60% of us here and not financial modulus And 40% are that's what I see. So we're about to start guys and thank you for joining us for the financial modeling webinar series This is a webinar monthly webinar series coming to you from D Brown consulting We do an Excel webinar series power bear and Excel webinar series financial modeling series and its talent development series So every third Thursday of the month we do these webinars And so you can share that with your friends and stuff and get them to register for our webinars every third Thursday of the month I'll just add the link on our chat so you can see This is the link to register for all our webinars there Excel webinars power BI as well as Financial modeling and talent development. So of course these starts at 9 modeling starts at 11 o'clock to 12 Excel and power BI from 9 to 10 and the other one for talent development from 2 to 3 Right. So today, we're going to be this is the June 23 18 episode and we're going to talk about five ways to incorporate Tenere and sensitivity analysis into your model. This is a repeat of last month. We had Wanted to just get through and kind of combine a couple of other things in that The one we did last month. So here I'm going to talk about some stuff like Monte Carlo simulation And how you can use sensitivity analysis to do a Monte Carlo simulation Then what exactly is the Monte Carlo simulation you will explain that to you We see a small model that we're going to use for the Monte Carlo And then we will do some scenario analysis to see okay if Production changes what's what's our profitability looking like so we'll build a scenario model And then we also do a sensitivity on top of that scenario model So it all sounds very interesting and complex, but it's not really complex when we start doing the demo So are we ready? Should we move on? Okay, I can see people are still coming into the room So about us and about me. I'm David So I'm the one taking you for this webinar and David Brown I've been working with Excel for like 20 years 20 plus years 22 years and I build a lot of solutions for clients and do a lot of report automation and just make sure people are more efficient when it comes to working with Excel and Also, of course building models on of course modeling Excel is the tool of choice for any financial modeler Company the sponsoring it is D Brown consulting. We do training consulting and payroll and the training We are more like analysts training where analyst trainers So we we have lots of courses online courses classroom courses blended courses where we take all of this Take stuff on financial modeling report automation with power bi report automation with Excel We do data presentation advanced presentation skills where we teach you how to use how to really create excellent power points That will make sense when you're doing a live presentation lots of stuff like that and Consulting as well who build models for you and we build reporting engines for you with power bi and payroll We do payroll outsourcing companies outsource the payroll to us to confirm or guarantee confidentiality We are also affiliated to a financial modeling Institute So I'll advise since you guys are interested in financial modeling. I'll advise you go to FM Institute comm I'll show you a little bit about them later But it's important you go there and go and check them out. So FM Institute comm of put it on the chat This is the financial modeling body, which is like CFA ICANN ACCA they give Certifications in financial modeling level one is called advanced financial model level two is called chartered financial modeler And level three is called master financial modeler, and I can tell you that I mean I've been a modeler for almost 20 years They Excellent the syllabus is excellent the exams are super and the exams can be written in Nigeria now So we wrote the last exams in April the next exams are in October and it's a four-hour exam You have a computer and you build a model from scratch. That's just a summary of the exam So you come to the exam sit down and build a model from scratch. So after the exam for sure. You're a modeler So I asked how many modelers do we have in the house? We have 60 40 or six thirty eight 63 so that's good. So what exactly is modeling? Let me just play you a short video recorded on what exactly who is a modeler so So who is a modeler? I'm a financial modeler, and I've been building models for deals for quite a long time Over these years over this year's working as D Brown consulting We've basically built up a methodology detailed methodology on how you build models How do you how do you consider yourself a modeler? For example, I mean someone wants to build a model for Telco or you want to build a model for an oil and gas firm It doesn't matter what organization what industry it is as long as you understand the mechanics as long as you understand How to sit down with the financial controller for example and understand the drivers of this deal You will be able to build any model So you're gonna build models from scratch in our courses you build model from scratch from a blank spreadsheet All the way to a detailed model with sensitivity analysis scenario analysis all the ratios You can think of do pond ratios and the likes detailed analysis that kind of helps management Make decisions on whether or not to go in with this I think I must have cut you guys out. That's an error I'll play the video again So who is a modeler? I'm a financial modeler and I've been building models for deals for quite a long time Over these years over this year's working as D Brown consulting We've basically built up a methodology detailed methodology on how you build models How you how do you consider yourself a modeler? For example, I mean someone wants to build a model for Telco or you want to build a model for an oil and gas firm? It doesn't matter what organization what industry it is as long as you understand the mechanics as long as you understand How to sit down with the financial controller for example and understand the drivers of this deal You will be able to build any model So you're gonna build models from scratch in our courses you build model from scratch from a blank spreadsheet All the way to a detailed model with sensitivity analysis scenario analysis all the ratios you can think of do pond ratios and the likes Detailed analysis that kind of helps management make decisions on whether or not to go in with this deal Or whether or not the firm needs to kind of change their strategy All sorts of decisions are made with your model and guess what there's also a certification an Internationally recognized global certification now. Yes, you can get advanced financial modeling certificate And you do the course you do that you do the course you get ready for the certificates You do the international certification and you have your certificate, which is recognized worldwide So come on over you could send us an email a training at D Brown consulting net We could go to our website D Brown consulting net or just call us on 0 700 training. That's plus two three four 700 training or in Nigeria or 700 training. So we're looking forward seeing you see in the next class Great guys. We're back. So we are going to start straight away. We're going to jump straight into our demo and So just let's get the tools up and then I'm going to jump into the demo right now Okay I'm sharing my screen with you to show you the tool we're going to use for the demo Right. Some of you will see this as a bit familiar, especially those that joined us at the CFA financial modeling live Breakfast we did and talked about financial modeling and the financial modeling Institute. So here we have a model And we're going to use this model to go We go through the whole process of building detailed scenarios sensitivity analysis for this model And hopefully you will be able to gain an understanding of how everything works. So here we have a model We have here. Look who is a fufu galore. She does fufu. I don't know who eats fufu here Anybody who is a fufu lover? I personally don't like it at all. I think it really smells I prefer pounded Yam or amala or something like that. Do you get fufu? No way. Yeah, so Well, and nobody here likes fufu, right? I can't say anybody chatting. Okay So here she produces fufu and if you look at it she The key thing here, but any any production of anything is demand. We don't know what demand is So demand is always a tough thing in a model to understand demand. So here she's wondering what can I produce? She's thinking, okay, if I produce 210 Plates so she sells by the plate 210 plates of fufu and my price is 600 My price proportion is 600 Naira. Let me make that a Naira, right? So my price proportion is 600 Naira And then my cost proportion is 300 Naira my price per scrap So what she does is every time everyone finishes the plate, right? She will there's some scrap So the scrap she sells it as dog food So she sells a scrap as dog food at 10 Naira and she has a fixed cost of 15,000 Naira So regardless of whether she sells anything that's going to be a fixed cost of 15,000 Naira Now somebody will calculate profit or say revenue revenue should be equal to Maybe a production Times times what times your price? That's typical, right? Revenue, but we'll see that that's not correct But we'll go on with this anyway, so revenue then variable cost is she has a variable cost per portion of 300 So variable cost being 300 times production, right? Again, this is not completely accurate Then you have fixed cost which we said it's 15,000 And then we have the dog revenue is going to be equal to this 10 times the number of plates that she has for dog food Which are the scraps 210 right again? This is not completely accurate But the profits will supposed to be right the revenue right minus the variable cost minus the fixed cost and This is revenue plus the dog food revenue, right? So that that's revenue So we get a profit of that right now everything here obviously it's in Naira So I put the currency sign for us There we go if you're interested in knowing how you get the Naira currency sign go visit our YouTube channel D Brown consulting on YouTube and you'll see how to change the default currency on your system to your local currency Instead of the dollar you will check that out. So now why I'm saying this is not feasible because there's 50,000 It's really almost fake. You can't Say you're going to produce 210 and then 210 people show up and buy your product unless your product is a special product that always sells out Then yeah, you could say that but most times our products don't always sell out So we really need to be more realistic. So we need to know what demand is and this is one of the Issues with modeling, right? You don't know what demand is. You can't just predict demand So we could if we say demand is 140. What does that mean? I say demand is a hundred and forty here four hundred and forty Well, what that means is we produce 210 But we only sold a hundred and forty, right? So the cost of production is still cost of production is still on 210. Isn't it? But we've only sold 140 so we need to modify this formula is not F eight times because we're not sure we're only going to produce the minimum of This F eight and the minimum of that and the demand So it's either going to be the lower of demand or Productions because if you had a demand of hundred and forty and only hundred people only hundred plates were produced Well, you have demand a hundred and forty, but you over produced. I mean you're under produced So see that's the risk is called the news vendor problem If you go online the news vendor problem because you're going to produce this news vendor problem is like a newspaper How many copies of the newspaper does the vendor produce? How many copies? Well, you wouldn't really know how many copies he needs to produce because He will produce and he's hoping that he will sell everything out But if he produces too little People will not get the paper and they'll be pretty pissed if he produces too much. Well, he loses money So that's the classic problem. How do we solve it? We're going to reduce this risk by doing something called Monte Carlo simulation. How many of us have heard about that? So if you notice the problem is I don't know what demand I don't know what demand will be So the best way to kind of check that is to go to historical data So here I have some historical data here, right? So I just kind of put some fictitious data here, right? Fictitious data on number of customers and then another set of assumptions about number of customers I put some formula there to do some random stuff. So Let's say we're going to use Well, we're going to use which one should we use? We have a random number generator here So let me fix let me fix this so I stop it being random. So I just copy paste which are values, right? So let's say I'll use this assumption this set of assumptions assumption too, right? So if this is my demand you can see that there was a demand on the first of January for 262 plates Then another day was 140 plates another day was 175 What you do is you you're going to use that we're going to use a normal distribution curve to try and do an Estimate of what is the standard deviation of this and what's the mean? So we have to calculate the mean so I'm going to calculate the mean which is the average What was the average demand was the average demand for all these things here? Let me just highlight and enter So my average demand is hundred and eighty nine point six four something something something Yeah, so let's just make that round. Let's round it. I'm gonna soon around. I also see a rounded figure So I'm rounding this average demands my average demand was Hundred and ninety what's my standard deviation? So this now this lady is the total You have to think about it this data Is this all the demand she has ever had since she started production? Let's just assume it is right Let's assume that she started production on the first of January 2016 That's the very first day she started production, which means all of these figures everything here is actual demand Actual demand fooled all the whole population of data. She can have that's what that is So if that's the whole demand all the demands she's ever had So we can kind of say that the standard deviation is not a sample. We're looking at is a population So that the formula is s in a function is STD EV dot P so that is the standard deviation of the entire population of data So I do that and then I come and highlight the Data standard deviation of this data from the beginning to the end So one of the ways to mitigate risk right is you need to do standard deviation. So you understand Okay, what is the how was the deviation? How is this data distributed here? I'm rounding it up. I'm shown to round it up to zero this muscle is just clean So we're saying the standard deviation is 39 the mean is 190 plates So on average she kind of Sells or there's a demand for 190 plates on average But because we have a standard deviation of 39 now with these two figures We can now calculate a demand you can do it more accurate demand if I delete this a more accurate demand Now if I deleted that my revenue is supposed to be almost zero, isn't it? So the minimum of F8 and 1 8 Let's see if we got that rights the minimum of this times F9 This should have given me zero shouldn't it? Let's see F9 of this 210 if I type zero so minimum you have to have a figure in there I see that minimum doesn't work with blank. You better have a figure in there if it's zero then it's zero Yeah, so revenue was zero and we have a loss. So here we're going to use a normal normal inverse What we call like a normal inverse curve Normal inverse curve and the normal inverse curve you use a function called norm norm dot INV norm dot INV Assumes that your your demand is normally distributed. Yeah, it's just a statistic term I'm sure you've seen the bell shape most things are kind of close to normally distributed, right? So that bell shape and then we are saying that the probability we need to have a probability What's the probability and what's the mean and what's the standard deviation for probability use the run function? So you just use the run function for Probability and of course our mean is this cell here and our standard deviation is this cell here then what this will do is to go through and calculate a Guestimate of demand so this is a guesstimate, right? Demand is guessed at two to eight if I press F9 F9 F9 you see that this recalates This constantly recalculates. I'm just pressing F9 for it to recalculate now at any particular run. So at this my run demand is 229 so it demands on a 29 we could say oh she may said, you know what I'm going to produce 220. Yeah Once she produces that guess what demand has changed to 227 if I if I keep on typing anything It just changes. This is just simulating real life. So you can imagine if she produced 220 demand was 228 That means she can only sell 202 20 isn't it? So Now you can see it's recalculating every time and that's because I have another table open Let me just close that I have a table open where there is Sensitivity analysis there. So I'm just going to close that table so that we don't have two Sensitivity tables confusing us. All right. So let me just close that Okay, so right so now This is fine, but for us to kind of reduce our risk We need to kind of do a detailed Monte Carlo simulation and that's what we have here So, let me show you how this was calculated this Monte Carlo, right? How did we build this Monte Carlo for us to build it? We need to do it like a thousand Simulation so you see every simulation this keeps changing every simulation this changes right at one simulation This has changed another simulation. It has changed another simulation. It changes So let's do 1,000 of this simulations and all we'll do is we want to see what the demand and the profit is for Various simulations. We're going to do a thousand So to do that I'm going to type one here and then I want to type one two three four five up to 1,000 fastest way to do that is you click you type one you go to the home tab Then you go to the right you see a fill if you go to fill and click You see feel down feel right feel up feel down you go to series go to fill series And then you say I want to fill a column. I want to fill a series. That's changes by one But it goes all the way to one thousand now you could do a simulation for ten thousand hundred thousand simulation I'm just going to do one thousand simulations right now. So I say, okay, so this fills one to one thousand So you can see that I see that I just filled it out one to one thousand very quick so you want to do one to ten you just say one you come here you go to home and Then you go to fill and then you go to series and then say look I want to feel every two Two two two two give me up to a 20 or something say, okay. Oh filled it to the right because what happened I Came to fill Series I didn't change this to column. So it filled it by row, right? So I say give me every three steps and give me up to 30 or something like that You feel okay, and it feels down. So that's a nice trick So now that I have this I need to now understand demand I want to calculate various various demands for various run for the first simulation run second simulation run third simulation run And this is where a sensitivity comes in. It's almost we're going to use a Simulation to build a simulation We're going to use the same tool we use for sensitivity analysis, which is called the data table tool So it's a data table tool, which we use for sensitivity and the trick is you just type one two three to whatever then you come to the top here and link this top to What it is you're trying to get out I want to get the demand, right? And then here I want to get the profit This is just how you set up the table then you highlight like this all the way down to the end and Then once you highlight to the end you now come to data What if analysis and then you say data table so I want to select data table That's a very funny trick because the data table says okay. Where are your inputs? This status is like where's the inputs? Where's the input? We really don't have any input all we wanted to do is run 1,000 times So the trick is you come to column for example since it's in column and just pick any blank cell Once you pick any blank cell and okay, it's going to fill it out with answers So this is basically a different runs of the simulation is running the simulation running the simulation running the simulation and for this run of the simulation at 216.7 9 blah blah blah demand you get profit of this of course This is running with decimals, but we can forgive that the error margin will be much So let me just format that Then this one is the demand. So if you have two on six you will get to five It's 1,279. So let's test that if I come here Just type 216 just remember this I'm breaking this up 5127 9 is what we should expect there something close anyway So 5 0 8 0 0. So that's cool. So undo so that we still have our normal formula in there So this is how you do it. So this simulation is all the profit So what is the average profit was the expected profit we're going to get for this? This production of this place was expected profitability For that I'm going to go into this report summary. So I have a set of summaries that we need here, right? Set of things we need. So what's our expected profit if you look to the bottom here. I have The profit what will it be? Well, it's going to be the average of all these profit items here So let's just say the average Average of all these guys down here So all our profit from this very profit here all the way down and we enter So this is our Expected Profitability Now if this is your target profit, we're going to do something later to say how many of it is above target So we can even try that here. So profitability profitability of Probability sorry of loss. What's the probability of us making a loss? So if you think of it if I come here and I produce only maybe 50 plates Yeah, I still make a profit. So I produce 20 plates But if I produce only 20 plates, I can't even cover my fixed costs. So I'm making a loss, but I'm not going to produce 20 So let's just put I put a fixed figure 2 to 10 So question is what's the probability of us making a loss? Another way you could make a loss is if you increase our cost Per portion just increase our cost per portion to 500 Yeah, and if you increase our possible post proportion 500 We're still making a very small profits probability of profit depending on demand But if demand changes you can see I'm making a loss. So let me leave this cost at this So expected profitability is a loss. So expected expected to make a loss. So really not really good. Let's make this 400 What I'm doing here guys is what I'm doing here is scenario analysis. Okay What I'm doing is I'm changing I'm saying what if what if what if when you see yourself saying what if in a model You're doing scenario analysis. I'm saying what if it's 400. What if it's the what if is that? So we're going to create a table of scenarios very soon and say, okay, oh In different scenarios sets instead of doing these what ifs let's just create a table So these are our inputs. I'm going to copy this and let's have a set of scenarios. Okay So we're going to have a set of scenarios Let's put that here for example, let's just say scenario table basis base special values so It's called this base case because typically when you have scenarios, we we have what we call the base case what isn't like it. So this open base case Is a assumption base case. Let me make any inputs So what about a set of assumptions? Let's copy this and just have another set of assumptions or let's make five assumptions. Let's call this one Uh, our worst case or likely let's say likely case likely maybe a production will be 180 and Our price per portion. Let me let me just make this Left aligned So price per portion All right, and price per portion. Let's say likely is going to be 550 and Let's leave that 400 scrap Price or scrap maybe they only give us eight. I think it's going to be 16,000. So this is our likely case What about our worst case one case is probably And we only produced only maybe hundreds Right, and We can only charge 500 and then this is 40. Let's let's call this worst Who are the worst case for example like Shell values worst case the expected Prophecies there profit down here. It's the don't look here. This is just for one run. Okay This is just for one run. The only you should be looking is expected profit down here. Very terrible expected profit of 265.57 The what about if it's likely case if I copy this and paste it here The profit goes up So I'm copying a piece and that's not good practice Let's Is The Ridiculously And I wrap this so you can see so have a ridiculously optimistic case And so a ridiculously optimistic case is Let's say we have a 200 250 we produce 250 750 for some reason and this 50 and I think because let's let's just leave the fifth class the same So let's delete this one. So these are our cases Now, how do we run these cases? How do you let me let this move down here again? How do we run these cases? The model how do you do that? How do you run these cases? So typically you want something maybe a drop down here that I can choose which case you have and in that way We can now see what our expected profit should be, right? Let's do we need to run the scenarios. We need something to help us run the scenarios So let's let me Just shade that make it look nice. That's good. So here. We're going to create some scenarios Out of the ground around different scenarios. We have these are our scenarios. So We call them both two scenarios. You need two things. You need two things to run scenarios. One is in the list And you need a switch Okay The two things you need to run scenario you need a list and a switch So this is our list of scenarios and we need a cell as our switch cell Okay, so this switch is going to act as our switch cell And why do I need a switch what I need to do is switch from one scenario to the other so I want to switch from base To likely to work on everything and see how that affects our except for the Say What for example what I mean is base case, right? If I see it's worst case So for the list we're using this as a list. So I need to create like a drop down all my scenarios I'm going to use something called a combo box And the combo box I need to go to the developer tab The developer under Something called the box second It's called the combo box. I click it and I just buy it like this. It creates the box Yeah, so this box I'm going to fill it out with the scenarios here So I right click this box and I say format control And then after saying format control under my input Ranges I'm going to highlight this like this. This is my input ranges Then the cell link this cell link is your switch, right? So and I say, okay Let's see if this works if I come to the drop down I click out first and come to the drop down I only see base. You see it's not working Now the reason you only see base is because unfortunately you can't have a list this way you need to have it this way So this list has to be transposed So it's a good idea to have like a control sheet, right? So you kind of highlight this like this you copy it Okay, so here I was talking about scenarios. So we created a list And we created a switch. So this is a list and a switch And what we need to we name this list L scenarios and then we name this switch s scenarios So based on that we go to the developer and we select insert Insert combo box. So I'm now going to create the combo box. See so this is my scenario box Now this where we haven't plugged this into the model yet The key thing is you build this list and a switch first you bring this combo box Then you make this connection work first before you plug it in. So now this this Combo box, I right click it and I go to format control Now the input range this input range is actually your list this cell link is your switch So input range you just type if you can remember you have to remember the spelling I think it was l on this. So it's a good thing to have a naming convention So if I click out and click this drop down you see that the list is working I click on likely it says to I click on what's case it says So this becomes this is our switch, right now you take this Combo box we copy it. I'm copying this combo box and I take it into my model So I come to my model and I paste it here Yeah So now this box for my reporting summary. I'm doing reporting summary based on worst case see But nothing is going to happen really this is still running through the randomness But I now need to plug this into my model and the way to do that is all of these inputs need to feed from here So when I choose likely can you see likely or let me choose another one if I choose base case, right? If I choose base case My base case if you look at my model up here Production is not base case supposed to be 210 price proportion is 550. It's supposed to be 600 So I'm going to change all of this. These are not going to be inputs anymore So I'm just gonna filter and going to kind of remove all this input Style I'm just going to filter and use this style So I have three formulas in here so that I can pick from this and to do that Good practice will be of course. I'm going to pick from this table So how we do that is I do an equals to and I do the index function So I'm going to use the index now The index function is one of the most powerful functions in excel as far as I'm concerned So index function I say, okay. Hey, and I say index, please. I would like you to have a look at this table Right. See this table I have here. This this table, all right So this big table I want you to just monitor this table for me I'm going to press f4 And then I would like you to go to a particular row and a particular column that gives me exactly what I want Now the row I want you to go to is the row corresponding to one of these items So here for example in this cell It's called production. So I'm saying hey go to The row that is equivalence to production. So that will be row one two For me to make that work. I need to use a function called match Because I need to say, okay. What row is production in this table production is in row two prices are in row three No, this is row one the heading. This is row two row three So my prices are in row three my cost proportion is all in row four My uh scrap is in row five and then my likely case is in column. What see if this is the table See your table here. This is your table. This is the first column This base is column two likely is column three. But right now I want my rows So I'm going to use a match function And by the way, if you like to learn a little bit more about index you go to our youtube channel dbrownconsulting.net And you'll see How to use the match function very well. So I'll use the index function very well youtube dbrown consulting So I type match open and close bracket. I enter my formula bar select match now This lookup value is there. So my lookup value is you can see it now boys this cell Then my lookup array is it needs to go and look for where production is in this list, right? Which I need to look Then my match type is always going to be zero It's a permanent match type then I go back to index and then my column is I'm looking for what scenario am I picking? What scenario and the scenario I'm picking is base base base case And if you remember when I select base case It will basically type the name of the scenario is going to type a number if you come to control You see it's typing one If I selected likely it's going to type two. So this is really the number I want But just with a twist because if base case is one if I come to my table If you remember my table, where's my table this table started from here like this Base case is not the first column base case is the second column likely is the third column So I just need to do a small modification this cell that has my switch I'll need to add one to it Just adding one to it to make it correctly the second column So if I click okay, it brings out exactly to 10 If I change this to likely guess what it brings 180 if I change this to worst case it brings what? 160 so it's working fine. The only thing now is for me to drag this down So it copies for everybody all the way down here So this is now me created a scenario analysis. So you see Base case brings it out likely brings it out Worst case brings it out ridiculous optimistic case Yeah, you see a ridiculous optimistic case. We're making 43 000 as our profit for that day worst case We are making 395 naira very tiny. That's the expected Profit you remember if you remind you the Monte Carlo simulation has simulated 1000 runs of demand Different different demand assumptions 1000 times and then you see we're making some losses sometimes profit sometimes losses sometimes By the end of the day the average which is the expected profit That's what we have here and that's what your model should show So let's even see how much profit What's the probability of a loss? Let's calculate that So calculate the probability of a loss from this our model The probability of a loss would be how many we're going to count if You're going to count if this range down here is a negative So we're going to count any time all this profit all the way down How many times is that profit right? So look up that profit comma is it less than zero right? So is it less than zero? But I can't just type that there if I enter it's not going to work right? Because this is less than you have to put the whole thing else kind of put it in double quotes So it's like putting it in double quotes less than zero So less than since I'm typing zero I can put everything in double quotes less than zero in double quotes enter So once you put it less than zero then obviously you divide how many times are less than zero divided by 1000 you know we did 1000 simulations So it's 18.9% likely that we're going to make a loss What about if we're a base case? Is 21% likely that we're going to make a loss likely case 19% likely we're going to make a loss ridiculously optimistic case Just 8.3% likely that we'll make a loss right in all this all the simulation runs the runs of the various simulations right So This is our probability of a loss. What about the probability of not meeting our target? Well, the probability is the same thing count if Counting if right Anything in this this range that is less than our target So highlight this range anything in this range this 1000 runs. How many of you are Then we'll put a double quote less than Then put a double quote and then put an and sign. This is how you do it. You say Double code less than double code and sign and then link it to the target So see count if all our runs are you less than Our target and close your bracket We enter let's see. So 227 of them is less than target. We divide that by 1000 So the probability That we are less than target if I make the percentage sign Is 21 percent. So 21 percent probability that we're less than target So the what's the probability that we exceed target is simply equal to 1 minus this isn't it if the probability that less than target Minor it has to be they have to add up to 100 percent So the probability that we're really going to be above target is 78 percent Which is nice. That's very nice But um, what about if it was the base case? Oh, so if we run a base case scenario, these are the scenarios we run if we run this our Our our shop by base case scenario the probability that we're going to make a profit is 41 percent And our likely expected profit is 11 000. Yeah, so that that's how Scenario analysis works. It's pretty powerful But there's something even more powerful that we're going to do next Which is we're going to do a sensitivity of this scenario And that way we're going to see all the answers. You notice that before I can get an answer for base case I have to change this to base case then I'll see the answer click Change it to likely I'll see the answer click. So I want to be able to see all the answers at once But before we do that, let me get back to the chat. Let's see how what do you think about this guys? What do you think about what we've just done? We've built a model. We use Monte Carlo simulation We've done some scenario analysis. We use normal distribution and you can see that the model is such a tiny model here But this is now a very realistic model because we are using We've done 1000 runs of possible demand So let's have a chat for a few couple of minutes and then we come back and do the sensitivity analysis Right, so I'm getting jumping into the chat to answer anybody's questions What questions do you have or anybody wants to talk? I can just request to talk and then or type that you want to talk Ask a question and then I'll put up your mic. All right. So let me hear from you before I continue So while I'm wasting for anyone to chat anyone interested in chatting I just want to remind you about financial modeling. There is this advanced financial modeler certification course coming up from FMI so advanced financial modeler certificate course So that is coming up and that's the financial modeling institute. So you see that on your screen So the walls financial modeling certification program So the next exams and you know, Nigeria, we can do it in Nigeria now. So The exam location in Nigeria. There's one in Lagos and the exams are going to be in october So if I come here, you just go this is FM institutes.com If I go to the exam location and fees That's coming up the next exams are october And I've seen Nigeria. How much are they for Nigeria? Here we go we have Emia, which should be africa So see Nigeria. So currently the fees for the exam is 85 000 but that fee is ending on june 30th So 85 000 are for the exam. That's level one. So you pay this to the financial modeling institute It goes all the way to 170 000 is a normal price for the exams So if you pay before 30th of june, which is what in a week and so You can pay 85 000 for the exams and do the exams in Nigeria Or else to go up to 100 000 that goes up to 129 170 But we have a good promo for you guys whoever is interested in modeling We will pay for the exam for you if you register with them before the 30th We will pay for the exams for you if you do If you do register if you check out the offer I have that's on your screen Is the advanced financial modelers certificate course. It's a very very very detailed course And this is one of the courses we have is an advanced financial modeler course is what $2 000 So we'll give you that course at 400 000 You pay for five days you do a five day classroom You have access to one very rigorous online course extremely rigorous online course Which you click on this to view course for example, you see the rigorous online course So this is the online course you would go through and then you still come for a five day classroom You come for a mock exam and then we pay for your certificate exam as well So if you're interested in this let us know Now anyone wants to chat about what I've done so far before I so I can continue So here I'm going to do this sensitivity and what I want to do is I just want all these answers I want all these answers for all my scenarios all at once. Okay So these are the answers that I got Expected profit and probability of a loss and probability of not meeting the target Oh, there's 100 100 percent probability of not meeting the target for likely it's not possible. Wow Oh, so so that means you will not if you're likely because you're never going to meet this target This target is too high. Let's reduce our target to 15 000 Still no possibility. Wow. You need to do Base case to be able to meet your target. So likely case is very very stringent and it seems you see So it's a very risky business. That's what this is telling us, right? So how do we do this scenario? So let me minimize this Monte Carlo simulation and minimizing it. So let's get to scenario analysis So I've opened up scenario analysis. I just preset a set of rules here. So let's see. What are we building? What we want is a bit different from what I have here This is another way to do scenarios, right? So You can basically say at these particular prices. Yeah, see at these prices. What What is going to be our scenarios? What would these prices on these costs? What is going to be our profits? But I want to do a different a slightly different kind of scenario. So I'm not going to do this one What I'll do if I just insert some rules here so we can see Just going to insert some rules. So my own scenario is I need some outputs. I need all these outputs Right, so I'm going to copy this I need these outputs But the way I need these outputs is I need them to show for all the scenarios at once So I need all of this, right? I don't need targets. Let me remove targets I need this I need profitability But I want this to show for every single scenario. So I want you to show for Let's say base case let me type all the scenarios here base case likely and worst case I need it to show here. Let me paste this I want the answers For base case. Give me the answers for base case the answers for likely the answers for worst case the answers for ridiculous Right. Give me those answers and for that to happen I would need to do some things. There's some rules for doing that automatically Because I want all these answers to appear for every case down here And to do that, we're going to build a small. This is a sensitivity analysis a sensitivity table We need to do a few housekeeping things First of all, you will need to first remove. Let me bring this down a bit Because you know that the model The way that the model identifies base case is not by saying base case it identifies it by this drop down You know, when I say likely that's how the model identifies likely But really this is not excel. This drop down is not excel Now the only thing there's excel is this if it's not in a cell in excel It is not excel So since this thing is not in a cell in excel, it isn't really excel Even though it's working that means it must be working with a cell Now if you remember, where is that cell that controls this it is in the control sheet That has this cell So this is the cell that controls this drop down if I say worst case it controls it Remember this drop down is the same as This drop down they work with each other So for this thing we're going to do to work One of the rules is this the input must be in the same sheet where you're building your sensitivity The input has to be in the same sheet So I have to come here click on this input and cut it I have to control x And then I have to bring that input to this same sheet. So you go and hide it somewhere Let's just let me just put it here. Just just for Let's put it there. I'm just hiding it, right? So that's my input isn't the same sheet, right? So this input being in the same sheet We now looking at this that means this base case. What scenario is base case scenario base case is actually one This is two. This is three and this is four, isn't it? That's what it is Because three is worst case. So if I come here and change this this is worst case So this is three if I change this to base case, you see this changes to one. That's one So this is what the system knows how to identify base case likely worst case. That's how a system identifies it So you have to type that here Then the next thing you need to do is for the outputs you need to now link all these outputs to the actual cell containing the output So i'm linking these outputs to the actual cell containing the outputs I can just come drop this down to D This is the outputs. Let me just make this a naira sign as well Right So once you've linked it. So this Is your inputs and then this is your outputs Now you won't show your user this this is just so that the thing works Then you now highlight like this Once you highlight like this, you know that this row contains the inputs and these are the outputs You need to populate the outputs in here Now these things have typed here just so that it works so that this analysis works So i'm going to hide that so after highlighting you go to Formula table data tab Under the data tab you go to what if analysis to the right under forecast Now you select what if analysis and then you say data table Once you say data table is a very tiny but extremely powerful box So this box says hey, what is the input which row the row these row entries Where is the input for these row entries and you have any inputs in your column entries? That's what it's asking Now if you look at our table this column doesn't contains inputs it contains outputs It is the row that contains inputs. So you just tell it. Hey, yeah, this row that contains inputs This is where the input is that's all it wants. This is where the input is You know, we just type one two three when you click okay It's going to now populate this So now because this is a sensitivity f9 f9 Because this is a sensitivity analysis And you have various scenarios running here. So if I click on likely Because it's running different scenarios at the same time if you say ridiculously optimistic It will show you the same thing because it's how I call it It has another sensitivity if you look at this Monte Carlo simulation This Monte Carlo simulation used the sensitivity table So it's already running in Monte Carlo simulation sensitivity So it's not going to be able to run various scenarios at the same time So that's just one flaw of this you you have to get one answer at a time based on this but Really when you're doing a scenario analysis You and you want to put a sensitivity on it You can't really put it on another top of another sensitivity It just kind of confuses the system a bit. It just jogs the system's memory So for this to work you have to kind of switch off this sensitivity You probably have to Not have two sensitivities running at the same time But this is how you do it Then you would have different answers if you're not already running One sensitivity on the back end. So I'm just changing the formats for this And then this one as well All right, and that's one scenario Right now if you want to do another Simpler scenario, which is let's say this one here So this one here we could say, okay We're going to say what is the price and what's the cost if our cost is this And this is our Inputs was the price. So that would be this is our price and this is our cost. What will be our profit? Now for that to happen, we're not going to use we're not going to use this Our Monte Carlo simulation. We're not going to we're not going to pretend it's not there And we'll just use fixed figures for our demand. Okay We just use a fixed figure for our demand of 180 It's just fixed. So we're not using normal distribution anymore We're just using a fixed figure and then all of these figures are going to turn them back to inputs. Okay So I'm going to turn all of this back to inputs copy pay special values Once I turn them back to inputs This is a typical model and most of us know just inputs. This is demand which is also an input So let's say a demand of only 210 Right. This is all the calculations that go on now. We want to do a sensitivity Where we're changing the price we keep on changing the price 400 change the price 400 was our profit I change the price to 200 was our profit. We get we're just changing the price. I'm going to hide this one We're not using this Just hide the row so So this is a typical model. So we're saying okay at different prices At different prices and at different costs. So this is our cost Let's I think the cost is going up too much. So let's say our cost starts at maybe 400 In the middle, let's just say 400 in the middle. That's our cost And so you now type all the different costs you want to analyze. Let's say it goes up by just let's say 400 here and let's say it goes up by Maybe 20 just plus 20, right? Just to make it a bit more realistic And then here we're saying the cost is going to go down by 20 that's just to be a bit more realistic So you build this table up. This is like your sensitivity table for our price. Let's say 500 and let's say the price only goes up by I don't know 30 Right price. This is the price for food and then here we say the price goes down by 30 minus 30 Okay, so this is our table you type out all the prices you want to analyze You type out all the costs you want to analyze and what we want to see in here Is our profit right inside here? I want to see at this particular price and this particular cost. What's my profit? So let's check this cell This cell is at this price of 380 and this this price of 500 and this cost of 380. Let's check at price of price of what 500 Let's say price at price of 500 And cost of 380 You see that our profit is 740 right, so if I come here price of at cost of 500 and 380 so this 500 this is 380 The answer is 7040 Now, what about at a price of 400 at the cost of 400 At a cost of 400 the profit is 3 For so it's like you have to copy and paste like 100 times So at a price of 500 and a cost of 400 Price of 500 and a cost of 400. This is our profit. So you keep on changing this changing this this like scenario scenario That's inefficient What we want is the system to automatically Populate it and the way to do that is you type the analysis of price you want to check Type there are different assumptions of cost you want to check and then the corner here You link that corner to your profit. See this corner. You link it to your profit and you say enter So it's in there. You can't see it because we just made it disappear But it's there is there so we just kind of formatted it as white or something You come here you really don't need to see that so we made it white you can see it Then once you type once you link the output to the corner you highlight like this I Notice this row here has our price and this column here has our cost once you highlight you go to data you go to what if analysis data table Then under your row input cells saying, okay, this is the row that has price you come up to your model and say price Where's price price and then for the column inputs which is in the table this column inputs in the table You come to your model and you select cost So what you're telling the system is come and change this automatically 100 times and give me all the answers down here so when you click okay You see that the answers come up Answers have come up Perfectly come up So see the answers and what we can do is we could format these answers Using our normal number format thousands of rates or negative values so that we can see those places where we made a loss And you can see that this company is not profitable If you if you have if you have a price of 470 you better Have cost that is less than 380 or less So this is a sensitivity and if you remember you can you remember this too? This is what we did manually These two up down here. You know, we did we did that manually and you would have had to do that So many times manually But you can see that the system has calculated everything automatically using a sensitivity table So this is the sensitivity table giving us all the scenarios at once And so that is really how to use scenario and sensitivity analysis in our model If you're doing a Monte Carlo simulation and you're using sensitivity for the Monte Carlo simulation You won't be able to do another sensitivity So you would have to use the old school way, which is not just typing typing the inputs manually not doing a normal distribution So that's generally where we are I don't know how did we get all of that? It seemed like a mouthful So what did we happen is how many of you want the actual exercise so you can practice it yourself? So you can watch the video and go step by step by step and practice it yourself Because in the one hour I'm kind of crammed in a lot How many of you want that if you want that send me in the chat so that I can Get that for you and we note your request Because what's going to happen is on our website. You'll see you'll see this very soon on our website If I go to video blog on our website, you'll be able to see it. Let me show you that So if I come to our website here Normally you would go to our website dbrownconsulting.net Type webinars you'll be able to see it But if you want resources you go to resources and you go to a section of our website called Video blog on our website This is where we're going to have we're going to host to this video when it's ready so if you got a video blog On our website where's video blog? So it's coming up now video blog You will see this video will be stored on onto video blog And then you'll be able to watch the video and in that video blog you see a link To download the exercises so you can actually watch the video and and learn how to do this yourself from scratch All right, so I hope you guys enjoyed it And so let me have your comment. I hope you enjoyed it And we will be doing this again next month. We do this every month Webinars are every month third Thursday of every single month And I hope you enjoyed it, but you have a small poll to answer again for me. Let me just put that up before We go So you can see our video blog coming up on the screen Let me quickly get you a poll One last poll one last poll before we go Let's want to know how many of you before this cause how many of you use scenarios As scenarios when performing your financial analysis So these are the this is the history of all our webinars so you can go there to our resource page And if you go to the video blog page, which I thought this was You'll be able to see a list of our blog posts So 50 50 some of you have used scenarios in your analysis. That's interesting Okay All right, so thank you very much guys and I hope you enjoyed this and we'll see you next month Same time next month or Thursday of the month. We'll talk about some other topic So remember to register share the links with your friends and I'll see you guys next month