For the Excel file Auto Survey, a. Find the best regression model to predict mil
ID: 3173309 • Letter: F
Question
For the Excel file Auto Survey, a. Find the best regression model to predict miles/ gallon as a function of vehicle age and mileage. b. Using your result from part (a), add the categorical variable Purchased to the model. Does this change your result? Please show how excel formulas were conducted.
file can be downloaded here http://wps.prenhall.com/bp_evans_bus_2/
Auto Survey Gender Type Purchased Vehicle Age Mileage MPG Male Mid-size Used 15 127233 28.7 Female Mid-size New 1 23970 43.4 Male Small New 7 77392 24 Female Large SUV Used 14 185397 15.2 Female Small New 2 26001 37 Female Minivan New 9 180643 20 Male Small Used 6 72083 45.7 Male Small New 11 165353 42 Male Small Used 13 205288 33 Female Small New 7 142897 31 Male Minivan Used 14 182584 12 Male Small SUV Used 13 140479 20 Female Small New 2 22114 28 Female Mid-size New 0.25 3454 28.3 Female Large SUV New 7 130905 21 Female Small Used 10 105628 35 Female Small New 5 48678 30.4 Male Mid-size New 0.5 6849 40.2 Female Small Used 10 137941 30 Female Small SUV New 4 29823 24.9 Male Small SUV Used 14 85763 21 Female Small Used 12 134172 31 Male Mid-size Used 12 86387 27Explanation / Answer
a).Find the best regression model to predict miles/ gallon as a function of vehicle age and mileage.
Regression Analysis
R²
0.222
Adjusted R²
0.145
n
23
R
0.472
k
2
Std. Error
8.188
Dep. Var.
MPG
ANOVA table
Source
SS
df
MS
F
p-value
Regression
383.63097035
2
191.81548518
2.86
.0807
Residual
1,340.70816008
20
67.03540800
Total
1,724.33913043
22
Regression output
confidence interval
variables
coefficients
std. error
t (df=20)
p-value
95% lower
95% upper
Intercept
36.1809
3.4528
10.479
1.43E-09
28.9784
43.3834
Mileage
-0.00000806
0.00004863
-0.166
.8701
-0.00010950
0.00009339
Vehicle Age
-0.7664
0.6306
-1.215
.2384
-2.0818
0.5490
Estimated regression line
MPG = 36.1809 – 0.00000808*mileage-0.7664* Vehicle Age
b. Using your result from part (a), add the categorical variable Purchased to the model. Does this change your result?
Coding categorical variable Purchased used=1 and new=0
Regression Analysis
R²
0.286
Adjusted R²
0.173
n
23
R
0.534
k
3
Std. Error
8.052
Dep. Var.
MPG
ANOVA table
Source
SS
df
MS
F
p-value
Regression
492.58818830
3
164.19606277
2.53
.0876
Residual
1,231.75094213
19
64.82899695
Total
1,724.33913043
22
Regression output
confidence interval
variables
coefficients
std. error
t (df=19)
p-value
95% lower
95% upper
Intercept
37.2519
3.4946
10.660
1.86E-09
29.9376
44.5661
Mileage
0.00001975
0.00005241
0.377
.7105
-0.00008995
0.00012946
Vehicle Age
-1.6820
0.9399
-1.790
.0895
-3.6492
0.2851
purchased
7.6036
5.8651
1.296
.2104
-4.6722
19.8793
Yes, by adding the variable purchased has changed the results.
R square value changed from 0.222 to 0.286.
c. Determine whether any significant interaction exists between Vehicle Age and Purchased variables.
Regression Analysis
R²
0.419
Adjusted R²
0.290
n
23
R
0.647
k
4
Std. Error
7.461
Dep. Var.
MPG
ANOVA table
Source
SS
df
MS
F
p-value
Regression
722.31655393
4
180.57913848
3.24
.0360
Residual
1,002.02257650
18
55.66792092
Total
1,724.33913043
22
Regression output
confidence interval
variables
coefficients
std. error
t (df=18)
p-value
95% lower
95% upper
Intercept
33.7957
3.6580
9.239
2.98E-08
26.1105
41.4809
Mileage
-0.00001946
0.00005227
-0.372
.7140
-0.00012926
0.00009035
Vehicle Age
-0.3346
1.0948
-0.306
.7634
-2.6345
1.9654
purchased
29.2806
11.9751
2.445
.0250
4.1218
54.4394
vage*purc
-2.4231
1.1928
-2.031
.0572
-4.9291
0.0829
Test for interaction between Vehicle Age and Purchased variables, t=-2.031, P=0.0572 which is > 0.05 level of significance.
The interaction is not significant.
Regression Analysis
R²
0.222
Adjusted R²
0.145
n
23
R
0.472
k
2
Std. Error
8.188
Dep. Var.
MPG
ANOVA table
Source
SS
df
MS
F
p-value
Regression
383.63097035
2
191.81548518
2.86
.0807
Residual
1,340.70816008
20
67.03540800
Total
1,724.33913043
22
Regression output
confidence interval
variables
coefficients
std. error
t (df=20)
p-value
95% lower
95% upper
Intercept
36.1809
3.4528
10.479
1.43E-09
28.9784
43.3834
Mileage
-0.00000806
0.00004863
-0.166
.8701
-0.00010950
0.00009339
Vehicle Age
-0.7664
0.6306
-1.215
.2384
-2.0818
0.5490