 Statistics and Excel, Binomial Distribution, Formula and Chart. Got data? Let's get stuck into it with Statistics and Excel. You're not required to, but if you have access to OneNote, we're in the icon left-hand side. OneNote Presentation, 1556, Binomial Distribution, Formula and Chart tab. We're also uploading transcripts to OneNote so you can go to the View tab, Immersive Reader Tool, change the language if you so choose. Be able to either read or listen to the transcript in multiple languages using the timestamps to tie in to the video presentation. First, a word from our sponsor. 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. One note desktop version here. In prior presentations, we've been thinking about how we can represent our data sets both using mathematical calculations like the mean or average, the quartiles, the median, and pictorially using tools such as the box and whiskers and histogram. The histogram being the primary tool we visualize when thinking about the spread of data and we're able to describe the spread of data on a histogram using terms such as it's skewed to the left or it's skewed to the right. We're now thinking about curves that have a formula related to them which in some cases might be able to approximate the data sets that we are looking at in real life. And if that's the case, then we can have more predictive power oftentimes using these formulas that represent a smooth curve or a line. We're now looking at a binomial distribution. In prior presentations, we looked at different families such as the uniform and distribution and the Poisson distribution. Now we're looking at a binomial distribution which oftentimes comes up quite often. There's a lot of scenarios actually where a binomial distribution could come up in practice such as in business scenarios. So let's look at the conditions of a binomial distribution. The binomial distribution describes the behavior of a count variable X if the following conditions apply. Number one, the number of observations in is fixed. So we have some observations we're going to be looking at. Those observations are going to be fixed. A common example is like a sales call type of situation when we're looking at business examples at least. So in a sales call situation, for example, you're going to have a fixed number of calls and each call that you're going to be making is going to have either one of two outcomes, a success, a sale, or a non-success, a non-sale. So I'll use that as my example as we kind of think about these conditions. Number two, each observation is independent. So in other words, when we're looking at our sales calls observations to see whether or not we made a sale or didn't make a sale in each of the sale call, they are independent. One sale that we made on one sale call has no impact on the second sale and whether or not we get a sale on the second call. And if you look at this and think of it in terms of cards with a statistics situation, for example, if you draw one card from the deck, then you've kind of changed the odds when you draw the second card from the deck if you don't put that first card back because now you're talking about 51 cards instead of 52 cards, for example. So if you put the card back, now you have kind of an independent. You reshuffle the deck and you're back to a randomness of 52 cards in the deck. Number three, each observation represents one of two outcomes, success or failure. So anytime there's kind of this binomial to type of outcome situation, then we want to be able to say, can I define whatever I'm looking at as either a success or a fail type of situation. So we're not looking at situations where there's gradients of success or sales, such as I had a sales call that got me a $500 sale versus a $100 sale, or something like that. We're saying, did I make a sale or did I not make a sale? Success being defined, in this case, if we made the sale, non-success if we did not. If you're talking about a coin flipping situation, then we only have heads or tails. So the question is, in a coin flipping situation, the success would be defined as either heads or tails, whichever you want to choose, non-success would be the other. Four, the probability of, quote, success, which is going to be represented by P, is the same for each outcome. So when you talk about a coin flipping situation, then if it's a fair coin, the probability of success, 50%, each coin flip, if you're talking about a sales call situation or a coin that is not fair, for example, then the sales call, you're going to have the probability of success for each call is usually much lower, especially if you're cold calling for sales. You might only have like a 10% or even lower probability of making a sale on any particular call, but that 10% we're imagining would be constant for each of the calls. So if these conditions are met, then you could have a binomial type of distribution and we can use this equation. We're not going to go into the equation in too much detail here because I don't want to be too intimidated by the equation because the idea would be that once the equation has been figured out to give us the curve of a binomial distribution, then we can apply that if we find that being applicable in our actual real-life situation, then we can apply that using our Excel functions and our Excel graphs. And if you wanted to type this in, of course, you can go to the insert and you can go into the equation and then you can make an ink equation we've seen in prior presentation. So I won't do the whole thing again here, but just know any Microsoft product, you can kind of type in a mathematical equation this way and that way you can represent that equation in Excel. Or any Microsoft product. So it's a kind of a nice tool to have. So let's go on over and approximate some data. So we're going to have the number is going to be in and then we've got P is going to be the percent of likelihood. P is the same for each outcome, which is the probability of success for each outcome. So let's imagine what we want to do now is plot out the binomial distribution and make a graph from it and see how the graph changes if we change the variables such as N and P. As we do this, it might be useful to envision a scenario. So let's imagine that we have that sales call scenario where N represents the number of calls and P represents the probability of success. Success in this case being that we made a sale on the sales call failure being that we don't have a sale on the sales call. So if I was to plot this out, we're going to say that X is on the left. So X is going to be zero through five. Notice that this sequence that we're putting into Excel, I could put a zero and a one and then use the fill handle to copy it down to five or just type in five. But if we use this sequence function, we could say equals sequence and then the number of rows is going to be this number five plus one because I actually want to add a zero and then comma, comma to the starting point of zero. The reason this sequence function is useful is because sometimes it's faster if you have a whole lot of columns and also you can change the number of rows automatically now by changing this N. So if I change this N, this will change automatically. So that's kind of a useful tool sometimes to use within Excel. The P of X is going to be our equation for the binomial distribution which is binom.dist.range. Now with the binomial distribution, it's got this newer function than we had with the Poisson distribution we talked about before. So you can actually, there's a binom.dist and there's a binom.dist.range. The .range is designed to have more flexibility and it's also could give you like the spill array kind of function. But the other function is still useful and it might, because it might be more similar to what you're used to with regards to having a cumulative component, the last argument being cumulative versus not cumulative. So we'll look at both of them in a little bit more in Excel and in future presentations here, but we're using the latest and greatest one for this first example which is binom.dist.range. So then we have the trials. So the trials are going to be the 5 here and then the probability is 10% per trial and then the numbers that we're going to have is going to be this range. What this hashtag is, it's going to spill out here. So the likelihood then of having zero successes is going to be 59.05. The likelihood of having one success out of the five is 32.81. Two successes, 7.29 successes and so on and so forth. It's quite unlikely that we're going to have four successes because each sales call that we have, we only have a 10% chance of success and getting four out of five would be fairly unlikely in that case. Now, we want to just get an idea of if we were to graph this now. So now we've got in one through five and we've got the percent likelihood going up to around 60 for zero, getting zero out of five, one out of five, two out of five and so on. So you can see you get a curve that looks something like this and now we just want to get an idea of feel for the curve as it changes when we change these variables of N and P. So if I was to change these variables to 10 versus five, notice now my X column in Excel because I used this cool sequence formula will actually populate automatically down to 10 and because I used a spill array, the fancy spill array formula for the binome.disk.range, this will spill automatically. So that's what's kind of nice about those formulas and you can practice that in our Excel presentation because it'll populate this automatically. Now on the chart, it will actually populate automatically as well, but you might have to copy down the chart data range to make sure it picks up the full data range down to 10. From the binome.disk standpoint, you can see it looks like this up top because it's stopping at zero, right? And now when you're moving it to the right, it's looking, it's going to approximate more of a bell shaped curve. So now that I've increased that. So if I do it again, I increased N to 20, P still at 10%. Now we can see it's moving to the right and it's looking more bell shaped still being kind of cut off on that left side. And then if I increase it again N to be 50, now it's out here kind of in the middle and you've got something that looks fairly bell shaped. That's going to be the general trend when we look at these curves. If I then plot this one back to five, but I increased P from I believe 10 what it was before to 20, now we've got it being cut off again over here and it looks a little less kind of bell shaped. If I increase it from P of 40, probability of 40, the probability of 240%, then I get it kind of moves to the right and you can see it's getting more of a bell shaped type of look here. And if we analyze this, of course, if P is 40%, then the likelihood that we get zero calls, zero successes out of five is now 7.78. The likelihood that we get one success is 25.92. The likelihood that we get two successes 34.56. If I'm thinking about the likelihood that I get from zero to two successes, I would add these up, right, 34.56, 25.92, plus 7.78 if I added that right, 68.26. I'm not totally sure I added it right, but that's the idea. We'll talk more about that in future presentations right now. We're just kind of focused on what the curve looks like. So if I then, if I move this up to 50%, now you've got something, again, looks somewhat more bell shaped. So as we increase N or P, as we increase them, then we tend to get something that starts to look more bell shaped. That's the general trend that you will see. In future presentations, we'll get more into detail with some actual kind of more scenario based problems.