Question

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 be 542.83, I just need to know how**

SIC Code	No. Emp.	No. Prod. Wkrs.	Value Added by Mfg.	Cost of Materials	Value of Indus. Shipmnts	New Cap. Exp.	End Yr. Inven.	Indus. Grp.
201	433	370	23518	78713	4	1833	3630	1
202	131	83	15724	42774	4	1056	3157	1
203	204	169	24506	27222	4	1405	8732	1
204	100	70	21667	37040	4	1912	3407	1
205	220	137	20712	12030	4	1006	1155	1
206	89	69	12640	13674	3	873	3613	1
207	26	18	4258	19130	3	487	1946	1
208	143	72	35210	33521	4	2011	7199	1
209	171	126	20548	19612	4	1135	3135	1
211	21	15	23442	5557	3	605	5506	2
212	3	2	287	163	1	2	42	2
213	2	2	1508	314	1	15	155	2
214	6	4	624	2622	1	27	554	2
221	52	47	2471	4219	2	292	929	3
222	74	63	4307	5357	2	454	1427	3
223	13	12	673	1061	1	20	325	3
224	17	13	817	707	1	84	267	3
225	169	147	8986	10421	3	534	2083	3
226	51	41	3145	4140	2	220	697	3
227	55	44	4076	7125	2	176	1446	3
228	84	76	3806	8994	2	423	1014	3
229	61	47	4276	5504	2	464	1291	3
231	27	22	1239	716	1	22	356	4
232	200	178	9423	8926	3	200	2314	4
233	294	250	11045	11121	3	189	2727	4
234	38	32	1916	2283	1	29	682	4
235	17	14	599	364	1	21	197	4
236	34	28	2063	1813	1	20	450	4
237	1	1	34	71	1	2	17	4
238	31	25	1445	1321	1	16	526	4
239	224	179	10603	12376	3	465	2747	4
241	83	68	5775	9661	3	539	578	5
242	172	147	10404	19285	4	1071	3979	5
243	257	209	13274	18632	4	711	3329	5
244	51	43	1909	2170	1	88	355	5
245	82	68	4606	7290	2	182	580	5
249	94	78	5518	8135	2	715	1604	5
251	273	233	12464	12980	3	481	3535	6
252	70	53	5447	4011	2	358	829	6
253	37	29	2290	5101	2	128	447	6
254	81	61	4182	3755	2	177	956	6
259	54	39	2818	2694	2	109	718	6
261	15	11	2201	3279	2	698	725	7
262	116	90	18848	20596	4	3143	4257	7
263	55	42	9655	10604	3	2360	1502	7
265	212	163	15668	24634	4	1352	3976	7
267	232	182	25918	28963	4	1750	5427	7
271	403	136	30692	8483	4	1277	894	8
272	121	16	17982	6940	3	311	1216	8
273	136	57	17857	8863	3	618	3736	8
274	69	25	9699	2823	2	144	874	8
275	604	437	38407	29572	4	2959	4300	8
276	41	28	3878	3811	2	198	688	8
277	21	12	3989	1047	2	66	577	8
278	65	50	4388	2055	2	130	504	8
279	55	39	4055	1098	2	210	236	8
281	80	45	16567	11298	3	2002	2644	9
282	115	79	25025	34596	4	3731	6192	9
283	213	106	59813	27187	4	4301	11533	9
284	126	75	31801	19932	4	1304	4535	9
285	51	28	8497	9849	3	404	2178	9
286	126	75	28886	46935	4	6269	8577	9
287	37	24	12277	11130	3	1025	2354	9
289	76	45	11547	13085	3	1006	2749	9
291	67	43	26006	132880	4	5197	10718	10
295	25	18	3464	6182	2	251	658	10
299	14	8	2187	4446	2	124	670	10
301	65	54	7079	7091	3	579	1067	11
302	8	7	442	496	1	9	175	11
305	61	46	4528	3805	2	341	1057	11
306	122	95	7275	7195	3	435	1411	11
308	763	598	55621	57264	4	5658	11874	11
311	15	12	1313	1865	1	52	404	12
313	3	2	162	163	1	1	35	12
314	37	31	1907	1682	1	35	716	12
315	2	2	53	85	1	12	62	12
316	6	4	747	395	1	18	199	12
317	8	7	328	255	1	6	75	12
319	7	6	233	177	1	4	40	12
321	12	9	1717	943	1	248	282	13
322	60	51	6532	3527	2	853	1505	13
323	64	50	4850	4254	2	493	883	13
324	17	13	3509	2282	2	495	828	13
325	31	25	2176	1387	1	201	700	13
326	45	36	2696	1183	1	154	600	13
327	205	152	15739	17010	4	1200	1966	13
328	17	13	999	565	1	50	263	13
329	72	53	7838	5432	2	464	1652	13
331	221	174	29180	45696	4	3433	12198	14
332	128	106	9061	6913	3	651	1543	14
333	35	26	4200	11184	3	635	1834	14
334	15	11	1410	5735	2	90	694	14
335	162	123	16670	31892	4	1761	6377	14
336	94	79	5856	4696	2	459	938	14
339	32	23	3164	2790	2	271	800	14
341	33	27	3999	9364	2	526	1453	15
342	140	107	11750	8720	3	620	3124	15
343	45	32	4412	3527	2	178	1121	15
344	432	315	27974	31527	4	1139	7204	15
345	104	81	6936	4909	2	421	1768	15
346	259	211	19880	21531	4	1908	3997	15
347	129	99	7793	6232	3	724	1181	15
348	40	24	3528	1689	2	85	1077	15
349	300	219	21718	19273	4	1273	6460	15
351	79	55	10513	12954	3	678	3679	16
352	94	70	9545	11858	3	414	3339	16
353	205	133	18178	23474	4	889	7344	16
354	295	211	22673	14343	4	1485	6730	16
355	192	110	19221	16515	4	1334	6823	16
356	265	172	23110	18543	4	1260	7898	16
357	259	96	41135	60857	4	2917	10277	16
358	201	147	17521	21819	4	907	4857	16
359	392	293	25322	13897	4	1568	4964	16
361	74	51	6700	5523	2	308	1495	17
362	171	120	14278	12657	3	784	3887	17
363	108	87	9466	12578	3	721	2299	17
364	157	117	13428	11065	3	671	3076	17
365	49	37	3459	7621	2	485	1070	17
366	258	120	38705	29591	4	2268	9467	17
367	588	368	84059	44486	4	14345	13145	17
369	151	106	13920	13398	3	1286	3514	17
371	772	634	105899	223639	4	10264	15852	18
372	377	190	45220	42367	4	2023	36814	18
373	141	108	7903	7760	3	351	2165	18
374	31	23	2590	4363	2	97	1233	18
375	18	14	1435	1674	1	131	412	18
376	81	29	9986	8120	3	490	4770	18
379	47	35	3564	5476	2	142	1102	18
381	186	68	21071	8760	4	1223	6183	19
382	272	141	29028	18028	4	1466	7681	19
384	268	157	31051	16787	4	1648	7761	19
385	27	17	2390	1020	1	197	426	19
386	61	36	14032	8114	3	724	2290	19
387	6	4	415	382	1	17	177	19
391	43	30	2761	3646	2	119	1451	20
393	13	10	685	506	1	15	328	20
394	103	76	8327	6604	3	396	2608	20
395	35	26	2643	1789	1	197	799	20
396	24	19	1406	997	1	51	415	20
399	179	123	11199	8530	3	595	2861	20

