Used Jaguars Uncle Bob owns a used car business that sales solely used Jaguars. He wants to develop a regression model to help predict the price he can expect to receive for the cars he owns. He collected the data on the table below describing the mileage, model year, and selling price of a number of cars he has sold in recent months. Let Y represent the selling price, X, the mileage, X2 the model year. The table at the end of this document shows a sample data. 1. Answer the following questions based on logical reasoning. Do no calculations in answering this question. a) Would you expect that the mileage and the selling price would be positively correlated, and a linear relation seems reasonable? Briefly explain, based on logical reasoning. b) Would you expect that the model year and the selling price would be positively correlated, and a linear relation seems reasonable? Briefly explain, based on logical reasoning. Do no calculations in answering this question. c) Would you expect the mileage and the model year to be correlated? 2. Paste the table of data from the end of this document into Excel. For the relationship between each independent variable and the dependent variable, use Excel to: Create the scatter plot. Graphically display the trend line and R2. Create the regression report. Remove the section ANOVA from your report. Move the graph into the report, to form a bloc. Guidelines for graphs: the graph should include appropriate axes, axis labels and title. Graph size, axis range, and presentation should be appropriate. . 3. Create the regression report for the model with two independent variables. The three reports (the first two forming a block with the associated graph) should be well proportionate an aligned from the top to the bottom of the page. 4. If Uncle Bob want to use of a simple linear regression function to estimate the selling price of a car, what X variable would you recommend? Justify your answer. 5. Is there evidence from this regression that X2 (model year) help explains Y (selling price) if X1 (mileage) is also in the model? What might the reason be? 6. Explain what the coefficient of X means. 7. Use this model to create an approximate prediction interval for how much a car with 90,000 miles, model year of 1980 will be sold for. Putting your report together: You need to submit and Excel file containing two (2) spreadsheets. . On the first sheet named Q&A copy and paste the QUESTIONS and ANSWERS in this report. On the second sheet named Report, Excel spreadsheet containing the data at the left side of page, regression output and graphs at the right side. Obs 1 2 3 4 5 6 7 8 9 10 11 12 13 Mileage 115 95 125 85 77 105 88 Year 1968 1970 1972 1974 1976 1978 1979 1981 1983 1987 1988 1988 1991 Price 13875 11000 8000 14950 15625 11300 13250 16500 16500 19500 22300 25500 31900 73 55 65 45 15 23 Used Jaguars Uncle Bob owns a used car business that sales solely used Jaguars. He wants to develop a regression model to help predict the price he can expect to receive for the cars he owns. He collected the data on the table below describing the mileage, model year, and selling price of a number of cars he has sold in recent months. Let Y represent the selling price, X, the mileage, X2 the model year. The table at the end of this document shows a sample data. 1. Answer the following questions based on logical reasoning. Do no calculations in answering this question. a) Would you expect that the mileage and the selling price would be positively correlated, and a linear relation seems reasonable? Briefly explain, based on logical reasoning. b) Would you expect that the model year and the selling price would be positively correlated, and a linear relation seems reasonable? Briefly explain, based on logical reasoning. Do no calculations in answering this question. c) Would you expect the mileage and the model year to be correlated? 2. Paste the table of data from the end of this document into Excel. For the relationship between each independent variable and the dependent variable, use Excel to: Create the scatter plot. Graphically display the trend line and R2. Create the regression report. Remove the section ANOVA from your report. Move the graph into the report, to form a bloc. Guidelines for graphs: the graph should include appropriate axes, axis labels and title. Graph size, axis range, and presentation should be appropriate. . 3. Create the regression report for the model with two independent variables. The three reports (the first two forming a block with the associated graph) should be well proportionate an aligned from the top to the bottom of the page. 4. If Uncle Bob want to use of a simple linear regression function to estimate the selling price of a car, what X variable would you recommend? Justify your answer. 5. Is there evidence from this regression that X2 (model year) help explains Y (selling price) if X1 (mileage) is also in the model? What might the reason be? 6. Explain what the coefficient of X means. 7. Use this model to create an approximate prediction interval for how much a car with 90,000 miles, model year of 1980 will be sold for. Putting your report together: You need to submit and Excel file containing two (2) spreadsheets. . On the first sheet named Q&A copy and paste the QUESTIONS and ANSWERS in this report. On the second sheet named Report, Excel spreadsheet containing the data at the left side of page, regression output and graphs at the right side. Obs 1 2 3 4 5 6 7 8 9 10 11 12 13 Mileage 115 95 125 85 77 105 88 Year 1968 1970 1972 1974 1976 1978 1979 1981 1983 1987 1988 1988 1991 Price 13875 11000 8000 14950 15625 11300 13250 16500 16500 19500 22300 25500 31900 73 55 65 45 15 23