 Statistics and Excel. Binomial distribution coin flip random number generation. Get ready, taking a deep breath, holding it in for 10 seconds, looking forward to a smooth soothing Excel. Here we are, and it's 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 CPA six pack shirts, a must have for any pool or beach time, mixing money with muscle, always sure to attract attention. But 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. 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. So 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, there's three tabs down below example practice blank example in essence answer key practice tab having preformatted cells. So you can get to the heart of the practice problem, the blank tab blank worksheet so we can practice format formatting 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 considering a binomial distribution situation scenario of a coin flip scenario where we have a fair coin 50% chance it lands on heads or tails. We need to define success. We're going to say that heads is a success, tails is a fail. We will be plotting this out using our binom.dist function. We'll also look at binom.dist.range. So you can compare and contrast the use of the two. We will plot it and then we'll use a random number generator, a little bit more complex one than we've seen in prior sections found in the data and the analysis tools here. And if you don't have that, I'll show you how to open that up as well. Let's go to the blank tab to get started. So we're going to then format the entire worksheet to start out. I'm going to do that by selecting the triangle up top right click and format the cells. I'm going to go to the currency and negative numbers red and bracketed no dollar sign and no decimals as our starting point. We have a coin flip scenario coin flips. I'll just call it up top and I'm going to make the whole thing bold as well. Selecting the triangle home tab font group and in bolding the entire thing. All right. So what we need for a binomial type distribution is P for the probability of success for each activity. So we're going to say this is going to be P or the probability of success. And for a coin flip, if it's a fair coin, we're going to say that's going to be point five typically. So I'm going to say 50% point five home tab number group. Let's percentify that cell and then the number of rounds. I'm going to say number of rounds and I'm just going to say they'll be 12 rounds, 12 rounds. All right. So then I'm going to make column C a little bit skinnier and plot this out. So let's plot it out. Now we'll plot this out in a similar way as we did with the prior presentation. I'm going to say X. This can be P of X. I'll make this black and white up top. Selecting these two up top home tab font group bucket drop down making it black and then making it white. And centering that. And so then now I'm going to say this is going to be numbered from zero to 12. But I'm going to use our nice sequence function to do that. So instead of going zero, one, two and selecting those and copying down to 12, which we could do, but I would like it to be adjustable. So I'm going to use the sequence thing here and I'm going to try to show the pros and cons of using the spill arrays to some degree. As well as we think about this will do this two different ways. So I'm going to say this equals the sequence s e q sequence tab. And then I'm going to say that we want the rows are going to be 12 of them plus one because I need 12 plus zero. And I'm going to start at zero instead of one number of columns, none. So I'm going to put two commas to skip that argument. The starting point will be zero. Closing it up and enter and you give us that nice spill format there. Now let's do the second bit here, which is the by gnome. And once again, I'm going to use an array kind of format. So this is going to be by by gnome by gnome.dist. So here we have the two that we have.dist and range. The.dist.range is the newer of the two and so it's and it's got more flexibility. But again, there's kind of pros and cons to using each of them and you might you might pick one or the other depending on your circumstance. But this is this one you should be able to use basically for all circumstances and therefore might be the default that you would want to be thinking of. So we're going to say then the number of trials is going to be 12 and then comma. The probability is going to be the 50 comma and then the numbers I'm going to select this range here. So I'm going to put my cursor on here d2 control shift down so it picks up that range. I'm also going to select these two and make sure that they're absolute f4 dollar sign before the B and the four. This one f4 dollar sign between the B and the four or before the B and the three and then enter. So then it spills down. So now we've got these arrays here. If I select this item home tab number group percentify it and add some decimals. There we have it. Now I'm going to do the same thing here and not use the arrays. One of the downfalls or pitfalls of using an array by the way is it's a little bit more difficult to say insert a table. If I want to go insert and put a table then you know it's not picking. See how it didn't pick the entire area that it normally would if there were not arrays here. And if I close this up and I try to say I want this whole thing in a table insert table and OK. It messes up the spills. So sometimes so you got to be a little bit careful when you're working with the tables. The other thing is that it doesn't have a formula down here. It only has a formula in that top cell which could be a pro or a con. But let's do the same thing. I'm going to copy this down here and do that do it without an array. So let's copy that. Let's give us some space and I'll put it down here to see the two methods you might use. I'm going to have the same headers. This equals the X and then copying that to the right for the F of X home tab font group black white centering it. So now I could use the standard the good old method of just saying 012 selecting those three copying it down with the fill handle. And then I'll use the other by no function. So equals by no by no dot dist but not the range this time. So by no dot dist double clicking it. So now we've got the numbers which I'm going to pick up this one and noted the order of of the arguments are a little bit different. So I'm going to say comma and then the trials that we're going to have will be 12. And then I'm going to say F4 on the keyboard to make that absolute because I'm going to copy it down and then comma. The probability is going to be 50% F4 on the keyboard because I'm going to be copying it down and then comma. And you'll note it's got this cumulative argument. Now the cumulative argument is the same thing or something we saw with the Poisson distribution where it's saying that if you pick false then you're not going to have a cumulative up to a certain point. Whereas if you pick true it'll try to do the cumulative up to up to the point up to that certain point. We want it not to be cumulative so we can type in false or we can put a zero here which will also say false that's telling it false and enter. I'm going to put my cursor on it and then double click the fill handle that should copy it down and then we'll percentify it and add a couple of decimals. Now with this second one note that what we can do here is insert a table because we didn't use the spill functions in any spill function. So I can go to the insert and if my cursor is in here I can make a table from it and insert the table and there we have it. And the tables can be nice sometimes because you have certain formatting within the tables and I feel like it's less likely that you're going to kind of mess up your data when it's in the table such as you know sorting the data on one column without sorting the data on the other column. When I'm in the table I can also go to the table tools up top we can add a total column and if I sum this up you can see that this sums up to 100 percent. That'll give us kind of a check figure that what we're doing is correct. However if I go back up top here you'll recall one of the benefits from the prior presentation you may recall is that now I can adjust this number a little bit more easily. So if I adjust this number and say I want to make it 15, notice now because we used a spill sequence it'll now increase to 15 automatically. And so I can bring it back down to 12 so you have a bit more flexibility with these ones. Also just note with this binome.dist.range the arguments are a little bit different and they're a little bit more flexible. We don't need the cumulative argument as much because it allows us to enter multiple arguments which allows us to kind of pick the middle of the range a little bit more directly as opposed to what we did with the Poisson distribution. If you recall if you saw the prior presentations where we had to do the cumulative up to a certain point and then subtract out the cumulative up to a different point in order to get that middle range. So it's a little bit it's got a little bit more flexibility. Okay, let's go ahead and graph this thing. I'm going to make column F a little bit skinnier and then let's select our data. And so I'm going to go up top and go to the insert and then we'll go to the charts and we'll enter the bar chart and add our bar chart. So I'm going to pull that to the right and then do our standard process. I'm going to click on it go to the data up top and I would like to go to the edit of this side to make sure it's picking up our X numbers which are going to be from 0 to 12. So I'm going to say OK and OK. And so there we have it. I'll just delete this top it. And so so there we have it and you can see of course that it kind of in the middle point is that six as we would expect. We can also plot it with a line chart to so I can select these insert. We can then go to the charts and have a line chart something like this one and have it look like this format. I'll do the same thing here. I'm going to click on the data and select this one and say I want to make sure that you pick up my numbers here 0 to 12 and OK OK. So we can format it like that. And it looks kind of like what you would expect the middle point being six. So let's do a little bit of an analysis similar to what we did in the prior presentation. If we think about our data over here if we have a fair coin 50 50 on the coin flips. Remember if I flipped it zero times if I had zero flips then if I define a success as heads then I'm not going to get any heads of course. So 100% likelihood that that it's going to be you know at zero right zero. And then I'm going to say if I have one well then if I have zero successes the likelihood is 50% the likelihood if under two flips that I get one success defined as a heads is 50%. If I say two then now we're going to say OK if I do it if I do the flip two times the likelihood that I get no successes defined as heads 25% likelihood that I get one success defined as heads 50% likelihood that I get two successes both heads is 25%. And if I go to three then you can see it's likelihood that I get zero successes out of three flips 12.51 success out of the three flips 37.5 two successes out of the three flips 37.5 and three successes they're all successful heads 12.5 and then four and so on and so forth. So you can see how this is being built and we looked at the changing of the curve on the right hand side in a prior presentation as well. So let's put let's put it back up to 12. So now we're saying 12 times we flipped it. And by the way one other thing to look at if the coin was not fair then if it was 60 like let's say you know it's going to land 55% of the time heads. So it's it's slightly tweak the casino tweak the coin or whatever right. So now if I go if I flip it one time now it's got a if we have zero successes zero heads if it's in our favor that it's 55% heads then it's going to be 45% no heads 55% that it will be heads two per two times. Now we've got only 20.25 no heads 49.5 that we get one head out of the two 30.25 two heads and so on and so forth. So we'll consider it a fair coin. We're going to flip it 12 times is going to be back to the norm here back to the where we started. So now let's mirror the experiment. So instead of us using simply a random number generation as we saw in prior examples where as if I was going to simulate each coin flip I can say equals random you know between one and two having one represent heads to represent tails. But instead we're going to get a little bit more sophisticated here and go to the data tab. And we're going to say that I want to have the data analysis tool help me generate the outcomes of 1212 flips according to the rules that we have here so I'm going to say these are going to be the outcomes that will generate. Let's make this home tab font black white center it. I'm going to put them here and then in the data tab if you don't have this analysis section you go to the file tab and then options and then add ins and then Excel add ins and go. And you want to take that analysis tool pack and if you have that tool pack then you've got our tools in the data tab. So let's open that up and I want to go to some random generation numbers and we're going to say OK. And I'm going to say one here that's basically the number of columns number of random numbers. Let's go to a thousand like we've been doing customarily this time we did this with a Poisson distribution this time we want to get the generated numbers in accordance with a binomial distribution P. We remember is point is point five fifty percent and the number of trials in is going to be twelve so we're going to have twelve flips with a P of fifty percent for each of the flips. And then I'm going to put down here the output range where do we want to put it I want to put those thousand numbers right there. So that's going to go to P two and I'll say OK. And now it's simulating these the these tests right so now we flipped these are representing for example one test of twelve flips where I've got five successes which we define as heads right. So five heads out of twelve seven heads out of twelve seven heads out of twelve four out of twelve four to twelve four to twelve nine out of twelve and so on and so forth. So let's put those results into a bucket if we could so I'm going to make this a little smaller and I'm going to say this is these are going to be our bends and this is going to be the frequency. Now when we have the bends are going to be anywhere from zero up to one to they could go up to twelve right because it's possible that I have some that had actually twelve successes but unlikely that we flip the coin twelve times but possible. All right so we got twelve up to here and then you would let's make this black and white hometown font group black white center to wrap it and center it OK. I didn't mean to center it that way. Let's center it back to OK. So now we could say you would think you could use the count if equals count if and then brackets and you'd say well this range the outcomes I have control shift down. I'm holding control backspace comma and that's the criteria close it up enter right meaning every time you find a zero over here you put it over here. We actually had two times where there were zero zero heads which is out of twelve flips right out of all these thousand flips that we did. So that's so interesting but I think it's easier or it's more useful often times are safer to use a frequency spill array because sometimes for whatever reason these numbers may not be exactly whole numbers or something like that. So sometimes it doesn't pick up all the numbers so I'm going to use equals frequency tab and then the data array is going to be here. I'm going to hold control shift on the keyboard and down and then I'm holding control backspace to get back to the top comma then the bends. I'm going to put my cursor here control shift down and there are the bends and so I can just hit enter and it spills them down it goes a little bit far here so I'm going to try to trim that last bit off double clicking on it. I'm going to get rid of that bring it back to 13 and there we have it now let's put the total down below and it should total up to a thousand here because we did this a thousand times we did a thousand twelve round flips of the coin. So this equals the sum or I can do it quickly by saying alt equals I got to click off and then back on alt equals enter comes out to a thousand. So that makes me feel like okay it picked up all the numbers of these thousand rounds of twelve flips that we had and these are the results. So two times out of a thousand we didn't get any heads in a twelve flips of the coin. One heads one time zero times we got two out of twelve heads thirteen times three out of twelve forty four times four out of twelve one hundred nineteen times and again you would expect that somewhere like six out of twelve would be kind of in the middle of all of all these right and so now let's take a percent of the total let's take this as a percent of the total. I'm going to format paint home tab format paint that here and this will equal the two divided by the one thousand I'm going to select f four to make that absolute so I'm going to take each number divided by the total enter. Let's make it a percent home tab font group percentify it adding a couple decimals and then double click the fill handle dragging it down or taking it down. I'm going to delete that bottom bit because I don't want to take this divided by this I want it to instead sum up alt equals some boom. Okay so now let's compare that and look at the difference between what was what was given by the bi-nome formula so I'm going to select fill filling that in. I got to turn my music back on in the background you can't hear the music I'd be copyright if I but here we go so then I'm going to say this is going to be equal to this minus. The I don't work without the music man I refuse to keep going okay we'll subtract that out. Percentify home tab number group percentify you better recognize you can't recognize unless you percentify. And then we're going to copy that down and then I could copy that across. And so so now you can see kind of the differences of what we got over here on the likelihood right that we get zero you know out of the out of the 12 flips right when we actually ran the test point to. Versus point to point to nine that we get one out of 12 the likelihood right and then this came out to zero and then getting to is 1.61 and 1.3 and you would you would expect then. That if I did this experiment more times like a thousand I did it a thousand times if I did it infinite amount of times like the entire population that we'd imagine the entire infinite that we would come out to. These numbers right but we're taking a finite sample. So so then the idea would be that this this then gives us pretty good you know predictive power about this scenario which has an element of randomness in it. Let's go ahead and plot this one to so I'll select I'll plot them together I'll select these items here and let's do it with a line chart insert charts. Let's do a line chart and go that one that's the percent of the total line chart I'm going to go to my data and edit the X I want to pick up my X's. So don't just do your own X's Excel and then I'm going to add another one add another data set so we can see the other data set which is going to be the P of X data set. And so we can see the differences on the side by side and we'll say OK pick those ones up or if you please and then OK and OK. And so there we have it so it's pretty you know they're pretty close right there. So you would think that the binom.dis gives us some predictive power of course and this kind of situation and in practical situations this actually comes up all the time. Whenever we can break something down to a success or fail situation and then get an idea of what the likelihood is for any one one round that we're doing like flipping the coins like a sales call is a common example right. So every time I call someone it might not be 50 percent chance of success it's probably going to be far lower than that. But but if I haven't a chance of success percent then then it's either a win lose situation I can start to plot out and and and get some idea of the results. All right. Let's go ahead and make this blue. I'll make this a header home tab font group black white. Blue and bordered font group border it make it blue. If you don't have that blue it's in here. You don't have to use that blue but that's what I do. It's nice and kind of bright. It doesn't remind me of the horror days of having to write this down in a spreadsheet and not being able to read my own writing and people are like you didn't add it up right because you couldn't read your own number two that two doesn't look right. Like whatever dude why don't you use Excel for crying out loud. What are we doing around here. You can't add that up in your mind. No I can't add it up in my head. I control shift down. We're going to say home tab. That's why we have computers for crying out loud. Control shift down. And we'll make this. Sorry to share my traumas with you. But I'm just saying the blues doesn't doesn't trigger the the nightmares quite as readily. So there we have it. Let's do a quick spell check on it. And frequency. Okay looks good.