 Statistics and Excel Poisson Distribution Roller Coaster Line Example Part Number 2 Get ready, taking a deep breath, holding it in for 10 seconds, looking forward to a smooth soothing Excel. First, a word from our sponsor. Yeah, actually, we're sponsoring ourselves on this one because apparently the merchandisers, they don't want to be seen with us. But, but that's okay, whatever, because our merchandise is better than their stupid stuff anyways. Like our Accounting Rocks product line. If you're not crunching cords using Excel, you're doing it wrong. A must-have product, because the fact as everyone knows of accounting being one of the highest forms of artistic expression means accountants have a requirement, the obligation, a duty to share the tools necessary to properly channel the creative muse. And the muse, she rarely speaks more clearly than through the beautiful symmetry of spreadsheets. So get the shirt, because the creative muse, she could use a new pair of shoes. If you would like a commercial-free experience, consider subscribing to our website at accountinginstruction.com or accountinginstruction.thinkific.com. Here we are in Excel. If you don't have access to this workbook, that's okay, because we basically built this from a blank worksheet. However, we started in a prior presentation, so you could go back there, start with a blank worksheet. Or you'd probably be okay to start at this point with a blank worksheet as well. If you do have access to this workbook, three tabs down below. Example, practice blank. Example, in essence, answer key, practice tab, having pre-formatted cells. So you could get right to the heart of the practice problem. Blank tab is where we started with a blank worksheet. And now we're continuing with it at this point in time. Quick recap of the scenario. We're looking at a Poisson distribution situation, which often has to do with line weighting situations. As it does here, lines related to a roller coaster ride in this case. Last time, we imagined that we wanted to figure out how many people arrive in general during like a busy time, for example, in each one minute time period. So we went out with our stopwatch and we looked at 1,001 minute time intervals and we counted how many people arrive in each of those one minute time intervals. So for example, in one one minute time interval, we had four and then in the next one minute time interval, three people arrived in the next one minute time interval, two people arrived and so on and so forth. And we imagined doing that a thousand times. Obviously, we didn't do that in practice a thousand times, but instead used the random generator function in Excel, which is in the data tab analytics group. If you don't have that analytics groups, you can add it in the file options that we looked at in a prior presentation. So this simulates us actually kind of gathering the data, which might be useful then to then think about, does it conform as many line situations do to a Poisson type distribution? So we then said, okay, let me count up using our buckets and bins and my frequency formula, how many we have counted in our 1000 intervals that had one person arrive versus zero versus one versus two versus three and so on. And then we can look at the percents. So in all of our one minute time frames, 6.2% of the time zero people arrived 17.10% of the time one person arrived and so on and so forth. So now that we had our data, we can then plot it and say, if I plot my data, does it look like possibly a Poisson distribution? That would be one indication. Does it conform to the conditions of a Poisson distribution, which we talked about in a prior presentation many line situations do? That would be another indication that we might be able to use a Poisson distribution and oftentimes the variance will be close to the mean if we have a Poisson type of relationship, which we have a pretty close relationship here. So now we're thinking, okay, then I might be able to predict what's going to happen in the future with regards to line waiting situations by instead of trying to extract the actual data using a smooth curve or function, the Poisson distribution, that's what we're going to do now. So if I go to the right and we say, all right, what if I just plotted this with a smooth curve? And by the way, I started on cell two. So I'm just going to delete cell one and hopefully that doesn't delete anything or row one. I'm going to put my cursor on row one, right click and delete. And so to bring everything up to the top because I started on the wrong column, hopefully that doesn't confuse anything too much. We're in column Y. Why? Because that's just how it happened. That's just how things happened. So once again, I'm just going to say equals and bring over my same data from the left. So I'm going to say equals this amount so I can just bring in over that same data. And then when I calculate the mean, I'm going to say that the mean now equals and notice the original mean that I used to calculate the data set was 2.75. When I actually look at the data, I calculated the data, it came out to a mean of 2.73. So if we were actually looking at our data, we would be using the 2.73 as the mean because that's the mean of our data. So let's just pick that one up. I'll say the mean is 2.73. That's what's in our data set. We're going to add some decimals, home tab, number group, add a couple decimals. Let's make column Y a little bit longer. Why? It's a long why. All right, so then we're going to say, all right, now let's do our data in accordance with a Poisson distribution. Let's just build a table, build a graph, and see how it compares to the actual data that we have. So this is the ideal data curve according to a Poisson distribution. We'll call it P of X Poisson function. Let's just call it P of S X for the Poisson. And I'm going to say then let's copy these two and go to the home tab, font group, black, white. Let's center it. And there we have it. And then I'm just going to take the X's down to what did we say 29. Remember that the Poisson distribution, I mean, it could go on forever. That's why it's skewed to the right or positively skewed, because you could imagine in one minute interval, infinite people arrive, but that's not likely to happen. So we're just going to cap it at 29. So I'm going to say one, two, select those two, put my cursor on the fill handle, drag it down to 29. 29. Bam. All right. And then I'm just going to do my Poisson dot dist function, putting my cursor in AC2 equals Poisson dot dist. And so we need the X value. I'm going to pick up this X value, comma, we need the mean. I'm going to pick up the mean here from our data set. I want that to be the same as I copy it down. Select F4. Notice you could use spills and arrays, but I think in this case, I kind of like using the absolute values, because if I wanted to put a table around it or something, I would rather do that typically. So I will switch back and forth from arrays in the areas that I think are most useful for the arrays versus not arrays. And then I'm going to say that we could have cumulative. We'll give us the cumulative down if I put true or I can put false for the probability mass of one. We want the probability mass of one because we're looking at each outcome here. So and I believe that is a zero. So I could put zero or false for probability mass function. That's what we want. Close it up, enter. And then I can put my cursor on at home tab, number group, percentify it, add a couple decimals. And then I'm going to double click on the fill handle to drag it down. Boom. So it copies it on down. So so there we have it. And that looks good. Okay, so let's take the difference between that and my actual data. So the difference just to get an idea. Home tab font group, black, white, wrap center. So my actual data that I came up with over here said 6.2% minus what I come up with a Poisson distribution 6.52. So let's add some deaths and percentify at some decimal. So you can see it's pretty close. My Poisson distribution, if I copy that down for each of these items is coming pretty close to the percent likelihood that we came up with our actual data. So so in other words, if I look at this, we're going to say for each one minute time interval, the likelihood that zero people show up is 6.52. According to the Poisson distribution, which is within 0.32% of what our actual data set, the likelihood that one person is going to show up just one, not cumulative, not zero or one. In which case we can sum the two up right to 24% that zero or one shows up. But no, just one person shows up likelihood 17.8%. Just two people show up in the one minute time period 24.3%. What's the likelihood that one to zero one or two people show up? Well then we can say, well, if I can sum those up and it's not going to mess up with calculus to have a curve, then it's going to be 48.63% that we have one zero one or two people show up. That's the idea of the curve. Now if I plot this, then I can plot this curve and say let's just select this area here. And I'm going to say insert and let's make it a chart. Let's go to a bar item and plot that. So I'm going to bring that I should have brought it up. So there it is. And then I'll go into my data. So let's say my data, this data is good. But for here, I want to make sure to pick up my own numbers from zero to 29. And so there we have it. So I could plot this data and compare it then to the data set that I have, the actual data set that we calculated. So I could add another one and say add another data set. And this one, hold on a second. I don't want to edit. I want to add a data set. And this one is going to be for this and the data is going to be here. Hold control shift down shift up so I don't pick up the total enter. And so let's see if it does what I was hoping it did not. What happened to that second data set? Okay. So let's what happened? Let's do another. Let's do it again. Let's say add data set. I want it to be equal to this. And then the series needs to be from hold on a second. It needs to be from here down shift up. Okay. The percentages closing that up. And this should be I'm going to say the percent of the total. Right. Isn't that right? Let's try it. Let's try it again. Okay. So there we have it. So you can see there, you know, pretty, pretty closely lined up. I've also added the legend so you can see the two different colors with the plus button and then the legend. I should probably adjust the labels, but we'll keep that for now. Now we might also represent this with a line if we have two items on the same chart because it's a little less messy. So I could select the same data. Put in my cursor on AC one control shift down and then insert the charts hitting the drop down. We want a line. So now we've got the line with markers. So let's pick that up. And if I go into the chart design data and the data. So here's our first data set. I'm going to add another one and I'm going to go to the right to do that. And I'm going to pick up this percent of total. I'm going to delete this bit. I'm going to hit this little thing to make sure I'm picking up the. It's going to sum this up, putting my cursor on L two control shift down shift up so I don't pick up the total. And there it is. Okay. And then I'm going to edit my horizontal, my horizontals to be equal to. I'm going to hit this to make sure it picks it up from here. Control shift down to 29. And there we have it. And okay. And okay. So you can see it's quite close when we when we plot them in this format. So I could add the legend again. So I could say add a legend. And then we could, you know, make one of these transparent or something like that. If we wanted to make it more clear. But those are the couple charts that we can put together and say, okay, that's a pretty close relationship. All right. So remember what we can do with this data. I'm going to pull this to the right now is we can start. We can start asking questions about the data in terms of how many people might arrive. Right. So let's just first let's calculate the mean and the variance. So I could calculate the mean of this data. And one way to calculate the mean is to basically we could take this, all of this times this. And that and that will be another way we can calculate the mean. So in other words, when we calculated the mean over here, what we did is we we just took the average of all of the results. If we look at this, then let's just I'll show you in a couple different ways. I'm going to pull this to the right. I could say, let's say this is going to be X times P times P of X brackets. I'm going to make this black, white centered and wrap. And so I could say this is going to be then this times this. But I'm going to copy that all the way down. So and then let's say that this is going to add. Let's add some decimals here. So basically we can say in the one minute time interval, right, there's a likelihood that one person arrives. We have 17.8%. It's going to be like 0.18% of a person, right? If we were to calculate this out to try to get the mean, right? And if the likelihood in one minute time of two people arrive is going to be the 24.3%. So we can say that'd be like 0.49% of a person. So then if I multiply that out and then I add up this, it equals the sum of this, control shift down, and I add a decimal, home tab, number group, adding some decimals. There's our mean. So we can also calculate, so you can see that's the mean that we came up to over here. So we can also calculate that using a function, which is a good time to introduce. We can use the sum product. So if I want to do that this way, I can say this equals the sum product brackets, and then I've got my two arrays because I want to multiply then this array times this array. So I'm going to select this entire array, control shift down, and then say comma, the next argument or the next array is going to be this, control shift down, and enter. So now I should get the same thing, home tab, number, couple decimals. Now if we wanted to get to the variance, we could do that a couple different ways. I'm going to pull this over. I'm going to pick these two up. I'm going to control X and put them over here, control V, and then I'll add another column here. This column is going to be then X minus the mean, and I'll pick this up and say home tab and clipboard. And so now I'm going to say this is going to be X, this number minus the mean, which we've calculated a few different ways. Let's pull it over here. I'm going to F4 to make that an absolute reference, F4 and enter. And then I can add a couple decimals, number group, adding a couple decimals, fill handle, copy that down. So I'm going to take that and then I'm going to square it. So I'm going to say this is going to be squared. And I'll format paint this, home tab, paint brush squared to do that. I'm going to say equals the cell to the left, carrot, shift, six, two to get to the power of two or squared, entered. I can add a couple decimals there. Double click on the fill handle, bringing it down. And now I'm going to take that column times the P of X times P of X. And I'll put my cursor here, paint brush here. And then this is going to be equal to this. That we just calculated times the P of X percent. And let's add some decimals. Decimalizing it, double click, bringing that down. And so to get to the variance, one way we can do that variance, is I'm going to say equals the sum of these items, control, shift, down, enter. And if I add some decimals, we get to the same 2.73, because that's part of the relationship of a Poisson distribution. And this is the perfect Poisson distribution. Now you can also do that variance calculation just to practice the sum product function. So we'll say this equals sum product. This is an array formula. I want another bracket because I'm going to take it to the power of two or square it. Put my cursor on cell AB2, control, shift, down on the keyboard. So I can go down to the bottom. I'm holding control backspace so I can go back up top with the keyboard. And then I want to take each of those minus the mean, which I'll pick up here in AK. So I'm going to pull this down. And so we have that closing the brackets, taking it to the power of two. So I'll say shift six carat to the power of two square in it. And then the second bit, comma, the second array is now going to be here AC2, control, shift, down. And then I'm going to hold control backspace to go back up to the top and close up the brackets and enter. So there we have it. I'm going to then go to the home tab and font troop and add some decimals. And once again, we have the variance should be the same as the mean. So just a little bit of practice with the Excel formulas there. Now some other questions that we might ask from like a from like a practical standpoint is we might say we might ask what's the likelihood that we have equal to or less than three arrivals within a one minute time period. These are the kind of questions we can ask. Now if I plotted everything out over here, I could say, well, these, this is the likelihood that zero people arrive, exactly one people arrive, exactly two people, three people. And I could sum those up. I could say, OK, this is equal to the sum of these. Hold on a sec. Some of these and enter and make that a percent home tab number percentify adding some decimals, or I can use the poisson cumulative function so I can say this is the poisson dot dist. And this I'm going to take the X is going to hard code is going to be three and then comma the mean 2.73. But this time instead of taking it at one point in time having just three people arrive, I want to go from zero up to three people. So that's going to be the cumulative distribution where I could type either true or a one. So I'm going to type a one, close it up. And then I'm going to say that this is a percent adding some decimals. So there we have it. So it gets a little bit more complex. If you were to say, for example, you want between between, let's say between two and five people between two and five. So I could once again go over here and say, OK, well, if I'm going to say this equals the sum of between two and five. Now if you're not including two or five, then I would say that would be either three or four. So you got to be kind of careful on where the starting point is. Are we saying equal to and including two and five or between, you know, the two and the five. So make that a percent. And then we could do that with a formula. And if I did that with a formula, notice what I kind of have to do is I've got to take the Poisson distribution for the upper limit, which in this case, the upper limit being five. But note, I'm not including five in this case. So I'm actually going up to and including four and then subtracting out that lower amount, which is going to be up to and including the two. And that will leave me with just the three and the four, the middle points, which is what we're looking at. So it looks something like this equals Poisson dot dist and that pick up the X. So the first X is going to be the upper limit, not five, but four because that's what's going to be included in it because it has to be below five comma. Then we'll pick up the mean 2.73 again. And then comma, it has to be cumulative up to that point. So then I can subtract out the lower bit. So we're going to say it's going to be cumulative, which is either type in true or put a one and then I'll close that up and then subtract out another Poisson dot dist. And this time I'm going to be picking up an X of two. So up to and including two is what I got to subtract out this bit over here and then comma. The mean is still 2.73 comma. This also needs to be cumulative. And therefore I can type in true or I can put a number one close up the brackets, enter, check it out, home tab, number group, percentify, adding some decimals. I could add decimals here if I wanted to. And so those are the ways that we can calculate. So those are the kinds of questions that we can then ask ourselves. What's the likelihood that amount, how many people will arrive within a certain timeframe, either one absolute number, two people arriving within, in this case, the one minute time frame or some combination such as zero, you know, zero to five people or what's the likelihood that, you know, from six to 10 people are going to arrive or sit over six people are going to arrive. Remember down below it's unlikely that you're going to have anything that's going to be way down here, although we're skewed to the right on this curve. So it's theoretically possible that infinity people arrive in a one minute time period, but it's quite unlikely. Right. So if you go six and above, you can kind of sum up everything down here and say, well, what's the likelihood we just get, you know, swamped over something over the six and you can, those are kind of the questions that you can get. And obviously those can be useful for planning purposes. Let's clean it up a little bit. I'm going to then, I'm going to add some blue to our data home tab, font group bucket. I'm going to make this blue and bordered. I'll do that here too. I'll select these control shift down and I'm going to make that border drop down. If you don't have that blue, it's right here in the fill color. I'm going to do that here too. Scroll into the right control shift down. I'm going to make that blue and bordered. I'll make this blue and bordered just to color it the way we normally do. We could actually make these a little skinnier and make column D a little skinnier and maybe I'll select column D home tab format paint it to column I format paint it to column M possibly. And then over here, I might format paint it to column X for skinny X. And then here, let's make this blue and bordered home tab font group border blue. Let's take the skinny X home tab format painter skinny AA to take this data, make that blue selecting these items control shift down. I'm going to make that blue and bordered here. And so we have that by the way, if I total this up equals the sum of this or I can do it this way. Alt equals. Let's do that again. Alt equals. It should come out to one or 100%. That's kind of a check number that we can have. Let's make that percent here. Let's put this as a total. Let's put an underline here, home tab font group underline. And then on the right, let's put some blue around this stuff going to home tab font group border blue. Let's take skinny AA home tab paint brush skinny AI put a skinny AI there. And might as well check the spelling while we're here spelling checkie. And it's good enough. All right.