G39 4 X V fx B C D E F G H K L M N O P Q R S T U V W X C The data on the worksheet titled "Data" contains two variables, Column B is Curb Weight, the weight of a vehicle in lbs. and Column C is Highway Mileage (miles per gallon). 10 The lower the mpg the less fuel efficient is a vehicle. 11 Use the vehicle data to answer the following questions. 12 You are welcome to organize your answers as you wish. I must be able to easily follow your answers and will grade according to the point scale in columns B and C. 13 Full points requires that your Excel output tables are visible and that answers are easy to follow. 14 15 Points Your Points Question 16 0.5 1 Which is the dependent and independent variable? 17 1 2 Construct a scatter diagram with the independent variable on the x axis. Label your graph (give it a title), label the x and y axis and make sure units are specified for the y variable. 18 0.5 What is the trend of the scatter plot? Can you see a trend? Positive? Negative? 19 0.5 UI A W What is the correlation coefficient, r? Make sure I can see your Excel formula. 20 0.5 Is r strong or weak? 21 0.5 6 Is r positive or negative? 22 0.5 7 What does this r mean for the relationship between curb weight and mileage? 23 0.5 8 Run a regression of the dependent and independent data (use Data Analysis, include data headings and check the Headings box). 24 0.5 9 What is the numeric value of bo? 25 0.5 10 What is the numeric value of b1? 26 11 What is the estimated equation of the line? Write out the numeric equation. 27 N 12 What is the interpretation of bO? Write out the answer as a full sentence. 28 N 13 What is the interpretation of b1? Write out the answer as a full sentence. 29 0.5 14 What is the numeric value of R squared? 30 1 15 How do you interpret R squared in words? What does it mean in terms of how well the independent variable explains the dependent variable? 31 0.5 16 Is the model statistically significant? F-test? Yes or no, and how do you know? 32 0.5 17 Are the coefficients bo and b1 statistically significant? Yes or no, and how do you know? 33 0.5 18 Regression analysis can be used to predict values. If the value of curb weight is 2,200 lbs., what is the predictive vehicle mileage (mpg)? Show your work in Excel, show how you arrived at your answer. 34 0.5 19 If the value of curb weight is 3,200 lbs., what is the predictive vehicle mileage (mpg)? Show your work in Excel, show how you arrived at your answer. 35 1 20 This is a simple model with one independent variable. What other independent variables might explain your dependent variable? 36 15 37Highway mileage Curb Weight (miles per gallon, (Lbs.) mpg) 3210 27 3426 28 3050 29 3020 28 3220 30 2860 30 3220 30 3220 28 3160 30 3320 27 3650 25 3860 24 3450 27 3700 26 3200 22 3900 22 3560 20 3240 24 3520 23 4550 18 4750 18 4480 21 4610 18 3016 30 3650 22 3448 22 3264 28 3420 20 4008 19 3116 22
