Welcome to our first model of the coffee shop. This is a really simple model or simplest of the models where we are going to simply simulate the daily volume of customers. Before we can do that, remember that we're given some information. We have some data available. For example, minimum and maximum volume of customers is given to us. So here we have minimum number of customers and maximum number of customers. But we do have to estimate a few things. Mean is not given, we don't know the standard deviation. It was told that there are more customers sort of in the middle of the distribution. So it is reasonable to assume that maybe we can apply a normal distribution here or estimate these numbers using normal distribution. So to calculate the mean is really easy. We can just take the average of minimum and maximum. So we can either use the function average or we can just add these two numbers and divide them by two, whatever you find easy. So we get 400. for the day, even though we are not going to do anything with daily revenue yet. But since we are estimating, we are going to be estimating the standard deviation. I taught what we'll do is we'll just estimate that right here. So given this mean and some information, now we have to estimate the standard deviation. In the tricks of the trade video, I showed you how to do this. But just to do this here, we can go ahead, let's just start with some assumption. Let's say we assume that this is 10 and this is 10. Now what do we have to do? Again, the goal is to figure out that majority of the values lie between this range, for example, between 300 and 500. So again, like we did in our tricks of the trade video, we're going to assume that 300 is very close to the minimum value. with only 0.5% probability below it. So that's what we're going to do. And as we did there, we can do this by using our normal distribution function in Excel, which allows us to provide the values. So you have to provide the value of the mean first -- sorry, first, the value of whatever we are estimating, then we want to specify the value of mean. Then we want to specify the value of standard deviation. So first, the mean. Comma standard deviation. And then we do want to have the cumulative distribution to be less than 0.005. So we are going to specify one for cumulative distribution. As you can see, we get a very, very small value, which is not what we're looking for. So we're going to correct that. But let's just copy this down so that we have a similar kind of idea here. But to get this again, we're going to use our Goal Seek. So go to the Data, What-if Analysis, and we'll do a Goal Seek. Now in The Goal Seek, again, what we are going to do is we want to set this cell to a value of 0.005 or 5%. And the way we are going to do that is because this was just a random starting point for standard deviation. We want to discover it for what value of the standard deviation At the value of 300, we'll get 0.005 probability. So we're going to just say, okay here, and let it calculate. So it'll do a few calculations, try to figure out what that value might be. And after doing a few iterations, it has found a value of 0.00499, which is close enough. So we're going to just say, OK, and that's the value of standard deviation 38.82 approximately. Now for the normal distribution for daily revenue, the is information given as slightly different, we don't have the minimum value, although we can calculate it by symmetry. But what we do have is the maximum value. So this is the extreme right portion of the distribution. So if there were .5% of the value below it, we had, for example, we would have 0.5% values above it, which means that 99.5% of the values were below this value. So that's the logic we are going to use here. And change this slightly. So we're not going to Compare to B4 which is this. Instead we are going to get the cumulative probability value for C4. So instead of B4 we write C4 there. And now you can see it comes up with one. So we are going to do again our goal seek, What-If Analysis, Goal Seek. And this time we are going to have for our value, as I mentioned to you, we should have instead of 0.005 because this value is on the right-hand side or near maximum, what we want now is 99.5% values below it. And again, by changing the cell where we just put an arbitrary value for a standard deviation. So again, once we start the analysis, it'll do some iterations, try to figure out what this value must be or should be. Still calculating, taking it a little bit of time. Finally, it found a value for which it satisfies. So 135.87 is what it calculates, that the standard deviation must be for this distribution. Now another thing that we need to compute to get our revenue calculations here is what is the average revenue? Now to calculate average revenue, what we can do is a very simplistic analysis. We can just take the average revenue, which is 20.50, and divide it by the average number of customers. So that should give us the average revenue and that's $5.125. Once we are done with this, we are ready to do our simulation. So to do that, as I mention in the slides, that strategy would be first to draw a random number. So let's just put a title here, random value, which would be a number between 0 and 1. And then based on that, we'll calculate For this probability, what value will we get from our normal distribution of customers? So the random value we can just calculate or get by using function rand. Function rand does not take any parameters. It just gives us a random value between 0 and 1. So here it's come up with a number 0.19. Now, our strategy again is if we think about our normal distribution, we want to know what this probability will give or what number will correspond to this probability from this normal distribution. For that, we can get that by using function norm dot INV, which gives the inverse of normal distribution. The first parameter that is asked for is what is the probability, which is this random value And then it asks us for the mean and standard deviation. So again, mean is 400, and then standard deviation is what we just calculated. At one value of normal distribution. We want to do 1000 of these simulations. So again, we can do that using our data table. So let say iteration, And then customers. Let's just do one more thing since we can't have partial number of customers. What we are going to do is we're going to use a function called floor. function floor dot map. And then what we're going to do is we are going to get the value from our norm dot INV distribution to just truncate this value to an integer value. That's all we're doing here. So we get a whole number like 380. Now for iterations again, what I'm going to do is I'm going to create just iteration numbers. And to do this, simply go to Edit and just create, fill it down. Series. In columns. We have step value of one and I want 1000 iterations. And now we are going to simply do the same thing we did in the first week when we were simulating multiple dice. And I'm going to specify this equal to this number that we calculated from normal distribution. And now copy this until the end again, just using the Shift . And then again go to What-If Analysis, go to data table. And again, we are going to use it in that strange way I mentioned. We are going to do, we're filling the column, so column input cell, just any arbitrary empty cell, okay? And once we say okay, that values are populated, as you can see, there are different number showing here. Again, they'll range from 300 to 500. And there might be some values before below 300, because now we are using a normal distribution where it is possible to get a few values below 300 as well. In the next video, we're going to look at some of the analysis that we can do with this data. We have simulated the number of customers we calculated the revenues now, and then do some analysis on revenues and get some idea about what kind of things we can understand by doing this simulation.
