IndividualTelevision 1 22 2 8 3 25 4 22 5 12 6 26 7 22 8 19 9 21 10 23 11 14 12 14 13 14 14 16 15 24 Radio 25 10 29 19 13 28 23 21 21 23 15 18 17 15 23 This Document is strictly a demonstration to help you fine the answers to the next page. In this demo, we'll lean how to use Excel to perform a hypothesis test for two matched samples. In recent years, a growing array of entertainment options compete for consumer time. Researchers used a sample of 15 individuals and collected data on the hours per week spent watching cable television and hours per week spent listening to the radio. We want to test whether the average number of hours spent watching televison and radio differs using = 0.05. The hypotheses are H0: d = 0 Ha: d 0 To do this test in Excel: 1. Click "Data" -> "Data Analysis". In Excel 2003, click "Tools" -> "Data Analysis". 2. Select "t-test: Paired Two Sample for Means". 3. In the "Variable 1 Range" box, highlight the data for Television (include the variable name). 4. In the "Variable 2 Range" box, highlight the data for Radio (include the variable name). 5. In the "Hypothesized Mean Difference Box" enter 0. 6. Check the box next to "Labels" (you can also change the value of alpha here if you want). 7. Check next to "Output Range", and then select an empty cell in the spread sheet where you want your output to appear. Click on the tab "Excel Output" below to see the Excel results. 8. What is the test statistic? t = -2.35772 9. What is the p-value? p-value = 0.033467 (this is a two-tailed test) 10. What are the critical values? -2.145 and 2.145 (this is a two-tailed test) 11. What is your conclusion? Use = 0.05. Reject the null hypothesis. The average number of hours watching television and radio are different. t-Test: Paired Two Sample for Means Television Radio Mean 18.8 20 Variance 29.31429 29.42857 Observations 15 15 Pearson Correlation 0.933854 Hypothesized Mean 0 Difference df 14 t Stat -2.35772 P(T<=t) one-tail 0.016734 t Critical one-tail 1.76131 P(T<=t) two-tail 0.033467 t Critical two-tail 2.144787 This is also for demonstration purposes. <-the degrees of freedom <- the test statistic <- p-value for a one-tailed test (lower or upper tail) <- the critical value for an upper tail test (adding a minus sign gives the critical value for a lower tail test) <- p-value for a two-tailed test <- the positive critical value for a two-tailed test (adding a minus sign gives the negative critical value) Player Round 1 Round 4 Michael Letzig 70 72 Scott Verplank 71 72 D.A. Points 70 75 Jerry Kelly 72 71 Soren Hansen 70 69 D.J. Trahan 67 67 Bubba Watson 71 67 Reteif Goosen 68 75 Jeff Klauk 67 73 Kenny Perry 70 69 Aron Price 72 72 Charles Howell 72 70 Jason Dufner 70 73 Mike Weir 70 77 Carl Pettersson 68 70 Bo Van Pelt 68 65 Ernie Els 71 70 Cameron Beckman 70 68 Nick Watney 69 68 Tommy Armour III 67 71 Excel Project 5 This is the assignment that needs answering. This Excel Project contains 6 questions. Work out the answers to the questions and then click on "Excel Project 5" to submit your answers. **Give your answers to 2 decimal places!** This dataset contains scores in the first and fourth rounds for a sample of 20 golfers who competed in PGA tournaments in 2009. A sports fan suspects that scores in the first round are lower due to less pressure. You are to test whether this is true. 1. 2. 3. 4. 5. 6. State the null and alternative hypotheses (take round 1 scores - round 4 scores). Is this a lower tail, upper tail or two-tailed test? Use Excel to carry out the test. Use = 0.05. What is the value of the test statistic? What is the p-value? What is your conclusion using = 0.05? State your conclusion in words a non-statistician could understand. Bank of America's Consumer Spending Survey collected data on annual credit card charges in seven different categories of expenditures: transportation, groceries, dining out, household expenses, home furnishings, apparel, and entertainment. Using data from a sample of 42 credit card accounts, assume that each account was used to identify the annual credit card charges for groceries (population 1) and the annual credit card charges for dining out (population 2). Using the difference data, the sample mean difference was d = $850, and the sample standard deviation was sd = $1123. 1. Formulate the null and alternative hypotheses to test for no difference between the population mean credit card charges for groceries and the population mean credit card charges for dining out. H0: (population mean)d = 0 Ha: (population mean)d 0 The test statistic, t= d 850 = =4.905 s d / n 1123 / 42 2. Use a .05 level of significance. What is the p-value? The p-value is, P-value = 2P (T41 > t) = 2P (T41 > 4.905) = 0.0000 Can you conclude that the population means differ? Since P-value = 0.0000 < = .05, reject the null hypothesis. 3. Which category, groceries or dining out, has a higher population mean annual credit card charge? Since d = $850 is positive, it can be concluded that groceries, has a higher population mean annual credit card charge What is the point estimate of the difference between the population means? The point estimate of the difference between the population means is d = $850 What is the 95% confidence interval estimate of the difference between the population means (to the nearest whole number)? The 95% confidence interval of the population mean difference, d tn 1, 2 sD 1,123 850 t41,0.025 n 42 850 2.020 177.92 850 350.0 500,1200 The 95% confidence interval estimate of the difference between the population means is ($500, $1200). Bank of America's Consumer Spending Survey collected data on annual credit card charges in seven different categories of expenditures: transportation, groceries, dining out, household expenses, home furnishings, apparel, and entertainment. Using data from a sample of 42 credit card accounts, assume that each account was used to identify the annual credit card charges for groceries (population 1) and the annual credit card charges for dining out (population 2). Using the difference data, the sample mean difference was = $850, and the sample standard deviation was sd = $1123. 1. Formulate the null and alternative hypotheses to test for no difference between the population mean credit card charges for groceries and the population mean credit card charges for dining out. H0: (population mean)d = 0 Ha: (population mean)d 0 The test statistic, 2. Use a .05 level of significance. What is the p-value? The p-value is, P-value = 2P (T41 > t) = 2P (T41 > 4.905) = 0.0000 Can you conclude that the population means differ? Since P-value = 0.0000 < = .05, reject the null hypothesis. 3. Which category, groceries or dining out, has a higher population mean annual credit card charge? Since = $850 is positive, it can be concluded that groceries, has a higher population mean annual credit card charge What is the point estimate of the difference between the population means? The point estimate of the difference between the population means is = $850 What is the 95% confidence interval estimate of the difference between the population means (to the nearest whole number)? The 95% confidence interval of the population mean difference, sD 1,123 d tn 1, 2 850 t41,0.025 n 42 850 2.020 177.92 850 350.0 500,1200 The 95% confidence interval estimate of the difference between the population means is ($500, $1200). d-bar s-d n 850 1123 42 t 4.905 p-value 1.520409E-005 Critical value 2.020 Margin of error, E 350.0 Lower Limit 500.0 Upper Limit 1200.0 Bank of America's Consumer Spending Survey collected data on annual credit card charges in seven different categories of expenditures: transportation, groceries, dining out, household expenses, home furnishings, apparel, and entertainment. Using data from a sample of 42 credit card accounts, assume that each account was used to identify the annual credit card charges for groceries (population 1) and the annual credit card charges for dining out (population 2). Using the difference data, the sample mean difference was d = $850, and the sample standard deviation was sd = $1123. 1. Formulate the null and alternative hypotheses to test for no difference between the population mean credit card charges for groceries and the population mean credit card charges for dining out. H0: (population mean)d = 0 Ha: (population mean)d 0 The test statistic, t= d 850 = =4.905 s d / n 1123 / 42 2. Use a .05 level of significance. What is the p-value? The p-value is, P-value = 2P (T41 > t) = 2P (T41 > 4.905) = 0.0000 Can you conclude that the population means differ? Since P-value = 0.0000 < = .05, reject the null hypothesis. 3. Which category, groceries or dining out, has a higher population mean annual credit card charge? Since d = $850 is positive, it can be concluded that groceries, has a higher population mean annual credit card charge What is the point estimate of the difference between the population means? The point estimate of the difference between the population means is d = $850 What is the 95% confidence interval estimate of the difference between the population means (to the nearest whole number)? The 95% confidence interval of the population mean difference, d tn 1, 2 sD 1,123 850 t41,0.025 n 42 850 2.020 177.92 850 350.0 500,1200 The 95% confidence interval estimate of the difference between the population means is ($500, $1200). Bank of America's Consumer Spending Survey collected data on annual credit card charges in seven different categories of expenditures: transportation, groceries, dining out, household expenses, home furnishings, apparel, and entertainment. Using data from a sample of 42 credit card accounts, assume that each account was used to identify the annual credit card charges for groceries (population 1) and the annual credit card charges for dining out (population 2). Using the difference data, the sample mean difference was = $850, and the sample standard deviation was sd = $1123. 1. Formulate the null and alternative hypotheses to test for no difference between the population mean credit card charges for groceries and the population mean credit card charges for dining out. H0: (population mean)d = 0 Ha: (population mean)d 0 The test statistic, 2. Use a .05 level of significance. What is the p-value? The p-value is, P-value = 2P (T41 > t) = 2P (T41 > 4.905) = 0.0000 Can you conclude that the population means differ? Since P-value = 0.0000 < = .05, reject the null hypothesis. 3. Which category, groceries or dining out, has a higher population mean annual credit card charge? Since = $850 is positive, it can be concluded that groceries, has a higher population mean annual credit card charge What is the point estimate of the difference between the population means? The point estimate of the difference between the population means is = $850 What is the 95% confidence interval estimate of the difference between the population means (to the nearest whole number)? The 95% confidence interval of the population mean difference, sD 1,123 d tn 1, 2 850 t41,0.025 n 42 850 2.020 177.92 850 350.0 500,1200 The 95% confidence interval estimate of the difference between the population means is ($500, $1200). d-bar s-d n 850 1123 42 t 4.905 p-value 1.520409E-005 Critical value 2.020 Margin of error, E 350.0 Lower Limit 500.0 Upper Limit 1200.0