A sample of 20 automobiles was taken, and the miles per gallon (MPG), horsepower
ID: 3065298 • Letter: A
Question
A sample of 20 automobiles was taken, and the miles per gallon (MPG), horsepower, and total weight were MPG HORSEPOWER WEIGHT (xa) 67 1.844 50 1.998 62 1.752 1.98 66 1.797 63 2.199 90 2.404 99 2.611 63 3.236 91 2.606 2.58 88 2.507 124 2.922 97 2.434 114 3.248 102 2.812 114 3.382 142 3.197 4.38 139 4.036 37 37 34 35 32 30 28 20 21 18 18 153 16 a. Develop a linear regression model to predict MPG, using horsepower as the only independent variable. Develop another model with weight as the independent variable. Which of these two models is better? Explain (Compare significances of the models and coefficient of determinations (r)) (40 points) Develop a multiple regression model that includes both horsepower and weight as the independent variables. Discuss significance of the model and coefficient of determination (r). (20 points) Develop a quadratic model as: b. c. Discuss significance of the model and coefficient of determination (r). (40 points) INSTRUCTIONS .Submit softcopy excel files via moodle. Data file is attached as a separate xls file Models should be developed with MS Excel Data Analysis Tool as shown in the class. .Every solution (regression model) should be on a different sheet. Discussion is to be made on the same sheet.Explanation / Answer
In excel, we can do this by installing data analysis package. In data analysis package, please select regression and enter y and x values. Check labels if you keep headers.
a)
Horsepower (X1)
SUMMARY OUTPUT
Regression Statistics
Multiple R
0.877606954
R Square
0.770193965
Adjusted R Square
0.757426963
Standard Error
4.481278353
Observations
20
ANOVA
df
SS
MS
F
Significance F
Regression
1
1211.476598
1211.476598
60.32692482
3.72351E-07
Residual
18
361.4734022
20.08185568
Total
19
1572.95
Coefficients
Standard Error
t Stat
P-value
Lower 95%
Upper 95%
Intercept
53.87237626
3.423059926
15.7380757
5.75523E-12
46.68079421
61.0639583
Horsepower (X1)
-0.269447549
0.034691146
-7.767040931
3.72351E-07
-0.342330942
-0.196564156
Weight (X2)
SUMMARY OUTPUT
Regression Statistics
Multiple R
0.373959976
R Square
0.139846064
Adjusted R Square
0.092059734
Standard Error
8.66980435
Observations
20
ANOVA
df
SS
MS
F
Significance F
Regression
1
219.9708657
219.9708657
2.926486804
0.104316028
Residual
18
1352.979134
75.16550746
Total
19
1572.95
Coefficients
Standard Error
t Stat
P-value
Lower 95%
Upper 95%
Intercept
35.9475193
4.792344041
7.5010306
6.05626E-07
25.87917808
46.01586052
Weight (X2)
-0.003268388
0.001910559
-1.710697754
0.104316028
-0.007282323
0.000745546
The model with the horsepower is more significant because it has a higher r^2 value and also significance F less than 0.05 which means independent variable has effect on the dependent variable.
b)
Horsepower and Weight
SUMMARY OUTPUT
Regression Statistics
Multiple R
0.889135468
R Square
0.79056188
Adjusted R Square
0.765922102
Standard Error
4.402109724
Observations
20
ANOVA
Df
SS
MS
F
Significance F
Regression
2
1243.51431
621.7571548
32.08477995
1.69424E-06
Residual
17
329.4356903
19.37857002
Total
19
1572.95
Coefficients
Standard Error
t Stat
P-value
Lower 95%
Upper 95%
Intercept
55.68661695
3.646627159
15.27071854
2.33029E-11
47.99290616
63.38032773
Horsepower (X1)
-0.257184492
0.035387713
-7.267621101
1.31493E-06
-0.331846041
-0.182522943
Weight (X2)
-0.001295259
0.001007365
-1.285789522
0.215755558
-0.003420614
0.000830095
r^2 is 0.79 which means 79% of the variation in dependent variable is accounted for by the independent variable. Also model is overall significant at a=0.05 looking at significance F.
c)
SUMMARY OUTPUT
Regression Statistics
Multiple R
0.893255456
R Square
0.79790531
Adjusted R Square
0.774129464
Standard Error
4.324246568
Observations
20
ANOVA
df
SS
MS
F
Significance F
Regression
2
1255.065158
627.5325788
33.55949203
1.25088E-06
Residual
17
317.8848425
18.69910838
Total
19
1572.95
Coefficients
Standard Error
t Stat
P-value
Lower 95%
Upper 95%
Intercept
69.92537601
11.020937
6.344775949
7.32719E-06
46.67323145
93.17752057
Horsepower (X1)
-0.619887885
0.231957635
-2.672418542
0.016073274
-1.109275717
-0.130500053
X1^2
0.001747187
0.001144362
1.526777707
0.145206684
-0.000667206
0.00416158
Model is significant at a=0.05 as Significance F is lower than 0.05. Also r^2 is 0.7979 which means 79.79% of variation in dependent variable is accounted for by the independent variables.
SUMMARY OUTPUT
Regression Statistics
Multiple R
0.877606954
R Square
0.770193965
Adjusted R Square
0.757426963
Standard Error
4.481278353
Observations
20
ANOVA
df
SS
MS
F
Significance F
Regression
1
1211.476598
1211.476598
60.32692482
3.72351E-07
Residual
18
361.4734022
20.08185568
Total
19
1572.95
Coefficients
Standard Error
t Stat
P-value
Lower 95%
Upper 95%
Intercept
53.87237626
3.423059926
15.7380757
5.75523E-12
46.68079421
61.0639583
Horsepower (X1)
-0.269447549
0.034691146
-7.767040931
3.72351E-07
-0.342330942
-0.196564156