Part 1: Pretend you are a real estate agent in Northampton, MA. You have two clients with different questions. Given your data, you can answer both their questions with a regression.

1. Client 1: Client 1 wants to live in Northampton. He has a moderate interest in walkability and wants to know what a house with an average number of bedrooms, garage space, number of full baths, number of square feet, number of rooms, and walk friendliness would sell for.

2. Client 2: Client 2 currently lives in Northampton. She owns a three bedroom, two bathroom house that is 2,000 square feet with a garage on .5 acre of land. Prior to selling her house, she is considering building an addition to her house with one extra bedroom and one half bath that would add about 300 square feet to her house. She would like to know how much the price of the house might change as a result.

For both clients, write down the equation of an appropriate multiple regression model that you can estimate with Excel to answer their questions. Identify the specific variables in the data set that you would use and discuss what values you would put into the regression model to answer their questions. If you need to calculate additional information in Excel (e.g., for client 2), then write down what you would do to calculate that additional information.

2.1

Before getting started, change the 1060 or 1062 variable to a 1 if in Northampton and a 0 otherwise. (Hint, use find and replace in Excel for that column).

Client 1

Create a copy of the data set by right-clicking the tab at the bottom and clicking Move or Copy..., then create another copy of the data set in the same workbook. Label this copy Client 1 and the original tab Original Data. Now click on the tab for Client 1. Delete all variables except the ones that you need, and use price 2014 for the price of the house. Make sure price 2014 is the far left variable, then estimate the model where price2014 is a function of the other variables. Then answer the questions:

1. Print out a copy of your Excel output.

2. For each variable, explain what the sign of the regression coefficient was, what the magnitude was (i.e., if x goes up by one unit how much does y increase by), and whether or not the results of your regression were consistent with what you believed the sign and magnitude to be. Then explain if any variables were insignificant.

3. Write down the equation of your regression line, including all variables. Next, calculate the average value for each of your six explanatory variables using the AVERAGE() function in Excel. Finally, make a prediction for the price if each of the variables take their average value.

Data

Name: Northampton Housing Price, Neighborhood Characteristics, and Railtrail Proximity Dataset

Type: Natural experiment

Size: 30 variables / 104 observations

Article Title: Rail-Trails and Property Values: Is there an Association?

Descriptive Abstract:

This dataset comprises 104 homes in Northampton: MA that were sold in 2007. We measured the shortest distance from each home to a railtrail on streets and pathways with Google maps and recorded the Zillow.com estimate of each homes price in 1998 and 2011. Additional attributes such as square footage: number of bedrooms and bathrooms are available from a realty database from 2007. We divide the houses into two groups based on distance to the trail (distgroup).

Sources:

Housing attribute data from the Multiple Listing Service. Housing prices in 1998 are from Zillow.coms 10-year price estimate while values from 2007: 2011 and 2014 are from Zillow.com's current price estimator. Distance from homes to the Rail-trail were mapped in Google maps "my places" feature by the first author and can be retraced by drawing a line from the home to the nearest railtrail access point. Bikescores and Walkscores from walkscore.com.

Variable Descriptions:

housenum: unique house number

acre: number of acres that the property encompasses

acregroup: less than or equal to 1/4 acre or greater than 1/4 acre

adj1998: estimated 1998 price in thousands of 2014 dollars

adj2007: estimated 2007 price in thousands of 2014 dollars

adj2011: estimated 2011 price in thousands of 2014 dollars

bedgroup: 1-2, 3, 4+ bedrooms

bedrooms: number of bedrooms

bikescore: bike friendliness (0-100 score, higher scores are better)

diff2014: difference in price between 2014 estimate and adjusted 1998 estimate (in thousands of dollars)

distance: distance (in feet) to the nearest entry point to the rail trail network

distgroup: distance is "Closer" (less than or equal to half mile) or "Farther Away" (greater than half a mile

garage_spaces: number of garage spaces (0-4)

garagegroup: any garage spaces (yes/no)

latitude:

longitude:

no_full_baths: number of full baths (includes shower or bathtub)

no_half_baths: number of half paths (no shower or bathtub)

no_rooms: number of rooms

pctchange: percentage change from adjusted 1998 price to 2014 (value of zero means no change)

price1998: Zillow 10 year estimate from 2008 (in thousands of dollars)

price2007: Zillow price estimate from 2007 (in thousands of dollars)

price2011: Zillow price estimate from 2011 (in thousands of dollars)

price2014: Zillow price estimate from 2014 (in thousands of dollars)

sfgroup: squarefeet less than or equal to 1500 or greater than 1500 sf

squarefeet: square footage of interior finished space (in thousands of sf)

streetname: name of street

streetno: house number on street

walkscore: walk friendliness (0-100 score, higher scores are better)

zip: location (1060 = Northampton: 1062 = Florence)