Monte Carlo Simulation For Any Model in Excel - A Step-by-Step Guide

hey guys welcome to another episode so it's a bit of a longer video today but it's definitely gonna be worth your while so stick to the end we're gonna take a traditional model for in Excel and transform it converted to a Monte Carlo simulation Monte Carlo simulations are an amazing way to take care of uncertainty in our models and gonna show you exactly how to do it step-by-step who even gonna write a small macro that gonna run the whole simulation for us by the way if you enjoy those videos out tremendously appreciate us up and maybe even a thumbs up if you enjoy the video let's get straight to it okay so we have here this simple model where we have our sales volume sales price cost of sales and our option for tax exemption and I've went ahead and calculated the traditional approach so let's look at what we have done our sales volume is so it's going to be minimum twenty five thousand most likely about forty thousand maximum forty five thousand and for our traditional model we would have gotten the most likely then our sales price we have a min and a Mac so I just went ahead and calculated an average and for our cost of sales we have our mean which is the expected and our standard deviation taken the expected here and for our tax exemption there's seventy percent chance that we're gonna get the tax exemption and thirty percent chance that we're not gonna get the exam so we're gonna have to pay thirty five percent tax and we use a decision tree formula here so basically we get the probability multiplied by the outcome plus the other probability multiplied by that outcome and multiplied it on to our profit and this is our traditional approach that we would usually follow when calculating this model what happens if you want to apply a Monte Carlo simulation so the first thing we need to do is gonna need our distribution types here okay so this is a triangular distribution we have a min and most likely and a max this here is uniform distributions of min and Max and everything is exactly the same probability of occurring this is a normal distribution and this is a discrete distribution have a list of options so it can advert this or this with those probabilities and it can never be like 25 or something like that let's go ahead and calculate some values for those before we build our Monte Carlo simulation so here we'll need to set our men are most likely and our max and gonna add some borders to those or min just link it from here most likely and our max and then for a triangular distribution there's no formula in Excel so we have to calculate it on our own we have a lower range higher range we have our total range and our cumulative probability which is actually our random I just format those as well our lower range is gonna be let me just carry over here some formulas for the triangular distribution because it's a bit harder to to do so here we have our triangular distribution random number generation so our lower range is our mode or most likely - our minimum our higher range is our maximum - our most likely our total range is our maximum - our minimum and our cumulative probability is just going to be the random number between 0 & 1 let's just go ahead and calculate it here for our model there's gonna carry all the formatting from here with the format painter and this formula I'm gonna carry over my prophet I'm gonna carry over my cost of sales I'm gonna carry over and my net profit I'm gonna carry over you notice that every time we save or edit a sale or press f9 will recalculate the random numbers and this is what we're going to use for our Monte Carlo simulation we're gonna run it over and over and every time all the random probabilities are gonna recalculate and we're gonna get a different result here this is the formula that you have to write it's it's a bit hard to comprehend but let's just type it in so we need to check if the cumulative probability so this is the low the lower range over the total range and then our random triangular is equal to our minimum plus the square root of our cumulative probability multiplied by our lower range multiplied by our total range and if it's not else our random triangular is our maximum so our max minus the square root of 1 minus our cumulative probability multiplied by the higher range now multiplied by the total range one two okay that's it and the easiest way to check if we've written our formula properly is that if our cumulative probability is zero we need to get 25 which is the minimum and if it's one need to get 45 which is the maximum so our formula is correct okay I'm gonna go ahead and get rid of this and so let's go ahead and continue with our assumptions below so for our uniform distribution of sales price we'll need our men our max our Max and min and a random number again just gonna copy this random from here and go over this format [Music] okay so our minimum is 8 or maximum is 850 and our max minus R min is 50 so the way we're gonna calculate this is we're gonna get the minimum and we're gonna add our random between 0 & 1 multiplied by our max minus min so basically if our random is 0 we're gonna get 8 if I run the MS one you're gonna get 850 okay now we have our sales and you see that every time we press f9 our sales change cost of sales is gonna be our normal distribution so in order to calculate this one we need our mean our standard deviation and a random number as well okay so this is our mean and this is our standard deviation and the way we calculate this one we're gonna use the inverted function so basically it's gonna be our min plus norm s inverted so this returns the inverse of the standard normal cumulative distribution vert it these are probabilities of our random multiplied by our standard deviation okay and what's left here we have our tax our discrete so have 0.7 chance and 0.3 chance and Tom just grabbed that random over here no saint this is at 0.200 percent and this is at 35% we're gonna calculate that this we're gonna see if our random reaches up to 0.3 it's gonna be 35 percent and if continues above that for the next 70% is gonna be zero so if a random is less than is here oops give me 35 percent otherwise give me this zero multiplied by our profit okay and now you can see that our tax is zero and if we keep pressing f9 at some point we come we go into this zero point three probability and we have a 35% tax [Music] okay so that's our Monte Carlo a model built here and now let's see how we can run a simulation I'm gonna start building it here we'll have our counter this is where we're gonna write in how many calculations how many iterations we want our model to run for so let's start with 500 and here we'll have our iterations counter our average result and our probability of it being above hundred and twenty thousand and one hundred and fifty thousand those are the two checks that we want to do so if it's above that we want to consider add the project and if it's above that we're definitely going forward with the project okay and the next thing we need is a list of our iteration and net profit or profit after tags at the spot net profit that we're gonna get okay I'm gonna grab that formatting from here and our iterations is going to be just a count of our net profits the average is going to be the same the average of photos and our probability above we're gonna do it with count if so MDF and my just got here my criteria is gonna be above hundred and twenty thousand okay and I'm gonna copy the formula here paste it here and just switch two hundred and fifty thousand okay now how are we gonna do that the idea is that we want this to change as if we're pressing f9 and every time it changes we want this figure to be copied here and going down but one way to do that is by hand so pressing f9 and copy pasting but we usually run hundreds and even thousands of iterations of each simulation so that's probably not the best idea what we can do is we can go ahead and write a simple macro to do that for us ok so just going to go to the Developer tab insert and grab a button put it over here and I'm gonna call that Monte Carlo sim and create a new subroutine so this is our subroutine this is the Microsoft Visual Basic for applications editor and let's just go ahead and write our macro so what we want is we want in a loop that's gonna run through our iteration so for for gonna call it iteration and it's gonna be from one two cells row index five so I want to be getting this here and this is my fifth row and M is my 13th column so five thirteen and this is gonna iterate from one to five hundred or whatever which I pin here and every for loop needs to end with the next iteration so it's gonna go from iteration one to run everything in between those lines that we're gonna input right now and go all the way to 500 before ending okay so what we wanted to do we want our cell [Music] so my iteration heading here is at the twelfth row so I wanted to start from the thirteenth row she's gonna type in 12 plus iteration because our iteration is gonna be one at the first run and then it's gonna grow so it's gonna move the cursor down and copy on the next row every time M is at 13 and here I wanted to place my result my net profit and my net profit is at my 31st row and add my fifth column so a b c d e my fifth column so 31 5 and every next iteration it's gonna copy this result in the next row down and I also wanna have my iteration index so once again on 12 + iteration 12 because I always my 12th column give me iteration and that's our that's our subroutine that's our macro you just save that and just name that a bit more appropriate run simulation and now the only thing that's left to do is go ahead and run it let's first run it for 100 iterations just to see how it works and if it works properly and let's go ahead run simulation okay we have some issue okay let me just see if we assigned the proper macro our Murray's gonna reassign it and now it should work yeah okay and that's it you see just for a few seconds we already completed our hundred iterations and you can see that the average that we get is one hundred and twenty thousand and here are all the results from all the iterations this probability here doesn't seem okay yep we forgotten something we just counted the instances and in order to get the probability we have to divide them over our iterations the right over our iterations and those need to be formatted like so okay I'm gonna go ahead and run this for a bit more just to see if you see that the average now is one hundred and twenty one thousand and the probability about it being above one hundred and twenty thousand but fifty percent and this is about twenty two percent but hundred iterations is quite a few so letís go ahead and try to run it for about ten thousand [Music] okay we've run it for 10,000 iterations and you see that our average dropped a bit but this is probably enough iterations 10,000 if you noticed like maybe after five six thousand it rarely moved from those from those probabilities and the average pretty much stuck at 113 thousand so we can consider 10,000 iterations enough and a hundred wasn't enough it gave us a bit of a bit of a misleading information what we can see here is that the average is a hundred and thirteen thousand that we're gonna get and this means that our probability of it being about 120,000 what we want is only 40 percent and about 150 thousand just about 16 percent and you can see that our traditional method here showed us that we're gonna get above 120 thousand which was a bit misleading because once we run our Monte Carlo simulation you can see that the possibility of that is only about 40 percent so we have to figure out if this forty percent is enough to pursue this project okay that's how to run a Monte Carlo simulation you can do that for as many assumptions you want with there's tons of different probability distributions that you can apply to different assumptions okay guys that was it for this video Monte Carlo simulations are amazing and they can help us a lot in any kind of modeling situation where we face uncertainty so I hope you enjoyed the video give it a thumbs up if you did also don't forget to subscribe if you're not already and maybe even punch that bye icon to receive notifications every time I upload a new video till then thanks for watching stay safe stay at home and toss your next one [Music] you

Channel: Dobromir Dikov

Views: 21,917

Rating: 4.9724612 out of 5

Keywords: excel, model, monte carlo simulation, step by step, guide, tutorial, financial modeling, excel modeling

Id: IuxWEBXlyBM

Length: 20min 7sec (1207 seconds)

Published: Tue Apr 21 2020