Answer 1

After performing regression analysis in EXCEL the following results has been obtained

Sl. No.	SIC Code	No. Emp.	Value Added by Mfg.	New Cap. Exp.	End Yr. Inven.	Cost of Materials
1	201	433	23518	1833	3630	78713
2	202	131	15724	1056	3157	42774
3	203	204	24506	1405	8732	27222
4	204	100	21667	1912	3407	37040
5	205	220	20712	1006	1155	12030
6	206	89	12640	873	3613	13674
7	207	26	4258	487	1946	19130
8	208	143	35210	2011	7199	33521
9	209	171	20548	1135	3135	19612
10	211	21	23442	605	5506	5557
11	212	3	287	2	42	163
12	213	2	1508	15	155	314
13	214	6	624	27	554	2622
14	221	52	2471	292	929	4219
15	222	74	4307	454	1427	5357
16	223	13	673	20	325	1061
17	224	17	817	84	267	707
18	225	169	8986	534	2083	10421
19	226	51	3145	220	697	4140
20	227	55	4076	176	1446	7125
21	228	84	3806	423	1014	8994
22	229	61	4276	464	1291	5504
23	231	27	1239	22	356	716
24	232	200	9423	200	2314	8926
25	233	294	11045	189	2727	11121
26	234	38	1916	29	682	2283
27	235	17	599	21	197	364
28	236	34	2063	20	450	1813
29	237	1	34	2	17	71
30	238	31	1445	16	526	1321
31	239	224	10603	465	2747	12376
32	241	83	5775	539	578	9661
33	242	172	10404	1071	3979	19285
34	243	257	13274	711	3329	18632
35	244	51	1909	88	355	2170
36	245	82	4606	182	580	7290
37	249	94	5518	715	1604	8135
38	251	273	12464	481	3535	12980
39	252	70	5447	358	829	4011
40	253	37	2290	128	447	5101
41	254	81	4182	177	956	3755
42	259	54	2818	109	718	2694
43	261	15	2201	698	725	3279
44	262	116	18848	3143	4257	20596
45	263	55	9655	2360	1502	10604
46	265	212	15668	1352	3976	24634
47	267	232	25918	1750	5427	28963
48	271	403	30692	1277	894	8483
49	272	121	17982	311	1216	6940
50	273	136	17857	618	3736	8863
51	274	69	9699	144	874	2823
52	275	604	38407	2959	4300	29572
53	276	41	3878	198	688	3811
54	277	21	3989	66	577	1047
55	278	65	4388	130	504	2055
56	279	55	4055	210	236	1098
57	281	80	16567	2002	2644	11298
58	282	115	25025	3731	6192	34596
59	283	213	59813	4301	11533	27187
60	284	126	31801	1304	4535	19932
61	285	51	8497	404	2178	9849
62	286	126	28886	6269	8577	46935
63	287	37	12277	1025	2354	11130
64	289	76	11547	1006	2749	13085
65	291	67	26006	5197	10718	132880
66	295	25	3464	251	658	6182
67	299	14	2187	124	670	4446
68	301	65	7079	579	1067	7091
69	302	8	442	9	175	496
70	305	61	4528	341	1057	3805
71	306	122	7275	435	1411	7195
72	308	763	55621	5658	11874	57264
73	311	15	1313	52	404	1865
74	313	3	162	1	35	163
75	314	37	1907	35	716	1682
76	315	2	53	12	62	85
77	316	6	747	18	199	395
78	317	8	328	6	75	255
79	319	7	233	4	40	177
80	321	12	1717	248	282	943
81	322	60	6532	853	1505	3527
82	323	64	4850	493	883	4254
83	324	17	3509	495	828	2282
84	325	31	2176	201	700	1387
85	326	45	2696	154	600	1183
86	327	205	15739	1200	1966	17010
87	328	17	999	50	263	565
88	329	72	7838	464	1652	5432
89	331	221	29180	3433	12198	45696
90	332	128	9061	651	1543	6913
91	333	35	4200	635	1834	11184
92	334	15	1410	90	694	5735
93	335	162	16670	1761	6377	31892
94	336	94	5856	459	938	4696
95	339	32	3164	271	800	2790
96	341	33	3999	526	1453	9364
97	342	140	11750	620	3124	8720
98	343	45	4412	178	1121	3527
99	344	432	27974	1139	7204	31527
100	345	104	6936	421	1768	4909
101	346	259	19880	1908	3997	21531
102	347	129	7793	724	1181	6232
103	348	40	3528	85	1077	1689
104	349	300	21718	1273	6460	19273
105	351	79	10513	678	3679	12954
106	352	94	9545	414	3339	11858
107	353	205	18178	889	7344	23474
108	354	295	22673	1485	6730	14343
109	355	192	19221	1334	6823	16515
110	356	265	23110	1260	7898	18543
111	357	259	41135	2917	10277	60857
112	358	201	17521	907	4857	21819
113	359	392	25322	1568	4964	13897
114	361	74	6700	308	1495	5523
115	362	171	14278	784	3887	12657
116	363	108	9466	721	2299	12578
117	364	157	13428	671	3076	11065
118	365	49	3459	485	1070	7621
119	366	258	38705	2268	9467	29591
120	367	588	84059	14345	13145	44486
121	369	151	13920	1286	3514	13398
122	371	772	105899	10264	15852	223639
123	372	377	45220	2023	36814	42367
124	373	141	7903	351	2165	7760
125	374	31	2590	97	1233	4363
126	375	18	1435	131	412	1674
127	376	81	9986	490	4770	8120
128	379	47	3564	142	1102	5476
129	381	186	21071	1223	6183	8760
130	382	272	29028	1466	7681	18028
131	384	268	31051	1648	7761	16787
132	385	27	2390	197	426	1020
133	386	61	14032	724	2290	8114
134	387	6	415	17	177	382
135	391	43	2761	119	1451	3646
136	393	13	685	15	328	506
137	394	103	8327	396	2608	6604
138	395	35	2643	197	799	1789
139	396	24	1406	51	415	997
140	399	179	11199	595	2861	8530

	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	-587.806	1806.693	-0.325	0.745	-4160.889	2985.277	-4160.889	2985.277
