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.
