Question

The following table shows the miles per gallon of various cars and their weight in pounds. Suppose we are interested in predicting the miles per gallon of a car based on its weight.

Car	Weight	MPG
Buick Lucerne	3735	17
Cadillac CTS	3860	16
Chevrolet Cobalt	2721	25
Chevrolet Impala	3555	19
Chrysler Sebring Sedan	3319	21
Dodge Caliber	2966	23
Dodge Charger	3727	17
Ford Focus	2605	24
Ford Mustang	3473	19
Lincoln MKZ	3796	17
Mercury Sable	3310	18

Compute the correlation coefficient, and explain what this tells us about the relationship between weight and MPG of a car. (2pts)

Give the equation of the regression line. (2pts)

Write a sentence interpreting the y-intercept. (2pts)

Write a sentence interpreting the slope. (2pts)

A Subaru Legacy weighs 3500 pounds. Using the equation of the regression line, what would you predict the MPG of this car to be? (2pts)

The approximate fuel mileage of a Subaru Legacy is 25 MPG. Give the residual for the Subaru Legacy. (2pts)

Answer 1

Answer:

Follow the given procedure to perform Regression Analysis in MS-Excel.

Enter the given data into Excel worksheet as,

Car	Weight (x)	MPG (y)
Buick Lucerne	3735	17
Cadillac CTS	3860	16
Chevrolet Cobalt	2721	25
Chevrolet Impala	3555	19
Chrysler Sebring Sedan	3319	21
Dodge Caliber	2966	23
Dodge Charger	3727	17
Ford Focus	2605	24
Ford Mustang	3473	19
Lincoln MKZ	3796	17
Mercury Sable	3310	18

1.Enter the data into Excel sheet.

2.If this is the first time you have used an Excel add-in, click the File tab, otherwise skip to step 7.

3.Click Options from the list on the left.

4.Select Add-ins in the Excel Options box.

5.In the Add-in list box, select Analysis Toolbox-VBA from the Inactive Application Add-ins list.

6.Click OK.

7.Then select Data/ Data Analysis tab from the menu bar.

8.The Data Analysis dialog box will appear on the screen.

9.From the Data Analysis dialog box, select Regression and click OK.

10.The Regression dialog box will appear on the screen.

11.Place independent variables (Weight) in Input X Range and place dependent variable (MPG) in Input Y Range.

12.Place appropriate confidence level in Confidence Level box. (If necessary)

13.Click OK.

The MS Excel output is as follows:

Summary:

Regression Statistics
Multiple R	0.959626
R Square	0.920883
Adjusted R Square	0.912092
Standard Error	0.93075
Observations	11

ANOVA Table:

	df	SS	MS	F	Significance F
Regression	1	90.7488	90.7488	104.7551	2.95E-06
Residual	9	7.796653	0.866295
Total	10	98.54545

	Coefficients	Standard Error	t Stat	P-value
Intercept	42.90678	2.290867	18.72949	1.62E-08
Weight (slope)	-0.00691	0.000675	-10.235	2.95E-06

Now,

I)

Correlation between Weight and MPG:

Procedure:

1.Enter the data into Excel sheet.

2.If this is the first time you have used an Excel add-in, click the File tab, otherwise skip to step 7.

3.Click Options from the list on the left.

4.Select Add-ins in the Excel Options box.

5.In the Add-in list box, select Analysis Toolbox-VBA from the Inactive Application Add-ins list.

6.Click OK.

7.Then select Data/ Data Analysis tab from the menu bar.

8.The Data Analysis dialog box will appear on the screen.

9.From the Data Analysis dialog box, select Correlation and click OK.

10.The Correlation dialog box will appear on the screen.

11.Give the range of data (Select the columns corresponding to weight and MPG) in Input Range.

12.Then select the Columns options in the Grouped By tab. Then click on Labels in first row.

13.Give the Output Range. Click OK.

The MS Excel output will appear on the screen.

The output is,

	Weight	MPG
Weight	1
MPG	-0.95963	1

From the above output, the correlation between weight and MPG is r = -9596.

Here we conclude that, there is strongly negative relationship (inverse correlation) between weight and MPG of a car i.e., they are uncorrelated.

II)

From above output, the equation of regression line is given by,

where x = weight of car and = predicred MPG of a car.

III)

Interpretation of y-intercept:

The y-intercept is simply the value at which the fitted line crosses the y-axis. The intercept is the expected mean value of y whrn all x = 0.

IV)

Here slope = -0.0069

The function with negative slope represents a negative correlation between two variables.

We conclude that, the MPG is expected to increase (or decrease) by 0.0069 on average per every 1 pound decrease (or increase) in weight.

V)

A Subaru Legacy weighs 3500 pounds i.e., x = 3500

Then from the above regression equation, the predicted value of MPG is given by,

Therefore, the predicted MPG for Sabaru Legacy car with weight 3500 pounds is 19.