Question

Explore the relationship between the selling price appraised value and the selling price.

(Draw a scatterplot and then do simple regression.)

. Draw a scatterplot first. What is the regression equation for Selling Price based on Appraised Value?

2. For which of the remaining variables is the relationship with the home's selling price Stronger?

3. Find a regression equation that takes into account ALL the variables in the data set.

4. What percent of a home's selling price is associated with all these v

House

Appraised Value

Selling Price (Y)

Square Feet (X)

Bedrooms (X)

Bathrooms(X)

1	119,370	121,870	2050	4	5
2	148,930	150,250	2200	4	4
3	130,390	122,780	1590	3	3
4	135,700	144,350	1860	3	3
5	126,300	116,200	1210	2	3
6	137,080	139,490	1710	3	2
7	123,490	115,730	1670	3	3
8	150,830	140,590	1780	3	4
9	123,480	120,290	1520	4	4
10	132,050	147,250	1830	2	3
11	148,210	152,260	1700	3	3
12	139,530	144,800	1720	3	4
13	114,340	107,060	1670	3	4
14	140,040	147,470	1650	3	3
15	136,010	135,120	1610	2	1
16	140,930	140,240	1570	3	4
17	132,420	129,890	1650	4	5
18	118,300	121,140	1640	3	4
19	122,140	111,230	1420	2	3
20	149,820	145,140	2070	4	3	149,820

Answer 1

1)

Following is the scatter plot of selling price against appraised value-

Then you can perform the simple linear regression in excel to get the following output -

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.875729814
R Square	0.766902707
Adjusted R Square	0.753952858
Standard Error	7123.208755
Observations	20
ANOVA
	df	SS	MS	F	Significance F
Regression	1	3004878321	3004878321	59.22097405	4.23985E-07
Residual	18	913321853.5	50740102.97
Total	19	3918200175
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	-17575.57081	19587.02547	-0.897306783	0.381400421	-58726.38432	23575.2427
Appraised Value	1.125611164	0.146268436	7.69551649	4.23985E-07	0.818312582	1.432909745

Thus, the fitted regression model is -

Selling Price = -17575.57081 + 1.125611164(Appraised Value)

____________________________________

2)

The regression output for other individual variables is as shown -

a) Selling Price vs Square Feet -

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.520865503
R Square	0.271300873
Adjusted R Square	0.230817588
Standard Error	12594.50729
Observations	20
ANOVA
	df	SS	MS	F	Significance F
Regression	1	1063011126	1063011126	6.70155284	0.01853284
Residual	18	2855189049	158621613.8
Total	19	3918200175
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	75783.99611	22149.37143	3.421496468	0.003043084	29249.8935	122318.0987
Square Feet (X)	33.33734108	12.87784624	2.588735761	0.01853284	6.281990088	60.39269208

b) Selling Price vs Bedrooms-

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.133407753
R Square	0.017797628
Adjusted R Square	-0.03676917
Standard Error	14622.02582
Observations	20
ANOVA
	df	SS	MS	F	Significance F
Regression	1	69734671.09	69734671.09	0.326162227	0.574989661
Residual	18	3848465504	213803639.1
Total	19	3918200175
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	124143.9106	15261.53921	8.134429231	1.9314E-07	92080.60653	156207.2147
Bedrooms (X)	2791.340782	4887.604194	0.571106143	0.574989661	-7477.134594	13059.81616

c) Selling Price vs Bathrooms -

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.16798841
R Square	0.028220106
Adjusted R Square	-0.025767666
Standard Error	14544.23932
Observations	20
ANOVA
	df	SS	MS	F	Significance F
Regression	1	110572023.8	110572023.8	0.52271292	0.478976724
Residual	18	3807628151	211534897.3
Total	19	3918200175
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	141380.119	12495.31514	11.31465013	1.29283E-09	115128.4361	167631.802
Bathrooms(X)	-2565.47619	3548.431052	-0.722988879	0.478976724	-10020.4532	4889.500816

Now note that the R-square value is less for all the other variables compared to Appraised Value. So, no other variable has stronger relationship with selling price than appraised value.

_____________________________

3)

The multiple regression model is as shown-

Thus, Selling price = -24463.40151 + 19.15206369(Square Feet) - 3921.481386(Bedrooms) + 382.3097127(Bathrooms) + 1.012288684(Appraised Value)

--------------

5) R-squared value is 0.8163.

So 81.63% variability is explained.