Problem 5-68 (Static) Interpretation of Regression Results: Simple Regression Using a Spreadsheet (LO 5-1, 3)

Goulburn, Incorporated produces parts for heavy equipment used in mining and construction. The plant that produces one part common to many vehicles is highly automated, so all labor is considered part of factory overhead. The plant manager, who has just been promoted, would like to understand how overhead costs fluctuate in order to improve planning and budgets. After discussions with both financial and operations members of the plant staff, there is general agreement that the best cost driver for overhead is machine-hours.

Monthly data were collected from the most recent two years on machine-hours and overhead. More months of data were available, but a process change had taken place about 30 months earlier, so the staff believed any data from before that time would be misleading. The data are shown in the following table:

Month	Machine-Hours	Factory Overhead
1	39,300	$ 527,100
2	37,300	416,500
3	30,100	365,800
4	42,400	498,200
5	50,600	590,700
6	45,500	503,400
7	42,400	476,900
8	62,100	702,400
9	36,300	425,300
10	53,700	661,000
11	52,700	616,700
12	36,300	490,500
13	59,900	693,000
14	58,900	654,700
15	45,500	580,000
16	39,300	527,900
17	55,800	683,400
18	50,600	613,700
19	60,900	669,500
20	49,600	619,100
21	53,700	672,100
22	29,100	445,000
23	59,900	692,400
24	58,900	718,500

Required:

1. Use the high-low estimation method to estimate the overhead cost behavior (fixed and variable components of cost) for the Goulburn plant.
2. Prepare a scattergraph showing the overhead costs plotted against the machine-hours.
3. Use a spreadsheet program to compute regression coefficients to describe the overhead cost equation.
4. Use the results of your regression analysis to develop an estimate of overhead costs assuming 48,000 machine-hours will be worked next month.