Question

In: Statistics and Probability

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.

Annual Food Spending ($) Annual Household Income           ($) Non mortgage household debt ($)
8909 56697 23180
5684 35945 7052
10706 52687 16149
14112 74041 21839
13855 63182 18866
15619 79064 21899
2694 25981 8774
9127 57424 15766
13514 72045 27685
6314 38046 8545
7622 52408 28057
4322 41405 6998
3805 29684 4806
6674 49246 13592
7347 41491 4088
2911 26703 15876
8026 48753 16714
8567 55555 16783
10345 71483 21407
8694 50980 19114
8821 46403 7817
8678 51927 14415
14331 84769 17295
9619 59062 16687
9286 57952 14161
8206 58355 19538
16408 81694 15187
12757 69522 14651
17740 96132 0
7739 57796 22057
15383 88276 1896
4579 32264 7979
11679 65928 0
12877 69924 27330
16232 91108 9876
9621 54070 19908
8171 47238 17819
12128 77427 31340
8642 59805 4963
12400 60334 6632
9185 54114 18593
7862 40680 15202
9775 58263 1486
6771 52008 21713
3059 39643 12179
13211 70309 13221
7408 46450 5602
11581 76140 33874
14233 80833 11478
3352 31899 2762
2630 21647 2663
9093 65924 11355
12652 65923 5132
9559 62811 12613
6112 42335 3149
10431 65134 15196
12630 64621 21433
4578 36553 5502
9551 62910 11376
10262 70727 13287
9551 57634 11857
10143 56549 16136
8955 59662 11627
10197 57350 18432
11234 56447 10871
9320 61136 0
9089 51526 4902
12300 79979 17270
11484 66733 15145
11215 75359 15611
7204 40795 8975
5579 39128 6576
11723 75482 12508
9353 63998 0
7761 45845 6671
4261 38223 8576
9830 66787 1178
12386 77852 936
8673 55825 14167
10944 57022 9018
9910 64263 12768
9928 75881 17423
4264 34343 21323
7971 41243 21009
8290 53021 20151
12669 66991 9250
7272 49719 20838
9784 58399 16065
9187 50477 9407
5866 39112 20409
9456 51886 11668
6270 34797 146
9518 62348 5201
10968 78704 17002
8865 53620 32004
9226 51577 15922
4913 34761 17704
6976 60968 17799
8152 51281 8167
2887 25013 18763
8062 59238 10815
8895 47344 11814
8444 52645 22469
6148 35309 17139
4563 34355 10612
8185 50630 21187
3391 29056 15735
7436 48721 18363
9522 50459 16478
11290 72805 21238
10403 56954 22218
4693 39343 24696
5626 38833 14371
11869 55021 35576
13055 77605 817
8783 57937 18591
13031 63343 25531
3681 36479 17950
5549 40381 14257
4108 26309 26581
6314 41421 22470
7700 54579 29065
7479 40551 31757
9093 50369 6404
9863 54422 24334
8043 51836 26213
9552 73600 36374
9286 51873 29631
7987 48003 17261
3875 36519 13579
10746 75152 10659
6888 44974 23711
5479 48923 4594
6949 43769 21221
10650 75947 33357
5188 41423 33641
5311 40189 17791
4691 36772 5829
8056 59690 19594
11304 53654 23066
8112 59067 240
8696 65962 0
5869 37254 10157
3776 33568 14143
11829 56934 0
13087 88822 17565
10986 59635 27863
5762 38407 18867
11617 78627 11894
9895 47710 22930
16293 64443 31687
8185 58871 35424
13972 87954 11549
11243 54778 12552
4635 39825 19494
10063 49536 12195
8426 60102 13787
7436 49139 22356
11747 51052 4553
15397 70500 12025
6842 54894 16217
9678 60570 4106
12852 57625 31228
10114 56956 25907
8496 61400 1093
6689 50532 17106
15696 72774 17793
9841 69981 21607
12529 66891 17689
10210 67431 19995
8868 64782 14489
6426 38987 17864
11096 64867 5839
10086 50421 8689
2587 27076 17534
12492 51784 20284
8456 54135 22037
6801 53291 23342
6339 49804 34943
7802 52205 28579
9717 72841 22349
6026 46238 20165
5618 45938 10538
10217 77716 18516
8338 59711 7980
9048 42106 19786
4017 36462 9935
10906 53403 18177
15148 71290 6696
8830 66759 20972
8481 57616 28767
11358 76221 1373
10553 78202 5920
6969 55164 24795
13219 61171 21482
3543 34093 25969
7326 50647 10750
8458 59898 22940
11766 52884 25970
9908 73629 7112

Solutions

Expert Solution

Step 1 : Create an Indicator Variable for metro cities using formula mentioned in formula bar.

Step 2: Filter the Data on Metro cities i.e. select only those cities with Metro Indicator 1.

Step 3: Paste this filtered data to a new sheet.

Step 4: Go to Data - Data Analysis - Regression

Step 5: Enter the range of Y-variable and X-variable as shown. Select Output range and click on residuals. It will give you Output Summary and the Predicted Values along with Residuals

The Final Tables are given as follows:

