OBJECTIVES:

• To use Excel to calculate test statistics and p-values when comparing two sample proportions
• To use Excel to calculate p-values when comparing two sample means

To use the p-value method to make conclusions.

To use confidence intervals to determine if proportions from two populations are different.

To use confidence intervals to determine if means from two populations are different

Part 1: Two Proportions

Experiment 1:

Of 38 randomly selected managers of mid-size stores, 21 report logging more than 7 hours of overtime each week.Of 45 randomly selected managers of small-stores, 36 reported logging more than 7 hours of overtime per week.Is there evidence at the .10 level of significance to suggest that the proportion of mid-size store managers that log more than 7 hours/week is different than the proportion of small-size store managers that log more than 7hours/week?

a.Ho:

Ha:

Excel Procedure:

1.In cell A1 through A8 type x1, x2, n1, n2, phat1, phat2, pbar, and qbar.

2.In cell B1 through B4 enter the corresponding values for the variables in column A.

3.In cell B5 enter =B1/B3 and in cell B6 type =B2/B4.

4.In cell B7 enter =(B1+B2)/(B3+B4) and in cell B8 type =1-B7.

5.In cell D1 type Z and in cell D2 type Area to the left of Z.

6.In cell E1 type =(B5-B6)/SQRT(B7*B8/B3+B7*B8/B4).This will calculate the test statistic Z for this hypothesis test.

7.Select cell E2, then Formulas (the tab at the top of the Excel window)->More Functions->Statistical->NORM.S.DIST.

8.When the Function Arguments window opens select (or enter) cell E1 for Z.

9.In the Cumulative field type TRUE.This gives the area to the left of Z in the standard normal curve.

b.Test Statistic: __________________

c.Area left of z:______________

d.P-value: __________________ (adjust your answer in part c) to match the tail(s) and check with your TI-84)

e.Conclusion (explain why and interpret in context).

Copy and paste the table of results from your Two Sample Proportions hypothesis test to the end of this lab.

Experiment 2:

Of 38 randomly selected managers of mid-size stores, 21 report logging more than 7 hours of overtime each week.Of 45 randomly selected managers of small-stores, 36 reported logging more than 7 hours of overtime per week.Construct the Confidence Interval to determine if the mid-size store managers that log more than 7 hours/week is different than the proportion of small-size store managers that log more than 7hours/week.

a.Mid-size store managers sample proportion:___________

b.Small-size store managers sample proportion: ___________

c.Use the significance level (0.10) from Experiment 1 to determine the Confidence Level needed.

Excel Procedure:

1.In cell A10 through A16 type x1, x2, n1, n2, Confidence Level, phat1, and phat2.

2.In cell B10 through B16 enter the corresponding values for the variables in column A (be sure to enter your Confidence Level as a decimal).

3.In cell B15 enter =B10/B12 and in cell B16 type =B11/B13.

4.In cell D10 through D14 type Zc, ME, Samp Diff, CI Low, and CI High.

5.In cell E10 type =ABS(

6.Select Formulas (tab from the top of the Excel window)->More Functions->Statistical->NORM.S.INV.

7.In the probability field type (1-B14)/2 and select OK.

8.Close your ) for the ABS function.

9.In cell E11 type =E10*SQRT(B15*(1-B15)/B12+B16*(1-B16)/B13).

10.In cell E12 type =B15-B16.

11.In cell E13 type =E12-E11.

12.In cell E14 type =E12+E11.

d.Confidence Interval: _________________________________

e.Interpret the Confidence Interval in context.

f.Conclusion (explain why and interpret in context).

g.Is this conclusion consistent with your conclusion in Experiment 1?______Explain.

Part 2: Two Means

Open the Lab 12 Excel Spreadsheet in Blackboard with data for this lab.

Experiment 1:

Data is collected from a random sample of 16 UPS truck loaders working without music on and then with music on, to see if music improves the average speed of loading boxes (boxes per hour - bph).It can be assumed that the box loading speeds of UPS truck loaders are normally distributed.See Excel spread sheet for data and test the claim at a 0.03 level of significance.Let ₁=without music

a.Ho:

Ha:

b.Are these samples independent or dependent?Circle one.

If dependent are they matched or paired?Circle one.

Excel Procedure:

1.In cell C5 type p-value.

2.Select cell C6.

3.Select Formulas (tab at top of Excel window)->More Functions->Statistical->T.Test.

4.In the Array1 field select the data for BPH without music and in the Array 2 field select the data for BPH with music.

5.Enter the appropriate number of tails in the Tails field based on your stated Ha.

6.Enter 1 in the Type field to indicate a Matched or Paired test (NOTE: Excel does not distinguish between paired and matched data since the test is the same for each).

c.P-value: __________________

d.Conclusion:

e.Should UPS play music or not?Explain.

Experiment 2:

A shoe store is running a Fathers Day Special that will give a Father and Son an extra 20% discount if they each purchase a new pair of shoes and the sons foot is smaller than the fathers.Shoe sizes are collected from a random sample of 8 fathers and their adult sons to determine if the mean shoe size for fathers is significantly smaller than the average shoe size of the sons.It can be assumed that the shoes sizes of both the fathers and the sons are normally distributed.See Excel spread sheet for the sample data and test the claim at a 2% level of significance.

a.Ho:

Ha:

b.Are these samples independent or dependent?Circle one.

If dependent are they matched or paired?Circle one.

Excel Procedure:

1.In cell G5 type p-value.

2.Select cell G6.

3.Select Formulas (tab at top of Excel window)->More Functions->Statistical->T.Test.

4.In the Array1 field select the data for Fathers and in the Array 2 field select the data for Sons.

5.Enter the appropriate number of tails in the Tails field based on your stated Ha.

6.Enter 1 in the Type field to indicate a Matched or Paired test (NOTE: Excel does not distinguish between paired and matched data since the test is the same for each).

c.P-value: __________________

d.Conclusion:

e.Will the shoe store be more likely to award the extra 20% discount based on your conclusion?

Experiment 3:

Fifteen female CSCC students and 10 male CSCC students are randomly selected to determine if the mean time studying for females is significantly different than the mean study time for males. It can be assumed that all study times are normally distributed. See Excel spread sheet for the sample data and test the claim at a 0.05 level of significance.

a.Ho:

Ha:

b.Are these samples independent or dependent?Circle one.

If dependent are they matched or paired?Circle one.

Excel Procedure:

1.In cell K5 type p-value.

2.Select cell K6.

3.Select Formulas (tab at top of Excel window)->More Functions->Statistical->T.Test.

4.In the Array1 field select the data for the males and in the Array 2 field select the females.

5.Enter the appropriate number of tails in the Tails field based on your stated Ha.

6.Enter 3 in the Type field to indicate a test with unequal variance (NOTE: we will always assume unequal variance unless specified or tested for).

c.P-value: __________________

d.Conclusion:

Copy and paste the table of results from your three Two Sample Means hypothesis tests to the end of this lab.