Question

3 54,15 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. 2.85 Boints 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. Book Month Labor-Hours Overhead Costs 251,650 $2,741,720 2 238,55 2,375,430 3 193,100 2,400,400 271,550 2,590,870 324,050 3,072.07 6 291,050 2,618,420 References 7 271,550 251,670 2,745,810 9 232,25 2,821,600 10 11 186,050 2,189,590 12 2,550,90 382,90 3,603,970 14 375,550 15 291,040 3,016,75 . 3.638,500 356,670 323,870 19 309,495 3.481,97 20 3,219,770 21 22 336.900 3.207,510 23 24 376,170 3.636.900 4 5 317. Required: a. Use the high low estimation method to estimate the overhead cost behavior fored and variable portions components of cost for the Lucas plant c. Using Excel, compute regression coefficients to describe the overhead cost equation, d. Use the results of your regression analysis to develop an estimate of overhead costs assuming 410.000 labor-hours will be worked next month Complete this question by entering your answers in the tabs below.

Answer 1

Month	Labour-hours	Overhead Costs
1	251,650	$2,741,720
2	238,550	2,375,480
3	193,100	2,400,400
4	271,550	2,390,870
5	324,050	3,072,070
6	291,050	2,618,420
7	271,550	2,480,490
8	251,670	2,745,810
9	232,025	2,821,600
10	343,700	3,437,960
11	186,050	2,189,590
12	232,000	2,550,930
13	382,920	3,603,970
14	376,550	3,405,040
15	291,040	3,016,750
16	396,050	3,630,590
17	356,670	3,554,150
18	323,870	3,191,870
19	389,495	3,481,970
20	317,300	3,219,770
21	343,500	3,495,640
22	336,990	3,207,510
23	382,930	3,600,880
24	376,370	3,656,900

2 y High dow Mehnod formula. Highest activity cost - Lowest Activity Cost per unit Highest activity Variable Cost Lowest Activity units Units Once du vauiable cost per unit is determined: fixed cost a Highest Vaniable Cost [ & Highest Activity Activity cost per unit Units fixed Variable Gost Gosty Lowest Activity Lowest activity cost Cost Units perunit At last Cost Model - Fixed cost + [vaniable Cost & Unit Activity

VOTAR Tuvana So, heu, Vaniable Cost 3,656,900 2,189,590 per unit 376,370 - 186,050 1,467,310 = 7.70 190320 Fixed Cost using high activity cost 3,656,900 - [4-70 x 3769370] 3,656,900 2,8948049 - $ 758,851 - Fixed Cost using low activity cost 2, 189,590 [7070 x 186,050] 2,189,590 -1,432,585 $ 757,005 The difference of $1,846 is nominal.

2.

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.933267114
R Square	0.870987506
Adjusted R Square	0.82553296
Standard Error	115869.6489
Observations	23
ANOVA
	df	SS	MS	F	Significance F
Regression	1	1.99408E+12	1.99E+12	148.5261	5.47096E-11
Residual	22	2.95367E+11	1.34E+10
Total	23	2.28945E+12
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	0	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
1	20175.17861	1655.449631	12.18713	2.95E-11	16741.9862	23608.37101	16741.9862	23608.37101

3) Overhead Cost assuming 410,000 labour- hour for next month is fixed cost + [ Vanoble Cost perunit x Activity Unit] 758,851 + [7.40 x 410,000) = 758,851 + 3,157, az - $ 3,915,851 Aub