Question

QUESTION 3 Regression Analysis

Guide to marks: 20 marks – 5 for a, 10 for b, 3 for c, 2 for d

Belinda, the accountant at Murray Manufacturing Company wants to identify cost drivers for support overhead costs. She has the impression that the staff spend a large part of their time ensuring that the equipment is correctly set up and checking the first units of production in each batch. Deborah has collected the following data for the past 12 months:

Month	OH Cost	MH	Batches
1	$80,000	2,200	300
2	40,000	2,400	120
3	63,000	2,100	250
4	45,000	2,700	160
5	44,000	2,300	200
6	48,000	3,800	170
7	65,000	3,600	260
8	46,000	1,800	160
9	33,000	3,200	150
10	66,000	2,800	210
Total	530,000	26,900	1,980

(a)Using the high-low method to estimate support overhead costs based on machine hours (MH), what would be the estimated support overhead costs (to the nearest $) for a month in which 3,000 machine hours were used?

(b)Using Excel, perform three regression analyses to regress Overhead Cost against Machine Hours, then against Batches, then against both of them simultaneously. Paste your results into Word. State the cost equation from each. Analyse and comment on the results of each regression as you perform it and determine the best one to use as a basis for future use.

(c)If you had to settle for the results of a simple regression, which one would you use and why?

(d)Using the best regression result determine the projected Overhead Cost in a month in which there were 2000 machine hours worked and 150 batches produced.

Answer 1

a)
High Low method
Step 1		Machine Hours	Cost of utilities
High Month	6	3800	$48,000.00
Low Month	8	1800	$46,000.00
Difference		2000	$2,000.00
Step - 2 Variable cost per Machine hour = $815/92	$1.00
Fixed Cost
June = $48,000 - ($1 x 3800)	$44,200.00
July = $46,000 - ($1 x 1800)	$44,200.00
Step -3 Fixed Cost per month	$44,200.00
Total Cost = $44200 + ($1 x Machine Hours)
Total Cost = $44200 + ($1 x 3000)	$47,200.00
b)
x = Maintenance ; y = Machine Hours
y = mx + b.
Slope (m)	-$2.30
Intercept (b)	$59,198.78
Y = $-2.30 X + $59,198.78
x = Maintenance ; y = Batches
y = mx + b.
Slope (m)	$234.57
Intercept (b)	$6,555.56
Y = $234.57 X + $6555.56
c)
Batches variable works best because its fixed cost is less than the machine hours variable though variable cost per unit is very high as compared to negative in case of machine hours and its not practical to have negative variable cost per unit
d)
Y = $-2.30 X + $59,198.78
Y = $-2.30 x 2000 + $59,198.78	$54,590.02
Y = $234.57 X + $6555.56
Y = $234.57 x 150 + $6555.56	$41,740.74