In: Accounting
Spark Electrical Company manufactures electrical components. The plant manager – Mr. Uptight, has experienced difficulties with fluctuating monthly overhead costs.
Mr. Uptight wants to be able to estimate overhead costs to plan its operations and financial needs. A trade association publication reports that for companies manufacturing electrical components, overhead tends to vary with machine hours.
Monthly data was gathered on machine hours and overhead costs for the past two years. There were no major changes in operations over this period of time. The raw data is in the chart below:
Month Numbers | Machine Hours (1000) | Overhead Cost ($), (1000) |
1 | 20 | 84 |
2 | 25 | 99 |
3 | 22 | 89.5 |
4 | 23 | 90 |
5 | 20 | 81.5 |
6 | 19 | 75.5 |
7 | 14 | 70.5 |
8 | 10 | 64.5 |
9 | 12 | 69 |
10 | 17 | 75 |
11 | 16 | 71.5 |
12 | 19 | 78 |
13 | 21 | 86 |
14 | 24 | 93 |
15 | 23 | 93 |
16 | 22 | 87 |
17 | 20 | 80 |
18 | 18 | 76.5 |
19 | 12 | 67.5 |
20 | 13 | 71 |
21 | 15 | 73.5 |
22 | 17 | 72.5 |
23 | 15 | 71 |
24 | 18 | 75 |
A. Use the high-low method to estimate the overhead costs and write the cost estimation equation.
B. Use the regression analysis program in Excel to obtain the regression output and write the cost estimation equation.
C. Use the results (formulas) in A. and B. above separately, to prepare the cost estimate for 22,000 machine hours.
D. Explain the results obtained from C. above after comparing them, using statistical data obtained.