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.

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

Solution:

We can use the excel regression data analysis tool to find the equation of the multiple regression line. The excel output is given below:

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.780602653
R Square	0.609340503
Adjusted R Square	0.597765406
Standard Error	15582.72237
Observations	140
ANOVA
	df	SS	MS	F	Significance F
Regression	4	51130741892	12782685473	52.64237039	0.0000
Residual	135	32780866936	242821236.6
Total	139	83911608829
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	-587.8061303	1806.693072	-0.325349191	0.745420499	-4160.889015	2985.276754
No. Emp.	1.696715833	18.21382118	0.093155402	0.925918211	-34.32461713	37.7180488
New Cap. Exp.	3.655194027	1.57594828	2.319361666	0.02187642	0.53845333	6.771934725
Value Added by Mfg.	0.769808992	0.2824569	2.725403391	0.007273847	0.211196171	1.328421814
End Yr. Inven.	0.402806454	0.493417774	0.816359839	0.415732535	-0.573022052	1.378634961

Therefore, the regression equation is:

The predicted value is:

Therefore, the residual is:

Residual = Actual value - Predicted value

=177 - (-365.83059)

=542.83