Multiple Regression Analysis: Estimation and Inference

A consumer organization wants to develop a regression model to predict mileage (as measured by miles per gallon) based on the horsepower of the car’s engine and the weight of the car (in pounds). Data were collected from a sample of 50 recent car models, and the results are organized and stored in Auto.

Step 1: Copy the data from the word file and paste it into an Excel worksheet.

MPGHorsepowerWeight
43.1481995
19.91103365
19.21053535
17.71653445
18.11393205
20.31032830
21.51153245
16.91554360
15.51424054
18.51503940
27.2713190
41.5762144
46.6652110
23.71002420
27.2842490
39.1581755
28882605
24922865
20.21393570
20.5953155
28902678
34.7632215
36.1661800
35.7801915
20.2852965
23.9903420
29.9652380
30.4673250
36741980
22.61102800
36.4672950
27.5952560
33.7752210
44.6671850
32.91002615
38671965
24.21202930
38.1601968
39.4702070
25.41162900
31.3752542
34.1681985
34882395
31822720
27.4802670
22.3882890
28792625
17.6853465
34.4653465
20.61053380
estimation and inference image 1

Step 2: Go to the Data thread and select the option – Data Analysis. From the data analysis dialog box, select the option – Regression.

estimation and inference image 2

Step 3: select cells A1 to A51 in the input y range and cells B1 to C51 in the input x range. Select the option of labels and set the confidence level at 95%. Select ‘New Worksheet Ply’ from the Output options. Click on Ok. The regression output will instantly display in a new worksheet.

estimation and inference image 3
estimation and inference image 4

A. State the multiple regression equation

From the coefficients provided in the regression results, the regression equation is: MPG = 58.16 – 0.11 b1– 0.007 b2

{`
  Here, b0 = intercept
  b1 = Horsepower of engine
  b2 = Weight
  `}

B. Interpret the meaning of the slopes, b¹ and b² in this problem

The slope b1 shows that with an increase of 1 horsepower in the engine, the mileage of the car decreases by 0.11 miles per gallon and vice versa.

The slope b2 shows that with an increase of 1 pound in the cars weight, its mileage will fall by 0.007 miles per gallon and vice versa.

Thus, it can be concluded that both, the horsepower of the car’s engine and the weight of the car are inversely related to its mileage.

C. Explain why the regression coefficient, bº, has no practical meaning in the context of this problem

The intercept b0 has no practical implication as the miles that a car will travel will not have a fixed minimum level beyond which it will depend on the horsepower of the engine and the car weight. This statement does not have any practical basis.

D. Predict the mile per gallon for cars that have 60 horsepower and weigh 2,000 pounds

{`
  b1 = 60
  b^2 = 2000
  Hence, the miles per gallon (MPG) will be:
  MPG= 58.16 – (0.11*60) – (0.007*2000) = 37.56
  Therefore, the miles per gallon will be 37.56
  `}

E. Construct a 95% confidence interval estimate for the mean miles per gallon for cars that have 60 horsepower and weigh 2,000 pounds

{`
  The confidence interval will be:
  Y = MPG ± tn-2,α/2 *sMPG
  To calculatetn-2,α/2, select the TINV function in Excel (Formulas -> More Functions -> Statistical -> TINV)
  `}
estimation and inference image 5

In the TINV dialog box,

estimation and inference image 6
{`
  Thus, we get the value of tα/2 as 2.01
  sMPG = 4.176 (from the regression results)
  Confidence interval = 37.56 ± (2.01*4.176) = (29.16, 45.95)
  `}

F. Construct a 95% prediction interval for the miles per gallon for an individual car that has 60 horsepower and weighs 2,000 pounds

{`
  The prediction interval will be:
  Y = MPG ± t48,0.05*√sMPG2 + MSE
  MSE = 17.44 (from the regression results)
  Y = 37.56 ± (2.01*√4.1762+17.44) = (25.70, 49.30)	
  `}

Excel Econometrics Tutorials

Multiple Regression Analysis

Time Series Analysis

Data Analysis