Question

Hartman Company's Lucas plant manufactures thermostatic controls. Plant management has experienced fluctuating monthly overhead costs and wants to estimate overhead costs accurately to plan its operations and its financial needs. Interviews with plant personnel and studies reported in trade publications suggest that overhead in this industry tends to vary with labor-hours.

A member of the controller's staff proposed that the behavior pattern of these overhead costs be determined to improve cost estimation. Another staff member suggested that a good starting place for determining cost behavior patterns is to analyze historical data. Following this suggestion, monthly data were gathered on labor-hours and overhead costs for the past two years. No major changes in operations occurred over this period of time. The data are shown in the following table:

Month	Labor-Hours			Overhead Costs
1		251,630		$	2,741,620
2		238,530			2,375,430
3		193,000			2,400,350
4		271,500			2,590,850
5		324,000			3,072,020
6		291,030			2,618,370
7		271,500			2,480,440
8		251,650			2,745,760
9		232,000			2,821,550
10		343,650			3,437,910
11		186,030			2,210,540
12		231,980			2,550,880
13		382,900			3,603,920
14		376,500			3,404,990
15		291,020			3,016,700
16		396,030			3,638,540
17		356,650			3,554,100
18		323,850			3,191,820
19		389,475			3,481,920
20		317,280			3,219,720
21		343,540			3,495,630
22		336,970			3,207,460
23		382,910			3,600,830
24		376,350			3,636,850

Required:

a. Use the high-low estimation method to estimate the overhead cost behavior (fixed and variable portions components of cost) for the Lucas plant. (Round variable cost per unit to 2 decimal places.)

c. Using Excel, compute regression coefficients to describe the overhead cost equation. (Round your answers to 2 decimal places.)

d. Use the results of your regression analysis to develop an estimate of overhead costs assuming 400,000 labor-hours will be worked next month. (Round Cost per Labor hour to 2 decimal places.)

Answer 1

a)
Variable cost per unit = (Highest Activity Cost - Lowest Activity Cost)/(Highest Activity Units - Lowest Activity Units)
Variable cost per unit = (396,030 - 2210540)/(396030 -186030 DL	$               6.80	per DL
Fixed cost = Highest Activity Cost - (Variable Cost Per Units x Highest Activity Units)
Fixed cost =  3,638,540 - (6.80 x 396,030 direct labour)	$    945,536.00
Y = $945,536  + $6.80 x Direct labour hours
b)
SUMMARY OUTPUT
Regression Statistics
Multiple R	0.94254329
R Square	0.888387854
Adjusted R Square	0.883314574
Standard Error	160599.2031
Observations	24
ANOVA
	df	SS	MS	F	Significance F
Regression	1	4.5165E+12	4.5165E+12	175.111163	5.9439E-12
Residual	22	5.6743E+11	2.5792E+10
Total	23	5.0839E+12
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	935053.6532	162837.579	5.74224732	8.9201E-06	597349.183	1272758.12	597349.183	1272758.12
X Variable 1	6.882756032	0.52012228	13.2329575	5.9439E-12	5.80408844	7.96142362	5.80408844	7.96142362
Fixed Cost	$    935,053.65
Variable Cost per unit	$               6.88
X = $935,053.65 + 6.88 x Direct Labour
d)
X = $935,053.65 + 6.88 x 400,000 DL Hours	$ 3,688,156.07