Using excel, need to understand how to do the below: Consider the housing data b

Question

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

Explanation / Answer

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 = b­0 + b1 x1 + b2 x2+ b3 x3 + b4 x4

Here b­0 = 145501.2 ; b­1 = 40.657 ; b­2 = -16232.2 ; b­3 = -1510.57 ; b­4 = 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

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.