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,610		$	2,741,520
2		238,510			2,375,380
3		192,900			2,400,300
4		271,450			2,590,830
5		323,950			3,071,970
6		291,010			2,618,320
7		271,450			2,480,390
8		251,630			2,745,710
9		231,975			2,821,500
10		343,600			3,437,860
11		186,010			2,231,490
12		231,960			2,550,830
13		382,880			3,603,870
14		376,450			3,404,940
15		291,000			3,016,650
16		396,010			3,638,490
17		356,630			3,554,050
18		323,830			3,191,770
19		389,455			3,481,870
20		317,260			3,219,670
21		343,520			3,495,580
22		336,950			3,207,410
23		382,890			3,600,780
24		376,330			3,636,800

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 and fixed cost to the nearest whole dollar.)

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 390,000 labor-hours will be worked next month. (Round Cost per Labor hour to 2 decimal places.)

Answer 1

High-Low Method Formula

Variable cost per unit is calculated using the following formula:

Variable Cost per Unit =	y2 -y1
	x2 - x1

Where,
y₂ is the total cost at highest level of activity;
y₁ is the total cost at lowest level of activity;
x₂ are the number of labour hours etc. at highest level of activity; and
x₁ are the number of labour hours etc. at lowest level of activity

The variable cost per unit is equal to the slope of the cost volume line (i.e. change in total cost ÷ change in Labour Hours)

Total fixed cost is calculated by subtracting total variable cost from total cost.

We have,

at highest activity: x_{2 =} 396010 y₂ = 3638490

at lowest activity : x₁ = 186010 y_{1 =} 2231490

Therefore, Variable cost per unit = ${(3638490-2231490) / (396010-186010)}= $6.70 per unit

Total Fixed Cost = 3638490 - (6.70*396010) = $985223

(c)

Regression Statistics
Multiple R	0.942132496
R Square	0.88761364
Adjusted R Square	0.882505169
Standard Error	160605.0714
Observations	24

Coefficients

Intercept(Fixed Cost) = $944377.96

Labour Hours = $6.85

d. Estimated Overhead Cost = $944377.96 + $6.85 * 390000

= $3615877.96