A national homebuilder builds single-family homes and condominium-style townhous

Question

A national homebuilder builds single-family homes and condominium-style townhouses. The Excel file House Sales provides information on the selling price, lot cost, type of home, and region of the country ( M = Midwest, S = South ) (M=Midwest,S=South) for closings during one month.

Determine if an interaction exists between lot cost and type of home and find the best model.

What is the predicted price for either a single family home or a townhouse with a lot cost of $30,000?

(Note: we are not concerned about the region here)

Please save the models you received from the data analysis and your interpretations of the models

Please use excel data analysis tool to build the multiple regression model

Please provide detail steps on how to build the regression model using the excel data analysis tool

Thanks!

Data below:

House Sales
Region Type Selling Price Lot Cost
Midwest Townhouse $85,145 $17,030
Midwest Townhouse $112,740 $20,700
Midwest Townhouse $115,350 $18,030
Midwest Townhouse $116,415 $19,600
Midwest Townhouse $133,070 $24,455
Midwest Single Family $136,530 $25,500
Midwest Townhouse $139,435 $29,155
Midwest Townhouse $139,955 $30,400
Midwest Townhouse $147,905 $24,650
Midwest Single Family $153,845 $27,500
Midwest Townhouse $155,270 $19,600
Midwest Townhouse $155,870 $24,650
South Single Family $160,000 $29,500
Midwest Single Family $165,220 $25,500
Midwest Single Family $165,350 $25,000
Midwest Single Family $168,354 $27,316
Midwest Single Family $168,500 $33,000
Midwest Single Family $170,000 $25,200
South Townhouse $172,749 $45,025
Midwest Single Family $175,000 $28,000
Midwest Townhouse $175,470 $28,600
Midwest Townhouse $179,365 $32,200
South Townhouse $181,916 $45,025
South Townhouse $182,237 $45,025
Midwest Single Family $183,370 $28,000
Midwest Single Family $184,210 $28,000
Midwest Single Family $184,460 $22,300
Midwest Townhouse $184,873 $33,400
Midwest Single Family $185,160 $29,000
South Single Family $186,000 $35,851
Midwest Townhouse $187,390 $27,000
Midwest Single Family $188,603 $31,300
Midwest Single Family $189,120 $35,000
South Single Family $191,028 $45,000
South Townhouse $196,898 $45,025
South Townhouse $198,202 $45,025
South Single Family $200,119 $45,000
South Townhouse $200,423 $45,025
Midwest Single Family $201,700 $40,940
South Single Family $202,000 $31,160
South Townhouse $203,076 $45,025
Midwest Single Family $203,950 $33,000
South Single Family $204,900 $34,000
South Single Family $205,821 $39,299
South Single Family $206,400 $35,851
South Single Family $207,345 $35,600
South Single Family $209,400 $43,579
Midwest Single Family $210,380 $33,856
Midwest Single Family $211,513 $31,300
South Single Family $211,797 $34,000
Midwest Townhouse $212,079 $33,400
South Single Family $214,205 $36,500
Midwest Single Family $214,900 $31,300
South Single Family $216,049 $35,600
South Single Family $219,990 $37,557
Midwest Single Family $220,257 $31,300
Midwest Single Family $223,890 $28,000
South Townhouse $224,108 $45,025
South Single Family $225,900 $28,618
South Single Family $226,000 $35,600
South Townhouse $230,000 $45,025
Midwest Single Family $230,440 $33,000
Midwest Single Family $230,555 $28,000
Midwest Single Family $233,900 $44,200
South Single Family $234,971 $29,202
South Single Family $239,000 $39,169
South Single Family $239,870 $41,354
South Single Family $241,195 $41,340
South Single Family $242,191 $33,434
South Single Family $242,899 $48,252
South Single Family $246,648 $41,600
Midwest Single Family $248,500 $20,000
South Single Family $249,900 $38,200
Midwest Single Family $250,005 $33,000
South Townhouse $250,800 $73,400
South Single Family $252,135 $41,341
South Single Family $253,055 $41,340
South Single Family $254,048 $39,680
South Single Family $255,000 $43,198
South Single Family $256,235 $48,500
South Single Family $257,040 $37,631
South Townhouse $260,100 $73,400
Midwest Single Family $262,740 $44,900
South Single Family $262,890 $48,500
South Single Family $265,058 $41,404
Midwest Townhouse $265,500 $35,800
South Townhouse $267,060 $73,400
South Single Family $267,250 $45,676
South Townhouse $267,640 $73,400
South Single Family $268,000 $43,344
Midwest Single Family $268,210 $29,700
South Single Family $268,500 $41,099
South Townhouse $269,410 $73,400
South Single Family $270,518 $46,499
Midwest Single Family $271,105 $45,000
Midwest Single Family $274,455 $41,000
South Single Family $274,903 $45,345
Midwest Single Family $277,720 $44,650
South Single Family $280,622 $45,130
South Single Family $280,804 $40,667
Midwest Single Family $281,487 $57,285
South Single Family $281,824 $50,448
Midwest Single Family $283,440 $50,900
South Single Family $286,758 $57,000
South Single Family $287,771 $46,300
Midwest Single Family $289,000 $44,000
South Single Family $290,000 $48,300
South Single Family $290,190 $82,250
South Townhouse $294,450 $73,400
South Single Family $294,990 $57,000
South Single Family $299,096 $43,784
South Single Family $301,500 $59,000
South Single Family $305,000 $49,067
Midwest Single Family $307,373 $41,292
South Single Family $307,387 $45,850
South Single Family $307,663 $44,840
South Single Family $309,075 $82,250
South Single Family $310,100 $41,800
South Single Family $310,372 $60,000
South Single Family $312,898 $40,768
South Single Family $318,274 $85,800
South Single Family $318,523 $50,000
South Single Family $319,602 $82,250
South Single Family $322,920 $82,250
Midwest Single Family $323,716 $34,500
South Single Family $324,266 $47,891
South Single Family $324,412 $62,523
South Single Family $326,570 $51,000
South Single Family $329,611 $48,611
Midwest Single Family $330,486 $35,000
South Single Family $330,710 $49,920
South Single Family $333,158 $49,123
South Townhouse $335,000 $68,375
South Single Family $337,374 $70,399
South Single Family $337,380 $49,150
South Single Family $338,065 $54,850
South Single Family $340,000 $40,000
South Single Family $340,730 $50,850
South Single Family $342,423 $48,309
Midwest Single Family $348,744 $53,000
South Single Family $350,702 $46,386
South Single Family $352,781 $60,000
South Single Family $354,065 $48,115
South Single Family $354,117 $56,219
South Single Family $355,688 $65,373
South Single Family $358,162 $44,470
South Single Family $359,949 $50,591
South Single Family $366,990 $55,508
South Single Family $367,600 $50,000
South Single Family $369,101 $46,773
South Single Family $371,152 $58,223
South Single Family $379,000 $72,915
South Single Family $379,424 $64,902
South Single Family $379,575 $44,294
South Single Family $385,000 $75,000
South Single Family $385,942 $49,123
South Single Family $387,527 $48,000
South Single Family $398,651 $65,429
South Single Family $400,330 $75,510
South Single Family $404,510 $58,225
South Single Family $407,076 $48,668
Midwest Single Family $410,810 $66,500
South Single Family $417,790 $63,099
South Single Family $432,426 $57,422
South Single Family $444,304 $53,938
South Single Family $446,507 $75,510
South Single Family $492,820 $84,122
South Single Family $575,120 $79,790

