NAME: For the following problems, you'll use various Excel functions. Please visit Microsoft website if you need more info on these. . 1 Dollar Express has determined that average purchase amount for a Halloween party is $59.23 with a standard deviation of $5.89. Assume the distribution to be Normal. Use NORMDIST to calculate the probability that a customer will spend a b c d more than $65.43 between $56.31 and $66.89. between $63.43 and $70.16. less than $65.14 2 a b c d Use NORM.S.DIST to answer the following questions. What is the area (probability) to the right of Z = 1.324? What is the area to the left of z = 2.41? What is the area to the right of z = - 1.31? What is the area between z= -1.37 and z = 1.69? 3 a b c The class average of test # 2 is 81.6. The std dev is 8.28. Use NORM.INV to answer the following questions. If the top 15% of the class gets an A, what is the cutoff score? If bottom 9.5% of the class gets an F, what is the cutoff score? If bottom 25% of the class is put on probation, what is the cutoff score? 4 a b c d Use NORM.S.INV to answer the follwing questions? What is the z-score if the right tail = 6.44%? What is the z-score if the left tail = 3.86%? What is the z-score if the right tail = 9.40%? What is the z-score if the left tail = 10.39%? 5 The Probability of success in an experiment is 39.74%. If the trial size is 45, answer the following questions. Use BINOM.DIST to calculate probabilities.. a b c d e f What is the expected number of success? What is the expected number of failures? What is the probability of 30 successes? What is the probability of less than 29 failures? What is the probability of more than 29 successes? What is the probability of more than 25 but less than 35 successes? 6 The Probability of success in an experiment is 45.73%. If the trial size is 47, answer the following questions by using Normal Approximation to Binomial. Use Excel functions for Normal Distribution or no credit will be given. a b c d e f What is the mean of the normal distribution. What is the Standard deviation of the normal distribution? What is the probability of 30 successes? What is the probability of less than 29 failures? What is the probability of more than 29 successes? What is the probability of more than 25 but less than 35 successes? 7 T.INV gives the t-score given area in one tail. T.INV.2T gives the t-score given the area in 2 tails. Answer the following t-distribution questions using these functions for a sample size of n = 38. To be sure you are getting the right results, check the function by using values from your T-table. a What is the t-score if the right tail area is .043? b What is the t-score if the left tail area is .086? c What is the t-score if the area around the mean is .78? d If the mean score on Test 3 is 82 and standard deviation is 7.5, what will be the cut-off score for an A if the top 10% students get an A? e If the mean score on Test 3 is 82 and standard deviation is 7.5, what will be the cut-off score for an F if the bottom 5% students get an F? 8 Pioneer Bank's customer require an average of 4.7 minutes of service. Use EXPONDIST function to answer the follwing questions. a b c What is the probability a customer will take more than 5.9 minutes? What is the probability a customer will take less than 3.2 minutes? What is the probability a customer will take between 3.4 and 6.9 minutes? Name: In Chapter 9, we learned the distribution of the sample mean x-bar and the Central Limit Theorem. W about . In this problem, we will use Excel's sampling feature to see how this checks out in practice Normally distributed population (Scores) of size N = 500 using Excels's Data Analys>Random Numbe the mean = 80 and stdev = 8. Now, use Excel's functions to find the mean and the population std d population. These should be pretty close to 80 and 8 respectively. Next, use Data Analysis > Sampling choose 12 samples of size n = 30 and calculate their means x-bar and stdev s. From the formula give Sigma-x-bar . How does the population mean compare to the mean of the sample means? How to the std deviation of the sample means? Put your explanations in the space provided below. Explanation: Population Mean = Sample Mean Population Std dev = Sample Std dev Scores Sample Sample Sample Sample Sample Sample Sample 1 2 3 4 5 6 7 entral Limit Theorem. We also learned s checks out in practice. First, generate a Analys>Random Number Generator. Keep and the population std deviation of this Data Analysis > Sampling to randomly From the formula given in Ch 9, find he sample means? How does compare e provided below. Mean of Sample means Sample Sample Sample Sample Sample 8 9 10 11 12 Std Dev of Sample means NAME: Do Normal distribution functions like NORM.DIST really work? We can check this by calculating the probabilities usi this function and then comparing them with the corresponding (actual) proportion in the population. To do so, 1st generate a normally distributed population of size 1,000 (starting at cell A21) with Mean = 100 and stdev = 20. Use Data Analysis>Random Number Generation to do this. Use AVERAGE and STDEVP functions to calculate the mean and std dev of this population. Then use COUNTIF function to calculate various proportions (probabilities) and compare them to the probabilites given by Normal distriubution function NORM.DIST (using calculated parameters). Always use cell references when using a calculated value from a cell. Population calculated Mean: Population calculated Std dev: Population How many in the data set? (Use Countif) In the population, how many numbers are 128 115 107 90 Proportion? Ptobability using NORM.DIST function How do the probabilities compare? For example, use Countif functi numbers in the population are the population size to get the a (probability). Now use NORM. calculated mean and std dev to alculating the probabilities using the population. To do so, 1st ean = 100 and stdev = 20. Use nctions to calculate the mean rtions (probabilities) and (using calculated parameters). For example, use Countif function to find how many numbers in the population are 128. Divide this by the population size to get the actual proportion (probability). Now use NORM.DIST function with calculated mean and std dev to find Prob of 128. NAME: We have used the normal distribution table. Now it's your turn to create one using Excel's NORMSDIS Note: this table should resemble the table that we use in our class (probabilities from the mean as sh picture). Use Excels's formatting function to show 5 decimal points. This problem will require some E manipulations and some creativity with reference cells. Done properly, you'll have to type the Excel fo once which will be copied to fill all the cells. So, if you don't know, it is time to learn these useful Exce which will change your whole life (it's an exxxxxaggggaration, but still a truth). I'll be more than happ you some basic Excel skills, but you have to come to my office. z-score 0.0 0.1 0.2 0.3 0.4 0.5 0.6 0.7 0.8 0.9 1.0 1.1 1.2 1.3 1.4 1.5 1.6 1.7 1.8 1.9 2.0 2.1 2.2 2.3 2.4 2.5 2.6 2.7 2.8 2.9 3.0 3.1 3.2 3.3 0.00 0.01 0.02 0.03 0.04 0.05 0.06 3.4 3.5 3.6 3.7 3.8 3.9 4.0 ate one using Excel's NORMSDIST function. obabilities from the mean as shown in the his problem will require some Excel , you'll have to type the Excel formula only s time to learn these useful Excel tricks a truth). I'll be more than happy to show 0.07 0.08 0.09