 Statistics and Excel. Binomial distribution formula and chart. Get ready, take it 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 CPA six pack shirts, a must have for any pool or beach time. Mixing money with muscle, always sure to attract attention. Yeah, even if you're not a CPA, you need this shirt so you can like pull in that iconic CPA six pack stomach muscle vibe, man. You know, that CPA six pack, everyone envisions in their mind when they think CPA. Yeah, as a CPA, I actually and unusually don't have tremendous abs. However, I was blessed with a whole lot of belly hair. Yeah, allowing me to sculpt the hair into a nice CPA six pack like shape, which is highly attractive. Yeah, maybe the shirt will help you generate some belly hair too. And if it does, make sure to let me know. Maybe I'll try wearing it on my head. And yes, I know six pack isn't spelled right, but three letters is more efficient than four. So I trimmed it down a bit. Okay, it's an improvement. 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'll basically build this from a blank worksheet. If you do have access, three tabs down below example, practice a blank example, in essence, answer key, practice tab having pre-formatted cells. Maybe you can get down to the heart of the practice problem, blank tab, blank worksheet, so we can practice formatting the cells within Excel as we work through the practice problem. Let's look at the example tab to get an idea of where we will be going. We're going to be talking about a binomial distribution, thinking about the conditions that need to be met for it to be applicable. Let's look at whether the formula and as we do so, trying not to be too intimidated by it because we're then going to think about the binomial distribution in more of an intuitive way and use functions within Excel such as the binom.disk, binom.disk.range, and we'll then create a chart that we can then adjust as we adjust our conditions up top. So let's go to the blank tab and get started with it. So first we're going to put down our conditions. I'm just going to copy and paste the conditions. You can type them in there if you so choose. So we've got the binomial distribution describes the behavior of a count variable X if the following conditions apply. So number one, the number of observations in is fixed. Number two, each observation is independent. Number three, each observation represents one of two outcomes. That's why it's by to binomial success or failure. So we have to be able to represent the outcomes as either, you know, success or failure. Number four, the probability of success P is the same for each outcome. So we have the same probability each time we do the activity that we have the same probability of success. All right, let's now create the formula. I'm actually going to draw it in here so we can practice doing our inserting of the formula. I don't want to get too hung up on the formula, however, as we do so because it is an intimidating looking formula. And then we'll get into the more intuitive stuff to build the actual chart and we'll talk about in a more intuitive way what is actually happening in future presentations as well. So let's go to the insert tab symbols. I'm going to put in an equation. So there's our equation. I'm just going to draw it in there with the ink equation just so we can practice putting this in place. I think this is just a really nice tool so that we can get the equation in Excel. So we have both the formulas in there as well as the functions in Excel. I'm going to draw it in here. I'm just doing this with my mouse. So if you had an actual pad, it would be even easier. I'm going to say this is P of X and it's drawing it up top. So it looks like it's picking up everything so far equals. And then up top I'm going to put an N and then we have a factorial. That's the explanation mark. We talked in my end so you didn't like the N or the explanation. So I'm just going to erase that whole thing and say I'm just going to try that again. So I'm going to say N and then exclamation mark for the factorial. We talked about factorial in a prior presentation with a Poisson distribution. So I'm going to then say this is going to be over X factorial. That's what the exclamation is times N minus X and take that factorial. And then we're going to take that whole thing. I'm going to represent multiplication with an asterisk. So I'm going to type in an asterisk like that and then I'm going to put P to the X. And then we're going to say that times I'll put another asterisk. And so this is getting intimidating. I know but we're just really practicing typing in our formula here with this cool tool. One minus P and then close up the brackets. And we're going to take this to the N minus X and close that up. And so it didn't like my N here. So I might be able to circle that and say why don't you give me another option N, something like that. That's the one I want. And so there we have it. So N factorial over X factorial times N minus X factorial times P to the X times 1 minus P to the N minus X. So again, intimidating looking formula. And once we look at it more intuitively when we come back to this formula, you'll probably be able to understand it a little bit better. But I just want to first put the formula out there. Home tab font group. I'm going to make it orange so I don't see those lines behind it. I'll increase the size of it a bit. And so we could see that a little bit more clearly. All right, let's get an intuitive sense of this by building up a table that we can then adjust with our binomial function. So I'm going to go over here. I'm going to say in column L, let's say that we're going to have N and P. What we need is N and the probability P. Now I'm actually going to format all the cells on the worksheet, which is usually what I typically do first. Select in the entire worksheet with the triangle, right click, formatting the cells, currency, negative numbers, bracketed and red. No dollar sign. I'm going to remove the decimals and then add them as needed and OK. I'm also going to go to the home tab, number group and make everything emboldened. All right, so let's start off and say that we're going to have NB5. And the probability, let's say is 0.02 or 2%. So I'm going to go up top and say home tab, number group and percent. Now note that oftentimes the first thing you might think of if there's just two outcomes of something might be like a coin flip situation. But you can also think of many other situations in like a business format as well, such as what's the likelihood if I'm going to call someone for a sales call or something like that. What's the likelihood of a success? And it might be something other than 50%. You would know the percent every time you call someone. So there's actually many different areas where this binomial distribution could be applicable and we'll take a look at more examples in future presentations. For now, let's plot out a graph with it. So I'm going to make L a little skinnier, M a little skinnier, and then I'll make N a little skinnier. And then we're going to graph this out. I'm going to say this is going to be X and this is going to be P of X. P of X, those are our headers. I'm going to select these two up top, make them into a header by going to the home tab, alignment, center it, and then let's make it black and white as is typically what we do with our headers. So what I'd like to do when we have our graph, I'd like to make it adjustable based on how many X we have here. So I would like it to be able to change as we change the number values. And I'm going to number these in the same number as how many ends we have up top. So one way I could do this is it with a sequence function. So we're going to say this equals a sequence, C, Q, quints, and then tab. So the number that we want is we're going to have this five plus one because I want to have a zero up top. So that's going to be how many numbers that we want to be going down here. And then comma, and I don't need any columns. So I could put a zero here or just two commas. And then the starting point, I wanted to start at zero. And so then close it up and enter. So now this is a spill array. So instead of me doing this, which I often do zero, one, and then copying it down, we then use the spill array to do it. And the benefit of that is that if I change this number now to like four, it adjusted all automatically, which is great. So now I have that adjusting. So now I'm going to use my binom.dist.range function. So this is going to be equal to binom.dist. Now notice that it has this.dist here and that one is not the latest and greatest thing, right? We're going to use this one.dist.range that actually has more flexibility than the.dist. However, you might still use the.dist sometimes as well. The.dist is kind of more similar to the poisson.dist. Whereas with the binomial, they have this range, which gives us a little bit more flexibility, which we'll talk about a bit more in future presentations. But for now, I want to use the range because I would like it to have that flexibility along with this column here. So I'm going to say the trials that we have are going to be some I'm going by the by the chart down here. The trials are going to be five comma the probability is going to be two. So the probability of success to and then the numbers. This is where I'm going to put the range in place are going to be from here control shift down to there. I'm also going to make these absolute M1 and M2 because I don't want them to move. So I'm going to say F4 absolute reference and then go in here and F4 dollar sign before the letter and the number. And then this is the range. It should spill down when I hit enter and it does it spills down like that, which is nice. I'm going to select the entire column now and make it a percent. So I'm going to go to the home tab and then numbers group percentify the entire column. Add a couple of decimals and so so there we have it. Okay, so now I'm going to I'm going to make a chart out of this and I want to show how the chart can basically be flexed. When I change these numbers and we'll kind of see what happens intuitively with a chart. So if I select, let's just select this column over here and I'm going to go into the insert. We're going to go into the charts, add a chart and boom, there's our chart. And we'll put this on the right hand side and I'll do some adjustments to it like normal. I will go up top to the data and this data looks good. But I want to change this data to make sure it's picking up the numbers that we want over here one to five. That will allow it to adjust as I change this number. So I'm going to say, okay, okay, and then boom. So now we've got this plotted and I can call it this is my binomial distribution chart. Right. Hopefully I spelled that right, possibly not, but that's what it is. So now we have a fairly flexible chart, although we have to be careful if we're going to be adjusting and so let's first play with in and see what happens to our chart. So if I take it from five, for example, to 10, then if I click on the chart, notice it's still not doing the column down here. So I'm going to pull the column down so that it goes down to 10. And so there's the adjustment. If I bring this up to say 40, let's say, and then I'm going to. So here's the chart here. I'm going to bring the bottom column down to 40, selecting the chart and I'll bring this down to 40. So see if I can go down to 40. And so there we have that. It's a little bit tedious to adjust the end, but there we have it. And let's let's adjust it all the way up to like 150. And then if I bring my, well, that's not 150. 150. And then I bring my chart down to 150. To here and this side down to here all the way down. All right. So then you can see that as I increase it, it gets to be, you know, more of a bell shape as I increase in. If I then bring it back down, let's bring it back down to five. And so now now my data is pulling all this information in all the way down there. So I'm going to go back down and pull it up to five. It's easier to be adjusting the other range. So I'm going to pull this back up and let's pull it all the way back up to five. And then let's pull this back up and pull that back up to here. And so if you, if you keep this, this at five, you can also kind of adjust that and see what happens within that range, right? 10 to here and, you know, 20 and so on within this range and 50 and 150 and so on. So let's bring this down to five again. And then if I adjust the P increasing P, let's bring this up to to 10. Let's bring it up to 20 and let's bring it up to 30 and then 50. And at 50, you can see it's getting more bell shaped. You can have it basically a bell shape at 50. So that's kind of the way the binomial will kind of look. Let's just think about it intuitively. Let's first think about a coin flip type of situation and just think about our graph. If I adjust this down to zero, for example, and I don't flip a coin at all. And then I define success from the coin flip because there's two things that can happen, heads or tails. If I define success as a heads or I can define it as a tails, well, let's define it as a heads. Heads is a success. So now we have the binomial thing. We have the success on any flip of the heads, the non success being the tails. And if it's a fair coin, each flip would have the 50 50. So if I didn't flip it at all, then we're going to have 100% of likelihood, of course, that I don't have a success or a heads. If I flip the coin one time, then the likelihood that I have a success is going to be 50% and the likelihood that I have a non success tails, in this case, is going to be 50%. And if I flip the coin two times, then the likelihood that I have no successes for the two flips, no heads, is 25%. The likelihood that I have one success in the two flips is 50%. The likelihood that I have two successes, two heads in the two flips, 25%. And then it gets a little bit more complicated if you go to three, right? Now you're going to say, well, at three flips, the likelihood that I get zero successes, heads, 12.5%, the likelihood that I get just one head is 37.5, two heads, 37.5, and three heads, three successes, 12.5, and so on if I move it up to four and so on and so forth. Now, if you were to think about this as something other than coin flips, or if you were to say, for example, that the coin is not fair, meaning it's distorted in our favor. So now each flip is not 50%, but 60%, and I do this again. I can say, okay, well, what if I had zero flips at 60%. Well, I'm still not going to get any heads or successes because I didn't flip the coin. 100% non-success. If I say one flip, now I have, of course, a 60% probability that that one flip is going to be a success or head versus 40, non-success, two flips. Now I have 16% non-success, or to get zero successes, zero heads, a 48% chance to get one success, one head, 36% chance to get two heads, and then we can continue on for three heads and so on. Now there's only a 6.4% chance that I get zero successes, 28.8% chance that I get one success, 43.2% chance I get two successes and 21.63 successes. Now you can also think about this in a situation, for example, where the percent is lower because a common example is if you're cold calling people for sales calls. It might be that you have a very low success rate per call. You might not only have like 10% of the time that you actually have a success on a cold call for a sales call. Well, obviously, if you don't call anybody, then once again, 100% of non-success. And we could define success, of course, here as we now are getting a sale from the call, which would be similar to a coin flip, in which case the coin is negatively tilted towards. We have a very skewed casino that we're at or something like that, where we only have a 10% chance of success, right? Now if I say that we have one call, then 90% chance we're going to fail, 10% chance that we have a success on that one call. Two calls, 81% chance that we have no sales, and 18% chance we have one sale out of the two calls, one success out of the two, 1% chance that we have two successes, very low chance that all of them are going to be successes with only two calls. If that does happen, we're going to be quite encouraged to keep calling. But then if we have three calls, still very likely that we don't have any successes, 72.9%, 24.1% that we get one out of three success, 2.7 that we get two, and then very low that we get all three, four calls. Now we've got 65% still that we don't get any benefit. And when does that get up to at least one sale? Well, five calls, six calls, seven calls. Now at least we have a higher probability that we get some, that we get a sale, but it's still pretty high likelihood that we don't get any sales, right? And then we've got the likelihoods that we might get one sale out of the seven, two sales out of the seven, three sales out of the sevens, and so forth. If we do 10 calls, now we still have a 34.87 likelihood that we don't get any sales, 15 calls, we still have a 20%, 21% likelihood that we don't get any sales. And then 20 calls, we still could have a 12% likelihood that we don't get any sales, right? If I have 50 calls, now I have a pretty low likelihood that I don't get any sales if I'm going to get an average of 10%. So those are the kind of scenarios that we could run, and you could see how that could be a beneficial tool to use to get an idea of what are the expectations to happen in a situation like this. So there's many different situations where you end up with these binomial distributions that you can kind of analyze in this way. Let's go ahead and format. I'm going to format the worksheet like we normally do. I'm going to select all of this because I could make this longer as we did, and I'm going to make this home tab, fonts group. I'm going to hit the bucket, make it blue, standard color. If you don't have the blue, there it is. I'm going to border it, fonts group and border. So there we have that. Let's make this blue and bordered, bordered blue. And then over here, this looks good. So let's go ahead and save it. Let's do a quick spell check on it. Check the spelling. Mui B to the N, BN, good.