Acct. 111
EXCEL: Chapter 4
Matchbox Co. Manufactures Product A and Product B. Annual Production in units for each product is as follows:
		Product A	Product B
Annual Production in units		4,800	22,000
The company currently uses direct labor hours to assign overhead costs to products. The estimates for total overhead and labor hours for the year are as follows:
Total Estimated Overhead		$ 980,000		Total Estimated Labor Hours			56,000
Other per unit information relating to Product A and B is
		Product A	Product B
Labor Hours to produce a unit		2.5	2
Direct Materials cost per unit		$ 42	$ 38
Direct Labor cost per unit		$ 25	$ 20
1. Compute the predetermined overhead rate under a traditonal costing system.				2. Compute the overhead cost to produce one unit of Product A and one unit of Product B under the traditional costing system.
							Overhead Rate Per Unit
cell reference	=	formula		Product A	cell reference	cell reference	formula
cell reference				Product B	cell reference	cell reference	formula
3. Compute the total cost to product one unit of Product A and Prodcut B under traditional costing. Use the chart below.
	Traditional Costing		Activity Based
Per Unit	Product A	Product B	Product A	Product B
Direct Materials	cell reference	cell reference	cell reference	cell reference
Direct Labor	cell reference	cell reference	cell reference	cell reference
Overhead	cell reference	cell reference	cell reference	cell reference
Total Cost Per Unit	formula	formula	formula	formula
4. Matchbox Co. is considering the implementation of an Activity Based Costing system and has identified the following activities, traceable overhead costs, and cost drivers. Complete the table below to compute the overhead rate for each activity
Activity	Traceable Overhead	Cost Driver Information				Overhead Rate
		Activity Driver	Product A Driver	Product B Driver	Total A & B Driver
Machine Set-ups	$ 300,000	# set-ups	3,200	1,800	formula	formula
Quality Inspections	$ 158,000	# inspections	5,000	2,900	formula	formula
Production Orders	$ 100,000	# orders	400	600	formula	formula
Machine Hrs. Worked	$ 325,000	# machine hrs	15,000	35,000	formula	formula
Materials Receipts	$ 97,000	# receipts	300	700	formula	formula
	formula
5. Compute the overhead cost to produce one unit of Product A and one unit of Product B under the Activity Based Costing System. Fill in this unit cost data along with unit Direct Material and Direct Labor cost data in the chart on the preceding page.
Activity	Cost Driver		Product A			Product B
				Overhead	Total		Overhead	Total
			A Driver	Rate	Overhead	B Driver	Rate	Overhead
Machine Set-ups	# set-ups		cell reference	cell reference	formula	cell reference	cell reference	formula
Quality Inspections	# inspections		cell reference	cell reference	formula	cell reference	cell reference	formula
Production Orders	# orders		cell reference	cell reference	formula	cell reference	cell reference	formula
Machine Hrs. Worked	# machine hrs		cell reference	cell reference	formula	cell reference	cell reference	formula
Materials Receipts	# receipts		cell reference	cell reference	formula	cell reference	cell reference	formula
			Total Product A Overhead		formula	Total Product B Overhead		formula
			Total Units		cell reference	Total Units		cell reference
			overhead cost per unit		formula	overhead cost per unit		formula
6. Save the spreadsheet as Your Name Run1				Type your response to #9 here
7. Make the following changes (do not change any other data):
change the # of units in cell D6 to 24,000
change the # of hrs. in cell C14 to 3
8. Save the spreadsheet as Your Name Run 2
9. Compare the two spreadsheets and explain why the following cells changed (type your response to the right):
Total Estimated Hrs in Cell H10
Overhead Rate Per Unit in H21
Overhead Cost Per Unit in I59
10. Upload both spreadsheets to Blackboard.