Explanation / Answer

create one column cost*type and include this new column also in the regression computation

Replace in the column Townhouse by 1 and Single Family by 0 ( Dummy variable)

Code the data with      Townhouse=1 and Single Family =0

Regression Analysis

R²

0.651

Adjusted R²

0.647

n

168

R

0.807

k

2

Std. Error

48830.238

Dep. Var.

Selling Price

ANOVA table

Source

SS

df

MS

F

p-value

Regression

734,704,076,454.2360

2  

367,352,038,227.1180

154.07

1.81E-38

Residual

393,424,703,538.9070

165  

2,384,392,142.6600

Total

1,128,128,779,993.1400

167  

Regression output

confidence interval

variables

coefficients

std. error

   t (df=165)

p-value

95% lower

95% upper

Intercept

116,426.1432

12,037.5556

9.672

8.26E-18

92,658.6441

140,193.6423

Lot Cost

3.6599

0.2460

14.879

2.66E-32

3.1742

4.1455

Type

-73,847.4117

9,650.4182

-7.652

1.56E-12

-92,901.6378

-54,793.1856

Selling price = 116,426.1432+3.6599*lot cost-73,847.4117*type(

Townhouse

Regression Analysis

R²

0.663

Adjusted R²

0.657

n

168

R

0.815

k

3

Std. Error

48113.205

Dep. Var.

Selling Price

ANOVA table

Source

SS

df

MS

F

p-value

Regression

748,488,382,441.7140

3  

249,496,127,480.5710

107.78

1.38E-38

Residual

379,640,397,551.4290

164  

2,314,880,472.8746

Total

1,128,128,779,993.1400

167  

Regression output

confidence interval

variables

coefficients

std. error

   t (df=164)

p-value

95% lower

95% upper

Intercept

99,283.9942

13,785.0265

7.202

2.03E-11

72,064.9828

126,503.0056

Lot Cost

4.0335

0.2867

14.070

5.36E-30

3.4674

4.5995

Type

-17,716.9597

24,890.1425

-0.712

.4776

-66,863.4066

31,429.4872

costtype

-1.3098

0.5368

-2.440

.0157

-2.3696

-0.2500

Selling price = 99,283.9942+4.0335 *cost-17,716.9597*Townhouse-1.3098*costtype

The model with interaction is best because its R square value is large compared to the without interaction model.

3)What is the predicted price for either a single family home or a townhouse with a lot cost of $30,000

Predicted a townhouse with a lot cost of $30,000

Selling price = 99,283.9942+4.0335 *30000-17,716.9597-1.3098*30000

=$163278.03

Regression Analysis

R²

0.651

Adjusted R²

0.647

n

168

R

0.807

k

2

Std. Error

48830.238

Dep. Var.

Selling Price

ANOVA table

Source

SS

df

MS

F

p-value

Regression

734,704,076,454.2360

2  

367,352,038,227.1180

154.07

1.81E-38

Residual

393,424,703,538.9070

165  

2,384,392,142.6600

Total

1,128,128,779,993.1400

167  

Regression output

confidence interval

variables

coefficients

std. error

   t (df=165)

p-value

95% lower

95% upper

Intercept

116,426.1432

12,037.5556

9.672

8.26E-18

92,658.6441

140,193.6423

Lot Cost

3.6599

0.2460

14.879

2.66E-32

3.1742

4.1455

Type

-73,847.4117

9,650.4182

-7.652

1.56E-12

-92,901.6378

-54,793.1856

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

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