Answer Happy

Regression Analysis of Real Estate Data Read the Multiple Regression Overview (Mult Regression Overview tab below) Refer to the data below which report information on homes sold in Somewhere, USA this year. 1. Perform correlation analysis of the independent variables and show the output 2. Use the selling price of the home (Y) as the dependent variable and determine the regression equation (model) based on the following independent variables: number of bedrooms (BE), size of the house (SF), whether there is a pool (P), distance from the center of the city (D), whether there is an attached garage G), and the number of bathrooms (BA). 3. Interpret each of the coefficients of partial determination. 4. Predict the selling price of a 2,500 square feet house that has 5 bedrooms, 3 bathrooms, a 3-car attached garage, no pool, and is at 18 miles from the city center. 5. Determine whether the p value of each independent variable is above 5% to decide removal from the model, then re-run the regression analysis using only independent variables with significant (p<0.05) regression coefficients. Show the new model. Instructions: yes, 0 = Pool (1 = Selling Number of Size in sq. ft. yes, 0 = Price ($) bedrooms no) Y BE SF Р 263,100 4 2,300 1 182,400 4 2,100 0 242,100 3 2,300 0 213,600 2 2,200 0 139,900 2 2,100 0 245,400 2 2,100 1 327,200 6 2,500 0 271,800 2 2,100 0 0 221,100 3 2,300 1 266,600 4 2,400 0 292,400 4 2,100 0 209,000 2 1,700 0 270,800 6 2,500 0 246,100 4 2,100 0 194,400 2 2,300 0 281,300 3 3 2,100 0 1 Mult Regression Overview Data ady Distance Attached Number of to CBD in garage (1 = miles bathrooms D G BA 17 1 2.0 19 0 2.0 12 0 2.0 16 0 2.5 28 0 1.5 12 1 2.0 15 1 2.0 9 1 1 2.5 18 0 1.5 13 1 2.0 14 1 2.0 8 1 1.5 7 1 2.0 18 1 2.0 11 0 2.0 16 1 2.0 Sheet2 + TIIULIT. 1. Activate the needed Excel tools: File/Options/Add-ins/Manage: Excel Add-ins/Go/check Analysis ToolPak and Solver Add-in/OK 2. Provide all your outputs and answers on this same worksheet. 3. For #1 (correlation analysis): Data/Data Analysis/Correlation/OK Click inside the Input Range box and Highlight the cells containing the Independent Variables data, including their labels. Check the Labels in First Row box Select Output and indicate the cell where it will go. OK Note: If two independent variables are highly correlated (r >=0.7, you may need to remove one of them from your model) 4. For #2, the expected results in your regression output: Coefficients Intercept 57034.90 Number of bedrooms 7117.97 Size in sq. ft. 38.00 Pool (1 = yes, 0 = no) -18321.45 Distance to CBD in miles -929.50 > U 7:21 2/13/
Н M к 0 0 7 3 3 1 0 1 11 16 16 21 Pool (1 = yes, 0 = no) -18321.45 Distance to CBD in miles -929.50 Attached garage (1 = yes, 0 = no) 35809.82 Number of bathrooms 23315.00 0 1 0 1 10 1 1 0 1 2 3 4 5 5 7 3 1 1 1 0 1 and the resulting regression model is Y = 57,034.9 +7,117.97 BE + 38 SF - 18,321.45 P .....etc. 5. For #3, one example for BE is "When the number of bedrooms increases by one unit (one bedroom), the selling price of the house will increase 7177.97 dollars." 6. For #4, plug the provided data in the model to solve for the price of that house. 7. For #5, based on analysis of p values, determine which independent variables to remove before re-running regression analysis with the remaining variables. 15 8 14 20 9 11 21 26 0 1 0 0 0 1 0 0 0 1 1 Note: p value should be < 0.05 to be an acceptable variable. 0 1 B 194,400 2 2,300 281,300 3 2,100 172,700 4 2,200 207,500 5 2,300 198,900 3 2,200 209,300 6 6 1,900 252,300 4 2,600 192,900 4 1,900 209,300 5 5 2,100 345,300 8 2,600 326,300 6 2,100 173,100 2 2,200 187,000 2 1,900 257,200 2 2,100 233,000 3 2,200 180,400 2 2,000 234,000 2 2 1,700 207,100 2 2,000 247,700 5 2,400 166,200 3 2,000 177,100 2 1,900 182,700 4 2,000 216,000 4 2,300 312,100 6 2,600 199,800 3 2,100 273,200 5 2,200 206,000 3 2,100 232,200 3 1,900 198,300 4 4 2,100 205,100 3 2,000 170 con Mult Regression Overview 0 0 0 0 1 1 0 1 2. 3 4 5 5 7 3 1 2.0 2.0 2.0 2.5 2.0 2.0 2.0 2.5 1.5 2.0 3.0 1.5 2.0 2.0 1.5 2.0 2.0 2.0 2.0 2.0 2.0 2.5 2.0 2.5 2.0 3.0 1.5 1.5 1.5 2.0 20 0 0 0 1 1 1 1 1 1 0 1 0 9 14 11 19 11 16 16 10 14 19 7 19 16 9 16 19 20 2A Sheet2 0 0 0 1 0 0 1 2. 3 4. 5 5 그 1 1 0 1 1 1 1 1 1 0 A מחבר Data +
B D H 5 7 G 2.0 2.0 3.0 3.0 2.5 3 2.0 1 2 3 7. 5 5 7 E 20 24 21 8 17 16 15 14 20 23 12 7 12 15 3 17 A 205,100 3 2,000 175,600 4 2,300 307,800 3 2,400 269,200 5 2,200 224,800 3 2,200 171,600 3 2,000 216,800 3 2,200 192,600 6 2,200 236,400 5 2,200 172,400 3 2,200 251,400 3 1,900 246,000 6 2,300 147,400 6 1,700 176,000 4 2,200 228,400 3 2,300 166,500 3 1,600 189,400 4 2,200 312,100 7 2,400 289,800 6 2,000 269,900 5 2,200 154,300 2 2,000 222,100 2 2,100 209,700 5 2,200 190,900 3 2,200 254,300 4 2,500 207,500 3 2,100 209,700 4 2,200 294,000 2 2,100 176,300 2 2,000 294,300 7 2,400 10nn Mult Regression Overview 1 1 1 0 0 1 0 1 0 0 0 0 1 0 0 1 0 0 0 1 0 0 1 1 1 1 1 0 1 0 0 1 1 1 1 0 1 0 1 0 1 1 0 1 1 0 1 1 1 1 0 1 1 1 1 0 1 1 0 1 1 2 B 7 5 5 2.0 2.0 2.0 2.0 2.0 3.0 2.0 2.0 1.5 2.5 2.0 3.0 3.0 2.5 2.0 2.0 2.0 2.0 2.0 2.0 2.0 2.5 2.0 2.0 7 19 24 13 21 11 13 9 13 18 15 10 19 13 17 8 3 2 1 2 3 1 5 חחח Aכב 2 1 6 1 20 Data Sheet2
UL 85 94 A 294,300 86 224,000 87 125,000 88 236,800 89 164,100 90 217,800 91 192,200 92 125,900 93 220,900 294,500 95 244,600 96 199,000 240,000 98 263,200 99 188,100 L00 243,700 L01 221,500 02 175,000 L03 253,200 04 155,400 05 186,700 06 179,000 07 188,300 L08 227,100 L09 173,600 -10 188,300 11 310,800 L12 293,700 13 179,000 L14 188,300 97 B С 7 2,400 3 1,900 2 1,900 4 2,600 4 2,300 3 2,500 2 2,400 2 2,400 2 2,300 6 2,700 2 2,300 3 2,500 4 2,600 4 2,300 2. 1,900 6 2,700 4 2,300 2 2,500 3 2,300 4 2,400 5 2,500 3 2,400 6 2,100 4 2,900 4 2,100 5 2,300 8 2,900 6 2,400 3 2,400 6 2,100 3000 Mult Regression Overview E 8 6 18 17 19 12 16 28 12 15 9 18 13 14 8 7 D 0 1 0 1 0 0 0 0 1 0 0 1 0 0 0 0 0 0 0 1 1 1 1 0 1 0 0 0 0 1 n Data F 1 1 0 1 0 0 0 0 1 1 1 0 1 1 1 1 1 0 1 0 0 1 1 1 1 0 1 1 1 1 G 2.0 2.0 1.5 2.0 2.0 2.0 2.5 1.5 2.0 2.0 2.5 1.5 2.0 2.0 1.5 2.0 2.0 2.0 2.0 2.0 2.5 2.0 2.0 2.0 2.5 1.5 2.0 3.0 2.0 2.5 15 18 11 16 16 Ooo 21 10 15 8 14 20 9 11 8 14 an Sheet2 15 277 10 1
2.1 112 113 114 115 116 117 118 293,700 179,000 188,300 227,100 173,600 188,300 6 3 6 4 4 5 2,400 2,400 2,100 2,900 2,100 2,300 0 0 1 0 0 0 11 8 14 20 9 11 1 1 1 0 1 1 3.0 2.0 2.5 1.5 2.0 3.0