 Statistics and Excel, Binomial Distribution, Manual and Excel Function, Sales Calls, Example. Get ready, taking 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 that's okay, whatever, because our merchandise is better than their stupid stuff anyways. Like this CPA thinking cap, for example. CPA thinking, CAP, you see what we did with the letters? And this CPA thinking cap is not just for CPAs either. Anyone can and should have at least one possibly multiple CPA thinking caps. Why? Because based on our scientific survey of five people, all of whom directly profit from the sale of these CPA thinking caps, wearing this CPA thinking cap without a doubt, according to the survey, increases accounting productivity tenfold. Yeah, at least. Yeah, apparently the hat actually channels like accounting energy from the quantum field ether directly into your head. Allowing you to navigate spreadsheets faster. It's kind of like how in like the matrix when Neo learns kung fu, or at least that's what the scientific survey's saying. So get one, because the scientific survey participants could really use some extra cash. 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. But if you do have access, three tabs down below example, practice, blank, example, in essence, answer key. Practice tab, having pre-formatted cells, so you can get 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 looking at a binomial distribution type situation with a scenario of sales calls. So when we have a sales calls, there's only two outcomes that could possibly happen. One, we have a sale, or two, we have no sale. So that's one of the conditions for a binomial distribution. We're going to think about the process in a more manual type of method to get a more intuitive understanding of what is happening. And then we'll do the same calculation with the binome.dist.range function. And then we will also plot our data on the right as well. So let's go back on to the blank tab and fill this thing out. We're going to select the entire worksheet to start out like we normally do. Put down, lay down the baseline foundation, right-click in the selected area formatting the cells. Currency, negative numbers bracketed and red, getting rid of the dollar sign and the decimal. For now, I'm going to add them as needed. Okay, hold and control, scroll and in a bit. We'll put down our conditions up top. We're going to say that N is going to be equal to the number of fixed trials or sales calls. So that's going to be the number of calls that we're going to be having. I'm going to double-click on A here to widen the cell. I'm going to select the entire thing. I'll make it bold, home tab, font group and in bolden the entire thing. I'm going to say that we're going to have four of them. So we'll start with four and then P is going to be equal to the probability of success, which is a sale or sale. So success is obviously defined here as making a sale, not success, not making a sale. And as with many sales calls, the success rate is quite low on it. So we're going to say it's 15%. I put 0.15 home tab number group percentified so we can recognize it. And then X is going to be what we're looking for equals the number of sales in four tries. So this is going to be the goal that we are looking for. So we're going to say what's the probability of success that we get three out of four sales. And so we'll do this kind of manually and see what's the probability that that's going to happen. And then we'll use the binom.dis hopefully have any better understanding of it after that point. So we're going to define success, success defined as a sale, right? And then we'll call that just an S here for our table. And then not success is defined as no sale. And we'll mark that off as in S not a sale. And then the possibilities now when I when I look at the table, then when we start to build our table, we're going to say, OK, well, how many combinations do we have? If if we're talking about four, we've got the number of fixed trials at four. And we're going to say three out of four are going to be successes. So when we start to build our table, we're going to think how many combinations do we have? So there's a formula for that. And it's going to be equal to count. I'm sorry, it's going to be equal to combine. Here it is. C O M B I N. And then I'm going to say the number is going to be four and then comma. And the number chosen is going to be three. And if I say enter, that's going to give us four combinations. Now note that this is going to change down here. If I said that I wanted two successes out of out of the trials of four, then I get a six down here. So we'll do a couple of examples to get an idea of a couple of different examples, but we'll start off with the three. Let's do a quick spell check because I'm pretty sure I misspelled a couple of things. Possibilities P for possibility fixed song. I don't there was a song I remember vaguely about P for pop. I'm going to close up column C a little bit. We can make column B a little bit smaller as well. And then let's build up our table. So I'm going to say we have the possibilities and I'll make this one a little bit wider. So I can see that possibilities. And I'm going to say then we have one, two, three, four up top. And then so so on each each of the trials, we have the four calls that are happening. And then I know that I have four possibilities. I'm going to just kind of call them P one, P two, P three, P four. And this is where this number four comes in. So I'm going to say, OK, there should be four possibilities. Let's make this top bit our header formatting, home tab, font group. I'm going to make it black and white. And then I'm going to center these numbers. I'm going to go to the font or the alignment and center those. OK, so what kind of possibilities do we have? And maybe I'll make this black and white too. I'll make just so I can this is going to be a kind of a detailed table is trying to make it as defined as we can. So what kind of possibilities might we have? Well, if if we're saying I'm not looking at this 15% right now, I'm just trying to say how many different combinations or what are the combinations that we could have, which would result in three sales out of the four possible calls, right? Three out of the four possible calls. Well, if I have four possibilities, then on on each of them, I'm going to have one of them be a not sale. Only one of them is going to be a not sale. So I could I could first think about the not sales. Like I could have a not sale on the first call and then sales on all the other calls or I could have a not sale on the second call and then sales on all the other calls. Or I could have a not sale on the third call and then sales on all other calls or I could have a not sale on the fourth call and sales on all the other calls now remember that it's unlikely that you're going to get three out of four sales we're going to we're going to take into consideration the probability of this happening per call which is only 15% right now we're just trying to think of what are the combinations that would have to happen to result in what we're looking for which are three out of four three out of four items being sales well it would be I would have all sales for all four calls except for one of them which means the combinations would have to be one of these right I would have a not sale I either call one of four at two or four at three or four at four or four and all the other ones would be sales which I'll say is an s equals s equals s all the other ones are sales success success even though this is an unlikely scenario we'll see the the likelihood of this is not high because we're not taking into consideration the 15% likelihood you know that we get a sale per call right now we're just looking at those combos and so there's the possible combos that we could have so if I if I give a check then just to check that x is three meaning three out of the four are sales we can do a formula to check that we should get three each time let's do a count if formula this is going to be equal to count if brackets we want to pick up this range count that if comma it meets the criteria of being an s so now it's going to count the all the s's in that range we're going to say okay there's three of them that makes sense perfect check that's what we want because copying that down oh I can't copy it down because I need an absolute double clicking here and that b5 I want to absolute it f4 to absolute and enter and now I can copy it down by double clicking the fill handle so they all come out to three out of four that looks good I'm going to go up top home tab font group black white let's make all these a little bit thinner put put all these on a diet make them a little bit thinner and boom better than than me on the diet right okay let's not get let's get sidetracked here so then I'm going to say that now now we can think about the the possibilities per out let's make it even a little thinner the possibilities per outcome so what's the likelihood that on each on each call I get a not sale versus a sale well so so now I can take into consideration my percentages well let's let's look at that I'm going to once again label them one two three and four but this time think about our our likelihoods the percent likelihoods let's make this black white let's make these thinner as well somewhat similar thinness and we can center it maybe okay so now we're going to say well the likelihood that that that we have an in a not sale is is going to be one minus 15 percent so the likelihood that we have a sale is going to be only 15 percent per call so let's do that first and we could I could just type in here you know 15 percent we could try to get fancy with a formula with a formula or something like that I could say that if I say equals if formula brackets and we're going to say logic test if this number is equal to a s then what do we want to have happen we want you to then put this number up top of 15 percent if not comma what do you want to do if it's not that then we want you to take one minus minus not minus this number so in other words I want you to if if this is an s I want you to then put the 15 percent if it's not an s I want you to take 100 percent or one minus 15 percent or 0.15 and enter let's percentify it and see if it doesn't home tab numbers percentify it and there's the 15 percent now I can copy that across if I double click on this I can say okay this everything that's in my data I need to make absolute so it doesn't move this one I don't want to make absolute because I want it to move so everything in column B like this B5 I'm going to select F4 dollar sign before the B in the 5 this one I'm going to select F4 dollar sign before the B in the 2 this one I'm going to select F4 dollar sign before the B in the 2 and enter and I should be able to copy this across right I'm going to copy it this way 15s I'm going to copy it this way and there's the 85 right so there's an 85 percent chance each call that I don't get a sale and there's a 15 percent chance each call that I get a sale and let's copy that down and we'll get the same a similar kind of outcome here you can see the pattern like a tic-tac-toe but four in a row right here four in a row but here right you can see those the same pattern here so 85 percent for all of the no sales and 15 percent for everything else which is a sale okay well then if I if I uh then then look at my totals over here we can say okay I can multiply those together to get my total percent so I can say this is going to be equal to that times this times this times this so we're multiplying them all together and I can then go to the home tab number group I'll percentify it add a couple decimals 0.29 percent about we can get to that same number by doing the product a product function so this would be a little bit faster so we'll multiply all them together instead of equal sum we say equals product brackets of those and it's a little bit faster of a formula and we get to that same 0.29 percent so I'm going to I'm going to copy those down and then at the bottom we're going to get the probability of x x let's say equal to and then again I'm going to say this is equal to this number three what's the probability it's the sum of these which I can say by going alt equals and then boom and uh let's go ahead and uh hold on that's the wrong cell look that's not right uh alt equals here and boom let's percentify this home tab number group percentify add some decimals and we get the 1.15 I'll just make this let's call it the totals and I'll make it black white center a little skinnier and underline home tab font group underline so we come up then so to the probability of of it equaling exactly three then x equaling exactly three number of sales and four tries in other words the likelihood that we get three successes sales and four tries is quite low right it's it's 1.15 percent so we thought about all the possibilities and then we applied our percentage outcomes for each of the possibilities which has the same likelihood per call one call is not influencing the other and there's our amount now we can also do that with the the excel function now that we have a better idea of what the excel function is doing we can say let's just do it with the excel function we can use the equals binom binom.dist and I'm going to use the range one because that's the newest the latest and greatest the number of trials is going to be we'll say four comma the probability per trial is 0.15 or 15 percent comma and then uh and then the number uh the numbers is going to be three and enter and percentify add a couple decimals make this a little wider and we get that so point 1.15 so we can get to that same uh result and kind of get an idea of what is happening all right let's go ahead and I'm going to make this a little fancier by putting our bluer into it highlighting this home tab we're going to go to the font group I'm going to make it blue hitting the more button blue here boom borders home tab font group borders and then this one I'll make it blue and border two these I'll make blue and bordered and then uh and then and then maybe this I'll make black and white home tab font group black and white because it's kind of like part of the header area and then maybe this I'll make blue and bordered home tab font group border and blue something like that all right let now I'm going to do the same the whole same thing again but this time I'll change this I'll change I'll change this to a two so we want two out of four and just see what the what the difference is so I'm going to copy the whole thing I'm putting my cursor on one down to eight control c and I'll paste this down here and just make that little tweak so now I'm going to say all right what if I say that I want to have the goal to be two so two out of four that you would think that would still be more likely you know to happen even though you still have got the 15 percent likelihood of each call being a sale versus a non-sale so now if I look at my table I can't just use the same table exactly because now the number of combinations where I would get two out of four out of four is now six instead of four so I have to add a couple rows down here to to make this work so I'm going to say all right let's let's go into here I'm going to select maybe I'll select these two and then I'm going to insert and push these cells down so I'm going to right click and insert but I want to shift the cells down and then okay so now I've got probability four p of five p of six and let's just redo this whole uh this whole table I'm just going to say let's just redo this whole thing and I'm going to say all right if I if I get two what's the like how how could the combinations work here well I could have like a sale and then a sale but and then not a sale and then not a sale that's one way it could happen or I could have a I can have a not a sale and then like a sale and a sale and then not a sale so you can see the pattern that's happening right I'm just thinking of these two s's going through the possibilities I could have then not a sale not a sale and then a sale and then a sale right or I could have I could have a sale and then not a sale and then not a sale and then a sale or I could have a sale a not a sale and then a sale and then a not a sale or I could have a not a sale and then a sale and then a not a sale and then a sale Right those gets a little tedious. So then if I if I try to so does this all work? So all of these I should have to sales so I can do that count if again equals count if Brackets the range is going to be this comma count it if you find a Sale in it, which is our success f4 on the keyboard so I can copy that down and Enter and so I've got two two two two all the way down So everything looks like these are all viable combinations. I'm pretty sure there's only six of them because I Calculated that there's six of them. So I think I got all the combinations I hope and then we can say all right one of the probabilities well This one the sales now. It's still 15 percent likelihood that of a sale happening So I can say this is equals if brackets logic test if if this sale equals The sale now this is outside of my table So I'm going to make it f4 because I want it and that's a dollar sign before the B in the 14 So it's an absolute reference if that's equal to that then what do I want it to do comma? I want you to then put 15 percent and then I'm going to make that f4 or absolute because I want to copy it down if not Comma then I want you to take one or a hundred percent Minus the 15 percent or point one five F4 on the keyboard because I want to copy that down and there's our logic test So I can say okay enter that should give me a 15 You could just type these in here, but I'm trying to practice our Excel formulas. I'm going to copy this to the right I'm going to copy that down and it should populate automatically and Then we could multiply this this Times this times this times this but the sum product is the Easier formula, so I'm sorry the product is The faster formula, so we're gonna instead of using the sum we're multiplying them with the product and Doing the same thing all the way down and So there we have that I'm going to underline it here And then I don't think this total is correct anymore. I'm going to delete that and say alt equals nine point seven five nuts double check it over here with our I'll just retype this in here equals the bi-nome Dot disk dot range and you could use the dot disk one as well But let's keep with the dot range because it's more flexible the trials. We're gonna say our four Comma the probability each time point one five or fifteen percent comma the numbers we want to and Enter so there we have it a couple ways now, of course You might put this in a in a in a a table type format, so you could say It's often useful once we're using the function. I'm gonna make a skinny. Oh up top and say X P of X I'll make this black and white home tab fonts group black and white center and We'll say this is going from zero one two three four And I could just do all of the outcomes nice and easily here right with our nice function So I'm gonna say this equals bi-nome Dot disk dot range Tab the trials are gonna be then the trials are gonna be these Control shift down Comma the probability. I'm sorry. Hold on a sec. The trials are gonna be four F4 on the keyboard then comma and then the probability going to the left is gonna be 15% F4 on the keyboard comma and then the numbers are gonna be this range control shift down and it'll give me my spill and Enter so it spills it down and I'm gonna go home tab number group percentify it and Add a couple decimals so there we have it so we get to the same result we had here for The likelihood of two Successes and the likelihood of the three successes right so then I'm gonna make this a little bit smaller Let's make it blue and bordered home tab font group blue border it or That was I did that in backwards reverse But you see what I'm doing and then let's insert a little chart for just for the fun of it insert Chart let's make this kind of chart a bar chart this time Pulling it over. It's not picking up the zero here because it's trying to guess its own number so I'm gonna go to my data and edit and say pick up the zero and Do use our data set and okay? And so we can plot it like that or we can plot it with a line chart as well, so that's the general idea and I was gonna clean it up a little bit More but that works pretty good. So let's put some borders around this maybe put some borders around this and I'll save it and We could review it maybe Try to spell check it Okay, so there we have it