 Statistics and Excel Poisson Distribution Potholes in Road Example Part Number Two. Get ready, taking a deep breath, holding it in for 10 seconds, looking forward to a smooth soothing Excel. Here we 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 that's okay, whatever, because our merchandise is better than their stupid stuff anyways. Like our crunchy numbers is my cardio product line. Now, I'm not saying that subscribing to this channel, crunchy numbers with us, will make you thin, fit, and healthy or anything. However, it does seem like it worked for her. Just saying. So, you know, subscribe, hit the bell thing and buy some merchandise so you can make the world a better place by sharing your accounting instruction exercise routine. If you would like a commercial free experience, consider subscribing to our website at accountinginstruction.com or accountinginstruction.thinkific.com. 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 to that presentation, start with a blank worksheet, or you'd probably be okay starting from this point going forward with a blank worksheet. 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 are continuing on with it at this point in time. Quick recap of what we did in a prior presentation, looking at a Poisson distribution situation, but this time, instead of a line waiting situation, instead of going over time intervals, we're going in a pothole situation looking at space intervals, space, and the roads in this case. So we started out imagining that we went out and actually counted how many potholes were in the road for every 100 mile time span, and we generated our data here using a random number generator, which can be found in the data and the data analysis. But it's not just simply random, it's random in accordance with the Poisson distribution and the mean being 20. In real life, we wouldn't really know the mean would be 20, but we would be counting the potholes and then possibly analyzing the data. When we then did analyze the data, we then grouped the data together and said, okay, how many times in 100 miles were there 12 potholes and there were four of them? We did 500 mile tests, right? How many times were there 13 potholes in our 500 tests? There were 17 of them. And then based on this data, we created our graph over here. We also took a look at the percent of each to the total, which could have a graph similar graph this way. We then calculated the mean of our data sets. So the average number of potholes was 20.14. That's pretty close to the variance, which gives us an indication that it might be a Poisson distribution. So now let's do an actual Poisson distribution, which will be a more exact curve now. So now we're going to say, let's see how close it lines up to an exact Poisson curve. I'm going to say this equals the mean. I'm just going to pick up the same mean. And I'm going to copy that down, putting my cursor on it, copy it down per miles. I'm just copying my data over so we can use that same data to be able to see it here. So I'm going to say this equals the 20 and this equals the 100 so that we just have that same data over here. But it's tying into our other data set. So if I change that other data set, this data set will change automatically. So now let's let's do our X number of potholes. So X, by the way, X, X is equal to number of potholes in the 100 mile in 100 mile span, let's say. And so I'm going to make let's make w a little bit smaller. I'm going to control scroll in a bit. All right. And so then we're going to say this is going to be P of X, which is going to be the Poisson. In this case, we're going to say what's the likelihood that we have that number of potholes. I'm going to make this a header format by going to the home tab font group black white center. And that should do it. And then I'm going to go from zero one to I'm going to bring it up to 100 again, zero potholes, one pothole, two potholes, select in those three, putting my cursor on the fill handle, bringing it down to 100. Now it could go up to like infinity in theory. But in practice, you would think that if you had 100 potholes in 100 miles, you know, you're getting up to a lot of potholes. So if you're around 20 in the mean, you would think it'd be very unlikely that you're going to have a scenario of over 100 potholes. Right. So then let's do our Poisson calculation. We're going to say this equals Poisson dot dist. And we're going to pick up the X, which in this case is going to be zero, comma, the mean is 20. I want to make that an absolute value. So I'm going to select F4 and the keyboard. So when I copy it down, that 20 will not move down dollar sign before the V dollar sign before the one comma, it's not going to be a cumulative. So therefore not true, but rather false, because we just want the occurrences of zero only, not everything up to zero, even though zero is the first one here. So we want to say false, or we could put a zero for false. Closing it up, zero is easier to do, I think, if you get used to it. But you could type in false, if you want, I always misspell false, which is another reason. And then, and then, you know, the whole classroom laughs at me, because I can't spell false, even though I do, you know, true false, whatever. Any case, home tab, and then number, let's percentify this thing, add some decimals. So there we have it. So if I scroll on down, and if we total this thing up total, it should add up to 100. So let's do, let's do it this way. Alt equals. I have to click off and on back it again. Alt equals. There's the sum function automatically populated comes out to one, or if I number group percentify it 100%. Okay, doke. So there, there we have that. Let's go ahead and make this into a graph and possibly graph it to see how closely it matches up to our data on the right. So let's do that. I'm going to select, let's just select this data. And I'll select this entire thing. Not the total though, not the tote. And then scroll back up. I'm going to go to the insert tab. Let's go to the charts group and insert a chart. And so there we have it. Nice smooth curve. So then I can go to my data up top. And I want to make sure that this data on the right is picking up my numbers. I don't want it to just make up, just make up your own thing. No, Excel. These are the numbers you use. You use my numbers. That's how you do it. Don't just make up stuff. And then we're going to say, okay. And then let's add also comparing that to the data that we actually counted. So I'm going to add another one here. And I'm going to say, what's that data? It's coming from and I want to pick up the percent ones. And then the data I'm going to delete this and hit this little thingy and control shift down and then control or shift up so I don't pick up the 100. And so there we have it. Okay. And okay, I think that should do it. So let's go back on over and check it out. So there's our data. So you can see it's not a perfect lineup, but you can see it's fairly well approximated, right? And let's do it again with a line graph. You might want a plus and a legend down here. We should probably label our graphs better, but I'm going to keep it at that. We could also scroll down and I could make this. Well, let's keep it down to 100. Let's do another one. I'm going to do this again with a line graph this time. So let's select this whole thing, alter base another time. And let's just bring it down to like something a little bit less. Let's bring it down, I don't know, to 70. Let's say 70. And then I'm going to go into insert charts. Let's do this one this time lines this time. Boom. Let's do that one. So I'll bring it down. Let's format this thing. Let's we're in the chart design data. So I'm going to say the other data here. I don't want you making up your own numbers. So do these are the numbers you want down to 70. That's that's what you do. That's what you do. You hear me. All right. And then I'm going to add another data set over here, representing our other data over here, total percent. And let's pick up this is going to be equal to down to 70 again. I'm just going to go down to 70. And boom. Okay, I think that's good. And okay, let's check it out. Check it out, man. So there we have it. So you can see it's not a perfect, but it lines up pretty well. And so now we're thinking so we had this one that kind of kind of outlined in our data set was kind of interesting. But it lines up pretty well. So you would think that we'd have some predictive power using the Poisson distribution, which would be easier to make predictions into the future than trying to extrapolate out some just random, random, you know, set of set of set of conditions. Otherwise, you know, it's going to be a lot more complicated for us to try to figure out how to extrapolate out just a random set of items as opposed to an actual curve that we can plot into a formula. Right. So, and then so that is going to be that now we could also ask questions, such as, you know, what if let's make Z a little bit smaller. What if we wanted to see how many potholes are from pot. So we wanted to say like zero to five. So let's say equal to and including five potholes. So now I'm including five, that's always going to be one of the sticking points. So in this particular one, you could sum that up equals the sum of our data here. And say, okay, boom, boom, boom, boom, boom. And then make that a percent home tab numbers percentify. And so you're down to point 007, right, so that you get between zero and five potholes from in 100 mile span. I can also do that with the cumulative. So if I didn't plot this data, I could say equals poisson dot dist. And then I could take my x value is going to be, I'm going to say five, it's going up to and including. If it was not including five, I would have to go to up to four, right, because if it was up to but not including, so you've got to be careful if you're working book problems with this. And then the mean comma the mean. And notice I picked up a mean here of 20. Notice the mean that we actually came out to with we'll just keep this for now and then I'm going to say comma. And then this time I want it to be cumulative so that it picks up to the up to that point, not the not probability of mass function. Therefore, we're going to put a one here and close it up and add some percents. And there we have it. Now notice when I generated this data, we generated it based on this 20, which is a little different than the than the actual data we came with our test. In other words, if I change this, I could change this number to this 20.14, because just which is pretty close to the same. Because what happened here is we used the 20 to generate the random numbers. And then when I created the random numbers, the mean of the random numbers is actually 20.14. So if we imagined doing the actual experiment, it would be 20.14 would be our mean that we would probably then want to use in our, you know, poisson distribution when trying to extrapolate forward. So in any case, there is that and then and then you could have questions like, I mean, it's likely that you can have a question like, well, what if it's going to be, you know, you're probably going to say questions likely are going to be what's the likelihood that you're going to have, you know, up to 12 potholes, but it's but you might have questions like, well, what if we had between, I'm going to say, equal to and including seven to, you know, 14 potholes. Now, again, when I say seven to 14, you've got to be kind of careful and say, well, what do you mean by seven? Do you mean it's including seven? Or do you mean you're going between seven and 14? Most of the time when people talk in just common language, they're saying what's the likelihood that you will be including seven through 14. But book problems, you got to be quite careful. If they're saying between seven and 14, but not equaling seven and 14, then you got to make sure you're picking up the space between. Now, how would you do that? Well, if I plotted it out, I can just take the sum here from seven to 14, right, seven to 14, and then percentify that add some decimals. If I'm using my cumulative, then I've got to take the cumulative up to the top point, which in this case is going to be 14. If I'm including the 14, then I'll go up to 14. If it's not including the 14, then I have to go up to 13. That's why you have to be careful. And then I subtract out everything that I don't want to include, which is up to seven. So I want to subtract everything out that I'm not including. So that means I'm going to go up to six because seven is equal. So it'll look like this. We'd say this equals Poisson. I forgot what we're doing in a second here. Poisson.dist, x is going to be the higher one, 14, because I'm including the 14, comma. The mean is that 20.14, and then comma, it needs to be cumulative or one. That'll sum everything up. You can imagine basically up to the 14 closing it up minus Poisson. And then I want to subtract everything up to but not including the seven because I got to subtract because I don't want to take out the seven bit. So I've got to go one down. So I'm going to take everything out down to six, comma. The mean is 20.14, comma, and it's going to be cumulative again, or one close it up and enter. And so there we have it. So I'm going to say boom. So notice when you're working like book problems, more likely you're going to have these kind of calculations in practice. It's quite nice, of course, to actually plot out, you know, the whole thing here. And then that gives you a more pictorial view of what exactly is going on, which in practice I think that would be the way to go. If you're trying to explain to somebody what is actually happening here, it's probably easier to do if you plot the whole thing out and say, okay, this is what we're doing instead of just entering the Poisson distribution like this and just saying, well, here's the prediction based on Poisson, right? Because that's not going to be too intuitively understandable to a lot of people that don't know what you're talking about. Any case, let's make this whole thing bracketed. Let's put some brackets around this thing and format it. Let's do some formatting here. Oh, by the way, we had this data. The other thing we might do is see how close this data lines up to what we got. So let's make one more right click. I'm going to insert one. Oh, not delete. Right click insert. And let's just make this is the difference. So let's just see this is going to be equal to what we got minus, when we counted the potholes, minus, hold on, did I pick up the right one? I'm getting a little tired, a little distracted here. Focus. What we got minus this and then I'm going to copy that down. And so there's our differences. That's another way you can kind of look at it and say, okay, how close is the Poisson versus the data that we calculated on our testing. All right, let's go ahead and format some of this. So we're going to say there's that. I'm going to select this whole thing, control shift down, make it blue and bordered home tab, border it, drop down on the bucket. I'm going to make it blue. If you don't have that blue, it's in the more colors standard blue. You don't have to make it blue, but that's what I do. And then control shift down and then order blue. The blue is just a happier color than the green for me. Green reminds me of the days of the spreadsheets where I had terrible handwriting and and I couldn't even read my own writing, but I tried so hard with my 10 key before the time in the in the dark ages before the before the Excel times. There was Excel, but people still insisted on on forcing me to do like a 10 key and the spreadsheet. And it was it was cruel. I think that was the point, honestly. It was supposed to be cruel and unusual punishment and it achieved its objective. Okay, so let's do a spell check on it. Maybe a spell check. That'd be nice. Can review spell checky potholes potholes. All right, there we have it.