Question

Regression Analysis Using Excel (Appendix). Walleye Company produces fishing reels. Management wants to estimate the cost of production equipment used to produce the reels. The company reported the following monthly cost data related to production equipment:

Reporting Period (Month)	Total Costs	Machine Hours
January	$1,104,000	54,000
February	720,000	30,000
March	600,000	24,000
April	1,320,000	108,000
May	1,368,000	114,000
June	744,000	36,000
July	1,056,000	45,600
August	1,092,000	57,600
September	1,272,000	93,600
October	1,152,000	61,200
November	1,680,000	115,200
December	1,176,000	64,800

Required:

1. Use Excel to perform regression analysis. Provide a printout of the results.
2. Use the regression output to develop the cost equation Y = f + vX by filling in the dollar amounts for f and v.
3. What would Walleye Company’s estimated costs be if it used 90,000 machine hours this month?

Answer 1

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.921954088
R Square	0.849999341
Adjusted R Square	0.834999275
Standard Error	123526.7057
Observations	12
ANOVA
	df	SS	MS	F	Significance F
Regression	1	8.6466E+11	8.6466E+11	56.6663738	1.9983E-05
Residual	10	1.5259E+11	1.5259E+10
Total	11	1.0173E+12
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	534766.5519	83965.169	6.36890937	8.1512E-05	347680.497	721852.607	347680.497	721852.607
X Variable 1	8.540797732	1.13458157	7.52770708	1.9983E-05	6.01279245	11.068803	6.01279245	11.068803
RESIDUAL OUTPUT
Observation	Predicted Y	Residuals	Standard Residuals
1	995969.6295	108030.371	0.91723654
2	790990.4839	-70990.484	-0.6027478
3	739745.6975	-139745.7	-1.1865169
4	1457172.707	-137172.71	-1.1646708
5	1508417.493	-140417.49	-1.1922208
6	842235.2703	-98235.27	-0.8340708
7	924226.9285	131773.071	1.11882497
8	1026716.501	65283.4987	0.55429237
9	1334185.22	-62185.22	-0.5279863
10	1057463.373	94536.6268	0.80266733
11	1518666.451	161333.549	1.36980949
12	1088210.245	87789.755	0.74538272
b)
	Coefficients
y-intercept	5,34,767
x variable	8.540797732
Y = $534,766.552 + 8.5408 x Machine hours
c)
Y = $534,766.552 + 8.5408 x 90000	$ 13,03,438.35