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.

1. Construct the multiple regression equation with input variables x₁, x₂, ..., x₈. (8 Points)
2. What is the adjusted coefficient of determination, , of the regression model? Explain the meaning of this value and how it differs from. (4 Points)
3. 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:
4. Determine the null and alternative hypotheses. (2 Points)
5. What is the value of the test statistics (F statistics)? (2 Points)
6. Determine the P-value. (2 Points)
7. Make a decision to reject or fail to reject H₀. (2 Points)
8. State the conclusion in terms of the original question. (2 Points)
9. 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.

1. Determine the null and alternative hypotheses. (2 Points)
2. What is the value of the test statistics? (2 Points)
3. Determine the P-value. (2 Points)
4. Make a decision to reject or fail to reject H₀. (2 Points)
5. 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