Question

The following data give the selling price and square footage of houses that have sold in Bend, OR in the past 6 months.

Selling Price ($)	Square Footage
84,000	1,670
79,000	1,339
91,500	1,712
120,000	1,840
127,500	2,300
132,500	2,234
145,000	2,311
164,000	2,377
155,000	2,736
168,000	2,500
172,500	2,500
174,000	2,479
175,000	2,400
177,500	3,124
184,000	2,500
195,500	4,062
195,000	2,854

1. Graph the data to see whether a linear equation might describe the relationship between selling price and the square footage of a house. Be sure to include suitable descriptors, elements, and other identifying characteristics on the chart (chart title, legend, axis titles, etc.).
2. Given the chart, describe why it would or would not be appropriate to use a linear regression model with this data set.
3. Develop a simple regression model relating selling price to square footage using the SLOPE and INTERCEPT functions.
4. Use the Data Analysis tool in Excel to develop the same simple regression model as in Part C.
5. Use the model to predict the selling price of a 2,000 square foot house.
6. If you were going to use multiple regression to develop an appraisal model, what other quantitative variables might be included? Should you be concerned about using certain variables? Explain your rationale.
7. A 2,000 square foot house recently sold for $165,000. Explain why this is not what the model predicted.

PART B:   The following data give the selling price, square footage, and age of houses that have sold in a Bend, OR in the past 6 months (note that this is the same base data as Part A, above, with new variables added).

Selling Price ($)	Square Footage	Age (Years)
84,000	1,670	30
79,000	1,339	25
91,500	1,712	30
120,000	1,840	40
127,500	2,300	18
132,500	2,234	30
145,000	2,311	19
164,000	2,377	7
155,000	2,736	10
168,000	2,500	1
172,500	2,500	3
174,000	2,479	3
175,000	2,400	1
177,500	3,124	0
184,000	2,500	2
195,500	4,062	10
195,000	2,854	3

8. Use the Data Analysis tool to develop a multiple regression model relating selling price to square footage and age.

(Hint: multiple regression means that you use more than one "x" variable to predict changes in the "y" variable. Look at Step d, below. Which variable are you predicting? Which variables – or values – are you given in order to predict it?)

1. Identify the coefficient of determination, the slope coefficients and the intercept value in the multiple regression summary output that was produced by Excel.

10. Use the multiple regression model developed in part h, above, to predict the selling price of a 2000 square foot house that is 30 years old.
11. A realtor is contemplating her appraisal of a beautiful 4,500 square foot, 5 bedroom house that was built in the Victorian era. Would it be reasonable to use this model to forecast the selling price of that house? Explain your answer.
12. l.Compute the coefficient of determination for the simple regression model developed in Step C (note that there are Excel functions that do this). What does this tell you? Of the two methods, which appears to yield the best forecast? Explain your answer.

Answer 1

Solution:

select the data go to

insert>scatter

click on+ to add axis titles

We get

from scatterplot

Solutin-b;

From graph :

Form:linear

strength:strong

Direction:positive

appropriate to use a linear regression model with this data set.

Solution-c:

SLOPE=SLOPE(B2:B18,A2:A18)
51.02721
Y INTERCEPT=INTERCEPT(B2:B18,A2:A18)	358.5583

26532.24

FORMULAS USED

=SLOPE(B2:B18,A2:A18)

=INTERCEPT(B2:B18,A2:A18)

slope=

51.02721

y intercept=

26532.24

Regression equation =

y^=yintercept+slope*x

y^=26532.24+51.02721*X

Solution-d:

Install analysis toolpak

Data >Data analysis regresiion

select Y as selling price

X as square footage

You get

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.83664
R Square	0.699967
Adjusted R Square	0.679965
Standard Error	21360.3
Observations	17
ANOVA
	df	SS	MS	F	Significance F
Regression	1	1.6E+10	1.6E+10	34.99449	2.83E-05
Residual	15	6.84E+09	4.56E+08
Total	16	2.28E+10
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	26532.24	21408.36	1.23934	0.234261	-19098.6	72163.07	-19098.6	72163.07
Square Footage	51.02721	8.625852	5.915614	2.83E-05	32.64164	69.41278	32.64164	69.41278

From output

Regression eq is

y^=26532.24+51.02721*x

Use the model to predict the selling price of a 2,000 square foot house.

We have

selling price=26532.24+51.02721*square footage

for square footage=2000 we get

selling price=26532.24+51.02721*2000

=128586.7