0:00

All right. In this screencast,

Â I'm going to go through the meat and potatoes

Â of putting together a Monte Carlo simulation using a user forum.

Â I'm going to give you this file.

Â This is a starter file.

Â It's not the entire file for what I showed in

Â the previous screencast on this cookie example,

Â but I will have quite a bit of

Â material in here that you're probably going to want to use.

Â One of the important things about this file is down here,

Â I've got a histogram and histogram data tabs

Â that you're going to have to have if you want to use my histogram tool,

Â which I would recommend.

Â So, I've got here,

Â if we could click on that,

Â this is just residual leftover data from one of the simulations I did.

Â The code that I've got is going to replace

Â all this data with new data and then it's going to plot.

Â So, it's really important not to delete,

Â especially the histogram data tab here.

Â It's okay if you actually delete

Â the histogram chart because it makes a new one but the histogram data,

Â you definitely need that.

Â You also need a main tab,

Â and you can't rename that tab.

Â It has to be named "main",

Â if you want my histogram code to work for you.

Â So, what you're going to want to do,

Â is you're going to want to replace all this with

Â the information related to the profitability analysis,

Â the net present value of the main project.

Â So, this cookie thing is just an example.

Â You're going to be doing this for

Â profitability analysis using cash flow analysis. All right.

Â So I've got a specific cost here.

Â I've already talked about the different costs of these things.

Â I'm just doing some conversions here.

Â You can look through the math if you want,

Â but what we've got here essentially is the total cost per 60 cookies,

Â which is a batch, and then we can get the total dollars per cookie.

Â Maybe, we sell it for 25 cents and then this is the profit.

Â So, if you change some of these things,

Â you notice that if I change this to a dollar stick,

Â the profit goes down a little bit because we're spending more.

Â So, you can kind of play around with this to get an idea of

Â how sensitive the profit is to the different inputs here.

Â So, I'm going to go through this,

Â and I'm just going to do two of these: flour and baking soda.

Â Flour follows a uniform distribution,

Â baking soda follows a normal distribution,

Â and then you're going to have to implement this.

Â So, I'll show you how to do this.

Â You're going to have to implement this type of stuff to your other project,

Â the cash flow analysis.

Â But I'm going to show you how you can set this up.

Â I would file you maybe just practice and complete this entire cookies simulation.

Â And then once you get that working then you can easily modify it

Â to accommodate the cash flow profitability analysis.

Â So, on the course web site, I have provided this file.

Â It's Montecarlosimulationstarter.xlsm and I

Â have provided you with a lot of code here that you can read through here.

Â But a lot of this code is for making the histogram,

Â which I think is going to be really beneficial.

Â I think a lot of you guys would like to see that,

Â the results of your analysis and graphical format and making the histogram

Â is a bit more complicated than I'd expect you to know how to do for this project.

Â But I've provided this main form, this user form.

Â And again, this is for the cookie example.

Â You're going to want to modify this for the cash flow analysis.

Â And I've got the different ingredients here,

Â their distributions, their parameters.

Â In this screencast, all I'm going to do is show

Â you how to set this up for flour and baking soda.

Â Flour is uniformly distributed between 45 cents and 70 cents a pound and

Â baking soda is normally distributed with an average of

Â 282 per pound and 50 cents per pound.

Â All of the other parameters,

Â I'm just going to leave as is.

Â So, all of these I'm just going to leave and we're not going to do anything with that.

Â So, let's go ahead and get started.

Â Again, I'm just going to be using these four.

Â What I've named this is flour min.

Â This input box is flour max.

Â I've got a soda ave and soda standard deviation stdev.

Â It's also important to note that this box down here is named n simulations.

Â That's the number of simulations we're doing.

Â Then I've got the go button here.

Â You're going to have to code the quit button which is quite easy,

Â but let's go ahead and code the go button for this.

Â So, this is where are you going to put all your code.

Â I like to get in the habit of deeming this workbook.

Â So, tWB as a workbook.

Â Set tWB equal to this workbook and then activate it.

Â Just in case somebody has the different workbook open,

Â you could accidentally do all of these changes to a different workbook.

Â I just realized that in making this starter file,

Â I eliminated all of the diming for these variables so I

Â will do that real quick and I'll be sure to include that in the starter file.

Â All right. So now I've added all the dim statements here that

Â go along with the histogram part of the code.

Â Now, what we need to do is we need to,

Â if I go back to the spreadsheet, into cell B3.

Â So, this is where a flour cost for one simulation is going to go.

Â So, I guess, let me just show you what's going to happen for each simulation.

Â The user form is going to randomly select a flour.

Â So, maybe it does 55 cents for

Â this particular simulation and it's going to calculate a baking soda.

Â And let's say it's 256.

Â It chooses based upon the normal distribution.

Â In my example that am working through all I'm doing is changing flour and baking soda,

Â but the full Monte Carlo simulation would do

Â the same thing for all of these other seven ingredients.

Â And after all those have been changed, then the result,

Â because this is a live solution, the Excel spreadsheet,

Â all you going to want to do is pluck out H19 which is the profit per cookie.

Â And that's going to be then stored for each of your thousands of simulations.

Â So, let's first code this to put in a random number that follows for flour.

Â Flour follows a uniform distribution,

Â so we need to put in some code to make B3 equal to

Â a uniformly distributed variable and B4 to be a normally distributed variable.

