 Hi, this is Dr. Don. I want to spend a few minutes and go over with you one way to approach the module 7 assignment 2 problems on simulation using Monte Carlo. And this first screen, I have just a very quick setup here of the outsourcing decision model. Do it this way or some other way, but this is the way I approached it and I just have two parts there. I've got the data, more or less in this case, the fixed or static cost, and then in the bottom down here I've got the model, which we use to make our decision. To watching those Excel's fun videos and reading a bit, I hope you have a generalized feeling about what we're doing with these Monte Carlo models. But in general, we've got our old fixed models in which things are not changing and we did some work with Solver and others in which we could change decision variables, things we have control over. What we're doing now is we're looking at variables for which we have little or no control, and so we model them using probability distributions. On this tab, I've added a new section down at the bottom called Assumptions. And in this particular model, I am going to use probability distributions, both normal distributions, and say that we are going to estimate the unit variable cost up here using a normal distribution. And we're also going to simulate the demand using a different normal distribution. And if you remember, we can define a normal distribution if we have a mean and a standard deviation. And that's all we need to define a normal distribution. And so I've got those here. So I modified the model a bit here. So in this revised model, in which we're going to use the Monte Carlo simulation method, I've changed the unit variable cost into a variable which is modeled by the normal probability distribution. And then demand, saying that we've got uncertainty in both of those variables. And I've used the formula that you saw in the Excel's fund video using the normal dot inverse function to come back with a probability. We base that on using the ran function, and that's tied back, of course, to the mean and standard deviation for unit variable costs. And so that produces some variation for unit variable costs. And then a similar thing here for demand, same basic formula, but tied to the assumptions down here a little bit differently. And the rest of the model is the same. Once we have the model set up that way, we can see the variation. And I'm simulating the change here. Every time I hit a key, you can see it's recalculating. And if you look down in the manufacturer and outsource section, you can see it changes sometimes from it's saying outsource, and then other times it'll say you should manufacture in-house. And so that's what we want to model and to capture. Using pretty much the procedure from the Excel's fund videos, I built out the next phase of the model, the Monte Carlo model. And remember, you need a blank cell that you always make sure stays blank, and so I like to label the adjacent cell to tell me that's my column blank cell that you need when you use the data table trick to fill this out. The demand here is linked down to my varying cell in the model, the one that's giving me the normal distribution there. The difference cell up here is linked down as well to the delta difference cell in the model. And then my decision cell here, whoops, is calculating based off of this cell. If this is negative, then it says manufacture. If it's positive, it says outsource. Positive meaning that it costs more to build in-house than to outsource. So that is the basic Monte Carlo model, and I'm going to go over here and just simulate. You can see every time I force it to recalculate, it's running those trials. In this picture one, I set it up for 1,000 trials, and there are a way to do that pretty easily, but every time I'm just repeating the simulation, and when I recalculate the sheet, all 1,000 trials are recalculated. And so that is the next part. Now you should be familiar with the rest of this exercise. I pulled out here the means of the demands from our Monte Carlo, and that's just using an average of all those means, and then the same thing for the difference. I've got the average difference for all 1,000 trials. And then I calculated the percent outsource, which is just the count of the number of times that the decision was outsourced divided by the total number of counts, which would be 1,000. And then we need the standard deviation calculated as well for some of the work you're going to do. To set up the histogram, I found the minimum difference and the maximum difference because we want to plot these differences in this column. The range of those differences is just the max minus the min. We wanted 20 bins, then I calculated the bin width and rounded that the way I've shown you in another video. And down here I've repeated that if statement, if the value of delta is less than zero, we manufacture, if it's more than zero, we outsource. And using the normal distribution function of Excel, I calculated what is the probability that we'll get less than zero. And based on the mean and standard deviation that I calculated up here, it shows that the probability is about 50% for the last trial that I ran. So that's all information you need. Then once you get the min and max and range and bins, you can create the histogram, which I've done over there. And that information over there allows me to produce this dynamic histogram. And I'll show you how that works. I'm just going to go up here and recalculate the sheet. And you can see as it recalculates, we're recreating the frequency distribution and that in turn changes the histogram. So that it is reflecting each one of those simulations of 1,000 trials. And you can see that the distribution does change because we're using probability here. And every time we run those 1,000 trials, that distribution of outcome change. And you can see over here that my probability of less than zero changes. And then I've got this linked again with an if state, but so it changes as well. If you can see there, the delta is less than 0.49% of the time. And that means that we should decide to outsource. So I hope this gives you an idea of what your assignment should look like. And if you have questions on how to create this interactive histogram, somebody ask me and I will show you in a video how to do that. So I hope this helps.