Question

In: Statistics and Probability

Use Excel to develop a regression model for the Hospital Database to predict the number of...

Use Excel to develop a regression model for the Hospital Database to predict the number of Personnel by the number of Births. How many residuals are within 1 standard error? Write your answer as a whole number.

Personnel Births
792 312
1762 1077
2310 1027
328 355
181 168
1077 3810
742 735
131 1
1594 1733
233 257
241 169
203 430
325 0
676 2049
347 211
79 16
505 2648
1543 2450
755 1465
959 0
325 1993
954 2275
1091 1494
671 1313
300 451
753 1689
607 1583
929 2017
354 995
408 2045
1251 1686
386 503
144 126
2047 2026
1343 1412
1723 1517
96 0
529 0
3694 2719
1042 1074
1071 1421
1525 762
1983 3194
670 496
1653 1442
167 0
793 1107
841 2989
316 113
93 0
373 0
263 173
943 1064
605 759
596 1317
1165 1751
568 0
507 0
479 714
136 99
1456 2243
3486 3966
885 1308
243 0
1001 2514
3301 3714
337 126
1193 556
1161 1327
322 415
185 216
205 339
1224 1217
1704 2641
815 790
712 520
156 35
1769 1168
875 793
790 0
308 0
70 14
494 0
111 0
1618 0
244 0
525 776
472 451
94 0
297 145
847 1284
234 1
401 319
3928 2154
198 295
1231 496
545 589
663 806
820 701
2581 3968
1298 0
126 0
2534 3655
251 0
85 0
432 0
864 0
66 0
556 3063
347 169
239 66
973 827
439 570
1849 0
102 0
262 0
885 342
549 494
611 0
330 0
1471 0
75 0
262 286
328 235
377 339
575 398
1916 1275
2620 5699
571 1364
703 714
535 0
160 0
202 0
1330 779
370 0
3123 2202
2745 3346
815 1071
576 352
502 254
808 0
50 0
728 699
4087 2462
3012 3311
68 0
3090 4207
1358 0
576 339
284 130
145 91
2312 1143
1124 0
336 0
415 509
1779 1026
338 0
453 447
437 1161
261 0
609 922
647 562
61 78
2074 0
2232 2122
948 0
409 0
153 0
741 710
1625 1165
538 466
789 1106
395 376
956 0
362 637
144 0
229 352
396 447
2256 1227
731 963
1477 3038
102 0
106 0
939 868
392 1189
3516 2849
785 1728
607 2171
273 364
630 0
1379 2993
1108 0
583 1964
514 601
216 387
1593 1946
1055 545
399 0
834 838
104 51

Solutions

Expert Solution

Sol:

Install analysis toolpak ine xcel

go to data>data analysis>regression we get

SUMMARY OUTPUT
Regression Statistics
Multiple R 0.697463
R Square 0.486455
Adjusted R Square 0.483861
Standard Error 590.2581
Observations 200
ANOVA
df SS MS F Significance F
Regression 1 65345182 65345182 187.5554 1.8E-30
Residual 198 68984120 348404.6
Total 199 1.34E+08
Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
Intercept 390.6214 54.07602 7.223561 1.07E-11 283.9826 497.2603 283.9826 497.2603
Births 0.538735 0.039338 13.69509 1.8E-30 0.46116 0.61631 0.46116 0.61631
RESIDUAL OUTPUT
Observation Predicted Personnel Residuals Standard Residuals
1 558.7067 233.2933 0.396236
2 970.8389 791.1611 1.343745
3 943.9022 1366.098 2.320245
4 581.8723 -253.872 -0.43119
5 481.1289 -300.129 -0.50975
6 2443.201 -1366.2 -2.32042
7 786.5916 -44.5916 -0.07574
8 391.1602 -260.16 -0.44187
9 1324.249 269.7509 0.458158
10 529.0763 -296.076 -0.50287
11 481.6676 -240.668 -0.40876
12 622.2775 -419.277 -0.71212
13 390.6214 -65.6214 -0.11145
14 1494.489 -818.489 -1.39016
15 504.2945 -157.295 -0.26716
16 399.2412 -320.241 -0.54391
17 1817.191 -1312.19 -2.22869
18 1710.522 -167.522 -0.28453
19 1179.868 -424.868 -0.72162
20 390.6214 568.3786 0.965361
21 1464.32 -1139.32 -1.93507
22 1616.243 -662.243 -1.12479
23 1195.491 -104.491 -0.17747
24 1097.98 -426.98 -0.7252
25 633.5909 -333.591 -0.56659
26 1300.545 -547.545 -0.92998
27 1243.439 -636.439 -1.08096
28 1477.25 -548.25 -0.93117
29 926.6627 -572.663 -0.97264
30 1492.334 -1084.33 -1.84168
31 1298.929 -47.9285 -0.0814
32 661.6051 -275.605 -0.4681
33 458.502 -314.502 -0.53417
34 1482.098 564.9016 0.959455
35 1151.315 191.6849 0.325567
36 1207.882 515.1177 0.8749
37 390.6214 -294.621 -0.5004
38 390.6214 138.3786 0.235029
39 1855.442 1838.558 3.122694
40 969.2227 72.77726 0.123608
41 1156.164 -85.1638 -0.14465
42 801.1374 723.8626 1.229442
43 2111.341 -128.341 -0.21798
44 657.834 12.16604 0.020663
45 1167.477 485.5228 0.824635
46 390.6214 -223.621 -0.37981
47 987.001 -194.001 -0.3295
48 2000.9 -1159.9 -1.97003
49 451.4985 -135.498 -0.23014
50 390.6214 -297.621 -0.50549
51 390.6214 -17.6214 -0.02993
52 483.8226 -220.823 -0.37506
53 963.8354 -20.8354 -0.03539
54 799.5212 -194.521 -0.33038
55 1100.135 -504.135 -0.85625
56 1333.946 -168.946 -0.28695
57 390.6214 177.3786 0.301268
58 390.6214 116.3786 0.197663
59 775.2782 -296.278 -0.50321
60 443.9562 -307.956 -0.52305
61 1599.004 -143.004 -0.24288
62 2527.244 958.7559 1.628396
63 1095.287 -210.287 -0.35716
64 390.6214 -147.621 -0.25073
65 1745.001 -744.001 -1.26365
66 2391.483 909.5171 1.544766
67 458.502 -121.502 -0.20636
68 690.1581 502.8419 0.85405
69 1105.523 55.47733 0.094225
70 614.1964 -292.196 -0.49628
71 506.9882 -321.988 -0.54688
72 573.2526 -368.253 -0.62546
73 1046.262 177.7382 0.301879
74 1813.42 -109.42 -0.18584
75 816.222 -1.22202 -0.00208
76 670.7636 41.2364 0.070038
77 409.4772 -253.477 -0.43052
78 1019.864 749.1362 1.272368
79 817.8382 57.16177 0.097086
80 390.6214 399.3786 0.678323
81 390.6214 -82.6214 -0.14033
82 398.1637 -328.164 -0.55737
83 390.6214 103.3786 0.175583
84 390.6214 -279.621 -0.47492
85 390.6214 1227.379 2.084637
86 390.6214 -146.621 -0.24903
87 808.6797 -283.68 -0.48181
88 633.5909 -161.591 -0.27445
89 390.6214 -296.621 -0.5038
90 468.738 -171.738 -0.29169
91 1082.357 -235.357 -0.39974
92 391.1602 -157.16 -0.26693
93 562.4779 -161.478 -0.27426
94 1551.056 2376.944 4.037112
95 549.5482 -351.548 -0.59709
96 657.834 573.166 0.973492
97 707.9363 -162.936 -0.27674
98 824.8418 -161.842 -0.27488
99 768.2746 51.72538 0.087853
100 2528.322 52.67841 0.089471
101 390.6214 907.3786 1.541134
102 390.6214 -264.621 -0.44945
103 2359.698 174.3024 0.296043
104 390.6214 -139.621 -0.23714
105 390.6214 -305.621 -0.51908
106 390.6214 41.37856 0.070279
107 390.6214 473.3786 0.804008
108 390.6214 -324.621 -0.55135
109 2040.766 -1484.77 -2.5218
110 481.6676 -134.668 -0.22873
111 426.1779 -187.178 -0.31791
112 836.1552 136.8448 0.232424
113 697.7003 -258.7 -0.43939
114 390.6214 1458.379 2.476979
115 390.6214 -288.621 -0.49021
116 390.6214 -128.621 -0.21846
117 574.8688 310.1312 0.526741
118 656.7565 -107.756 -0.18302
119 390.6214 220.3786 0.374301
120 390.6214 -60.6214 -0.10296
121 390.6214 1080.379 1.834966
122 390.6214 -315.621 -0.53607
123 544.6996 -282.7 -0.48015
124 517.2241 -189.224 -0.32139
125 573.2526 -196.253 -0.33332
126 605.0379 -30.0379 -0.05102
127 1077.508 838.4915 1.424133
128 3460.872 -840.872 -1.42818
129 1125.456 -554.456 -0.94171
130 775.2782 -72.2782 -0.12276
131 390.6214 144.3786 0.245219
132 390.6214 -230.621 -0.3917
133 390.6214 -188.621 -0.32036
134 810.2959 519.7041 0.88269
135 390.6214 -20.6214 -0.03502
136 1576.916 1546.084 2.625942
137 2193.228 551.7715 0.937155
138 967.6065 -152.607 -0.25919
139 580.2561 -4.25613 -0.00723
140 527.4601 -25.4601 -0.04324
141 390.6214 417.3786 0.708895
142 390.6214 -340.621 -0.57853
143 767.1971 -39.1971 -0.06657
144 1716.987 2370.013 4.025342
145 2174.373 837.6273 1.422665
146 390.6214 -322.621 -0.54796
147 2657.079 432.9208 0.735293
148 390.6214 967.3786 1.643041
149 573.2526 2.747423 0.004666
150 460.657 -176.657 -0.30004
151 439.6463 -294.646 -0.50044
152 1006.395 1305.605 2.2175
153 390.6214 733.3786 1.245605
154 390.6214 -54.6214 -0.09277
155 664.8375 -249.838 -0.42434
156 943.3635 835.6365 1.419284
157 390.6214 -52.6214 -0.08937
158 631.4359 -178.436 -0.30306
159 1016.093 -579.093 -0.98356
160 390.6214 -129.621 -0.22016
161 887.335 -278.335 -0.47274
162 693.3905 -46.3905 -0.07879
163 432.6428 -371.643 -0.63122
164 390.6214 1683.379 2.859129
165 1533.817 698.1831 1.185827
166 390.6214 557.3786 0.946678
167 390.6214 18.37856 0.031215
168 390.6214 -237.621 -0.40359
169 773.1232 -32.1232 -0.05456
170 1018.248 606.7524 1.030537
171 641.6719 -103.672 -0.17608
172 986.4623 -197.462 -0.33538
173 593.1858 -198.186 -0.33661
174 390.6214 565.3786 0.960265
175 733.7956 -371.796 -0.63148
176 390.6214 -246.621 -0.41887
177 580.2561 -351.256 -0.59659
178 631.4359 -235.436 -0.39988
179 1051.649 1204.351 2.045526
180 909.4232 -178.423 -0.30304
181 2027.298 -550.298 -0.93465
182 390.6214 -288.621 -0.49021
183 390.6214 -284.621 -0.48341
184 858.2433 80.75665 0.137161
185 1031.177 -639.177 -1.08561
186 1925.477 1590.523 2.701418
187 1321.555 -536.555 -0.91131
188 1560.215 -953.215 -1.61898
189 586.7209 -313.721 -0.53284
190 390.6214 239.3786 0.406572
191 2003.055 -624.055 -1.05992
192 390.6214 717.3786 1.218429
193 1448.697 -865.697 -1.47034
194 714.4011 -200.401 -0.34037
195 599.1119 -383.112 -0.6507
196 1439 154.0004 0.261562
197 684.232 370.768 0.62973
198 390.6214 8.37856 0.014231
199 842.0813 -8.0813 -0.01373
200 418.0969 -314.097 -0.53348