No. Emp.	1.697	18.214	0.093	0.926	-34.325	37.718	-34.325	37.718
Value Added by Mfg.	0.770	0.282	2.725	0.007	0.211	1.328	0.211	1.328
New Cap. Exp.	3.655	1.576	2.319	0.022	0.538	6.772	0.538	6.772
End Yr. Inven.	0.403	0.493	0.816	0.416	-0.573	1.379	-0.573	1.379

Regression Statistics
Multiple R	0.781
R Square	0.609
Adjusted R Square	0.598
Standard Error	15582.722
Observations	140

ANOVA
	df	SS	MS	F	Significance F
Regression	4	51130741892	1.28E+10	52.64237	1.17757E-26
Residual	135	32780866936	2.43E+08
Total	139	83911608829

RESIDUAL OUTPUT

Observation	Predicted Cost of Materials	Residuals
1	26413.40	52299.60
2	16870.49	25903.51
3	27276.12	-54.12
4	24622.41	12417.59
5	19872.12	-7842.12
6	13939.91	-265.91
7	5298.10	13831.90
8	37010.20	-3489.20
9	20931.81	-1319.81
10	21922.93	-16365.93
11	-337.55	500.55
12	693.72	-379.72
13	224.58	2397.42
14	2844.14	1374.86
15	5087.58	269.42
16	156.35	904.65
17	484.56	222.44
18	9407.36	1013.64
19	3004.67	1135.33
20	3869.03	3255.97
21	4439.20	4554.80
22	5023.43	480.57
23	635.61	80.39
24	8668.58	257.42
25	10202.85	918.15
26	1332.34	950.66
27	58.27	305.73
28	1312.36	500.64
29	-545.78	616.78
30	847.53	473.47
31	10760.72	1615.28
32	6201.64	3459.36
33	13230.60	6054.40
34	14006.48	4625.52
35	1432.95	737.05
36	3995.94	3294.06
37	7079.06	1055.94
38	12652.37	327.63
39	5366.60	-1355.60
40	1885.75	3215.25
41	3801.02	-46.02
42	2360.77	333.23
43	3975.35	-696.35
44	27321.39	-6725.39
45	16169.29	-5565.29
46	18376.65	6257.35
47	28340.36	622.64
48	28750.74	-20267.74
49	15086.78	-8146.78
50	17153.22	-8290.22
51	7874.05	-5051.05
52	42550.85	-12978.85
53	3467.94	343.06
54	2992.26	-1945.26
55	3578.59	-1523.59
56	3489.74	-2391.74
57	20684.08	-9386.08
58	35003.49	-407.49
59	66184.74	-38997.74
60	30699.78	-10767.78
61	8393.80	1455.20
62	48231.96	-1296.96
63	13620.70	-2490.70
64	13214.57	-129.57
65	42858.85	90021.15
66	3303.73	2878.27
67	1842.64	2603.36
68	7518.11	-427.11
69	-130.59	626.59
70	4673.58	-868.58
71	7377.92	-182.92
72	68988.35	-11724.35
73	801.21	1063.79
74	-440.25	603.25
75	1359.34	322.66
76	-474.78	559.78
77	143.37	251.63
78	-269.59	524.59
79	-365.83	542.83
80	1774.40	-831.40
81	8266.49	-4739.49
82	5412.05	-1158.05
83	4285.14	-2003.14
84	2156.55	-769.55
85	2368.53	-1185.53
86	17054.19	-44.19
87	498.78	66.22
88	7929.57	-2497.57
89	39711.91	5984.09
90	9605.67	-2692.67
91	5764.57	5419.43
92	1131.59	4603.41
93	21525.27	10366.73
94	6135.25	-1439.25
95	3214.97	-424.97
96	5054.56	4309.44
97	12219.58	-3499.58
98	3987.11	-460.11
99	28744.90	2782.10
100	7179.05	-2270.05
101	23739.57	-2208.57
102	8752.27	-2520.27
103	2940.46	-1251.46
104	23895.11	-4622.11
105	11599.38	1354.62
106	9777.73	2080.27
107	19961.29	3512.71
108	25505.45	-11162.45
109	22158.84	-5643.84
110	25439.02	-6896.02
111	46319.58	14537.42
112	18512.75	3306.25
113	27301.29	-13404.29
114	6423.47	-900.47
115	15125.05	-2468.05
116	10443.90	2134.10
117	13707.24	-2642.24
118	4361.87	3259.13
119	41748.75	-12157.75
120	122847.89	-78361.89
121	16500.18	-3102.18
122	126146.26	97492.74
123	57085.99	-14718.99
124	7890.28	-130.28
125	2309.81	2053.19
126	1192.20	481.80
127	10949.37	-2829.37
128	3198.47	2277.53
129	22909.28	-14149.28
130	30672.19	-12644.19
131	32920.19	-16133.19
132	2189.52	-1169.52
133	13886.44	-5772.44
134	-124.72	506.72
135	2630.04	1015.96
136	148.52	357.48
137	8495.13	-1891.13
138	2548.10	-759.10
139	888.85	108.15
140	11664.27	-3134.27

First Locate the observed value serial number (79) in the given data for Industrial Group 12 and has 7 employees. for that corresponding serial number (79) Based on the model and the multiple regression output, the corresponding residual of this observation is 542.83.