Â And then you're going to have to go through and do different coding

Â for all of the other seven ingredient.

Â And then you'll adapt this to the profitability analysis problem.

Â So let's go back into here.

Â And now we're putting the code to do this.

Â Now, we're going to do this iteration.

Â We're going to do this simulation I equals one to n simulations.

Â And I'm going to put in next I. N simulations, recall,

Â is on our user form here is just this number of simulations down here.

Â If you want to put default values in here, by the way,

Â you can just input them in there or you can go down here to

Â text or value and you can put in 1,000.

Â Plus, it just allows us to have nice defaults.

Â We don't have to always be putting in numbers there when we're troubleshooting.

Â Before we do the for loop,

Â I'm just going to add some code.

Â First of all, we need to ReDim R. Remember R is going to be

Â our vector of all the results of our simulations.

Â R is going to be composed of cell H19,

Â the profit per cookie for each of our 1,000 simulations.

Â Then I'm adding this line On

Â Error Resume Next and application.DisplayAlerts equals false.

Â If there is an error,

Â we just want to go onto the next line and

Â we don't want to display any boxes that say there was an error.

Â So, now we're going to enter into our for loop over all the 1,.000 simulations.

Â I forgot a line of code.

Â I'm just going to select the main sheet in case one or

Â the other sheets is selected like histogram

Â or histogram data then the first thing we do inside

Â the for loop is we say range B3 equals flour min,

Â plus flower max, minus flour min,

Â times a random number there.

Â VBA is going to choose.

Â So, this is where in B3,

Â we're going to be placing a random number that follows

Â a uniform distribution between 45 cents per pound and 70 cents per pound.

Â So, that's how I get this.

Â And that's exactly how I showed you in previous screencasts on how

Â to determine a number that follows a uniform distribution.

Â Next, we're going to use the inverse,

Â the norm inverse function in Excel to calculate a variable that follows

Â the normal distribution for baking soda with

Â baking soda average and baking soda standard deviation

Â and that's going to be placed in range B4.

Â So, when the VBA subroutine places different values in these two cells,

Â because again this is a live solution,

Â we're going to automatically update cell H19 which

Â is the result that we're looking for for that particular simulation.

Â And again, you're going to want it, in a full simulation,

Â you would want to do that for the other seven ingredients.

Â Here, you'd want to, just like I did here for flour and baking soda,

Â you're going to have range B5,

Â B6 and so on,

Â and then similar to what we did here but for

Â whatever distribution is required for that particular ingredient.

Â And lastly, at the very end of each iteration,

Â I'm going to say the result vector R(i) is just going to be equal to a range H19.

Â H19 is the profit per cookie.

Â So that's what we're going to want to record for each of our 1,000 simulations.

Â And then we'll keep going and going and going,

Â putting in different values of flour,

Â baking soda and we'll populate our R vector.

Â So, again for the full simulation,

Â and you might want to practice this,

Â you're going to have a lot more stuff going on this for loop.

Â You're going to have some "If then" statements for the discrete variables.

Â You're going to use the triangular inverse function that I provided,

Â if you have a triangular distribution,

Â you don't in this cookie's example but you will in

Â the cash flow analysis profitability problem for your main project.

Â So, we're ready to go just with these two.

Â So, I'm going to just show you how we can do one of these iterations.

Â Let's go ahead. I'm going to put a breakpoint

Â here and then I'm just going to press play here.

Â It brings up our user form and I'm going to click go.

Â I'm not changing any of these.

Â Click go and it's stopped here.

Â And then I'm going to step through here.

Â So, into range B3,

Â we're going to choose a random number

Â between 45 cents and 70 cents and we place that into range B3.

Â So, what it did was it just placed,

Â you can't see it here,

Â I can't scroll up but it's in B3.

Â And then we run the next line.

Â It chooses based upon the normal distribution,

Â places that in range B4.

Â When that happens, when those are placed,

Â it automatically updates cell H19 over here and that next line then

Â says the first element of R is equal to range H19,

Â which if I hover over it is 0.1208.

Â So, we store that into,

Â and if I bring up the locals window here,

Â I put that into my vector R here.

Â And you see R is size 1,000 and then we keep going.

Â So, I'm just going to press F5.

Â It does it again and it will go through and create a vector.

Â Let me stop down here before we make the histogram,

Â then we'll go ahead and run this, click go.

Â And as a result,

Â we can open up our R vector here and we've got all 1,000.

Â I can scroll all the way down and I've got 1,000

Â different simulations for this and it did it very quickly.

Â The rest of the code here,

Â I'm not going in much detail at all.

Â But what it will do is it'll create,

Â and this is the code that I provided.

Â It will create a nice histogram.

Â So, I'm just going to continue and we could go

Â to the histogram here tab and it's automatically created that.

Â There's data here, if you want to look at that.

Â But there's a histogram here and I've got the bin center.

Â So, the bin center is just the average of the left side and right side of each bin,

Â so you can just see the distribution.

Â And then if you wanted to,

Â you could play around with the the min and max,

Â the standard deviations, the averages of some of those ingredients.

Â So, I would recommend doing this,

Â maybe completing this for the cookie simulation and then you

Â can adapt this starter file very

Â nicely for the cash flow profitability analysis that is going to be your main project.

Â Hope this helps. Thanks for watching and good luck with your project.

Â