PRELIMINARY ACTIVITY:

A local car dealer would like to determine how to best price used cars on his lot. Go to craigslist.com or an appropriate website such as Carmax and select 20 Honda Civics from your area for sale (choose cars no more than 10 years old). For each car, determine the mileage on the car and the price of the car and record the results below.

Only select cars manufactured within the last 10 years.

Mileage (odometer)	Price ($)

1. Would you expect a positive or negative correlation between the model mileage and price?

1. Is mileage the independent or dependent variable?

EXCEL PROCEDURES:

1. In Excel, type "Mileage" in cell A1 and "Price" in cell B1. Enter the data you collected from the above table in columns A and B respectively.

1. Have Excel calculate the linear correlation coefficient (r), the coefficient of determination (r²), and the test statistic (t) for you:
2. In cell D1, type "r".
3. In cell D2, type "r-square".
4. In cell D3, type "n".
5. In cell D4, type "d.f.".
6. In cell D5, type "t".
7. In cell E1, find thecorrelation coefficient (r) by select the Formulas tab and clicking on the More Functions iconand selecting Statistical. Under the statistical menu select CORREL. You will see the Function Arguments dialog box open. In the Function Arguments dialog box select cells A2 through A21 for Array1 and select cells B2 through B21 for Array2. Click OK. The correlation coefficient will now be displayed.
8. In cell E2, find thecoefficient of determination (r²) by using the formula =E1^2.
9. In cell E3, find the sample size (n) by using the formula =COUNT(A2:A21).
10. In cell E4, find the degrees of freedom (d.f.) by using the formula =E3-2.
11. In cell E5, find the test statistic (t) by using the formula =(E1)/SQRT((1-E2)/(E4)).
12. Copy these cells HERE.

1. Have Excel produce a scatterplot for you:
   1. Click on cell A1 and drag through cell B21 to select the values to be graphed.
   2. In the Insert tab, click on Scatter in the Charts menu.
   3. Select the first chart in the Scattermenu.
   4. Click OK and your scatterplot will appear.

1. Have Excel add a linear regression line to the scatterplot you constructed above:
2. Click anywhere in the chart region of your scatterplot and notice that a new Chart Tools tab is added to the ribbon at the top of Excel.
3. Click on the Quick Layouttab at the top left and then click on Layout 9.

1. Click on the legend labels to the right of the plot area and delete the legend.
2. Edit the both axis labels with the appropriate data variables.
3. Click on the equation and move it to a location on the graph outside of the plot area so the equation is visible.
4. Right click on the equation and choose Format Treadline Label. Then change 'general' to 'number' so the equation is no longer in scientific notation.
5. Edit the chart title to include your name and "Price/Mileage".
6. Right click on the graph and select Copy.
7. Copy the scatterplot HERE.

ANALYSIS:

1. What is your regression equation for the best fit line? Be sure to confirm your equation from Excel with your TI-84.

1. Identify the values for the following from Excel.
   1. Slope of regression line:

1. y-intercept of regression line:

1. Coefficient of determination (r²):

1. Linear correlation coefficient (r):

1. Interpret the linear correlation coefficient (r).Does it agree with the strength and slope of your scatter plot?

1. Determine if there is a significant negative linear correlation at the 2.5% level of significance.
   1. State the null and alternate hypotheses to test for a significant negative linear correlation.

1. Determine the critical value and sketch (or describe) the rejection region.

1. Record the test statistic (t) from your Excel printout.

1. Use the traditional method or p-value method to make a conclusion, explain why, and interpret in context.

1. Based on your conclusion from #4d, is this regression equation appropriate for predicting the price of a Civic? If so, for which mileages is it appropriate?

1. Use the regression equation to determine the appropriate sale price for a Honda Civic with 50,000 miles on it.

1. Interpret the coefficient of determination (r²) in context. See Unit 11 for examples.