Question

Excel Problem 2 - Chapter 12: 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

1. 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?)

2. Identify the coefficient of determination, the slope coefficients and the intercept value in the multiple regression summary output that was produced by Excel.
3. 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.
4. 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.
5. 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.

You are on the right track if the slope value for Step C is 51.03; if the intercept value for the multiple regression under Step H is 94195.1; and if the forecasted value for Step J is 111978.6

Answer 1

Part a.

The excel report is as follows:

Part b.

The coefficient of determination = r² = 0.87

Intercept = 9,4195.09

Slope coefficient of Square footage = 31.45

Slope coefficient of Age (years) = -1504.11

Thus, the regression equation can be given as follows:

Selling Price ($) = 94195.09 + 31.45(Square footage) + (-1504.11)(Age)

Part c.

Predict the selling price of a 2000 square foot house that is 30 years old.

Selling Price ($) = 94195.09 + 31.45(Square footage) + (-1504.11)(Age)

Selling Price ($) = 94195.09 + 31.45(2000) + (-1504.11)(30)

Selling Price ($) =$111,978.57

Part d.

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.

Since the data is for the region at Bend, OR, the condition or parameters in the other region might not be same. Thus it is not reasonable to use the same equation for the other region. Also the age of the house is not provided.