The Prescott County Housing Problemhttp://1.bp.blogspot.com/_XCmydF9EO6A/SxD1-4dLejI/AAAAAAAABfQ/oRSDJ_sor5c/s400/2008-07-01+54+Flora+house.JPGQnt. 5040 – Mini Report #1
Regressions

Introduction:

The Prescott county mayor, Robert (Pete) Smith has been worried for some time that housing values in the county have been declining. Pete said to the county commission recently,

Cloud Callout: We want a park.“Our housing stock is getting so old and tired, and I’m afraid that if we don’t start building new homes that our children will just move away to Orlando or even to, heaven forbid, to South Florida. I think that we need to study this situation, and do something about it right now!”

http://3.bp.blogspot.com/-Mwr4UZALiA0/TWBt-3vFR8I/AAAAAAAAA4Y/0tXjI-NhVPM/s1600/j0262568.jpgWhat Pete did not say, but what each of the commissioners knew, was his brother-in-law, Bo Bradley is a developer who wants the commission to rezone 350 acres in the north county for a new development. This land is currently envisioned to be a county park, but old Bo wants to develop it. Bo said recently, “What this county needs is my development, not some old park for them deer.” Bo it seems is interested in making money more than he is in protecting undeveloped land for the public.

In order to get this study going Pete has asked you to look at some recent sales of homes in the county to understand what is going on with the housing stock, and then to project out what kind of values that five typical housing could bring when sold. What he is hoping is that the values will be so low that the commission will want to rezone those 350 acres for Bo’s development.

Using the Excel data file that has been provided you are to completely answer the following questions:

1. What is the current status of the housing stock in the county?
a. To do this you will create a one-variable summaryusing StatTools and analyze the size of the recently sold homes, their average price sold, the number of bedrooms, bathrooms and number of cars that can be garaged, if they have a pool or not, and how far they are from the city center[1].
b. What does the skewness and kurtosis tell you about these data?
c. Would it be better to use the Interquartile range to analyze this data (not a yes or no answer) and if so why, or why not?
2. Doing two Q-Q plots, do you consider the data for price and square footage to be normal or not, and why?
3. Doing a correlation in StatTools and using all eight of the variables, how are each of these variables correlated to each other. Which ones are important or unimportant in the case study and why? Again, be specific.
4. Doing a scatterplot of price versus square footage and adding a trend line to the plot, what does this tell you about the data?
a. Now do a scatterplot of price versus age and adding a trend line[2], what does this tell you about the question of new homes versus price?
b. What does the equation tell you about the impact of these two variables, and what does the r-squared value tell you?
5. Next do a multiple regressionusing price as the dependent variable, and all other variables as independent variables:
a. Do any of the variables have a t-value that is greater than the alpha (.05) for this assignment? If so, delete them and rerun the regression and compare and contrast the old regression versus the new regression without one or more of the variables.
b. Is the F-ratio for this/these regressions significant? Why?
c. Is the r-squared values for this/these regressions appear to be valid? Does it show that it explained a sufficient amount of the total variation?
6. Using the coefficients from this regression estimate the selling prices for the following typical Prescott county homes.

NOTE: If you have deleted any of the variables because they exceed alpha then only use those variables that apply to the new regression. If you have not deleted any variables than use all of the data found below. Explain in the report how you came up with the projected values for these homes. Table One below must be included in your mini report.

Table 1. Typical homes found in Prescott County Florida[3]
Home number
Number of bedrooms
Square footage
Pool
Distance from city center
Township
Size of the garage (cars)
Number of bathrooms
Value (as determined by you)
1
3
1,850
0
11
1
1
2
$
2
4
2,200
1
13.5
3
2
3
$
3
5
3,000
1
16
4
3
4
$
4
5
3,400
0
19
5
3
5
$
5
3
2,200
1
15
1
1
2
$

· Bedrooms = number of bedrooms in the home.
· Size = Square footage of the home.
· Pool = yes = 1 no = 0.
· Distance = distance from Prescott City Center.
· Twnship = number of township in which the home is located.
· Garage = yes = 1; no = 0.
· Baths = number of bathrooms in the house.
· Value = the value as determined by YOU based on the multiple regression(s) you conduct.

7. Based on the projected selling price of these homes, will Mayor Pete convince the county commission to rezone the land for his brother-in-law, or does the county get a new park? Defend your answer based on the statistics that you have calculated in this case study. Your argument should be no more than 300 words.

Submit your mini report by the date indicated in the weekly/monthly calendar for your section.

# # #

Adding a Trendline to a Graph in Word

First, create the XY scatterplot graph in Excel as shown by your professor during the chats.