Chapter 9 Multiple Regression 389
because it gives a direct measure
of the energy consumption. Redo
your regression model with this
new dependent variable in place of
MPG. How does the residual versus
predicted value plot compare to the
earlier one? Compare the regression
results with the previous results for
MPG (including R squared).
g. Save your changes to the workbook
and write a report summarizing your
conclusions.
- Return to the Cars workbook and per-
form the following analysis:
a. Open the Cars workbook from the
Chapter09 folder and save it as Cars
Reduced Model.
b. Recreate the GPM100 variable de-
scribed in the previous exercise and
then regress GPM100 on the same
numeric variables. Try to reduce the
number of variables in the model
using the following algorithm:
i. Perform the regression.
ii. If any coeffi cients in the regres-
sion are nonsignifi cant, redo the
regression with the least signifi -
cant variable removed.
iii. Continue until all coeffi cients
remaining are signifi cant.
To do this, you may have to move
the columns around because the
Regression command requires
that all predictor variables lie in
adjacent columns.
c. How does the R^2 value for this re-
duced model compare to the full
model with six predictors?
d. Report your fi nal model and save your
changes to the workbook.
- Perform the following fi nal analysis on
the Cars data:
a. Reopen the Cars workbook from the
Chapter09 folder and save it as Cars
Final Analysis.
b. Regress the variable GPM100
described in Exercise 11 on Cyl,
Eng size, HP, Weight, Price, and
Eng type01 for only the U.S. cars.
(You will have to copy the data to a
new worksheet using the AutoFilter
function.)
c. Analyze the residuals of the model.
Do they follow the assumptions rea-
sonably well?
d. In the Car Data worksheet, add a new
column containing the predicted
GPM100 values for all car models
using the regression equation you
created for only the U.S. cars. Create
another new column containing the
residuals.
e. Plot the residuals against the predicted
values for all of the cars, and then
break down the scatter plot into cat-
egories on the basis of origin. Rescale
the x axis so that it ranges from 3 to 8.
f. Calculate descriptive statistics (in-
clude the summary, variability, and
95% t-confi dence intervals) for the
residuals column, broken down by
region.
g. Save your changes to the workbook
and write a report summarizing your
conclusions, including a discussion
of whether Asian and European cars
appear to have better MPG after cor-
rection for the other factors. Because
the model was developed for U.S.
cars, the average residual for U.S. cars
will be 0. If a car has a negative re-
sidual for GPM100, then the car uses
less energy than was predicted for it,
and therefore it gets better gas mileage
than predicted (the value predicted for
a U.S. car).
- The Temperatures workbook contains
average January temperatures for 56 cities
in the United States, along with the cities’
latitude and longitude. Perform the fol-
lowing analysis of the data:
