EXCEL MODEL #1

Hickory Company manufactures two products - 14,000 units of Product Y and 6,000 units of Product Z. The company uses a plantwide overhead rate based on direct labor-hours. It is a considering implementing an activity-based costing (ABC) costing to allocate all of its manufacturing overhead to their products using four cost pools. The following additional information is available for the company as a whole and for Products Y and Z.

Activity Cost Pool	Activity Measure	Total Activity Cost Pool	Total Amount of Activity Allocation Base
Machining	Machine-hours	$200,000.00	10,000	<--Machine Hours
Machine setups	Number of setups	$100,000.00	200	<--Setups
Inspection	Number of inspections	$50,000.00	25,000	<--Inspections
General factory	Direct Labor-hours	$300,000.00	12,000	<--Direct Labor Hours
		$650,000.00	<--Total Manufacturing Overhead
Activity Measure	Total Amount of the Activity Allocation Base for All Units of Product Y	Total Amount of the Activity Allocation Base for All Units of Product Z	Total Amount of Activity Allocation Base
Machine-hours	7,000	3,000	10,000
Number of setups	50	150	200
Number of Inpections	12,000	13,000	25,000
Direct Labor-hours	8,000	4,000	12,000
Direct Costs for Products Y & Z:
Direct Costs:	Total Cost for All units Produced of Product Y	Total Cost for All units Produced of Product Product Z
Direct Material	$100,000.00	$110,000.00
Direct Labor	$50,000.00	$60,000.00
Number of Finished Units Produced	14,000	6,000

INSTRUCTIONS:

Set up an excel model to calculate the following (use the tab called "Your Excel Model" at the bottom of this screen):

1. The company's plantwide overhead rate.

2. Using the traditional plantwide overhead rate , the amount of manufacturing overhead cost allocated to Product Y and Product Z.

3. Total product cost for Product Y and Z under the traditional plantwide overhead rate system.

4. Total product cost per unit for Product Y and Product Z under the traditional plantwide overhead rate system.

5. The company switches to an ABC costing system for allocating overhead to product Y and product Z.

Calculate the overhead activity rate for the four cost pools (machining, machine setups, inspections

and general factory)

6. Using the ABC system, calculate total manufacturing overhead cost assigned to Product Y.

7. Using the ABC system, calculate total manufacturing overhead cost would be assigned to Product Z.

8. Total product cost for Product Y and Z under the Activity based costing system?

9. Total product cost per unit for Product Y and Product Z under Activity Based Costing

system.

10. Which product was overcosted when using the Plantwide rate, in comparison to ABC Costing?

11. Which product was undercosted when using the Plantwide rate, in comparison to ABC Costing?