 Recall that our newsboy collected some data over the past several months plotting that data and producing a histogram We see that it's a approximately normal distribution with this right skew and a potential outlier there But if you calculate the mean and standard deviation of the data with and without that outlier It really doesn't change the mean and the standard deviation all that much So let's go ahead and model this particular situation using a normal distribution with a mean of 44.5 and a standard deviation of 260 I'm going to use an online calculator just for a moment to help you remember some things about the normal distribution In our case here, we've got a mean of 44.5 and a standard deviation of 260 If we know a probability then we can find out the x value that is associated with that probability Here I put in a probability of 0.75 and it says that the x that matches up with that is 45.80 If we put in a probability of 25% then the x is further down on the scale there about 40 to 30 So how can we use this information to come up with the demand for our newsboy? Well, if we can come up with a way in Excel to generate randomly these probabilities Then we can use the normal distribution to tell us what the x or the demand would be for that probability Now let's do that Excel has a pretty good tool for generating random numbers And it is the ran function which is just r, a, n, d and a opening and closing parentheses and nothing in between And that's what I put in this cell that I label in random variable For dealing with the normal distribution if we have a probability the function we need to use is the norm inverse And I've got it here and I'm just going to click on it for a second And it will open up and it will tell us, remind us that the arguments for the norm inverse are a probability, the mean and the standard deviation So we have those now, we are going to use the random variable to give us the probability And then we know the mean and standard deviation thanks to the new boys records I'm going to manually recalculate the spreadsheet You can see every time I do the random variable changes and it's always going to be between 0 and 1 And of course it allows us to use that as a probability And the norm inverse is returning x values based on that probability That are going to fit into that distribution that our good news boy provided us with Now here what I've done and I like to do in my model, I like to build my random variables outside my model And then link to them so that it keeps my model fairly simple The only thing I've added, you can see here I've linked down here to B25 which is this output of the norm inverse I wanted to round that because we can't sell fractional newspapers And I used the round function to 0 decimal places to turn that fractional value there of 4496.67 into a discrete value And you can see as we recalculate this that we don't see the profit changing very often Because so far the demand is higher than the amount that our news boy is buying There I finally hit one that has the demand lower than the quantity which shows his profit change a bit So how do we automate that? You can't just manually click recalculate, recalculate, recalculate and capture these values There's a way to do that and I'll show you in the next video There's a suggestion I need to give you about using these random variables in a decision model Think very carefully about the variable in your model that you are trying to simulate In this case we're simulating demand and demand logically and happily could be very large But we don't ever want it to be negative, that's not really realistic In this case people wouldn't bring back newspapers so we want to prevent demand from being zero With the current setup here the mean of 4200 and standard deviation of 200 We're okay, that's a pretty high mean and a small standard deviation If you recall from your statistics if you're 3 sigma, 3 standard deviations below the mean That only happens 3% of the time would you be further away from the mean of that So if you look up here I've added a column that gives the statistics for this demand column in our simulation model And you can see the minimum right now is 35, 65 and the max 48, 70 But if we had a much higher standard deviation, much more variation in demand which could happen You can see now that the simulation or 1000 trials, some of them are going as low as negative 407 Which is not realistic and we wouldn't want that to show up in our model That causes our minimum profit to go negative, you know we're losing money because the demand is negative So we need to stop demand from going below zero There's a good way of doing that in Excel, we've got a neat function that's called max I'm going to start typing ma, there's max and it says it returns the largest value and a set of values Which is what we want, so I'm going to double click on max to select it And the first variable which we already had in there is our random variable for demand But we want to put a second variable in there of zero and then close that and hit enter And now we have the formula max of either the random variable for demand or zero, whichever is larger And you can see over here in our model that now our minimum value of demand stops at zero Which is what you want Remember that, look at your variables in your decision model and make sure your random variable is not going to return an unrealistic value