 Statistics and Excel. Binomial distribution coin flip random number generation. Got data? Let's get stuck into it with statistics and 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. You're not required to, but if you have access to one note, we're in the icon. Left hand side, one note presentation, 1560 binomial distribution coin flip random number generation tab. We've also been uploading our transcripts so that you can go to the view tab, immersive reader tool, change the language if you so choose, be able to read or listen to the transcripts in multiple different languages, and use the timestamps to tie into the video presentation. In prior presentations, we've been thinking about how we can represent different data sets, both with mathematical tools, such as using calculations of the average or mean, the median, quartiles, and with pictorial representations like the box and whiskers, and histogram, the histogram being the primary tool we use to envision the spread of data, and we can describe the spread of data on a histogram using terms like it's skewed to the left, it's skewed to the right. We're now looking at smooth curves that have formulas related to them that sometimes can represent data in real life, at least approximating that data set, and if we can do that, then the curves give us more predictive power into the future because we have a mathematical formula related to them. Prior presentations, we looked at the uniform distribution, Poisson distribution. We're now looking at the binomial distributions, which you will recall are those that we need to break out into whatever we're looking at have basically two outcomes related to them that we can define as basically success or failure. Let's look at a coin flip type of scenario to apply our concepts here. So we're going to say that the probability of a coin flip is going to be 50-50 because we're going to imagine it's a fair coin flip. Now let's just imagine if we were to plot out our binomial distribution with just x as zero and then we'll increase the number of flips and we'll see because that will give us more of an intuitive feel of what is going on. So if we just simply have x being zero, meaning we don't actually flip the coin at all, then that means that the likelihood of us getting zero successes and if we define success in terms of a coin flip, we can define it as either heads being a success or sales being a success and the other being a fail, we will define it here as heads being a success. Well, if there's no flips then of course then there's going to be 100% chance that we have no successes. We're using the binomial.dist.range and we'll talk more about the binomial.dist.range versus the binomial.dist in future presentations but the range is the latest and most flexible one of the two binomial functions. So we have the trials and the trials are going to be zero. In this case the probability per trial 50% and then the numbers we're looking at are going to be then the zero. So let's take a look at a scenario this time where we have the probability of success is still 50% because it's a coin flip situation but we flip the coin one time. Well if we only flip the coin one time and we're defining success as heads, then as you would expect the likelihood of having zero heads out of two flips is 50%. The likelihood of having one success out of two flips is 50%. So that means and again we're doing this with our binom.dist.range and this is a spill array. So we just once again took the trials, the probability of the 50% and then the numbers being this range and it spilled out these two ranges. We'll do that. We have done this in Excel if you want to check it out in Excel as well. Let's add it to two. So now you've got the number of rounds is two. So if we have two numbers of rounds, what's the probability that we get zero successes, zero heads, out of two rounds or two flips? 25%. What's the likelihood that I get one success defined as a heads out of two flips? We have the 50% and then what's the likelihood that we get two successes out of two flips, two heads? In other words, 25%. You'll notice that the total adds up in all these cases to 100% which is kind of our check figure. What if we bring it up to three then? So now we've got the likelihood that we get zero successes out of three flips, zero heads, that is 12.5% likelihood that we get one success out of three flips, 37.5 likelihood that we get two successes, 37.5, three successes, three heads, 12.5. And if we can also ask questions of course, what's the likelihood that I get either zero or one successes? And that would be a cumulative type concept, 12.5 plus 37.5. We're coming to the 50 if I added those correctly. Let's do one more. If we bring it up to four rounds, each individual flip being at 50%, we have the 6.25% that we get in zero successes, zero heads. We get 25% that we get one success, exactly one success, one heads out of four. We have the 37.5% that we get two heads out of four. We have the 25% that we get the three heads out of four and then the 6.25 that we get four successes or heads out of four. Now we did this with a coin flipping scenario, but you can imagine just like we talked about in the prior presentation, other scenarios, many different scenarios actually, there's a lot of applicability here. If you can break out the outcomes to success or fail, we talked about sales calls in a prior presentation. Now let's think about a situation where the probability of success is the 50% and the number of rounds is 12. So we have a coin flipping situation, success is heads. We're now flipping it 12 times. So now we have zero, if we have zero out of 12 successes, that's very unlikely that we don't flip any heads, one head or a success out of 12.29 and so on and so forth. If we were to plot this out, then we get something that looks like this and it looks somewhat like a bell shaped curve in this case because as we looked at in prior presentations, the percent of success is at that 50%. And this looks kind of like what you would expect in a coin flipping scenario, right? Because you're saying, okay, that means if I flipped it 12 times, you would kind of think that six, the data would be formatting around six and basically is tapering off given those conditions. And if I look down here, we can do a similar kind of chart with a line type of chart as opposed to with the bar chart or a histogram type of chart. All right, now let's actually approximate this. Let's say that we simulate this situation in Excel. We have some tools instead of using just the equals random function that we did when we did a normal, like a coin flip in prior presentations to represent a heads or a tails. We're going to represent the outcomes for the sets of flips that we have. So we're going to say that we flip them 12 times. So when we run these tests, I'm going to say that we're flipping the coin 12 times each round and see how many heads we're defining as successes that we get every 12 times we flip. So in the first 12 times, we flip the coin, we've got five successes. And the second 12 times, we flip the coin, seven successes or seven heads out of 12. The third time, seven successes out of 12. Fourth time, four successes or heads out of 12 and so on. So in the past, you know, the teachers union and the colleges, you know, they had people that just sat in there and flipped coins all day and it was like a union job and whatnot. But even they couldn't even hold on to that, right? They moved over to write for Hollywood and whatnot. But now we have this number generator and you could check out how to do that in Excel to kind of simulate these models. So now we're going to imagine a real-life scenario or in practice, we actually did this test and see how closely it matches what we did theoretically with the binome.dist, which would be the smooth curve. So if we break this out into our bends, we're going to say, all right, how many times are we going to get zero? How many times are we going to get one success out of 12 flips? How many times did we get two successes out of 12 flips? We're using our frequency, which is taking our outcomes here of 12 flip outcomes. I can't remember exactly how many. We did it a thousand times, a thousand 12-time flips. And we're saying out of a thousand 12-time flips, how many times did we get zero out of 12? Two. Out of a thousand 12-time flips, we never got exactly one success or a head. We got how many times did we get two successes or heads out of 12 when we flipped it a thousand times, 13 times, and so on. Now again, you might ask, why don't I use the COUNTIF function? I could do that. I could say, Excel, I would like you to count all the items in this column if there's a zero. However, because we use this random number generator, sometimes it kind of throws out that COUNTIF or throws it off. Therefore, this frequency usually picks up all the numbers. It's an array function, so it's kind of a fancier function. So if you want to check that out in Excel, we will do it there. But if I sum up the columns, it should come out to a thousand. Why? Because we ran a thousand 12 flip tests. So that's our check figure. Then we can also look at the percent of the total. So for example, how many times, what's the percent of times that we got four successes or heads out of the thousand trials that we did? Well, we have 119 divided by a thousand, and that's going to give us the 11.9. Now remember that if we actually run the test, I can make a graph about the frequency, and I can make a similar graph about the percentages. When I look at the Poisson distribution data that we have over here, I didn't actually run the tests. What we got instead is simply the frequency. So over here, if I got the frequency, I would predict then the number of flips if I ran a thousand tests by doing it this way. I would say, okay, how many times would I get a five if I ran the test a thousand times, one thousand times the point 1934 or so on. And over here, what we did is we actually ran the test and got the frequency, and then we divided by the total to get the percent of the total that outcome to that five percent. If we compare the two, I'm subtracting this number from what we got over here to see how close it lines up to a binomial distribution. And we can see it's not perfect, but it looks pretty close to the binomial distribution. So as we would basically expect, and if it is, if it was a real-life situation like phone calls or something like that, then we would be likely to be able to use the binomial formula to make predictions about outcomes on sales calls. So if I was doing my own sales calls, I can kind of figure out what would be expected and see if my data is lining up to that or if you're supervising someone, you might have some expectations that are designed in a similar fashion. So if I graph these two things together, meaning this is the percent of the total of the actual data in blue and this is the actual smooth curve from the function that we graphed, or basically we graphed this data that we generated from the function or formula, we could see they're pretty smooth. So we have a similar situation that we've seen in prior presentations. We're saying we could say whatever scenario we're looking at, does it fit the conditions to be a binomial distribution? If it does, then maybe we can use the function. If we actually plot data outcome and say does the data outcome look like it ties out to what we would get from using the binomial distribution, again, good evidence that we could use the binomial distribution. If we plot the data that we actually got versus what we would get with the smooth binomial distribution curve and those two things line up, again, we would say, okay, now maybe I'm convinced that I can use the binomial distribution curve to make predictions about whatever we're talking about, in this case coin flips and future presentations, we'll talk about the sales calls and whatever we're talking about might be able to be predicted with this smooth curve or formula.