Question

Use Excel Analysis ToolPak to solve.

Recent family home sales in San Antonio provided the following data (San Antonio Realty Watch website, November 2008).

Square Footage   Price ($)
1580   142,500
1572   145,000
1352   115,000
2224   155,900
1556   95,000
1435   128,000
1438   100,000
1089   55,000
1941   142,000
1698   115,000
1539   115,000
1364   105,000
1979   155,000
2183   132,000
2096   140,000
1400   85,000
2372   145,000
1752   155,000
1386   80,000
1163   100,000

a. Develop the estimated regression equation that can be used to predict the sales prices given the square footage.
b. Construct a residual plot of the standardized residuals against the independent variable.
c. Do the assumptions about the error term and model form seem reasonable in light of the residual plot?

Answer 1

a. Develop the estimated regression equation that can be used to predict the sales prices given the square footage.

	r²	0.570
	r	0.755
	Std. Error	19166.000
	n	20
	k	1
	Dep. Var.	Price ($)
ANOVA table
Source	SS	df	MS	F	p-value
Regression	8,74,85,62,230.8129	1	8,74,85,62,230.8129	23.82	.0001
Residual	6,61,20,39,769.1871	18	36,73,35,542.7326
Total	15,36,06,02,000.0000	19
Regression output					confidence interval
variables	coefficients	std. error	t (df=18)	p-value	95% lower	95% upper
Intercept	22,635.9491
Square Footage	58.9595	12.0814	4.880	.0001	33.5775	84.3416

The estimated regression equation is:

Price = 22,635.9491 + 58.9595*Square Footage

b. Construct a residual plot of the standardized residuals against the independent variable.

Observation	Price ($)	Predicted	Residual
1	1,42,500.0	1,15,792.0	26,708.0
2	1,45,000.0	1,15,320.3	29,679.7
3	1,15,000.0	1,02,349.2	12,650.8
4	1,55,900.0	1,53,762.0	2,138.0
5	95,000.0	2-8-2020 10:35.06	-19,377.0
6	1,28,000.0	1,07,242.9	20,757.1
7	1,00,000.0	2-8-2020 10:35.06	-7,419.8
8	55,000.0	86,842.9	-31,842.9
9	1,42,000.0	1,37,076.4	4,923.6
10	1,15,000.0	1,22,749.2	-7,749.2
11	1,15,000.0	1,13,374.7	1,625.3
12	1,05,000.0	1,03,056.8	1,943.2
13	1,55,000.0	1,39,316.9	15,683.1
14	1,32,000.0	1,51,344.6	-19,344.6
15	1,40,000.0	1,46,215.1	-6,215.1
16	85,000.0	1,05,179.3	-20,179.3
17	1,45,000.0	1,62,488.0	-17,488.0
18	1,55,000.0	1,25,933.1	29,066.9
19	80,000.0	1,04,353.9	-24,353.9
20	1,00,000.0	91,205.9	8,794.1

c. Do the assumptions about the error term and model form seem reasonable in light of the residual plot?

The assumptions about the error term and model form seem reasonable from the residual plot.