Develop a regression model using the manufacturing.xlsx file. Use Number of prod
ID: 3044485 • Letter: D
Question
Develop a regression model using the manufacturing.xlsx file. Use Number of production workers, Value Added by manufacturing, Value of industrial shipments, New capital expenditures, and End of year inventory to predict Cost of materials. The level of significance will be 0.05.
Set up and solve this problem in Excel. Use highlighting to emphasize important cells. Insert a text box to provide answers to the following questions, based on your final output (run the regression a second, or third time, if appropriate):
1. What proportion of the variance in the dependent variable is accounted for by the combined effect of the independent variables?
2. Does it appear that an appropriate number of variables are used? Explain.
3. Were any variables that were included in the initial solution not significant? If yes, list them.
4. Are there any outliers? If yes, list them.
5. What is the final regression equation?
Number of employees Number of production workers Value Added by manufacturing Cost of Materials Value of industrial shipments New capital expenditures End of year inventory Industrial group 52 47 2471 4219 2 292 929 3 74 63 4307 5357 2 454 1427 3 13 12 673 1061 1 20 325 3 17 13 817 707 1 84 267 3 169 147 8986 10421 3 534 2083 3 51 41 3145 4140 2 220 697 3 55 44 4076 7125 2 176 1446 3 84 76 3806 8994 2 423 1014 3 61 47 4276 5504 2 464 1291 3 27 22 1239 716 1 22 356 4 200 178 9423 8926 3 200 2314 4 294 250 11045 11121 3 189 2727 4 38 32 1916 2283 1 29 682 4 17 14 599 364 1 21 197 4 34 28 2063 1813 1 20 450 4 31 25 1445 1321 1 16 526 4 224 179 10603 12376 3 465 2747 4 83 68 5775 9661 3 539 578 5 172 147 10404 19285 4 1071 3979 5 257 209 13274 18632 4 711 3329 5 51 43 1909 2170 1 88 355 5 82 68 4606 7290 2 182 580 5 94 78 5518 8135 2 715 1604 5 273 233 12464 12980 3 481 3535 6 70 53 5447 4011 2 358 829 6 37 29 2290 5101 2 128 447 6 81 61 4182 3755 2 177 956 6 54 39 2818 2694 2 109 718 6 116 90 18848 20596 4 3143 4257 7 55 42 9655 10604 3 2360 1502 7 212 163 15668 24634 4 1352 3976 7 232 182 25918 28963 4 1750 5427 7 403 136 30692 8483 4 1277 894 8 121 16 17982 6940 3 311 1216 8 136 57 17857 8863 3 618 3736 8 69 25 9699 2823 2 144 874 8 604 437 38407 29572 4 2959 4300 8 41 28 3878 3811 2 198 688 8 65 50 4388 2055 2 130 504 8Explanation / Answer
1. What proportion of the variance in the dependent variable is accounted for by the combined effect of the independent variables?
Solution:
The value of the coefficient of determination or R square is given as 0.9247, which means about 92.47% of the variance in the dependent variable cost of materials is accounted for by the combined effect of the independent variables.
2. Does it appear that an appropriate number of variables are used? Explain.
Solution:
Yes, it appears that an appropriate number of variables are used because multiple correlation coefficient value is given as 0.9616 which indicate that there is a strong relationship between dependent variable cost of materials and combined effect of independent variables.
3. Were any variables that were included in the initial solution not significant? If yes, list them.
Solution:
Yes, there is one variable ‘value added by manufacturing’ is not significant because p-value for this variable is given as 0.3759 which is greater than 5% level of significance.
4. Are there any outliers? If yes, list them.
Solution:
Yes, some variables included outliers. These variables are given as cost of materials, number of production workers, value added by manufacturing, and new capital expenditures.
5. What is the final regression equation?
Solution:
Final regression equation is given as below:
Cost of materials = -1830.068402 + 16.65776167* Number of production workers + 1426.644281* Value of industrial shipments + 2.390667027* New capital expenditures + 2.426873994* End of year inventory
First Regression
SUMMARY OUTPUT
Regression Statistics
Multiple R
0.961610238
R Square
0.924694251
Adjusted R Square
0.913284289
Standard Error
2242.431623
Observations
39
ANOVA
df
SS
MS
F
Significance F
Regression
5
2037616181
407523236.2
81.0427105
1.47823E-17
Residual
33
165940486.3
5028499.584
Total
38
2203556667
Coefficients
Standard Error
t Stat
P-value
Lower 95%
Upper 95%
Intercept
-2167.411939
1187.686116
-1.82490299
0.07707972
-4583.7775
248.95362
Number of production workers
19.0485025
6.880248761
2.768577585
0.00916395
5.050531213
33.04647378
Value Added by manufacturing
-0.078803362
0.087799646
-0.89753622
0.37593464
-0.25743308
0.099826359
Value of industrial shipments
1721.808578
770.7390016
2.233971
0.03237736
153.7282968
3289.888859
New capital expenditures
2.684434086
0.766074752
3.50414118
0.00134022
1.125843291
4.24302488
End of year inventory
2.380197705
0.503683044
4.725586322
4.1315E-05
1.355446851
3.404948558
Second regression after deleting insignificant variable
SUMMARY OUTPUT
Regression Statistics
Multiple R
0.960653914
R Square
0.922855943
Adjusted R Square
0.913780172
Standard Error
2236.010767
Observations
39
ANOVA
df
SS
MS
F
Significance F
Regression
4
2033565366
508391341.6
101.6834715
2.02563E-18
Residual
34
169991301.1
4999744.149
Total
38
2203556667
Coefficients
Standard Error
t Stat
P-value
Lower 95%
Upper 95%
Intercept
-1830.068402
1123.420054
-1.629015252
0.112541757
-4113.132625
452.9958216
Number of production workers
16.65776167
6.325547773
2.633410144
0.012628389
3.802702012
29.51282133
Value of industrial shipments
1426.644281
695.0616974
2.052543374
0.047878174
14.10897102
2839.179591
New capital expenditures
2.390667027
0.690651369
3.461467154
0.001468254
0.987094583
3.794239471
End of year inventory
2.426873994
0.499556606
4.858056056
2.62207E-05
1.41165283
3.442095157
Cost of materials = -1830.068402 + 16.65776167* Number of production workers + 1426.644281* Value of industrial shipments + 2.390667027* New capital expenditures + 2.426873994* End of year inventory
SUMMARY OUTPUT
Regression Statistics
Multiple R
0.961610238
R Square
0.924694251
Adjusted R Square
0.913284289
Standard Error
2242.431623
Observations
39
ANOVA
df
SS
MS
F
Significance F
Regression
5
2037616181
407523236.2
81.0427105
1.47823E-17
Residual
33
165940486.3
5028499.584
Total
38
2203556667
Coefficients
Standard Error
t Stat
P-value
Lower 95%
Upper 95%
Intercept
-2167.411939
1187.686116
-1.82490299
0.07707972
-4583.7775
248.95362
Number of production workers
19.0485025
6.880248761
2.768577585
0.00916395
5.050531213
33.04647378
Value Added by manufacturing
-0.078803362
0.087799646
-0.89753622
0.37593464
-0.25743308
0.099826359
Value of industrial shipments
1721.808578
770.7390016
2.233971
0.03237736
153.7282968
3289.888859
New capital expenditures
2.684434086
0.766074752
3.50414118
0.00134022
1.125843291
4.24302488
End of year inventory
2.380197705
0.503683044
4.725586322
4.1315E-05
1.355446851
3.404948558
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.