Annual Food Spending ($) Annual Household Income ($) Non mortgage household debt ($) Metro - Indicator
14112 74041 21839 1
13855 63182 18866 1
15619 79064 21899 1
13514 72045 27685 1
10345 71483 21407 1
14331 84769 17295 1
16408 81694 15187 1
12757 69522 14651 1
17740 96132 0 1
15383 88276 1896 1
11679 65928 0 1
12877 69924 27330 1
16232 91108 9876 1
12128 77427 31340 1
12400 60334 6632 1
13211 70309 13221 1
11581 76140 33874 1
14233 80833 11478 1
9093 65924 11355 1
12652 65923 5132 1
9559 62811 12613 1
10431 65134 15196 1
12630 64621 21433 1
9551 62910 11376 1
10262 70727 13287 1
9320 61136 0 1
12300 79979 17270 1
11484 66733 15145 1
11215 75359 15611 1
11723 75482 12508 1
9353 63998 0 1
9830 66787 1178 1
12386 77852 936 1
9910 64263 12768 1
9928 75881 17423 1
12669 66991 9250 1
9518 62348 5201 1
10968 78704 17002 1
6976 60968 17799 1
11290 72805 21238 1
13055 77605 817 1
13031 63343 25531 1
9552 73600 36374 1
10746 75152 10659 1
10650 75947 33357 1
8696 65962 0 1
13087 88822 17565 1
11617 78627 11894 1
16293 64443 31687 1
13972 87954 11549 1
8426 60102 13787 1
15397 70500 12025 1
9678 60570 4106 1
8496 61400 1093 1
15696 72774 17793 1
9841 69981 21607 1
12529 66891 17689 1
10210 67431 19995 1
8868 64782 14489 1
11096 64867 5839 1
9717 72841 22349 1
10217 77716 18516 1
15148 71290 6696 1
8830 66759 20972 1
11358 76221 1373 1
10553 78202 5920 1
13219 61171 21482 1
9908 73629 7112 1

Summary Output

Predicted Values:

Observation Predicted Y Residuals
1 12284.987 1827.013
2 10551.236 3303.764
3 13066.554 2552.446
4 12065.489 1448.511
5 11880.739 -1535.74
6 13881.747 449.253
7 13371.147 3036.853
8 11471.151 1285.849
9 15379.352 2360.648
10 14187.851 1195.149
11 10685.278 993.7225
12 11730.352 1146.648
13 14751.794 1480.206
14 12958.629 -830.629
15 9918.8016 2481.198
16 11571.273 1639.727
17 12797.93 -1216.93
18 13179.789 1053.211
19 10860.838 -1767.84
20 10764.127 1887.873
21 10396.558 -837.558
22 10797.658 -366.658
23 10814.703 1815.297
24 10392.751 -841.751
25 11637.26 -1375.26
26 9940.5416 -620.542
27 13136.934 -836.934
28 11045.371 438.629
29 12393.188 -1178.19
30 12364.158 -641.158
31 10385.332 -1032.33
32 10837.054 -1007.05
33 12552.937 -166.937
34 10624.621 -714.621
35 12502.428 -2574.43
36 10994.002 1674.998
37 10209.599 -691.599
38 12934.625 -1966.62
39 10190.598 -3214.6
40 12083.572 -793.572
41 12512.704 542.2961
42 10679.67 2351.33
43 12441.972 -2889.97
44 12284.184 -1538.18
45 12759.914 -2109.91
46 10690.562 -1994.56
47 14515.822 -1428.82
48 12843.404 -1226.4
49 10946.139 5346.861
50 14287.582 -315.582
51 9993.7613 -1567.76
52 11582.4 3814.6
53 9916.286 -238.286
54 9998.5292 -1502.53
55 12025.303 3670.697
56 11650.413 -1809.41
57 11109.398 1419.602
58 11229.1 -1019.1
59 10731.983 -1863.98
60 10610.982 485.0183
61 12106.405 -2389.41
62 12804.568 -2587.57
63 11622.492 3525.508
64 11139.822 -2309.82
65 12306.24 -948.24
66 12684.662 -2131.66
67 10279.291 2939.709
68 11992.456 -2084.46

orchestra answered 1 year ago
Next > < Previous

Related Solutions

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:...
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 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...
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 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...
Use the “Consumer Food” Database on “Excel Databses.xls”. As the researcher you are interested in predicting...
Use the “Consumer Food” Database on “Excel Databses.xls”. As the researcher you are interested in predicting the annual food spending according to annual household income as well as a qualitative variable: location or region. Link to data: https://drive.google.com/file/d/1YMYMy7H0sLRZJzXwFKMrANuAGxmp-z9I/view?usp=sharing Please use excel and post step by step Construct two regression models to predict annual food spending: Model 1: Food = b0 + b1Income + b2Metro Model 2: Food = b0 + b1Income + b2NE + b3MW+ b4S NOTES: DO NOT: Print...
Using the data in the Excel file Home Market Value, develop a multiple regression model for...
Using the data in the Excel file Home Market Value, develop a multiple regression model for estimating the market value as a function of house age and house size. Predict the value of a house that is 30 years old and has 1800 square feet, and also predict the value of a house that is 5 years old and has 2800 square feet. Conduct your analysis using the following Multiple Regression Model Building and Interpretation Rubric: Identify the dependent variable...
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...
QUESTION 8 Use the Manufacturing database from “Excel Databases.xls” on Blackboard. Use Excel to develop a...
QUESTION 8 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, Number of Production Workers, Value Added by Manufacture, New Capital Expenditures, and End-of-Year Inventories. Use Excel to perform a backward elimination regression analysis at a 5% level of significance. What is the test statistic of the independent variable that is dropped from the linear model in the first step. Write your answer...
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 Nikkei by the DJIA, the Nasdaq, the S&P 500, the Hang Seng, the FTSE 100, and the IPC. Assume a 1% level of significance. What percent of residuals are within 1 standard error? Write your answer as a number rounded to 1 decimal place. Do not include the % sign in your answer. Excel Data: https://drive.google.com/file/d/1TQG5r2wzLGk--75whZXyb0SDTHZTWS0S/view?usp=sharing
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT