QUESTION : Regression Analysis

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 10 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, 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. PLEASE SHARE THE EXCEL FILE TOO.