 Statistics and Excel. Exponential distribution. Create and compare sample line weighting data to exponential distribution. Get ready, taking a deep breath, holding it in for 10 seconds, looking forward to a smooth, soothing Excel. Here we are in 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, trust me, I'm an accountant product line. Yeah, it's paramount that you let people know that you're an accountant because apparently we're among the only ones equipped with the number crunching skills to answer society's current deep complex and nuanced questions. 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 formatting the cells within Excel as we work through the practice problem. Let's go to the example tab to get an idea of what we will be doing looking at an exponential distribution situation within business scenarios that often deals with line waiting situations as we will be working here. Oftentimes it's related to a Poisson distribution that we've seen in prior presentations. Poisson distributions typically asking a question such as what's the likelihood for a certain number of customers to be arriving within a certain time interval such as minutes or seconds. Whereas the exponential distribution kind of flips the question a bit and is asking what's the interval of time that is going to be passing before between customers. So what we would like to do this time is try to simulate a situation where we're going out there and we're just basically have our stopwatch and we're marking down the time that is passing between consecutive customers. And if it's following up an exponential distribution, then we'll be able to plot it out and possibly recognize that and compare that then to the smooth exponential distribution curve to try to get a better intuitive understanding of what is actually happening here. All right, let's go to the blank tab and start this out. We're going to say that first we'll say that the mean, well let's format the worksheet. Don't get ahead of yourself. Format the worksheet. We'll hit the triangle up top, right click on the worksheet and format the entire worksheet. Currency negative numbers bracketed and read no dollar sign. We'll get rid of the decimals for now. Add them as we need them. Okay, I'm going to embolden the entire sheet home tab font group emboldened. I have been emboldened to proceed. All right, here we go. No fear now because we're been emboldened. So we have the mean arrival rate and this is going to be in hours. So the mean arrival rate, meaning the average arrival rate we're going to imagine is 10, meaning we're imagining that on average 10 people arrive an hour. Now again, when we're thinking about the number of people that arrive in a time interval that may follow a Poisson distribution. And if it does, then we would think that the intervals between arrivals would typically be following the exponential distribution. Alright, so then we could say that the mean arrival rate in minutes then if that if it's 10 in an hour, the mean arrival in minutes is going to be equal to 10 divided by 60. I'm going to add some decimals. So we're going to say home tab number group adding some decimals. So we have about 0.166 people arriving per minute now. Okay, so then and so obviously we have to be thinking about what kind of time interval which would be best used for whatever we're working with hours, minutes, seconds. And so we can say then the inter arrival time in hours. So the inter arrival time between how long does it take between arrivals would be equal to 1 divided by 10. So if we think about in hours, how long does it take for people to be showing up? We're going to say home tab font group, add some decimals, 0.1 hours, right? So it's probably easier to see this in minutes. So if I say the inter arrival time in minutes, we're going to say, well, how many people show up 0.166. I'm sorry, one, let's say one minute divided by the average people that arrive in a minute, 0.1666. And we'll say enter. So that comes out to about exactly six, I think home tab number adding some decimals, exactly six. Okay, so so now that we have, let's try to see if we can simulate this data as though we're out there with our stopwatch and trying to and trying to see how many people are showing up and how long it takes between each person showing up. So I'm going to make a skinny C here. I'm going to say that the customer and we'll try to count the number of customers that come in, right? So let's say let's just say we do this for like 300 customers, let's say. So I'm going to say then we have one, two, I'm going to select those two and copy it down to 300. It's going to say copy it down to 300. And we're just watching these customers come in to pick up our data. I just made up 300 as a random number. So I'm going down to 300. You could use the sequence fill to fill that in if you so choose. And then we have the let's call it the enter, enter arrival times. I'm going to make this a header format selecting these two go into the home tab alignment wrapping the text centering it make it black and white. Okay, so now we're going to use a formula. Now, to simulate this, this data to a little bit of a complex formula, I can't just use a random generator. And we don't have the same kind of random generator we saw with the Poisson distribution and the binome with the data analysis. So I'm going to, I'm going to try to make my little random generator here, imagining that we're sitting there and we're simulating us checking out how many people are showing up in the interval of time between people showing up, right? We're just collecting our data. So that's going to be equal to ln, which is going to be the natural logarithm. So we have to so don't we're not going to be using a lot of calculus here, but we need that for a random generation. And then I'm going to say one minus the random. Now we're going to enter our random generation number embedded. And then we're going to say close that up. And I'm going to divide that by then the, which one is it? It's going to be the mean arrival rate. Let's do it in minutes here. And then I want this B2 to not move when I copy it down. So I'm going to select F4 on the keyboard dollar sign before the B and the two and then close it up. So it's a little bit of a complicated formula there. But again, this is kind of our random number generator tool to to simulate us out there, trying to with our stopwatch, trying to see the intervals between customers, right? So if I hit enter, there it goes. So I'm going to add some decimals, home tab, number group decimals, decimalizing it. I want it to be positive. So I'm going to double click on it again. And before the before the whole thing, I'm just going to put a negative, which will basically multiply the whole thing times negative one, enter. And so now we get a positive number. I'm going to double click the fill handle to just copy it down. And there we have it. So now we've got our randomly generated numbers simulating us out there, just counting how long it takes the intervals in minutes between customers coming in. Now, if you look at this, notice what kind of happens. This is this is kind of the key to kind of understanding why the curve is this way with a line waiting situation. Notice this first one we have 22, which is quite long. But when but then we had only one, one minute, five minutes, seven minutes, one minute, less than a minute, three minutes, five minutes, one minute, and then eight minutes, fairly long. So notice you have a what ends up happening with these line waiting situations is you you have some of these long waiting situations intermixed with with a lot of shorter wait situations is often what you end up with. And that's what's going to give us our common exponential kind of curve that will be that we will be generating. So notice this is going to regenerate because I have that random number generator. So what I want to do is copy this whole thing because this is my generator and then paste it 123. So it's not going to keep on trying to regenerate the numbers. So I'm going to put my cursor on D and E. I'm going to right click and copy those. And then I'm going to paste them in column G, right click, but paste it 123. So we just have the numbers. I can also paste the formula. I mean the format without the formulas, right click and paste the formatting so that I get those header formatting. I'll make column F a little bit skinnier. So there we have that I'll make column C a little skinnier. And then if I took the average of these arrival times, I should get to around, you know, that six over here. So I'm going to say to so I'm going to say then my my average or my mean of my data is going to be equal to the average brackets. I'm going to put my cursor here control shift down taking the whole average enter. And if I add some decimals home tab number group. So you can see that the that the average we get is around six, although it's kind of all over the place and we have some of those high numbers in a lot of in a lot of the lower numbers right we got this 2681 up here and then a lot of these lower numbers that's often kind of the case you'll have in like a line waiting type of situation. So now let's let's we could take a histogram of this and see what it would look like. So if I for example take my data here control shift down and I'm going to then control backspace and go back to the top and I insert say charts and a histogram. Now we've got our our chart. I'm going to call the chart equal to enter arrival times let's say or it won't let me do it this way in a histogram enter arrival times. So I let it I let it choose the boxes down below. So we've got you know 0.02 to 3.02 and 3.02 to 6 and so on and so forth and you can see what we end up having here is that most most of them are grouped up in the lower range not a whole lot of wait time but we have a few that are way out here at 21 and 24 minutes and some that are even further out which which is kind of what's so that's kind of giving us our characteristic exponential distribution look here. So this you and if we look at this we're saying how that looks kind of like an exponential curve. So let's let's then try to do this with bends this time. So I'm going to make column I a little bit skinnier and let's put our bends together and do this in a manual way and I'm going to use our frequency to do that and this is going to be the frequency. Let's make these two home tab font group black white. Let's put let's strap it. Let's center it and let's say that our bends are going to go up to like to like I don't know let's say let's say 35. So I'm going to say from bends of 0 1 2 I'm going to copy this down to let's say 35. Let's go to 40. Let's go to 40. We'll take the bends up to 40. So and then we'll and then I'm going to do my frequency. Now I'm trying to see how many times and this and this data set these numbers are showing up but notice they're not even. So I want to use a frequency which is going to kind of simulate the the bends the bends up to a certain rate. It has a range to it right. So I'm going to use my equals frequency tab. The data array that I want to be picking up is going to be then this data control shift down and then control backspace to get back up and then comma and the bends that I want are these bends. So control shift down and control backspace to get back up and there's my my spill array frequency enter and so there we have it. So now we're going to say that that one and this is going up to and including one. I believe we've talked about the bends you know how these happen and how these work in a prior presentation. We have 51 there and then two we've got the 30 and then three we've got the 31 and so on and so forth. Now I can also look at the let's take a look at the total let's total these bends up. So if I go down to the bottom I want to get rid of this last bend down here. So I'm going to go into this one and say take that to 41 get rid of that last bend and then I'm going to say the total is going to be alt equals summing that up gets to 300 that makes sense because I had a count of 300 here so it looks like it at least picked up all the numbers so that's good. So then we're going to say one more column which I'm going to call it the percent of the total and I'm going to make that black white wrap it center it and I'm going to take each of these results the zero divided by the total down here the 300 I'm going to f4 absolute reference dollars time before the k and the 43 that second number so I can copy it down enter and then percentify it number group percentify add a couple decimals fill handle double click it down so now we've got the percents and the total then should add up to 100 I'm going to delete this to give me my check number which shouldn't just be 300 divided by 300 but alt enter summing it up and so that adds up then to 100 so so then of course I can do my frequency distribution and make a make a chart based on this right I can take this if I so choose and say we're going to then pick that up and then insert this time I'm going to do a bar chart bar chart boom and there's our frequency I'm going to do what we typically do and go to my data on the bar charts and adjust the columns to pick up our columns from zero to 40 and enter and so there's our frequency you can see you have a similar you have a a a similar look to what we had up top except except the bends are different right with we have bends of uh of the the numbers here whereas the bends here a point oh two to point oh three and so on so if I want to compare like to like then I could adjust this histogram and I can just I could adjust the bends on it so if I go into the bends for example this is from our actual data you'll recall this came from our our actual data if I adjust the bends on the histogram to have an interval of uh one so I'll click on the bends and we're going to go here and then I'm going to go to my bend width and change it from from three to one and so there we go and now you've got something that's capping out at the 50 right and these two look a little bit more similar now okay so okay so now we're saying okay that looks kind of like an exponential distribution situation so now I could say well what if I used my to make predictions into the future then I can use my exponential uh function to get a nice smooth curve that would be easier to use to make predictions with right so I'm going to do this again I'm going to say this but now I'm not going to use my actual data but I'm going to try to make my curve so x equals arrivals arrivals arrivals during one minute it's going to be my header and then I'm going to say this is x p of x exponent function that will do let's start there I'm going to select these two home tab and then we're going to go to the font group black white wrap it center it so so there we have it I'm going to make this go from zero down to 40 zero one two copy that down to 40 just like we did before 40 and boom and then I'm going to use my my exponent dot disk so this is going to be x phone equals x phone dot dist and I'm going to say the x is going to be a spill function again so I'm going to put my cursor on that zero control shift down selecting the entire thing control backspace backup to the top comma lambda is going to be one divided by going to to the left we want to pick up that six which is the enter arrival time in minutes comma and then I'm going to scroll to the right just so we can see what we're doing here we're looking should it be cumulative or not we do not want it to be cumulative therefore we're going to say uh false or we can put a zero close it up enter spills it down selecting the whole column of zero home tab number group and I'll percentify it adding a couple decimals and and so there uh so there we have it now if we compare that to the bends for example uh if I had uh the one here on the bends we came out to with our actual data 17 versus you know 14 on the two the actual data was 10 versus the 11 the actual data was three versus uh the 10 11 and so on and so forth so I can then I can graph this we can say okay let's go ahead and graph this picking this up and we're going to say pick that up down to here and insert and uh we could do another one like this again I can say let's do that and so now we've got that smooth graph using our font let's keep it up here for now we've got that graph and I will then change the the x's to be my own so I'm going to go from zero to 40 so there we have that okay so that looks good and then we might want to try to put on top of that uh the actual data just to compare and contrast so let's go to the data again I'm going to go to the edit tab hold on not edit add and I'm going to also be putting in place the percent of the total and it's going to be the series of this series of data and we'll say okay boom and okay and okay so you can see them plotted kind of on top of each other there if I pull this down let's pull it down somewhere so it's not on top of everything else and so there's kind of our our comparison we might want a key on the right a legend a legend I'm already a legend man okay calm down so you could see so you could see you know the relationship basically on top of that so clearly in this case the exponential distribution uh has some predictive predictive power so we might want to use that for projections in the future because it looks like it's following that uh that pattern so that's the general idea let's clean this thing up uh let's make this whole thing blue and bordered home tab font group I'm going to make it bordered and then drop down on the bucket that blue if you don't have it more colors color wheel light blue I'm going to select these two control shift down border blue these two control shift down border blue and then these three hold on three control shift down border blue and then these two control shift down border blue let's do it oh not white with the oh man you messed it all up hold on a second that needs to be black and then I can review it spell checky arrivals arrive x oh that's no not a problem whatever get out of here all right we can make some of these little skinnier maybe the eye can be skinnier look how skinny eye is that should be way skinnier there's no need for a wide cell to put a skinny eye in okay