dummy-variables

1. Today we will take another look at the predictors of home sale prices. We will estimate models with both dummy variables and other quantitative independent variables.
   On the open worksheet A – single, create a scatterplot for the house price data. Be sure that Lot size is on the x-axis and that Sale price is on the y-axis. Right click on one of the observations in the scatterplot and choose Add trendline. In the formatting pane that opens on the right-hand side, check the appropriate boxes to display the equation and R² value to the chart.
2. Left click on the box containing the equation and R², then increase the font size to make it readable.

   Copy the estimated values of alpha, beta and R² to the worksheet titled Results. Recall that beta is the value in the equation that gets multiplied by x.

3. Navigate to worksheet B – separate. You will see that there is an additional qualitative variable that indicates whether or not the house is in a desirable area. Now, follow the same steps as in 1 and create two separate scatterplots. One plot for homes in desirable areas and one for homes in undesirable areas. Record your regression estimates on the Results worksheet.
4. Navigate to worksheet C – two trendlines. This time, you will create a single scatterplot with both groups of homes but include two different trendlines. First, fill in column B for those houses in desirable locations. Then fill in the appropriate parts of column C. All you are doing is copying the sale price data to the appropriate column. Don’t change anything in Column A.

   You are trying to create the following:

   Column A should contain the lot sizes for both groups. Column B should contain the prices for homes in desirable locations and be blank for the others. Column C should contain the prices for homes in undesirable locations and be blank for the others.

5. Navigate to worksheet D – dummy var. Instead of creating a scatterplot, we will estimate the model using the built-in regression tool. First create a dummy variable for the desirable area variable. It should equal one for homes in desirable locations and zero otherwise.

   Go to the data tab at the top of the excel window and choose Data Analysis then select Regression. (If you need to install it, refer to one of the earlier labs for instructions.)

   Select your data as shown above and hit OK. Now you will see your results appear to the right in column F. Copy over the estimated coefficients and R² to the Results worksheet.

6. Last, navigate to worksheet E – dummy var and other IVs. Once again, estimate a regression model using the data analysis tool. Here however, go ahead and include two other quantitative variables: #beds and #baths. Here, you will need to select the data columns B, C, D, and E for the independent variables. Fill in the results.

Verify that your estimates are the same as what you found on worksheet B.

Finally, estimate the sale price for a home with a lot size of 6,000 sq. feet, 3 bedrooms, 2 bathrooms, that is
not in a desirable location. Enter your answer in B34.