LAB12 Instructions and Questions for Excel 2003 CONFIDENCE INTERVALS FOR POPULATION PROPORTION NAME LAB TIME LAB BLDG 1 In this lab we will examine the procedure for estimation of the proportion of a population which would favor a certain issue 2 To estimate the population proportion a random sample of people is selected and each person is asked whether he she supports the issue in question 3 The sample proportion P HAT number of yes answers total number of answers 4 The formula for the Confidence Interval for the population proportion is P HAT Z SQRT P HAT 1 P HAT N 5 6 PART A SETTING UP YOUR SPREADSHEET Use rows 2 and 3 for column headings as follows Column A Leave blank Column B Sample size Column C Confidence Level Column D Z Column E P HAT Column F Margin Of Error Column G Confidence Interval Column H Leave Blank Column I Interval Width 7 Lets pretend that a sample of 1000 people is taken and 500 people answer yes and 500 people answer no on the issue in question Calculate a 95 Confidence Interval for the population proportion as follows In B5 enter 1000 In C5 enter 95 and format cell to display a percentage with 0 decimal places In D5 enter value of Z for 95 confidence 1 960 In E5 enter the value of P HAT 0 5 In F5 enter the formula D5 SQRT E5 1 E5 B5 In G5 enter the formula for the low limit E5 F5 In H5 enter the formula for the high limit E5 F5 In I5 enter the formula for the interval s width H5 G5 8 9 For n 1000 P HAT 0 5 the 95 Confidence Interval is 10 The margin of error in the above calculation is 11 PART B EFFECT OF THE CONFIDENCE LEVEL ON THE MARGIN OF ERROR AND THE WIDTH OF THE CONFIDENCE INTERVAL Enter 1000 in B8 through B12 to hold sample size constant at 1000 Enter 0 5 in E8 through E12 to hold p hat constant at 0 5 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 Enter the confidence levels given below in C8 through C12 Enter the corresponding values of Z in D8 through D12 80 1 282 90 1 645 95 1 960 99 2 576 99 9 3 291 Copy the formulas in F5 I5 down to F8 I8 The copy them down through line 12 Use X Y Scatterplot to plot the margin of error on Y axis vs confidence level on X axis and place the graph in the spreadsheet using the space J8 P23 A 99 confidence interval for the population proportion would be wider or narrower than a 90 confidence interval PART C EFFECT OF SAMPLE SIZE ON THE MARGIN OF ERROR AND THE WIDTH OF THE CONFIDENCE INTERVAL In B25 through B29 enter the sample sizes 500 1000 2000 4000 and 8000 We will use 95 confidence with z 1 96 and we will use P HAT 0 5 Enter these values in C25 D25 and E25 and then copy them down through line 29 Copy the formulas from F12 I12 to F25 I25 Then copy them down through line 29 Use X Y scatterplot to plot the margin of error y axis vs the sample size x axis and place the graph in the spreadsheet using the space J25 P39 As the sample size increases the margin of error and the width of the confidence interval 1 Increases 2 Decreases To cut the margin of error in half the sample size must be by a factor of PART D EFFECT OF THE PROPORTION ON THE MARGIN OF ERROR AND THE WIDTH OF THE CONFIDENCE INTERVAL We will hold the sample size constant at 1000 and the confidence level constant at 95 Enter 1000 in B41 95 in C41 1 96 in D41 and copy these values down through line 53 In Column E on lines 41 through 53 enter the following values of P HAT 005 05 1 2 3 4 5 6 7 8 9 95 995 Copy the formulas in F29 I29 and paste them in F41 I41 Then copy them down through line 53 29 30 31 32 33 34 35 36 37 38 Use X Y Scatterplot to plot the margin of error y axis vs P HAT x axis and put the graph in the spreadsheet using the space J41 P57 What value of the proportion produces the maximum margin of error PART E EFFECT OF THE PROPORTION ON THE SAMPLE SIZE REQUIRED TO OBTAIN A DESIRED MARGIN OF ERROR In C59 enter 95 and in D59 enter 1 96 Enter 03 in F59 Then copy these values down through line 71 Enter the following values for the proportion in Column E lines 59 71 005 05 10 20 30 40 50 60 70 80 90 95 995 In B59 enter the formula to calculate the sample size E59 1 E59 D59 F59 2 Copy the formula in B59 down through B71 Use X Y scatterplot to plot the sample size y axis vs the proportion x axis and put the graph in the spreadsheet using the space J59 P71 Print the spreadsheet from A1 to P71 on one page and turn in with your answers The spreadsheet should have four graphs on it What value of the proportion requires the largest sample size in order to maintain a margin of error of 03