Real Estate Regression Exercise Directions: Use the real estate data you used for your Week 2 learning team assignment. Analyze the data and explain your answers. You are consulting for a large real estate firm. You have been asked to construct a model that can predict listing prices based on square footages for homes in the city you’ve been researching. You have data on square footages and listing prices for 100 homes. 1. Which variable is the independent variable (x) and which is the dependent variable (y)? 2. Click on any cell. Click on Insert→Scatter→Scatter with markers (upper left). To add a trendline, click Tools→Layout→Trendline→Linear Trendline Does the scatterplot indicate observable correlation? If so, does it seem to be strong or weak? In what direction? 3. Click on Data→Data Analysis→Regression→OK. Highlight your data (including your two headings) and input the correct columns into Input Y Range and Input X Range, respectively. Make sure to check the box entitled “Labels”. (a) What is the Coefficient of Correlation between square footage and listing price? (b) Does your Coefficient of Correlation seem consistent with your answer to #2 above? Why or why not? (c) What proportion of the variation in listing price is determined by variation in the square footage? What proportion of the variation in listing price is due to other factors? (d) Check the coefficients in your summary output. What is the regression equation relating square footage to listing price? (e) Test the significance of the slope. What is your t-value for the slope? Do you conclude that there is no significant relationship between the two variables or do you conclude that there is a significant relationship between the variables? (f) Using the regression equation that you designated in #3(d) above, what is the predicted sales price for a house of 2100 square feet? Address 237 Woodhill, Mankato 232 Woodhill, Mankato 112 S Mayfair Drive, Mankato 105 Larkspur, Mankato 121 Kestrel, Mankato 119 Red Oak, Mankato 133 Hidden Oaks, Mankato 101 Mount Curve, Mankato 20031 Hawkeye, Mankato 105 Sunburst, Mankato 148 Country Club, Mankato 2800 Monks, Mankato 129 Joshua, Mankato 101 Buckingham, Mankato 101 Manchester, Mankato 56601 200, Mankato 136 Pebble Creek, Mankato 817 Tanager, Mankato 100 Tanager, Mankato 1009 N 5th, Mankato 1946 Third, Mankato 10 Trail, Mankato 1001 Applewood, Mankato 102 Red Oak, Mankato 20455 598th, Mankato 120 Pollei, Mankato 124 Viking, Mankato 224 Sunset, Mankato 101 Jacob, Mankato 54557 Grouse, Mankato 112 Cedar, Mankato 317 Meadow Woods, Mankato 133 Timberwolf, Mankato 10 Hensonshire, Mankato 304 Woodhaven, Mankato 20186 589th, Mankato 304 Tanager, Mankato 370 Hensonshire, Mankato 105 Lynx, Mankato 421 Woodhaven, Mankato 108 Lynx, Mankato 205 Copperfield Drive, Mankato 19196 State Hwy 22, Mankato 204 Wickfield, Mankato 505 River Hills, Mankato 112 Center, Mankato Listing Price Square footage $839,900 $799,900 $189,000 $895,000 $799,000 $675,000 $549,900 $459,900 $429,000 $415,000 $409,900 $399,900 $379,000 $349,899 $344,900 $269,900 $249,900 $239,900 $203,000 $174,500 $164,900 $895,000 $698,000 $599,000 $519,900 $510,000 $499,000 $495,000 $399,900 $395,000 $349,000 $339,000 $329,900 $329,500 $309,900 $299,900 $289,900 $275,000 $264,900 $262,000 $259,999 $258,900 $249,900 $239,900 $239,900 $239,900 Number of Bedrooms 6,920 6,120 2,603 6,271 6,206 4,853 4,138 5,780 3,837 3,416 4,217 5,088 3,840 4,089 4,124 3,328 2,612 2,448 2,810 2,712 2,256 7,018 4,202 5,704 4,692 4,142 3,250 3,879 4,774 5,014 3,300 4,434 3,782 3,082 2,984 2,912 2,548 2,344 2,772 2,640 2,472 2,290 3,378 2,424 2,456 4,066 6 6 6 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 5 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 212 Falcon, Mankato 229 Wadsworth, Mankato 653 Grayhawk Dr, Mankato 210 Dane, Mankato 204 Royal, Mankato 229 Cypress, Mankato 108 Trumpeter, Mankato 100 Olympic, Mankato 116 N Belmont, Mankato 200 Thro, Mankato 310 Fulton, Mankato 2712 E Main, Mankato 215 E Pleasant, Mankato 155 James, Mankato 1012 Monks, Mankato 208 Alta, Mankato 60418 200th, Mankato 54648 211, Mankato 2216 Shalom, Mankato 313 Dancing Waters, Mankato 117 Creekside, Mankato 100 Creekside, Mankato 309 Dancing Waters, Mankato 2009 Shalom, Mankato 116 Redtail, Mankato 117 Hope, Mankato 15 E Raven Rock, Mankato 125 Goldfinch, Mankato 304 Woodshire, Mankato 216 Muriefield, Mankato 300 Tranquility, Mankato 1028 Woodland Ave, Mankato 604 Tranquility, Mankato 305 Homestead, Mankato 105 Cole, Mankato 19975 S Hwy 22, Mankato 19982 Mill Rae, Mankato 144 Savannah, Mankato 132 Catalina, Mankato 104 W Welcome, Mankato 121 Jacob, Mankato 170 Terrace View East, Mankato 303 Floral, Mankato 424 Deldona, Mankato 212 Terrace, Mankato 808 W Pleasant, Mankato 505 W 8th, Mankato $234,900 $234,000 $232,500 $225,000 $225,000 $224,900 $224,900 $223,900 $219,900 $219,900 $184,900 $182,700 $179,900 $175,500 $159,900 $156,600 $799,000 $699,000 $454,900 $419,900 $409,900 $395,000 $389,900 $379,900 $359,900 $334,900 $319,450 $309,900 $289,500 $285,900 $264,000 $254,900 $239,000 $226,500 $224,900 $219,900 $204,900 $199,000 $189,900 $184,900 $182,500 $179,900 $164,500 $159,900 $159,900 $155,000 $152,500 3,036 3,192 2,324 2,529 3,528 2,130 2,324 2,096 1,920 2,496 3,349 2,112 4,429 2,310 2,208 2,428 3,560 4,600 2,223 2,167 3,576 2,221 1,785 1,727 1,869 3,354 3,200 2,656 2,596 2,628 3,444 2,576 2,607 1,976 2,424 2,624 2,408 1,984 2,936 2,702 1,740 2,322 2,758 2,576 2,408 2,650 2,304 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 4 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 3 727 Belwood, Mankato 1424 Fair, Mankato 717 N 6th, Mankato 520 W Pleasant, Mankato 1016 N 4th, Mankato 928 N Broad, Mankato 324 N Broad, Mankato Total $150,000 $149,900 $149,900 $149,900 $146,000 $144,900 $144,900 $32,334,648 2,494 1,702 1,585 3,145 2,063 2,016 2,036 316,280 3 3 3 3 3 3 3 Number of bedrooms 3 4 5 6 Absolute frequency 38 41 18 3 Relative frequency 0.38 0.41 0.18 0.03 Histogram 45 40 Frequency 35 30 25 20 15 10 5 0 3 4 5 6 Number of bedrooms 100% 3 6 18 5 41 4 38 3 0 10 20 30 40 50 Total number of Bedrooms Per House Total Number of Bedrooms Per House 3 18 4 3 5 6 41 38 41 38 Frequency Distribution Listing Price Frequency $140,000 up to $250,000 48 250,000 up to 360,000 23 360,000 up to 470,000 14 470,000 up to 580,000 5 580,000 up to 690,000 2 690,000 up to 800,000 5 800,000 up tp 910,000 3 Total 100 Relative Frequency 0.480 0.230 0.140 0.050 0.020 0.050 0.030 1.000 Frequency Distribution Histogram 60 50 40 30 Frequency 20 10 0 $140,000 250,000 up to up to $250,000 360,000 360,000 up to 470,000 470,000 up to 580,000 580,000 up to 690,000 690,000 up to 800,000 800,000 up tp 910,000 Parameter Mean Median Mode 1st quartile 3rd quartile Range St. deviation Variance Percentile (68) Percentile (95) Percentile (99) Mean+stdev Mean+2stdev Mean+3stdev Listing price $323,346 $259,450 $239,900 $192,175 $395,000 $750,100 $181,452 $32,924,801,312 $347,688 $799,000 $895,000 $504,798 $686,250 $867,702 Square footage 3,163 2,679 2,424 2,324 3,823 5,433 1,202 1,443,890 3370 6103 7017 4,364 5,566 6,768 Quartiles for Price Listings Minimum Value 100% 90% 80% 70% 60% 50% 40% 30% 20% 10% 0% 1st Quartile Median Value 3rd Quartile 895000 395000 259449.5 196725 144900 1 Quartiles For Square Footage 7018 3795.75 2679 2324 Maximum value 2324 1585 1 MINIMUM VALUE 1ST QUARTILE Median Value 3rd Quartile MAXIMUM VALUE
Purchase answer to see full attachment