See attached files.
QMTH 205 Lab 3: Probability Distribution and Interval Estimation
Purpose: Students will learn (1) how to make a table of standard normal distribution; (2) how to use Excel to select a simple random sample; (3) how to use Excel built-in functions and procedures to estimate population mean and population proportion. Please turn in your printouts and a CD with file lab3.xls. Penalty is possible for late work.
Download Excel file lab3.xls from http://faculty.winthrop.edu/caoq.
Part I. Make Standard Normal Distribution Table
Refer to the standard normal distribution table in textbook. Use function NORMSDIST(z) to make the standard normal distribution table. The function NORMSDIST returns a cumulative probability value of P(-  < Z < z). You will do the following to complete the standard distribution table.
(1) Enter a formula in a cell B10. You may start with fx button and select the function NORMSDIST. When you enter addresses, pay attention to Absolute Reference and Relative Reference so you can correctly copy this formula to the rest of the table.
(2) Copy the formula to other cells to complete the table.
Part II. Random Sampling with Excel
The population includes 110 students and their test scores shown in worksheet "Sampling".
1. Enter formulas to compute the population mean score, population stardard deviation (stdevp()) and the population size.
2. Select a simple random sample of 4 students.
(1) Enter =Rand() in cell C4 and copy it down for all students.
(2) Sort Students and Scores by Random Number. The 4 students at top make a random sample.
3. Enter the sample size and enter formulas to compute the sample mean score and sample standard deviation.
Part III. Interval Estimation of Population Mean
1. The  known case (worksheet "Sigma Known Case")
A sample of 45 customers is collected and the satisfaction scores are shown in worksheet "Sigma Known Case". The population standard deviation is 12. For the  known case, the Excel function CONFIDENCE(, , n) computes the margin of error. Construct a 95% confidence interval estimate of population mean (confidence level 1- = 95%): enter Excel formulas to compute sample mean score, sample size, margin of error, lower and upper limits (Upper Limit = the sample mean + the margin error and Lower Limit = the sample mean - the margin error) of confidence interval.
2. The  unknown case (worksheet "Sigma Unknown Case")
For the  unknown case, you use Excel built-in data analysis procedure "Descriptive Statistics" to estimate population mean. You use the random sample of 4 students you took in worksheet "Sampling" to estimate population mean test score. Run Excel built-in procedure "Descriptive Statistics" to develop a 90% confidence interval estimate for the population mean score. Show label "Score" in output. Report sample size, sample mean, margin of error, lower limit and upper limit of confidence interval. You will see two numbers from the output of "Descriptive Statistics" procedure: the sample mean in the row of "Mean" and the margin of error in the row of "Confidence Level (90%)". We have Upper Limit = the sample mean + the margin error and Lower Limit = the sample mean - the margin error. Enter formulas to compute the upper confidence limit and lower confidence limit of your estimation.
Part IV. Small program for interval estimation of population proportion
A sample of 600 responses is shown in worksheet "Proportion". Develop a 90% confidence interval estimation for proportion of "Yes". Then,
(1) Enter "Response of Interest" (Yes), and Excel formulas for "Count for Response" (COUNTIF()) and "Sample Size" (COUNTA()).
(2) Enter an Excel formula to compute the sample proportion. Enter the confidence level.
The Excel formulas in the protected cells give out values of Z, margin of error, upper limit and lower limit of confidence interval.
A Complete, Neat and Step-by-step Solution is provided in the attached file.