1. Analyze manufacturing overhead
2. Develop a comparison of application of manufacturing overhead using a traditional and an ABC approach.
3. Recommend a course of action on how to apply manufacturing overhead.
4. Develop scattergraphs, simple regression output tables, and multiple output table.
5. Develop cost estimation based on your regression analysis.
6. Analyze results and recommend the best approach for the cost estimation.
Part 1-- Using a predetermined overhead rate
XYZ company manufactures chemicals used in radiological imaging systems. The company had originally used machine hours as the cost driver to develop one predetermined overhead rate.
You have been recently hired by this company to review the costing of chemicals and realize that maybe a predetermined overhead rate does not give an accurate cost picture.
Discuss overhead cost issues want to implement ABC, so you have done some research and come up with the following activity cost pools and cost drivers.
| Activity cost Pool |
Budgeted
Overhead
Cost |
Cost driver |
Budgeted Level
for cost Driver |
| Material handling |
20,000 |
Weight of raw materials in pounds |
80,000 |
Hazardous waste
control |
30,000 |
Weight of hazardous chemical
used in pounds |
10,000 |
| Other overhead costs |
400,000 |
Machine hours |
50,000 |
| Total Budgeted overhead cost |
$450,000 |
|
|
Required:
1. Using the information above calculate the single predetermined overhead rate this company is using based on machine hours as the cost driver.
2. If the company uses the ABC approach, what would be the pool rates for the activities you have identified above.
I have computed the first activity pool rate above in column E and you should compute the remainder of the rates for the other 2 activities.
3. An order for radiological development chemicals has the following production requirements:
| Material handling |
1000 |
Raw material in pounds |
Hazardous waste
control |
600 |
Hazardous Chemical used in pounds |
| Other overhead costs |
1500 |
Machine hours |
Write a short report that identifies factors that explain why the two product-costing systems result in differing costs.
Which system do you recommend? Why?
Part 2
Explanation of multiple regression, simple regression, and the scattergraph.
Food services company is looking at the costs involved with their catering services.
In the past, the company has used meals produced as the cost driver for Overhead costs, but you have been recently hired as a consultant and have decided to use your new MBA skills to make a recommendation to management on what should be the cost driver(s) for overhead costs. You have decided to include number of deliveries in your analysis, too.
| Month |
Deliveries |
Meals
produced |
Total
Overhead
Cost |
| 1 |
1,340 |
12,690 |
$ 69,094 |
| 2 |
1,180 |
11,980 |
64,927 |
| 3 |
1,050 |
10,950 |
60,332 |
| 4 |
930 |
10,280 |
57,953 |
| 5 |
840 |
9,020 |
55,984 |
| 6 |
780 |
8,130 |
53,119 |
| 7 |
700 |
7,540 |
52,706 |
| 8 |
630 |
6,980 |
53,874 |
| 9 |
680 |
8,930 |
53,445 |
| 10 |
760 |
9,800 |
54,869 |
Complete the following 4 Requirements:
1. Prepare 2 scattergraphs and 2 simple regression analyses***** to estimate the Overhead costs using each of the cost drivers above. Make sure you show the equation of the line and R-squared on each of your scattergraphs.
2. Prepare a multiple regression table the 2 independent variables-Deliveries and meals produced. Do not try to prepare a scattergraph, since you cannot make a chart with multiple X's.
Using the output from the multiple regression, give the equation to estimate overhead costs in terms of these 2 cost drivers. Excel reads left to right, so X1 will be Deliveries and X2 will be meals produced.
3. Assuming the following level of cost-driver volume, what is the estimated overhead cost using simple regression for each of the cost drivers and using multiple regression to estimate the cost of overhead? You should have 3 computations for this answer (you need to determine 2 since I have calculated the first one for you).
Your calculation should only use cell references. You should use the applicable regression table to estimate the cost by referencing the cost drivers below and the output from the applicable regression table.
4. Using the simple and multiple regression analyses above, what would you recommend to estimate the Overhead Costs? Why? Make sure you discuss the Coefficient of Determination in your response. Your comments should be at least 75 words.