Homework SourseUsing excel need to understand how to do the below Consider
Using excel, need to understand how to do the below:
Consider the housing data below. By generating a series of multiple regressions, determine the ideal model for predicting Selling price. Be sure your final model has only significant predictor variables. What is the regression equation for this model? How good is this model, based on the data given in the output? Using this ideal model, predict the selling price for a home with 2000 square feet, 3.5 Bedrooms, Age of 12 years, with a pool.
| Selling price | Square Footage | Bedrooms | Age | Pool |
| 85236.7319 | 1695 | 3 | 45 | 0 |
| 96118.8595 | 1364 | 4 | 45 | 1 |
| 97677.927 | 1737 | 3 | 37 | 0 |
| 111947.925 | 1865 | 4 | 27 | 1 |
| 119614.14 | 2325 | 3 | 44 | 0 |
| 120522.227 | 2259 | 3 | 60 | 0 |
| 129956.182 | 2336 | 4 | 28 | 1 |
| 162200.266 | 2402 | 4 | 10 | 0 |
| 164066.74 | 2761 | 5 | 14 | 1 |
| 181735.851 | 2525 | 5 | 5 | 1 |
| 181904.835 | 2525 | 4 | 3 | 1 |
| 186372.661 | 2504 | 4 | 4 | 0 |
| 188491.711 | 2425 | 4 | 2 | 1 |
| 194121.459 | 3149 | 5 | 0 | 1 |
| 195619.573 | 2525 | 4 | 1 | 1 |
| 211387.712 | 4087 | 5 | 15 | 1 |
| 229431.836 | 2879 | 4 | 4 | 1 |
Solution
Selling price (Y)
Square Footage
(x1)
Bedrooms (x2)
Age
(x3)
Pool
(x4)
85236.7
1695
3
45
0
96118.9
1364
4
45
1
97677.9
1737
3
37
0
111948
1865
4
27
1
119614
2325
3
44
0
120522
2259
3
60
0
129956
2336
4
28
1
162200
2402
4
10
0
164067
2761
5
14
1
181736
2525
5
5
1
181905
2525
4
3
1
186373
2504
4
4
0
188492
2425
4
2
1
194121
3149
5
0
1
195620
2525
4
1
1
211388
4087
5
15
1
229432
2879
4
4
1
The ideal model will be the least square regression model
SUMMARY OUTPUT
Regression Statistics
Multiple R
0.956036
R Square
0.914005
Adjusted R Square
0.88534
Standard Error
15165.3
Observations
17
ANOVA
df
SS
MS
F
Significance F
Regression
4
2.93E+10
7.33E+09
31.88587
2.62E-06
Residual
12
2.76E+09
2.3E+08
Total
16
3.21E+10
Coefficients
Standard Error
t Stat
P-value
Lower 95%
Upper 95%
Lower 95.0%
Upper 95.0%
Intercept
145501.2
36752.05
3.958997
0.001897
65425.4
225577.1
65425.4
225577.1
Square Footage
40.65741
8.419115
4.829178
0.000413
22.31373
59.00108
22.31373
59.00108
Bedrooms
-16232.2
10360.21
-1.56678
0.143143
-38805.1
6340.757
-38805.1
6340.757
Age
-1510.57
274.8204
-5.49658
0.000137
-2109.35
-911.79
-2109.35
-911.79
Pool
11327.24
11375.64
0.995746
0.339029
-13458.1
36112.63
-13458.1
36112.63
Let the regression line be :
Y = b0 + b1 x1 + b2 x2+ b3 x3 + b4 x4
Here b0 = 145501.2 ; b1 = 40.657 ; b2 = -16232.2 ; b3 = -1510.57 ; b4 = 11327.24
Therefore our regression line equation is,
Y = 145501.2 + 40.657 x1 – 16232.2 x2 – 1510.57 x3 + 11327.24 x4
Here we need to find the selling price for a home with 2000 square feet, 3.5 Bedrooms, Age of 12 years, with a pool.
Y = the selling price of a home
x1 = 2000 square feet ; x2 = 3.5 bedrooms ; x3 = 12 yrs of age ; x4 = 1 pool
we plug in this values in regression line equation we get :
Y = 145501.2 + 40.657x 2000 – 16232.2 x 3.5 – 1510.57x 12 + 11327.24 x 1
= 163202.9
Therefore the selling price of a home = 163202.90
| Selling price (Y) | Square Footage (x1) | Bedrooms (x2) | Age (x3) | Pool (x4) | ||||
| 85236.7 | 1695 | 3 | 45 | 0 | ||||
| 96118.9 | 1364 | 4 | 45 | 1 | ||||
| 97677.9 | 1737 | 3 | 37 | 0 | ||||
| 111948 | 1865 | 4 | 27 | 1 | ||||
| 119614 | 2325 | 3 | 44 | 0 | ||||
| 120522 | 2259 | 3 | 60 | 0 | ||||
| 129956 | 2336 | 4 | 28 | 1 | ||||
| 162200 | 2402 | 4 | 10 | 0 | ||||
| 164067 | 2761 | 5 | 14 | 1 | ||||
| 181736 | 2525 | 5 | 5 | 1 | ||||
| 181905 | 2525 | 4 | 3 | 1 | ||||
| 186373 | 2504 | 4 | 4 | 0 | ||||
| 188492 | 2425 | 4 | 2 | 1 | ||||
| 194121 | 3149 | 5 | 0 | 1 | ||||
| 195620 | 2525 | 4 | 1 | 1 | ||||
| 211388 | 4087 | 5 | 15 | 1 | ||||
| 229432 | 2879 | 4 | 4 | 1 | ||||
| The ideal model will be the least square regression model | ||||||||
| SUMMARY OUTPUT | ||||||||
| Regression Statistics | ||||||||
| Multiple R | 0.956036 | |||||||
| R Square | 0.914005 | |||||||
| Adjusted R Square | 0.88534 | |||||||
| Standard Error | 15165.3 | |||||||
| Observations | 17 | |||||||
| ANOVA | ||||||||
| df | SS | MS | F | Significance F | ||||
| Regression | 4 | 2.93E+10 | 7.33E+09 | 31.88587 | 2.62E-06 | |||
| Residual | 12 | 2.76E+09 | 2.3E+08 | |||||
| Total | 16 | 3.21E+10 | ||||||
| Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | Lower 95.0% | Upper 95.0% | |
| Intercept | 145501.2 | 36752.05 | 3.958997 | 0.001897 | 65425.4 | 225577.1 | 65425.4 | 225577.1 |
| Square Footage | 40.65741 | 8.419115 | 4.829178 | 0.000413 | 22.31373 | 59.00108 | 22.31373 | 59.00108 |
| Bedrooms | -16232.2 | 10360.21 | -1.56678 | 0.143143 | -38805.1 | 6340.757 | -38805.1 | 6340.757 |
| Age | -1510.57 | 274.8204 | -5.49658 | 0.000137 | -2109.35 | -911.79 | -2109.35 | -911.79 |
| Pool | 11327.24 | 11375.64 | 0.995746 | 0.339029 | -13458.1 | 36112.63 | -13458.1 | 36112.63 |
