 Statistics and Excel Poisson Distribution Random Number Generation Example. Get ready, taking a deep breath, holding it in for 10 seconds, getting ready for 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 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. You'd probably be okay to start with a blank worksheet from here going forward as well. If you do have access to this workbook, there's three tabs down below. Example, practice blank. Example, in essence, answer key, practice tab, having pre-format of sales, so you can get right to the heart of the practice problem. Blank tab is where we started with a blank worksheet and are continuing at this point in time. So quick recap of what we have seen in the past. We've talked about the Poisson distribution. We looked at the conditions that would generally need to be met in order for the Poisson distribution to be useful. We looked at the equation, putting the equation together, trying not to be too intimidated by it because we will be using the function in Excel. We looked at some of the components of the equation so we can understand them better and how to express them in Excel. Then we plotted our Poisson distribution in a graph using the mean. This is what we need to know in order to be able to plot the graph. It's going to be represented by lambda and we used our Poisson dot dist. Now we have our graph that we can adjust by adjusting lambda or the mean, which is great. Now what we would like to do is generate some random number generation to see how closely that information will tie into what we have created here. Remember, the general idea in practice would be that we're trying to look at things that follow a certain pattern. We've noticed this pattern that has happened and then we're trying to put a curve related to the actual data so that that curve then can give us predictive power out into the future. This is basically the information that we are plotting based on the formula. Now we want to see how closely that might tie into the actual data. This is often a case where we have a line waiting type of situation for example. We're trying to see for example what's the likelihood that three people will show up in the next interval of time. Those are the types of questions. We'll get into some examples more in future presentations. For now, we'd like to say how can we actually generate a real world kind of situation where we have actual data that has a random element to it but follows the conditions of the Poisson distribution. In prior presentations, we've been using a function to represent our randomness. When we had dice rules, for example, we said equals and then we said random and between. We've been generating random numbers between random numbers. Now we're going to get a little bit more advanced because we want the random numbers to be in accordance with basically these conditions. What we want to do then, I'm going to go to the data tab and there's an analysis group over here which you may not have and will not have generally by default but you can add it in oftentimes. You have the latest version of Excel, Excel 365. I can add this in so I can then go to do that. I go to the file tab and then we're going to go to the options down below and then within the options you want to go into the add ins and then you've got this little box down below which says manage. You want this to be on the add ins and then hit the go. So now we have our add ins and I want this analysis tool pack. That's the one I want. So I'm going to pick up that analysis tool pack and then hit OK. And if you have that, then when I go into the data tab, I should have this group of analysis and I've got my data analysis up top. So I'm going to put a header over here. I'm just going to call this, this is going to be my data for lambda. I'm going to make a lambda insert symbol. I've got my lambda down here for the mean. I'm going to insert that close. I'm going to enter and then go back into it when that equals let's say 10. That's going to be the conditions I'm going to put in for the random generated numbers. Then I'm going to go to the home tab. Let's go to the alignment, wrap that. I'm going to put center around it, make it a header of black and white is my usual routine. Then I'm going to go to the cell below it and I want my random numbers to be plotted here. I'm just going to make a thousand numbers that are going to be generated using this random generation method. So then I'm going to go to the data tab. Let's go to the analysis group. Let's go to the data analysis. All right. So then what we want is you've got your options here. We want the random generation. So that is here. It's like this. It's like right in the middle. So it's kind of difficult to find it first, but right in the middle. Random number generation. Okay. And then the number of variables. I'm going to say one here. We're going to say the number of random numbers. So the number of random numbers. I'm going to put a thousand. We're going to generate a thousand random numbers. And then you've got your options for the distribution. So it's on discrete right now. I think that's the default. I'm not sure. But what we want it is to give us the numbers in accordance with a poisson. So I'm going to be picking poisson. And then the lambda, which is the mean, remember, we're going to say is that 10. It won't let me like use a formula to pick up the 10 over here. So I'm just going to, you know, we type in 10 for the mean. And then I'm not going to put a seed. I'm going to put the output range that we want. I want it to be in this workbook. So I'm going to put it in our output range. And then I'm just going to point to this cell. And I'm going to open that up again. So that's what so that's so you can see it's going to start putting all of my numbers right there. A thousand numbers starting on Z two. So this I think believe this is the number of columns. So it's one and then a thousand numbers according to the poisson distribution with a lambda of 10. And then we're going to say I want you to put the output starting on that cell. Let's do it. Okay. And boom, it generates all those numbers down to here. So this is so this is what we're going to imagine is our actual data. Right. So now we've generated our actual data in a similar way as generating data by like a coin flip by us doing the random generation of 5050 one or two that we've seen in prior presentations. So now let's plot this information using our our bends. So I want to see the results of this of this. So I'm going to make our bends and I'm going to use a frequency and then the relative frequency I'm going to call it. I'm going to see if I spelled those right going to the view tab spell check. It seems like I did. I'm shocked. Home tab. Let's go to the font group. Make it black, white. Let's center it. Let's wrap it. All right. We're wrapping. We're wrapping style. So in any case, I'm a rapper. So we're going to say that the bends are going to be from zero one to and I'm going to and I'm going to go up. Let's put it to go up to like 30 bends. Let's say I'm going to select these and go up to let's say 30. So 30 bends. Now notice when we're thinking about a poisson distribution, if you're trying to think of, for example, how many people are going to show up at a restaurant or something during rush hour or something like in a certain timeframe, it could go up to infinity. That's why that's why it's going to be skewed to the right generally. Right. So but that's obviously in a practical in practicality the odds of it being way up like, you know, the number going being like a hundred that are going to show up to the to the restaurant given the mean is going to be low. Right. So so from a practical standpoint, we're not going to go on forever. Right. We can count it up to somewhere here and see if our thousand results are being plotted within that range. So then I'm going to do my frequency. So the frequency calculation. Remember that the frequency represents the bends that we can put together in our histogram. In this case, however, we have basically the whole number. So it's not like we need ranges down here. Generally, we're just going to basically say, hey, look in this data set, the random numbers that have been generated, how many zeros are there, how many ones are there, how many twos are there. So you would think that you could be using the count if function to do that. However, sometimes when you're generating data, the numbers aren't exactly whole numbers. And therefore the bends kind of work. So the frequency distribution or the frequency formula is often a good one to use. Let me just show you the example of the two functions that you might think of. However, what I'm trying to do is see how many zeros, how many ones, how many twos are in this set of a thousand numbers. So the one function you might use is equals count if brackets. And I'm going to say this is the range. I'm going to put my cursor on Z2, hold control shift down and then control backspace to get back up. And then comma, the criteria then is that zero and then close it up and enter. And so I don't have any zeros if I copy that down. So there's our numbers. Now we can double check because I should be able to add up to a thousand here. So if I total this up and I can use the sum function, which I'm now going to start using alt equals. And that will guess that I'm summing above and enter. So it doesn't quite come out to a thousand. So either this count if didn't quite pick something up because possibly it gave me something that's not exactly a whole number possibly, or maybe there are numbers past 30, which is unlikely given the conditions that we put in place, but theoretically possible, right? So let's try the other method. I'm going to delete all of these and say, okay, I'm going to use the frequency distribution. And so let's do that one. And that should pick up because it's using a range if there's anything other than a whole number here. So I'm going to say equals frequency tab. And this is a spill or array type of function, which works quite well here. The data array, I'm going to put my cursor in Z to hold control shift down arrow and then control backspace to get back up. And then comma and the bends array. I'm going to put my cursor in AB to control shift down arrow, holding control backspace to get back up. And then that's what we need. And so I'm going to pull this down so you can see it. So there we have it. And I'll close it up and then enter. So then it spills it spills our results down. I'm going to and then if I double click on this spill, I trimmed it back a bit so that the spill goes down exactly to where I want it to go. So now it's spilling down to this cell on 32 so that we can then sum it up and total it up. So now I'm going to sum it up again, alt equals and enter. And that adds up to 1000. So I'm much more confident now that everything has been picked up because I've got my check number down below. And then I can take my relative frequency. So these are the number of times that they have showing up in our randomly numbered sample. So we're kind of running a test. This would be kind of like the real life test that has randomness to it similar to flipping a coin or rolling a dice. And I want to then think about how often these results showed up in comparison to the total to the thousand. What's the percentage of the time that they showed up because that's what we generally have here with the Poisson distribution, right? What's the percent likelihood that it's going to be 2, right? It's 0.23% that it's going to be that. So let's see our ranges. This is going to be equal to 0 over 1000 that 1000. I don't want it to move when I copy it down. So I'm going to select F4. Now this is I could I could also use the spills arrays with some of these formulas, but I still like the absolute references when I can use them. And I typically use the spills and arrays when they give me some added value, such as this frequency one that gives me added added value. Because sometimes when I put tables in place, those spills and arrays can kind of make it difficult with the table. So there's pros and cons of them. Any case, I'm going to double click on the fill handle and copy that down. So there it is. And then I'm going to go to the home tab, number and make it a percent, add some decimals to it. So there we have it. And then down below, I'm going to sum it up instead of doing it this way. I'm going to delete this and then alt equals. So it sums up. It should still come up to 100% because I compared each one of these outcomes to the total. And so now we can kind of we can kind of compare this data that we generated to what we would get from the poisson, what we got from the poisson distribution. And so this would be like us running the actual exercise and seeing what actually happened in real life, but just for a thousand rounds. And this is the poisson distribution. And we could see how closely then just like when flipping a coin, how closely our estimated predictions are to, you know, the poisson distribution. So here we had 10 versus 523 versus 10.76 versus this 1.4 versus 1.8 3.78 versus 3.36.31 versus 6.30. So it's very close. And then 9 versus 711 versus the 12 1251. Right. We can put the differences here. Let's just do the differences. I'm going to say the differences are this minus this. And I can copy that down and possibly make that home tab numbers making it a percent adding a couple of decimals. Let's format paint this over home tab clipboard format paint it to here. And so now you can see the differences between the two could sum those up. And so there we have that. So then we could plot these two. We could plot recreating this graph that we did before and then that we did with the function and then using a graph based on this data here. So what I'm going to do is I'm just going to select the data. I'm going to select this is the data that I'm going to make the second graph from. And I'm going to go up to the insert and we're going to go do a bar chart bar chart and boom. There it is. Now let's do some of our typical adjustments to this. So I'm going to go into here we're going to go into the chart designs data selection. So it picked up that that's the correct data set I want there. I'm going to add another data set adding another which I'm going to be picking up from this data set. I'm going to call it P of X. And we're going to say this is going to be equal to these numbers. I'm just going to go down to 30. So I go down to the same distance I have on my other graph. The two data sets going down to the same area. And then I also want to change my X's to make sure that I'm picking up the X's from zero to 30. Okay, so then you can see down below it's given us data that you can see they're lined up pretty close, but they're not, you know, exact on top of each other. If I hit this item and I look at I don't know if I could do my data labels. That's way too cluttered for the data labels. But you could see that, you know, if so this. So if, for example, you saw this data with the blue curve. And you would then say, okay, if I see that happening, I could say, well, maybe there's a Poisson distribution happening here. And if there is, then I can plot the Poisson distribution, right? And that gives me the predictive power because now I have a formula that can help me to kind of plan and think about what's going to happen in the future, giving the past data is the general idea. Now I can then say I can have my access titles. Let's not do that. I'm just going to do the, the legend. I need a legend down here. So they put the legend here, we can put it on the side. All right, that's fine. We could move it on the bottom or something like that. But so this is the relative frequency. In other words, this is the actual data that we ran versus the perfect curve based on the Poisson formula in orange is the general idea. So we'll take a look at a couple more examples that are kind of practical examples with this idea in future presentations.