HSC 544 Excel Homework 7 40 Points Part 1 An important problem in real estate is determining
Question:
HSC 544
Excel Homework 7
40 Points
Part 1
An important problem in real estate is determining how to price homes to be sold. There are so many factorssize, age, and style of the home; number of bedrooms and bathrooms; size of the lot; and so onwhich makes setting a price a challenging task. In this project, we will try to help realtors in this task by determining how different characteristics of homes relate to home prices, identifying the key variables in pricing, and building multiple-variable regression models to predict prices based on property characteristics. Our analysis will be based on the Mount Pleasant Real Estate Data (available on stat.hawkeslearning.com). This data set includes information about 195 properties for sale in three communities in the suburban town of Mount Pleasant, South Carolina, in 2017. Consider the following variables associated with each property.
x1= number of bedrooms | x5=age |
x2=number of bathrooms | x6=fenced yard |
x3=number of stories | x7=golf course? |
X4=square footage | x8=number of fireplaces |
Go to Data Analysis ToolPak in Excel and use the Regression function. The linear regression equation will look like:
Where is a coefficient and each of the is an independent variable. In the context of real estate pricing, = predicted home price. Excel can calculate regression models with multiple variables via the same regression tool that it does for single-variable regression models by simply using more columns of data for the X inputs. Intuitively, this should be more realistic for real estate pricing as there may be several variables that contribute to property values.
- Construct the multiple regression equation with input variables x1, x2, ..., x8. (8 Points)
- What is the adjusted coefficient of determination, , of the regression model? Explain the meaning of this value and how it differs from. (4 Points)
- Perform a hypothesis test to determine if the model is useful for predicting home values at a significance level of = 0.05. State the followings:
- Determine the null and alternative hypotheses. (2 Points)
- What is the value of the test statistics (F statistics)? (2 Points)
- Determine the P-value. (2 Points)
- Make a decision to reject or fail to reject H0. (2 Points)
- State the conclusion in terms of the original question. (2 Points)
- Are any variables not useful predictors of home price at a significance level of = 0.05? State the P-values of these variables. Intuitively, what does this mean with respect to pricing properties? (Show your results) (8 Points)
Part 2
A government lobbying firm is interested in getting more money directed to people who have special needs. To convince state legislators that more money needs to be diverted to the California Department of Developmental Services, they first must determine which groups of people are in most need of those funds. This firm has hired you to conduct the analysis to answer the questions below.
Determine if there is a difference in the average expenditures by gender. Conduct one-way ANOVA to test the difference of mean expenditure and gender. Use a significance level of = 0.05.
- Determine the null and alternative hypotheses. (2 Points)
- What is the value of the test statistics? (2 Points)
- Determine the P-value. (2 Points)
- Make a decision to reject or fail to reject H0. (2 Points)
- State the conclusion in terms of the original question. (2 Points)
| Expenditures | |
| Male | Female |
| 28205 | 55430 |
| 54115 | 3726 |
| 39844 | 58392 |
| 3988 | 10434 |
| 13345 | 4629 |
| 7207 | 3392 |
| 386 | 1783 |
| 1315 | 54616 |
| 1725 | 30504 |
| 32344 | 2471 |
| 641 | 49851 |
| 1437 | 1814 |
| 4483 | 10696 |
| 2695 | 1432 |
| 1824 | 55015 |
| 42739 | 7571 |
| 11668 | 8530 |
| 5041 | 2059 |
| 7878 | 4054 |
| 4014 | 3529 |
| 3196 | 41984 |
| 2028 | 2900 |
| 41144 | 9504 |
| 4067 | 9200 |
| 1974 | 60198 |
| 3153 | 374 |
| 52076 | 37778 |
| 9369 | 47778 |
| 33721 | 3728 |
| 41013 | 75098 |
| 10385 | 11125 |
| 51599 | 1335 |
| 6255 | 10531 |
| 3075 | 8714 |
| 36712 | 55428 |
| 33278 | 2532 |
| 10535 | 8700 |
| 6620 | 3530 |
| 9846 | 49075 |
| 12265 | 2157 |
| 1594 | 46911 |
| 10976 | 2917 |
| 12392 | 32886 |
| 2597 | 6771 |
| 11282 | 1339 |
| 3419 | 3060 |
| 4710 | 39454 |
| 3707 | 28581 |
| 12234 | 52421 |
| 50542 | 18435 |
| 9923 | 7683 |
| 1691 | 13264 |
| 48064 | 40240 |
| 30803 | 11803 |
| 25348 | 48968 |
| 40999 | 4216 |
| 33634 | 12243 |
| 54481 | 34274 |
| 9979 | 1645 |
| 4031 | 7475 |
| 2623 | 8820 |
| 9080 | 44293 |
| 3099 | 1219 |
| 12651 | 57497 |
| 42560 | 34795 |
| 40614 | 3867 |
| 3382 | 2559 |
| 49449 | 42192 |
| 7288 | 8730 |
| 3873 | 5324 |
| 5696 | 46873 |
| 37247 | 2341 |
| 46513 | 1398 |
| 4814 | 37697 |
| 8687 | 4949 |
| 1212 | 48021 |
| 4554 | 1790 |
| 2797 | 54680 |
| 3909 | 41522 |
| 43545 | 3584 |
| 49056 | 1733 |
| 44910 | 31853 |
| 4918 | 1611 |
| 12259 | 3125 |
| 36576 | 2834 |
| 38085 | 51084 |
| 46132 | 43109 |
| 3948 | 7061 |
| 5583 | 5183 |
| 5025 | 240 |
| 1854 | 6400 |
| 55442 | 4566 |
| 38400 | 1748 |
| 4166 | 4515 |
| 52618 | 46571 |
| 2261 | 2354 |
| 11491 | 2895 |
| 58594 | 1915 |
| 28061 | 3212 |
| 60808 | 1130 |
| 9675 | 3638 |
| 11327 | 1423 |
| 1454 | 4395 |
| 2887 | 39521 |
| 3048 | 4525 |
| 10047 | 60871 |
| 1861 | 49028 |
| 1598 | 1977 |
| 1066 | 3450 |
| 674 | 3007 |
| 4968 | 40980 |
| 4091 | 51149 |
| 4798 | 11038 |
| 11998 | 12649 |
| 50304 | 2664 |
| 38310 | 59926 |
| 7621 | 5920 |
| 15018 | 40277 |
| 3796 | 8096 |
| 1363 | 3678 |
| 1713 | 15176 |
| 9161 | 1963 |
| 3269 | 995 |
| 1196 | 16237 |
| 9972 | 2119 |
| 2549 | 10292 |
| 2686 | 3501 |
| 4097 | 1582 |
| 1957 | 5718 |
| 1646 | 3141 |
| 1525 | 2039 |
| 1294 | 43858 |
| 1435 | 2291 |
| 1123 | 40809 |
| 1740 | 3959 |
| 4670 | 3378 |
| 3394 | 1473 |
| 9592 | 7667 |
| 741 | 6218 |
| 1016 | 59622 |
| 4723 | 4921 |
| 2460 | 35088 |
| 1028 | 3695 |
| 3585 | 1767 |
| 762 | 36738 |
| 7081 | 7605 |
| 2359 | 38031 |
| 2211 | 12083 |
| 11527 | 10294 |
| 966 | 16310 |
| 7339 | 11811 |
| 4513 | 2781 |
| 4112 | 48684 |
| 4653 | 3257 |
| 12587 | 4495 |
| 668 | 2744 |
| 2015 | 2906 |
| 8948 | 5637 |
| 561 | 2904 |
| 1084 | 39147 |
| 49502 | 4774 |
| 3522 | 36691 |
| 2776 | 5040 |
| 10150 | 2797 |
| 2954 | 2016 |
| 1173 | 2854 |
| 4533 | 1592 |
| 62097 | 4696 |
| 11935 | 4163 |
| 11034 | 3562 |
| 2490 | 8469 |
| 3945 | 1363 |
| 1597 | 36827 |
| 4975 | 3539 |
| 2247 | 8869 |
| 1758 | 3379 |
| 51392 | 3580 |
| 3561 | 5228 |
| 6397 | 13129 |
| 7446 | 4152 |
| 5163 | 3190 |
| 52243 | 2184 |
| 51777 | 4527 |
| 3141 | 3003 |
| 53211 | 7122 |
| 2167 | 2191 |
| 55187 | 1641 |
| 4975 | 42041 |
| 2755 | 47954 |
| 2810 | 1632 |
| 3745 | 2339 |
| 3445 | 4345 |
| 4608 | 9061 |
| 46951 | 4798 |
| 2746 | 1713 |
| 4219 | 2462 |
| 52147 | 4884 |
| 3113 | 3464 |
| 776 | 5066 |
| 10230 | 456 |
| 3443 | 1147 |
| 5412 | 4731 |
| 57363 | 5616 |
| 54875 | 27845 |
| 11986 | 2409 |
| 47420 | 10247 |
| 4300 | 3698 |
| 4787 | 2308 |
| 4931 | 1254 |
| 11479 | 14926 |
| 4058 | 2143 |
| 1539 | 13579 |
| 1286 | 2797 |
| 3629 | 1417 |
| 2153 | 1903 |
| 847 | 6690 |
| 11470 | 11629 |
| 4527 | 5055 |
| 1110 | 7805 |
| 3423 | 16677 |
| 1184 | 3099 |
| 1689 | 3088 |
| 1560 | 2634 |
| 14729 | 45409 |
| 8511 | 3829 |
| 49548 | 3060 |
| 2493 | 1702 |
| 36621 | 3633 |
| 2498 | 12603 |
| 4963 | 1609 |
| 2643 | 5347 |
| 2760 | 4378 |
| 3911 | 40572 |
| 3225 | 2865 |
| 4152 | 1167 |
| 3702 | 15711 |
| 3395 | 9341 |
| 14221 | 4260 |
| 6977 | 1458 |
| 3203 | 1804 |
| 11176 | 1934 |
| 11180 | 65581 |
| 1416 | 46437 |
| 3677 | 987 |
| 1661 | 1590 |
| 793 | 4496 |
| 5304 | 1905 |
| 1776 | 3251 |
| 3704 | 10439 |
| 9162 | 11810 |
| 48121 | 37663 |
| 37367 | 3732 |
| 37708 | 8957 |
| 13468 | 5280 |
| 42070 | 3749 |
| 40159 | 1416 |
| 50176 | 3747 |
| 34021 | 4221 |
| 50230 | 3060 |
| 9932 | 12523 |
| 2924 | 1714 |
| 31943 | 1875 |
| 26178 | 2717 |
| 9760 | 1684 |
| 2839 | 5988 |
| 8707 | 3702 |
| 6904 | 3887 |
| 3703 | 13730 |
| 3319 | 35435 |
| 4791 | 3478 |
| 1898 | 1189 |
| 40517 | 2484 |
| 1997 | 3792 |
| 2430 | 14003 |
| 2212 | 1485 |
| 1360 | 62717 |
| 4680 | 37378 |
| 36440 | 2104 |
| 46144 | 222 |
| 52222 | 1003 |
| 29588 | 5241 |
| 2441 | 3641 |
| 1411 | 2918 |
| 5298 | 1622 |
| 7494 | 7564 |
| 3638 | 11054 |
| 5340 | 1053 |
| 3775 | 5062 |
| 1149 | 697 |
| 4664 | 1893 |
| 2296 | 669 |
| 3399 | 2335 |
| 2140 | 2359 |
| 1144 | 1048 |
| 38619 | 3673 |
| 3000 | 50141 |
| 2885 | 40727 |
| 3259 | 2018 |
| 2258 | 2113 |
| 37479 | 3915 |
| 4615 | 5021 |
| 41924 | 4154 |
| 4412 | 2757 |
| 1070 | 43025 |
| 7219 | 57928 |
| 4681 | 4002 |
| 40781 | 56134 |
| 4046 | 35284 |
| 36184 | 2750 |
| 36901 | 46660 |
| 36057 | 36428 |
| 31512 | 50419 |
| 37727 | 340 |
| 13677 | 49239 |
| 38236 | 54267 |
| 6798 | 2029 |
| 2924 | 45412 |
| 27364 | 4052 |
| 1057 | 49424 |
| 42623 | 31027 |
| 29144 | 30998 |
| 904 | 2521 |
| 10716 | 42139 |
| 34558 | 57702 |
| 1997 | 36607 |
| 37416 | 7193 |
| 43292 | 54309 |
| 1631 | 987 |
| 1644 | 41107 |
| 8999 | 56752 |
| 27417 | 12306 |
| 2553 | 42702 |
| 4818 | 42455 |
| 650 | 9078 |
| 10008 | 1068 |
| 42840 | 15179 |
| 40992 | 47242 |
| 37916 | 58922 |
| 4556 | 42346 |
| 1024 | 1339 |
| 12854 | 54266 |
| 46703 | 47204 |
| 2807 | 49142 |
| 46246 | 2431 |
| 2357 | 49749 |
| 9597 | 3374 |
| 722 | 48215 |
| 2380 | 791 |
| 1455 | 2189 |
| 38730 | 52472 |
| 12178 | 54655 |
| 6362 | 3335 |
| 1582 | 11775 |
| 1347 | 36279 |
| 4003 | 47021 |
| 67884 | 13593 |
| 39007 | 2880 |
| 10046 | 46590 |
| 64898 | 43134 |
| 34394 | 14578 |
| 7083 | 9207 |
| 2877 | 384 |
| 3157 | 3694 |
| 12481 | 8299 |
| 36469 | 37948 |
Expert Answer:
