Share
Explore BrainMass

Regression Analysis Model: Forecast sales price for car

See attached file.

Dan Jones is thinking about purchasing of a car. One of his main concerns is how well the car will maintain its value. In particular, he is wondering how certain options affect a car's resale value, including the model year, type of transmission, mileage, air conditioning, leather interior, and the average number of miles driven per year.

One car Dan is considering is a Ford Mustang two-door coupe. To analyze the situation, Dan contacted a friend who works at a used car lot. He provided Dan with a spreadsheet with data on all 25 two-door Mustang coupes that were sold in 2005. (See Excel spreadsheet.)

1. Develop a regression model for the sales price with factors model year, type of transmission, mileage, air conditioning and leather interior. Eliminate factors that seem inappropriate and compute a formula for the sales price.

2. Dan is unsure whether the 'mileage' factor should be replaced by 'mileage per year' (computed by taking the mileage and dividing with the number of years used with the current year being 2005). Repeat problem 1 above with 'mileage per year' instead of 'mileage'.

3. For each of the regression models above, compute its MAD value (difference between the actual sales value and the forecast value from the model). Which method has a smaller MAD?

What would be the best formula to use to forecast the sales price? And please help with the regression analysis or steer me in the correct path.

Attachments

Solution Preview

See attached file.

I completed this work in Excel for you. Start with the first tab to the left of the initial data, where I turn all variables ...

Solution Summary

I completed this work in Excel. Start with the first tab to the left of the initial data, where I turn all variables into numerical format (dummy variables). Next, I ran an exploratory regression to find out which variables were significant. Three of the five variables were good predictors so I threw out the two insignificant ones and ran a regression with just the significant three. Then, I computed miles per year and ran a regression substituting mileage with miles per year. The two prediction equations are shown and the MAD are computed for both equations.

$2.19