Question

here is the data i collected for questions #4-10 thabk you in advance.

Price/Mileage

1.Mustang 2006: $6500 – 134000 2. Toyota 2006: $3999 – 201101

3. Honda 2008: $6000 - 179950

4.Toyota 2011: $17500 – 137270

5. Honda 2004: $3595 -185000

6. Volkswagen 2006: $3699 - 197285

7.Lexus 2003: $4000 – 230000

8. Toyota 2003: $2500 – 180000 9.Porsche 2001: $18000 - 90000

10.BMW 2003: $4999 – 124389 11. Toyota 2008: $4800 – 184445 12. Chrysler 2008: $3750 - 186000

13.Kia 2011: $ 6000 – 104000

14. Chevrolet 2004: $5500 – 158650 15. Ford 2007: $6450 - 181000

16.Chevrolet 2004: $5999 – 114747

17.BMW 1999: $2500 – 108000 18. Jaguar 2002: $3500 - 113239

19.Audi 2003: $2900 – 125084 20.Nissan 2004: $4499 - 171708

need help with this LAB. please be as informative as possible with each questions. **How does used car mileage affect its predicted asking price?** 1.) state population? 2.) state variable being examined? 3.)would you expect a positive or negative correlation between mileage and price? 4.)Explain the processor you are going to use to collect data. you will need a sample size of at least 20, 5.)Report data into a table 6.)run a linreg test in the form of y-a+bx 7.)state slope and how your slope would affect the projected price of the car? 8.) state correlation. is it strong or weak 9.)explain some variable that go into setting price of a car? 10.)can we conclue that the study states that mileage causes the price of a used car to go down? why or why not?

Answer 1

Question 5 and Question 6

Step 1 - Put the data in excel as shown and arrange the variables as shown

Price Milage 1 sr.no Car Model 2 1 Mustang 2006 2 Toyota 2006 3 Honda 2008 4 Toyota 2011 5 Honda 2004 6 Volkswagen 2006 7 Lexus 2003 8 Toyota 2003 6500 134000 3999 201101 6000 179950 17500 137270 3595 185000 3699 197285 4000 230000 2500 180000 18000 90000 4999 124389 4800 184445 3750 186000 6000 104000 5500 158650 6450 181000 5999 114747 2500 108000 3500 113239 2900 125084 4499 171708 4 6 8 10 9 Porsche 2001 11 1O BMW 2003 12 11 Toyota 2008 13 12 Chrysler 2008 14 13 Kia 2011 15 14 Chevrolet 2004 16 15 Ford 2007 17 16 Chevrolet 2004 18 17 BMW 1999 19 18 Jaguar 2002 20 19 Audi 2003 21 20 Nissan 2004

Step 2 - Select the regression option from the data analysis tab

Formulas Review View Developer Help O Tell me what you want to do Group ▼ +-E-Data Analysis Ungroup ▼ -- ?, solver Queries & Connections 순↓MA YR 目貿8-郾圈 Clear Properties Edit Links Filter Reapply Text to E. z I Sort What-If Forecast Analysis ▼ Sheet EH subto ' Advanced Columns co Analyze Queries & Connections Sort & Filter Data Tools Outline Forecast Data Analysis Analysis Tools Covariance Descriptive Statistics Exponential Smoothing F-Test Two-Sample for Variances Fourier Analysis Histogram Moving Average Random Number Generation Rank and Percentile Regression OK Cancel Help

Step 3- Input the values as shown below.

Regression Milage 1 sr.no Car Model 2 Price 1 Mustang 2006 2 Tovota 2006 3 Honda 2008 4 Toyota 2011 5 Honda 2004 6 Volkswagen 2006 7 Lexus 2003 8 Toyota 2003 9 Porsche 2001 6500 134000 999 201101 6000 179950 17500 137270 3595 185000 3699 197285 4000230000 2500 180000 18000 90000 4999124389 4800 184445 3750 186000 6000 104000 500 158650 6450 181000 5999114747 2500 108000 3500 113239 2900 125084 4499 171708 Input OK SC$1:$C$21 Input Y Range: Input X Range: Labels 4 SD$1:$D$21 Help Constant is Zero 95 LI Confidence Level: Output options 10 11 10 BMW 2003 12 11 Toyota 2008 13 12 Chrysler 2008 14 13 Kia 2011 15 14 Chevrolet 2004 16 15 Ford 2007 17 16 Chevrolet 2004 18 17 BMW 1999 19 18 Jaguar 2002 20 19 Audi 2003 21 20 Nissan 2004 SF$1 OQutput Range: New Worksheet Ply: ONew Workbook Residuals Residuals Standardized Residuals Residual Plots Line Fit Plots Normal Probability Normal Probability Plots

Step 4 - The output is generated as follows.

SUMMARY OUTPUT Regression Statistics Multiple FR R Square Adjusted R Square Standard Error Observations 0.382479293 0.14629041 0.098862099 4048.63594 20 ANOVA df MS F Significance F Regression Residual Total 1 50558671.42 50558671 3.084453 0.096043683 18295046153.6 16391453 19 345604825 Coefficients Standard Error Lower 95% 3772.84646 3.251397 0.004432 4340.564016 20193.48 4340.564 20193.48 t Stat P-value Upper 95%DWer 95.09pper 95.0% 12267.0203 0.04142 ntercept 21724 0.0235851731.75626 0.096044-0.090972333 0.008129 -0.09097 0.008129 Correlation CORREL(C2:C21,D2:D21) -0.382479293

The regression equation ( This equation is obtained from coefficient of the regression output. Highlighted in green)

Regression equation
List price = 12267 - 0.0414 Milage

Question 7

The slope of the regression equation is - 0.0414.

With one unit increase in the mileage, the price decreases by 0.0414.

Question 8

correlation = -0.3824

It is negative correlation or an inverse relationship. As one increases the other one decreases.

Question 9 : Need background to the problem

Question 10
Yes we can conclude that the mileage causes the price of used car to go down. We see that the pvalue of the anova output is less than 0.05,hence indicate that the relationship is significant.