Develop a regression model using the manufacturing.xlsx file. Use Number of prod
ID: 3044920 • 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.
What proportion of the variance in the dependent variable is accounted for by the combined effect of the independent variables.
Does it appear that an appropriate number of variables are used? Explain.
Were any variables that were included in the initial solution not significant? If yes, list them.
Are there any outliers? If yes, list them.
What is the final regression equation?
Number of employees
Industrial group
Cost of Materials
End of year inventory
New capital expenditures
Value of industrial shipments
Number of production workers
52
3
4219
929
292
2
47
74
3
5357
1427
454
2
63
13
3
1061
325
20
1
12
17
3
707
267
84
1
13
169
3
10421
2083
534
3
147
51
3
4140
697
220
2
41
55
3
7125
1446
176
2
44
84
3
8994
1014
423
2
76
61
3
5504
1291
464
2
47
27
4
716
356
22
1
22
200
4
8926
2314
200
3
178
294
4
11121
2727
189
3
250
38
4
2283
682
29
1
32
17
4
364
197
21
1
14
34
4
1813
450
20
1
28
1
4
71
17
2
1
1
31
4
1321
526
16
1
25
224
4
12376
2747
465
3
179
83
5
9661
578
539
3
68
172
5
19285
3979
1071
4
147
257
5
18632
3329
711
4
209
51
5
2170
355
88
1
43
82
5
7290
580
182
2
68
94
5
8135
1604
715
2
78
273
6
12980
3535
481
3
233
70
6
4011
829
358
2
53
37
6
5101
447
128
2
29
81
6
3755
956
177
2
61
54
6
2694
718
109
2
39
15
7
3279
725
698
2
11
116
7
20596
4257
3143
4
90
55
7
10604
1502
2360
3
42
212
7
24634
3976
1352
4
163
232
7
28963
5427
1750
4
182
403
8
8483
894
1277
4
136
121
8
6940
1216
311
3
16
136
8
8863
3736
618
3
57
69
8
2823
874
144
2
25
604
8
29572
4300
2959
4
437
41
8
3811
688
198
2
28
21
8
1047
577
66
2
12
65
8
2055
504
130
2
50
Number of employees
Industrial group
Cost of Materials
End of year inventory
New capital expenditures
Value of industrial shipments
Number of production workers
52
3
4219
929
292
2
47
74
3
5357
1427
454
2
63
13
3
1061
325
20
1
12
17
3
707
267
84
1
13
169
3
10421
2083
534
3
147
51
3
4140
697
220
2
41
55
3
7125
1446
176
2
44
84
3
8994
1014
423
2
76
61
3
5504
1291
464
2
47
27
4
716
356
22
1
22
200
4
8926
2314
200
3
178
294
4
11121
2727
189
3
250
38
4
2283
682
29
1
32
17
4
364
197
21
1
14
34
4
1813
450
20
1
28
1
4
71
17
2
1
1
31
4
1321
526
16
1
25
224
4
12376
2747
465
3
179
83
5
9661
578
539
3
68
172
5
19285
3979
1071
4
147
257
5
18632
3329
711
4
209
51
5
2170
355
88
1
43
82
5
7290
580
182
2
68
94
5
8135
1604
715
2
78
273
6
12980
3535
481
3
233
70
6
4011
829
358
2
53
37
6
5101
447
128
2
29
81
6
3755
956
177
2
61
54
6
2694
718
109
2
39
15
7
3279
725
698
2
11
116
7
20596
4257
3143
4
90
55
7
10604
1502
2360
3
42
212
7
24634
3976
1352
4
163
232
7
28963
5427
1750
4
182
403
8
8483
894
1277
4
136
121
8
6940
1216
311
3
16
136
8
8863
3736
618
3
57
69
8
2823
874
144
2
25
604
8
29572
4300
2959
4
437
41
8
3811
688
198
2
28
21
8
1047
577
66
2
12
65
8
2055
504
130
2
50
Explanation / 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.