Using the Data files of your textbook, extract the Excel data relating to 2500 managers of Electronic Associates (EAI) of Chapter 7. Save this information in an Excel file on the desktop of your computer. You can find the mean and standard deviationof the population of EAI managers as 51,800 and 4000 respectively as shown in the textbook. Assume that this population of EAI managers is distributed as Normal. (Note: I will post all the Data files from the book in the course information section of the Blackboard, but YOU DON’T NEED IT FOR THIS EXERCISE as the mean and standard deviation have already been estimated)
Answer the following with this population data.
- Using the Data Analysis tool pack in Excel;
- Select Data from tabs
- Select Data Analysis
- Select Random Number Generation
- Then hit “OK” and fill in the necessary field.
Draw 50 random samples of size 30 each from this population using the mean of 51,800 and standard deviation of 4000 stated above. In Excel that we use, the number of variables is same as the number of samples, and the number of random numbers is same as the sample size. Input last two digits of your ID in Random Seed row.
- Compute 90% confidence interval for the population mean for each of the 50 random samples, assuming that the population standard deviation is unknown (use the sample standard deviation as an estimate of population standard deviation as in section 8.2 of Chapter 8). Put “X” mark in the row next to the interval for those intervals that do not contain the population mean. Compute the percentage of the X marked intervals and reconcile with the interpretation of 90% confidence level. Then answer three questions:
- What does the theory say?
- What do you find from your results?
- Explain the difference. Even if you get the same result as per the theory, does it happen again if you were to repeat the estimation with the same number if samples? If not explain why?
Hints:
- As per the theory, the number of intervals that contain the population mean should be 45 out of 50 giving you 90%.
- To answer C, think about explaining 50% probability of tossing a head or a tail in tossing a coin. What does it take to realize this 50% rule? Does it happen if we toss the coin 50 times?
Note: In answering question 2, compute the following items for the very first sample in column 1:
- Calculate the mean.
- Calculate the standard deviation. To find the standard deviation of the sample with Excel, use stdev.s
- Calculate the standard error as (s/
) where s stands for the sample standard deviation (Same as s/SQRT(30).
- Find z by using the function key; use NORM.S.INV(0.0.5) function
e) Find margin of error as (M.E) z*standard error (s/).
f) Lower Confidence Limit (L.C.L) = sample mean- M.E
g) Upper Confidence Limit (U.C.L) = sample mean + M.E
h) Copy the formula to calculate each item (a-g) for the other 49 samples.
i) Finally, find the number of confidence intervals that contain the population mean of 51,800 out of these 50 samples. In cases where the intervals don’t contain 51,800, put an X mark in the cell below.
To minimize the whole Excel spreadsheet to two or three pages,
- Go to File button
- Then go to Print on the left menu
- Go to Settings
- Change portrait orientation to Landscape Orientation
- Click No Scaling to open the scaling menu, then click Custom Scaling. Fit to: 2 page(s) wide by 1 tall
- Click OK.
- Upload through the submission link on Blackboard after providing answers to the questions A, B and C highlighted in 2) above.
