Direct labor hours, overhead rate, product cost using ABC

Problem 2 Please use this template below to solve problem 2 and save your file using your first initial, last name, name of assignment, so jstevensproblem2.xls
Due July 6th 11:59 pm central Upload your solution in area 2 of the problem 2 assignment area. Make sure you type your name in cell A3
Name: Sheila Cole You should always verify that your problem has been loaded correctly by either going back to the assignment and clicking on Ok and you should
see the file you uploaded and you can also go to view grades and click on the exclamation to see the file you have loaded.  Remember it is your
responsibility to take the time to verify that your file has uploaded correctly.  If there are problems, please contact me immediately.
You will be graded on the accuracy of your answer and the usage of excel.
You must use excel cell referencing and all of your calculations.
The problem solution is worth a total of 10 pts. I will take off 5 pts. if you do not use the excel application appropriately
Note, see demonstration exercise related to traditional and ABC methods and power point in chapter 5 course documents area for helpful information!

Industries Inc. manufactures 2 products: A and B. A review of the company's accounting records revealed the following per-unit costs and production volume:
Product A Product B
Production volume(units) 2500 5000

Direct material cost per unit $40 $60

Direct Labor cost:
2 hours at $12 $24
3 hours at $12 $36

Manufacturing overhead
2 hours at $93 $186
3 hours at $93 $279

Manufacturing overhead is budgeted at $1,860,000.

Part 1
a. Using direct labor hours as the base for assigning manufacturing overhead cost to products, compute the direct labor needed and the predetermined overhead rate using direct labor hours.
Hint: the predetermined overhead rate you compute should be $93, since I have given you the answer but I want to make sure you know how to determine the $93.
Direct labor hours, budget hours
Product A
Product B
Total direct labor hours

"Predetermined Overhead
rate" per direct labor hour
b. Using the predetermined overhead rate you determined in "a" above, determine the unit product cost of each product.
Hint: Don't forget that unit product cost includes direct material, direct labor, and Manufacturing overhead. Don't make this complicated, since the numbers are already there for you.

Product A Product B
Direct material cost per unit $40
Direct labor cost per unit
Manufacturing overhead per unit
Total product cost per unit

Part 2
You are a recent hire to Industries Inc. and you have made it known that you just recently completed an MBA program that had a management accounting
course that taught you about another costing system that might be appropriate. During a meeting, you suggested that an ABC system might be a more
accurate costing system. Management has asked you to recompute the unit cost using the ABC costing approach, since the company is showing declining profits.

a. First complete the following chart that is set up like exhibit 5-7 on page 177, and there is a more detailed explanation of the 3 step calculation approach example
on page 176 exhibit 5-6. I have highlighted the columns in red you need to complete.
Activity Activity Cost pool Cost driver Cost Driver Quantity Pool rate Product line "Cost driver
quantity for product line" "Activity cost
for product line" "Product
line
production
volume" "Activity cost per unit
of product"
Setups $240,000 Number of setups 120 Product A 100 2,500
Product B 20 5,000
Total 120
General Factory 1,500,000 Direct labor hours 20,000 Product A 5000 2,500
Product B 15000 5,000
Total 20000
Machine processing 120,000 Machine hours 3,000 Product A 2200 2,500
Product B 800 5,000
Grand Total $1,860,000

b. Complete the following table to determine the unit product costs from ABC, just like the exhibit 5-8 on page 178

Product A Product B
Direct material cost per unit $40
Direct labor cost per unit
Total direct costs per unit

Manufacturing overhead(based on ABC)
Setups
General Factory
Machine processing
Total product cost per unit