________________ ________________ ______________ 11. Using Excel – Scatter diagrams, estimated regression equations, and trendlines Suppose a…

________________

________________

______________

11. Using Excel – Scatter diagrams, estimated regression equations, and trendlines Suppose a company records data on sales calls, induding the length of each call and whether a sale was made. The manager is interested in determining whether there is a relationship between the average time spent per call and the number of sales made by each employee, so she obtains the average call length and the total number of sales over a 2-week period for a random sample of 30 employees. To answer the questions that follow, download an Excel@ spreadsheet containing the given data by dicking on the following words in bold: Download Excel File. The upper left-hand corner of the Excel spreadsheet in the file you download should look like the following sample spreadsheet. A 1 Employee B Average Length of Calls (Minutes) 2.3 C D Sales 50 2 3 2 5.4 57 4 3 1.1 41 5 4 3.2 54 O 5 54 7 6 1.4 38 Construct a scatter diagram using Excel that shows the average length of calls versus the number of sales. Of the following, select the chart that best matches your results. 70 Number of Sales Number of Sales 70 Number of Sales 60 60 60 50 50 50 40 40 40 30 30 30 0 0 2. 6 Average Call Length (Minutes) Average Call Length (Minutes) 0 Average Call Length (Minutes) O Add a linear trendline to your scatter diagram and include the estimated regression equation and coefficient of determination. From your scatter diagram, the estimated regression equation is for an employee and x is the value of the which means of the variability in the X + where is the predicted value of the The coefficient of determination is can be explained using the estimated regression equation. 12. Using Excel – Obtaining estimates for a simple linear regression To answer the questions that follow, download an Excel spreadsheet containing the demographic data for a sample of 30 adults by dicking on the following words in bold: Download Excel File. Use Excel to obtain an estimated regression equation predicting the value of income from number of children. (Note: Use the income (in $1,000s] variable, not the income category variable.) The regression equation is 9 – X, where is the predicted value of and x is the value of You believe there is a linear relationship between number of children and income. You conduct a hypothesis test with the hypothesis Ho: 0 – 0 versus the alternative hypothesis Ha: B 0. Based on these results, with a significance level of 0 – .05, you reject the hypothesis. You conclude that number of children and income are linearly related. 1 ID 2 1 3 4 5 6 7 8 9 10 A B gender marital status 1001 female married 1002 male married 1003 male married 1004 male never married 1005 female widowed 1006 female never married 1007 female married 1008 female divorced 1009 female married 1010 male married 1011 male married 1012 male separated 1013 male divorced 1014 female divorced 1015 female married 1016 male married 1017 female married 1018 male married 1019 male divorced 1020 male never married 1021 male divorced 1022 female separated 1023 male married 1024 female married 1025 male widowed adult demographics1 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 D E F number of children age (year age category 2 39.5 31-40 62.5 61 or older 4 25.6 26-30 0 41.3 41-50 2. 44.2 41-50 0 64.3 61 or older 3 54.0 51-60 2. 40.3 41-50 0 58.7 51-60 2 29.1 26-30 1 45.8 41-50 2 46.2 41-50 0 43.2 41-50 3 40.6 41 – 50 6 35.0 31-40 2 39.0 31-40 32.2 31-40 0 27.6 26-30 1 55.2 51 – 60 1 41.9 41-50 0 39.6 31-40 5 32.4 31-40 45.9 41.50 3 47.6 41-50 3 31.2 31.40 G H income income category 116.4 $100K – $149K 77.5 $60K – $79K 95.5 $BOK – $99K 129.9 $100K – $149K 128.0 $100K – $149K 188.7 $150K 68.7 S60K – $79K 39.6 $20K – $39K 97.6 $80K – $99K 41.6 $40K – $59K 259.8 > $150K 174.1 >$150K 162.3 > $150K 132.2 $100K – $149K 61.1 $60K – $79K 71.2 $6OK – $79K 111.5 $100K – $149K 69.0 $60K – $79K 228.3 > $150K 81.5 SBOK – $99K 101.5 $100K – $149K 68.8 $60K – $79K 102.3 $100K $149K 158.7 > $150K 24.5 $20K $39K education years of schooling 2 yr college 17 4 yr college 17 post-gradus 19 4 yr college 18 2 yr college 15 high school 13 high school 13 2 yr college 14 less than hi 10 high school 14 4 yr college 17 post-gradu 21 4 yr college 19 4 yr college 20 less than hi 11 some colleg 15 post-gradu: 20 some colleg 15 post-gradu 22 high school 14 2 yr college 17 4 yr college 16 4 yr college 17 post-gradu 18 less than hi 13 2 2 1 2 0 3 6 2 2 0 1 1 1011 male 1012 male 1013 male 1014 female 1015 female 1016 male 1017 female 1018 male 1019 male 1020 male 1021 male 1022 female 1023 male 1024 female 1025 male 1026 male 1027 female 1028 female 1029 male 1030 female married separated divorced divorced married married married married divorced never married divorced separated married married widowed never married married divorced never married married 45.8 41-50 46.2 41 – 50 43.2 41-50 40.6 41 – 50 35.0 31-40 39.0 31 – 40 32.2 31-40 27.6 26-30 55.2 51 – 60 41.9 41 – 50 39.6 31 – 40 32.4 31 – 40 45.9 41 – 50 47.6 41 – 50 31.2 31-40 43.141 – 50 32.8 31 – 40 49.4 41 – 50 45.7 41 – 50 22.8 22-25 259.8 > $150K 174.1 > $150K 162.3 > $150K 132.2 $100K – $149K 61.1 $60K – $79K 71.2 $60K – $79K 111.5 $100K – $149K 69.0 $60K – $79K 228.3 > $150K 81.5 $80K – $99K 101.5 $100K – $149K 68.8 $60K – $79K 102.3 $100K – $149K 158.7 > $150K 24.5 $20K – $39K 70.4 $60K – $79K 54.2 $40K – $59K 122.3 $100K – $149K 101.9 $100K – $149K 18.6 <$20K 4 yr college post-gradua 4 yr college 4 yr college less than hi some colleg post-gradua some colleg post-gradus high school 2 yr college 4 yr college 4 yr college post-gradu: less than hi some college some college some colleg high school some colleg 0 5 2 3 3 0 4 2 0 0 13. Testing a population mean - The test statistic You conduct a hypothesis test about a population mean u with the following and alternative hypotheses: He: H: - 15.7 15.7 Suppose that the population standard deviation has a known value of a - 13.5. You obtain a sample of n - 89 observations, which provides a sample mean of - 18.1. Since the sample size large enough, you assume that the sample mean x follows a normal distribution. Let o denote the hypothesized value of the population mean u when the hypothesis is true as an equality. Click on the blue dropdown menus that follow, and select the appropriate expressions to obtain the correct formula for the test statistic for your hypothesis test: Suppose that the population standard deviation has a known value of a = 13.5. You obtain a sample of n - 89 observations, which provides a sample mean of 18.1. Since the sample size large enough, you assume that the sample mean I follows a normal distribution. Let e denote the hypothesized value of the population mean u when the hypothesis is true as an equality. Click on the blue dropdown menus that follow, and select the appropriate expressions to obtain the correct formula for the test statistic for your hypothesis test: When the hypothesis is true as an equality, the test statistic for this hypothesis test follows which of the following distributions? O A normal distribution with mean 0 and standard deviation 1 O At distribution with 89 degrees of freedom O A normal distribution with mean 15.7 and standard deviation 13.5 O At distribution with 88 degrees of freedom The value of the test statistic is 14. Testing a population mean - Reaching a conclusion by the p-value approach You conduct a hypothesis test about a population mean w at a significance level of a -0.05 using a sample of size n = 54. The population standard deviation o is known, so you use the z test statistic. Your test statistic follows a standard normal distribution when the hypothesis is true as an equality, and its value obtained from the sample is z - 0.25. Use the Distributions tool to help you answer the questions that follow. Select a Distribution 10 Distributions 1 Select a Distribution Standard Normal t Distribution Distributions You the hypothesis in this case, because the p-value is If you perform an upper-tailed test, the p-value is than the significance level. You the hypothesis in this case, because the p-value is If you perform a two-tailed test, the p-value is than the significance level.