 Statistics and Excel. Binomial distribution, manual method and Excel function using a sales call example. 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 OneNote, we're in the icon left-hand side. OneNote presentation, 1566 binomial distribution, manual and Excel function sales calls example tab. We're also uploading transcripts to OneNote so that you can go to the view tab, immersive reader tool, change the language if you so choose and be either to read or listen to the transcript in multiple languages using the time stamp to tie into the video presentations. OneNote desktop version here and prior presentations, we've been thinking about how we can represent our data sets in different ways using both mathematical calculations such as the mean or average median quartiles and so on and with pictorial representations like the box and whiskers, the histogram. The histogram being the primary tool we use to envision the spread of the data and we can talk about the spread of the data on a histogram using terms such as it's skewed to the left, it's skewed to the right. We're now looking at those smooth lines which are created with mathematical formulas which sometimes can approximate actual data sets in real life. If we have a scenario like that, then the actual formula will be great because that gives us more predictive power about whatever it is that the data is related to. In prior presentations, we talked about a uniform distribution, a Poisson distribution. We're now looking at the binomial distribution and in the binomial distribution, we're looking at those scenarios and situations where we can break things out into binome two different items, either a success or failure type of situation. We talked about a coin flip last time, now we're going to be thinking about a sales call situation and we'll do this in a more manual method to get a better understanding of what is going on with the binomial distribution so that when we use the functions, we have a better understanding of what they are doing. So we're going to say that then N is going to be equal to the fixed trials or sales calls. So we're going to have four sales calls that we're imagining and we're going to hopefully make a sale or we're trying having a success being making a sale. The probability of success being defined as making a sale versus not success not making a sale is 15% that we make a sale. So obviously that's much lower than a coin flip scenario, which if it was a fair coin would be 50-50 success if we define success as heads versus tails, for example. So X is the number of sales in four tries, meaning the goal that we want to see, what's the probability then that we get three sales out of four? Now you can see that the likelihood is fairly low, you would think, because each time we make a call, we only have a 15% likelihood that we make a sale, so to get three sales out of four calls, you would think it would be a fairly low likelihood, but that's what we're going to be looking for here. Now we'll do this manually and then with the binome.dist function. So then we have the success defined as a sale, so when we build our table, S represents success or sale, the not success defined as not sale, which we're going to represent as NS, and then the possibilities are four, meaning we're making four calls. Alright, let's build our table. So what we have here is our possibilities and we've got the number of calls that we're going to be making. Actually, notice that this calculation down below is, let me see this, hold on a second. The possibilities I put the formula here is being calculated as this combine, and it's combining the numbers up top, which are the four and the three. So what that's telling us is the different number of combinations that would result in what we're looking for here, which would be three out of four sales. And so this number will differ. We'll take a look at another example later if I was to say, for example, that we only wanted two successes out of four. So this again is the number of combinations that we can have in order to get three successes out of four tries. So that'll make more sense in a second, but there's a nice formula for that in Excel. That means when I build my table, I can say, okay, there's four possibilities. There's four combinations that I could have that would result in three out of four successes. So if I look at those combinations, they look like this. So notice I've just got the non-success. If I make four calls, I could have a non-success on the first call, a non-success on the second call, third call, or fourth call, and all other calls need to be successes in each of them. So this first one, for example, if I had four calls and I'm going to get and end up with three out of four successes, I could have one non-success followed by three successes, or I could have one success followed by a non-success and then two successes, or I could have the first calls being successes, the third call not a success, and the fourth call a success, or I can have the first three calls as a success, and then the fourth call a non-success. So each of these result in three out of four successes. Those are the combinations we can come up with. Now to get this number, what I did is I said I want you to count Excel, this column, and I want you to count it if you see a success, which is that cell. So you can use like a formula in order to get that three. That three represents three successes, which I can get by using a formula telling Excel every time you see an S in this area, the range, then count it, then count that. And so then notice this doesn't take into account the probabilities yet. So you might say, well, wait a second, because if you first look at this, you might be saying, well, that seems to be indicating that I have like a three fourths chance or something like this. Why is my calculator going crazy? I want to make it small. That seems to be like I have like a three out of four chance or something like that, or a one out of four, one out of four, or something like that. But that's not the odds because on each of these calls, we have only a 15% chance that we're going to get a success. So it's still kind of unlikely that we would actually get this result. These are just the different combinations that would come up in order to have three out of four. So now we have to apply the fact that there's a 15% likelihood of each call to get a success versus not success. So to do that, I'm going to say that this first one here, the non-success, I have an 85% likelihood to get a non-success. Why? Because if there's a 15% likelihood to get a success, then I have one minus 0.15 or 85% to get a non-success. So and then on the second one, this one here, I have a success. So there's a 15% likelihood of a success. And then this one represents the third one. Again, there's a success 15% likelihood. This one, there's a success 15% likelihood of the success. On the second one, we had a 15% likelihood of success. And then the second non-success, 85% likelihood of the non-success. And then the success 15% likelihood of the success and so on. So you can see that the 85s here are mirroring the non-successes over here. Again, you can do that with a formula by using an IF, a logical test. I won't go over it in detail because you can take a look at the Excel problem to get into it in more detail. And we just want to look at the concepts mainly here. But the general idea would be saying Excel, I want you to say IF. And then the logical test would be that if this cell is equal to, for example, the S, let's say we're going to say if it's equal to the S, then I would like you to return the 15%. And then the next argument, the next comma, if it's not that, then we would like you to return 1 minus the 15%. And you can see we're using absolute references here and here so that I can copy that formula across. And we can use some fancy formulas to calculate that. Then we can multiply this out. So this is a multiplication now, which would be 0.85 times 0.15 times 0.15 times 0.15. That's giving us the 0.28%, 0.29% about. Now I can do that process in Excel by multiplying each of those cells, or I can use instead of a sum function, the product, which instead of adding these up is multiplying them together to get that 0.29%. I get the 0.29 in each of them because we have 185% and 315% in each. If I then add them up down here, we get to the 1.15. So this is just the 0.29 times 4 and it's approximately that because it's been rounded. So there's our probability. So the probability then that we get three calls, X being three calls, as a success out of four is a pretty low probability. Once we take into consideration the fact that an actual success, a sale, is only 15% likelihood per call. Now obviously this table method is a long tedious method, and that's what the binome function is doing here. So if I can calculate that with the function, which would be the binome.dist.range, you could also use just the binome.dist, but that's what the binome.dist is in essence doing. In this case, I'm taking the trials, the probability per trial, the 15%, and then the numbers that we want is going to be the three in this case, and that gives us the 1.15. Now just to get another idea of this, let's do it with a function now, and hold on a second, let me go down here. Let's do this again down here and then we'll take a look at the function. So another example of this, let's say that N is equal to the number of fixed trials, which is now four still, the probability is still 15%, but now we want two successes out of four. What's the likelihood we get two successes out of four? Success is defined as an S, non-success, a non-S, and the possibilities that we want, we're using this function again, combine the four and the two to tell me all the combinations that would come out to get two successes out of four, two sales out of four calls. Now there's six of them. So if I was to plot this out on a table, note that it's not always going to be the same number here. We had four of them up top. Now we have six possibilities. So I'll go through this quicker. What are those six possibilities? Well, we could have a sale, a sale, a not sale, a not sale. We could have a not sale followed by a sale, followed by a sale, and then a not sale. We could have a not sale, a not sale, and then a sale, and then a sale. We could have a sale, then a not sale, a not sale, and a sale. We could have a sale, not sale, sale, not sale. And we could have a not sale, sale, not sale, sale. Each of those combinations would give us two sales out of four. Again, these combinations are not taken into consideration. the fact that there's a 15% likelihood on each of them that I get a sale versus a not sale. So now we add our probabilities. Same kind of idea here. We can use the formulas to do the if function as well as the counting to get these numbers. Same as we did up top. So now this one same kind of idea. It's a sale. So there's a 15% likelihood and we can use our if logical function to get it in Excel. This one's a sale 15% likelihood. This one's a not sale 85% likelihood and then a not sale 85% likelihood and so on. If we take the product of all of these now we're getting to the product multiplying these four numbers together. We get the 1.63. If we add that up then the likelihood that we get two sales instead of three out of four is still relatively low because there's only a 15% likelihood per call but it's at 9.75 which is a lot higher than the 1.15 for three out of four sales. We could do that with the binome.dist.range taking the trials for probability which was the 15% and the number in this case we want two and we get to the same thing. Okay so then if we if we plot this out this way we can say okay we have one to four calls and we're plotting it out using the binome.dist.range in a similar way I believe we saw in a prior presentation still using the fancier.range calculation and if we if we plot this out we can also see the the table related to it and plotting it out this way is useful of course because we might want to ask multiple questions like well what's the likelihood that I get zero calls out of four and I think this is still using the the three out of four or or it doesn't matter this one's using the what's the what's the likelihood I get zero out of four 52.2 which kind of makes sense because it's because each call isn't a 50-50 you know likelihood you know there's a there's a there's an 85% likelihood each call so it's it's still fairly likely that I get zero zero sales over 50% likelihood that I get zero sales and this is what if you're if you're teaching someone how to do cold calls this is the cold reality that you got to basically imprint on people and say we got to do a lot of calls here because you could do four of them and it's not likely that you're more likely not to get any sales than sales right if you do what's the likelihood we get one sale out of four 36 85 two sales 975 3 1.15 and four sales out of four calls when there's only a 15% likelihood per sale only 0.05% and clearly this number the 1.15 is right there and this number that we got here the the the 9.75 for two is the 9.75 here so that's the general that's kind of what the binome.dis.range is doing