To measure value, Consumer Reports developed a statistic referred as a value score-family Sedan

Question

To measure value, Consumer Reports developed a statistic referred as a value score. The value score is

based upon five-year owner costs, overall road-test scores, and predicted-reliability ratings. Five-year

owner costs are based upon the expenses incurred in the first five years of ownership, including

depreciation, fuel, maintenance and repairs, and so on. Using a national average of 12,000 miles per

year, an average cost per mile driven is used as the measure of five-year owner costs. Road-test scores

are the results of more than 50 tests and evaluations and are based on a 100-point scale, with higher

score indicating better performance, comfort, convenience, and fuel economy. The highest road-test

score obtained in the tests conducted by Consumer Reports was a 99 for a Lexus LS 460L. Predictedreliability

ratings (1=Poor, 2=Fair, 3=Good, 4=Very Good, and 5=Excellent) are based upon data from

Consumer Reports’ Annual Auto Survey. A car with a value score of 1.0 is considered to be an “averagevalue”

car. A car with a value score of 2.0 is considered to be twice as good a value as a car with a value

score of 1.0; a car with a value score of 0.5 is considered half as good as average; and so on. The data for

three sizes of cars (13 small sedans, 20 family sedans, and 21 upscale sedans), including price ($) of each

car tested, are provided in spreadsheet tab labeled Car (Consumer Reports, April, 2012).

Answer a – e below:

a. To incorporate the effect of size of a car, create two dummy variables in Columns H and I using the

following coding.

Family-Sedan: 1 if the car is a family sedan and 0 otherwise.

Upscale-Sedan: 1 if the car is an upscale sedan and 0 otherwise.

b. First treating Cost/Mile as the dependent variable, develop an estimated regression with

Family-Sedan and Upscale-Sedan as the independent variables. With Cell K3 as the upper left hand

corner of the output, fit the full regression model (report Regression Table only). Summarize your

model in Cells K12:S20. Are the coefficients significant? Interpret the coefficients.

c. Next treating Value Score as the dependent variable, develop an estimated regression

equation using all other variables as the independent variables. Choose Stepwise as the Regression

Type. With Cell K26 as the upper left hand corner of the output, fit the full regression model

(report Regression Table only). Does your model support the claim that “smaller car provide better

values than larger cars?” The Small Sedans represent the smallest type of car and the Upscale

Sedans represent the largest type of car. State your reasons in Cells K39:S47.

d. Using the model developed in part c, calculated the predicted Value Score in Column J.

Which car has the smallest Value Score residual and which car has the largest Value Score residual?

e. Place the Residual Plot for the model developed in part c in Cells K62:S78. Are the

assumptions being satisfied? Is the variance of the error terms constant? Are residuals scattered

randomly around zero? Place your assessments in Cells K80:S88 in in the model. Also shade the t stat

and p-value.