Sampling

A manufacturing company's quality control personnel have recorded the proportion of defective
items for each of 500 monthly shipments of one of the computer components that the company produces.
See data below.

The company doesn't have time to review all of these.

(a) Use MS Excel to generate a random sample of 25 from the 500.
(b) Computer the point estimate of the mean.
(c) Compute a 90% confidence interval of the mean.
(d) Compute a 99% confidence interval of the mean.
(e) Chart the 90% and 99% confidence intervals using the stock chart (high-low-close).
(f) Repeat (c) through (e) for a random sample of 50.
(g) Comment on the differences in confidence intervals.

To answer these questions, you will need to calculate the random samples using MS Excel and perform some statistical calculations. Here's a step-by-step explanation on how to perform each task:

(a) Use MS Excel to generate a random sample of 25 from the 500:

1. Input the data of 500 monthly shipments into an Excel spreadsheet, with one column for the monthly shipments.

2. Next, select a new column and use the "RAND()" function to generate random numbers between 0 and 1 for each row. Type =RAND() in the first cell of the new column and drag the formula down to generate random numbers for the entire column.

3. Sort the data by the random column in ascending order.

4. Take the first 25 rows of the sorted data as your random sample.

(b) Compute the point estimate of the mean:

1. Calculate the mean of the random sample obtained in step (a). To do this, use the "AVERAGE()" function in Excel. Simply select the range of data in your random sample, and the function will provide the average value.

(c) Compute a 90% confidence interval of the mean:

1. Calculate the sample standard deviation of the random sample obtained in step (a). To do this, use the "STDEV.S()" function in Excel. Select the range of data in your random sample, and the function will provide the sample standard deviation.

2. Calculate the margin of error using the formula: Margin of Error = Critical Value * Standard Deviation / Square Root of Sample Size. The critical value depends on the confidence level, and you can find it using a t-table or a statistical calculator. For a 90% confidence level, the critical value is approximately 1.96 (for large samples).

3. Compute the confidence interval using the formula: Confidence Interval = Mean ± Margin of Error. In this case, the Mean is the point estimate of the mean calculated in (b), and the Margin of Error is calculated in step (c).

(d) Compute a 99% confidence interval of the mean:

1. Repeat steps (c)(1) and (c)(2), but with a critical value of approximately 2.58 for a 99% confidence level.

2. Compute the confidence interval using the same formula as in step (c)(3).

(e) Chart the 90% and 99% confidence intervals using the stock chart (high-low-close):

1. Create a stock chart in Excel using the high-low-close format.

2. Plot the mean as the close value, the upper limit of each confidence interval as the high value, and the lower limit of each confidence interval as the low value.

(f) Repeat (c) through (e) for a random sample of 50:

1. Repeat steps (a) through (e) with a random sample of 50, instead of 25, to compute the confidence intervals and chart them.

(g) Comment on the differences in confidence intervals:

1. Compare the confidence intervals calculated in step (c) and (f) for both the 90% and 99% confidence levels. Note any differences in the width or coverage of the intervals.

2. Comment on how the confidence intervals change with the increase in sample size and the different confidence levels.

Remember, to perform these calculations accurately, it's important to have a good understanding of statistical concepts and use appropriate formulas and functions in Excel.

What kind of HELP do you need? You need to be specific when asking questions here.

If all you do is post your entire assignment, with no evidence of thinking on your part, nothing will happen since no one here will do your work for you. But if you are specific about what you don't understand about the assignment or exactly what help you need, someone might be able to assist you.