Part 1–covers chapter 3 using a predetermined overhead rate and chapter 5 using ABC method to allocate manufacturing overhead.
XYZ company manufactures wheel rims. The company had originally used direct labor hours as the cost driver to develop one predetermined overhead rate.
You have been recently hired by this company to review the costing of this product and realize that maybe a predetermined overhead rate does not give an accurate cost picture.
You remember discussing overhead cost issues in MBA642 and 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
Cost” Cost driver “Budgeted Level
for cost Driver””Activity
Material Handling 72,000 Number of parts 12,000 $6.00
Machine Setup18,000 Number of setups per 1,000 rims 30
Insertion of parts 2,88,000 Number of parts 12,000
Finishing 2,40,000 Finishing Direct labor hours 8,000
Total Budgeted overhead cost $6,18,000
Total Direct Labor hours 13,000
The company budgets to produce 1,000 units of each rim model
Required: Round all answers to the nearest cent
1. Using the information above calculate the single predetermined overhead rate this company is using based on direct labor hours as the cost driver.
Predetermined overhead rate:
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 activities.
3. The company produces two wheel rims as follows along with the usage of activities:
Parts per rim 5 7
Setups per 1,000 rims 15 15
Finishing Direct labor hours per rim 3 5
Total direct labor hour per rim 5 8
If the company is using the predetermined overhead rate based on direct labor hours, what is the overhead cost for one rim of each wheel type?
“Total Overhead cost per each rim using a
predetermined overhead rate”
If the company is using the ABC method, what is the overhead cost for one rim of each type?
Insertion of parts
Total Overhead cost for each rim using ABC
4. In addition to the overhead cost above, the rims have the following prime costs:
Direct Materials 34 51
Direct Labor 48 54
“Compute the Total product cost if the company
uses the predetermined rate?”
“Compute the Total product cost if the company
uses the ABC rate?”
5. If there is not enough experienced labor to produce more standard and deluxe rims, so the company is trying to maximize profits.
Therefore the company should either produce 2,000 standard rims or 2,000 deluxe rims.
Below is the sales price per rim:Standard Deluxe
Sales price per rim $920.00 $1,100.00
Compute the profit for each rim using the total product cost that you feel is more accurate.
Which rim would they produce and which method did you use to compute the profit? Why did you use either the predetermined rate or the ABC rate in the total product cost?
Your answer will be graded based on your application of excel and the accuracy of your solution.
Please make sure you have reviewed the demonstration in the Course Documents area
for chapter 6 before attempting this part of the project. I have an audio step by step excel explanation
of multiple regression, simple regression, and the scattergraph.
I do have a word tutorial and my excel solution is also available in the course documents area. You need to have the data analysis tool available.
It would be good practice for you to try to prepare the demonstration problem using my tutorial before attempting to solve this problem. This project focuses on chapter 6, but ties in chapter 5 by trying to choose an independent variable that relates to the cost we are trying to measure.
Computer Company’s ABC analysis indicates that Manufacturing overhead costs include large costs incurred for setups when a new batch of carpet is started. In the past the company has used Direct Manufacturing labor hours as the cost driver for Manufacturing 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 manufacturing overhead costs.
Do not retype the information above. You should use this sheet as your data sheet and your solution should be on separate
Part 2 should contain 2-simple regression tables, 1-multiple regression table, and 2-scattergraphs when you are done.–I have done one of the scattergraphs and simple regression using direct labor for you.
1. Prepare 2 scattergraphs and 2 simple regression analyses***** to estimate the Manufacturing 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.
I have gone ahead and developed the simple regression table and the scattergraph using Direct Manufacturing labor hours as the cost driver (I.e. independent variable)
You do not need to redo these, but you may want to use these as a verification that you know how to develop a scattergraph and a simple regression table.
The output and chart for your simple regression should be on separate sheets that should be named appropriately.
Type the equation of the line under the regression output on each of the simple regression worksheets.
To make a scattergraph with data from nonadjacent columns highlight the X data (i.e. independent variable-cost driver) first and then hold down the
control (ctrl) key and highlight the Y data (i.e. the dependent variable-cost).
If you have done your scattergraphs correctly the equation of the line and the R-squared should match the output you have on your regression table.
(i.e. R-squared, Intercept, and X Variable.) Hint: the Y(dependent) variable is the same in all your scattergraphs and regression analyses–which is Manufacturing overhead cost.
2. Prepare a multiple regression table using batches and direct labor hours for the independent variables. 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 manufacturing overhead costs in terms of these
2 cost drivers. Type out the equation of the line on your multiple regression worksheet.
X1 Number of batches
X2 Direct Manufacturing labor hours
3. Assuming the following level of cost-driver volume, what is the estimated Manufacturing overhead cost using simple regression for each of the cost drivers and using multiple regression. You should have 3 computations for this answer. I have completed one of the answers for you. You must use cell referencing from your regression tables to compute the estimated cost to earn credit for part 3.
Cost Drivers (independent variables):
Number of batches 14
Direct Manufacturing labor hours 40
4. Using the simple and multiple regression analyses output tables, what independent variable(s) would you recommend to estimate the Indirect Manufacturing Overhead Costs? Why? Your response should be at least 75 words and discuss the R-Squared.