From

standard Residual column

count standard Residual values which are in between -1 and +1

=COUNTIFS(C2:C201,">=-1",C2:C201,"<=1")

=159

we get

159

orchestra answered 2 years ago
Next > < Previous

Related Solutions

Use Excel to develop a regression model for the Hospital Database to predict the number of...
Use Excel to develop a regression model for the Hospital Database to predict the number of Personnel by the number of Births. How many residuals are within 1 standard error? Write your answer as a whole number. Personnel(y) Births(x) 792 312 1762 1077 2310 1027 328 355 181 168 1077 3810 742 735 131 1 1594 1733 233 257 241 169 203 430 325 0 676 2049 347 211 79 16 505 2648 1543 2450 755 1465 959 0 325...
Use Excel to develop a regression model for the Hospital Database (using the “Excel Databases.xls” file...
Use Excel to develop a regression model for the Hospital Database (using the “Excel Databases.xls” file on Blackboard) to predict the number of Personnel by the number of Births. Perform a test of the overall model, what is the value of the test statistic? Write your answer as a number, round your answer to 2 decimal places. SUMMARY OUTPUT Regression Statistics Multiple R 0.697463374 R Square 0.486455158 Adjusted R Square 0.483861497 Standard Error 590.2581194 Observations 200 ANOVA df SS MS...
Use Excel to develop a multiple regression model to predict Cost of Materials by Number of...
Use Excel to develop a multiple regression model to predict Cost of Materials by Number of Employees, New Capital Expenditures, Value Added by Manufacture, and End-of-Year Inventories. Locate the observed value that is in Industrial Group 12 and has 7 employees. Based on the model and the multiple regression output, what is the corresponding residual of this observation? Write your answer as a number, round to 2 decimal places. SIC Code No. Emp. No. Prod. Wkrs. Value Added by Mfg....
Use Excel to develop a regression model for the Consumer Food Database (using the “Excel Databases.xls”...
Use Excel to develop a regression model for the Consumer Food Database (using the “Excel Databases.xls” file) to predict Annual Food Spending by Annual Household Income. Assume a 5% level of significance. (file here: https://drive.google.com/file/d/13uDUXwoSRZHEUtjMUedu2yjR_4lrLepC/view?usp=sharing ) Must complete all the parts to this problem: PART 1: Perform a simple linear regression in Excel to predict Annual Food Spending by Annual Household Income and output the results. Include the Regression Statistics, ANOVA, and table of Coefficients for each model. PART 2:...
1) Use Excel to develop a regression model for the Consumer Food Database (using the “Excel...
1) Use Excel to develop a regression model for the Consumer Food Database (using the “Excel Databases.xls” file on Blackboard) to predict Annual Food Spending by Annual Household Income for those living in the Metro area only.    Suppose a household in the metro area has an annual income of $60,000. Predict how much they spend on food per year. Write your answer as a number (do not include the $ sign or comma) and round to 2 decimal places....
Use the Manufacturing database from “Excel Databases.xls” on Blackboard. Use Excel to develop a multiple regression...
Use the Manufacturing database from “Excel Databases.xls” on Blackboard. Use Excel to develop a multiple regression model to predict Cost of Materials by Number of Employees, New Capital Expenditures, Value Added by Manufacture, and End-of-Year Inventories. Locate the observed value that is in Industrial Group 12 and has 7 employees. Based on the model and the multiple regression output, what is the corresponding residual of this observation? Write your answer as a number, round to 2 decimal places. **Answer should...
An electronics company is looking to develop a regression model to predict the number of units...
An electronics company is looking to develop a regression model to predict the number of units sold for a special running watch. Data is provided below: Sales (units) Price ($) Advertising ($) Holiday 500 100 50 Yes 480 120 40 Yes 485 110 45 No 510 103 55 Yes 490 108 40 No 488 109 30 No 496 106 45 Yes Compile a spreadsheet for the data and determine the predicted number of units sold if the watch is sold...
An electronics company is looking to develop a regression model to predict the number of units...
An electronics company is looking to develop a regression model to predict the number of units sold for a special running watch. Data is provided below: Sales (units) Price ($) Advertising ($) Holiday 500 100 50 Yes 480 120 40 Yes 485 110 45 No 510 103 55 Yes 490 108 40 No 488 109 30 No 496 106 45 Yes Compile an excel spreadsheet for the above data and determine the regression equation Answer (a) X3 = 1 if...
Use the following data to develop a multiple regression model to predict from and . Discuss...
Use the following data to develop a multiple regression model to predict from and . Discuss the output, including comments about the overall strength of the model, the significance of the regression coefficients, and other indicators of model fit. y x1 x2 198 29 1.64 214 71 2.81 211 54 2.22 219 73 2.70 184 67 1.57 167 32 1.63 201 47 1.99 204 43 2.14 190 60 2.04 222 32 2.93 197 34 2.15 Appendix A Statistical Tables *(Round...
Use the International Stock Market database from “Excel Databases.xls” on Blackboard. Use Excel to develop a...
Use the International Stock Market database from “Excel Databases.xls” on Blackboard. Use Excel to develop a multiple regression model to predict the DJIA by the Nasdaq, the S&P 500, the Nikkei, the Hang Seng, the FTSE 100, and the IPC. Performing a stepwise regression analysis at a 5% level of significance, which independent variable is the best single predictor of the DJIA? This is Step 1 of the stepwise regression. Nasdaq S&P 500 Nikkei Hang Seng FTSE 100 IPC https://drive.google.com/file/d/19TI3HId0greXS0nkmDuoITv1IMPF_TUK/view?usp=sharing...
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT