Activity based costing

This assignment should be done using Excel. Parts 1, 2, 2A should be performed on separate spreadsheets in your Excel workbook.

Part 1 of the assignment requires you to determine the cost of a unit of coffee (Mona Loa and Malaysian) using the traditional overhead allocation method. A unit of coffee is a one pound bag. The unit cost will include direct materials, direct labor, and manufacturing overhead (as determined using the predetermined overhead rate). You NEED to use Excel formulas whenever possible. Basically, the only time you will not use formulas is when you enter given information.

Part 2 of the assignment requires you to determine the cost of a unit of coffee (Mona Loa and Malaysian) using the activity-based costing method. As with the traditional method, you will need to show direct materials, direct labor and manufacturing overhead (as determined using ABC). Once again, use formulas wherever you can.

Part 2A is a comparison of both methods. Link the numbers to Parts 1 and 2 whenever possible. Show a side-by-side comparison of unit product costs calculated under each method (show the unit product cost of each type of coffee using both traditional and ABC costing). Unit product costs include direct materials, direct labor and manufacturing overhead costs on a per unit basis. Further, determine the changes in the profit margins for each bag of coffee under both methods (the sales price mark up % is given below). How much was the company losing per bag of the Malaysian coffee under the Traditional Method? Remember to use formulas.

Activity-Based Costing as an Alternative to Traditional Product Costing

Coffee Bean, Inc. (CBI), is a processor and distributor of a variety of blends of coffee. The company buys coffee beans from around the world and roasts, blends, and packages them for resale. CBI currently has 40 different coffees that it sells to gourmet shops in one-pound bags. The major cost of the coffee is raw materials. However, the company's predominantly automated roasting, blending, and packing process requires a substantial amount of manufacturing overhead. The company uses relatively little direct labor.

Some of CBIs coffees are very popular and sell in large volumes, while a few of the newer blends have very low volumes. CBI prices its coffee at manufacturing cost plus a markup of 30%. If CBI's prices for certain coffees are significantly higher than market, adjustments are made to bring CBI's prices more into alignment with the market because customers are somewhat price conscious.

For the coming year, CBI's budget includes estimated manufacturing overhead cost of $4,500,000. CBI assigns manufacturing overhead to products on the basis of direct labor-hours. The expected direct labor cost totals $600,000, which represents 80,000 hours of direct labor time. Based on the sales budget and expected raw materials costs, the company will purchase and use $6,000,000 of raw materials (mostly coffee beans) during the year.

The expected costs for direct materials and direct labor for one-pound bags of two of the company's coffee products appear below.

		Mona Loa			Malaysian
Direct materials		$	4.20		$	3.20
Direct labor (0.05 hours per bag)		$	0.30		$	0.30
(note that it takes 0.05 DLHs to make one bag of coffee youll need this to apply MOH using the Traditional Method)
CBI's controller believes that the company's traditional costing system may be providing misleading cost information. To determine whether or not this is correct, the controller has prepared an analysis of the years expected manufacturing overhead costs, as shown in the following table:
Activity Cost Pool	Activity Measure			Expected Activity for the Year					Expected Cost for the Year
Purchasing	Purchase orders			3,000			orders		$	600,000
Material handling	Number of setups			2,500			setups			1,120,000
Quality control	Number of batches			1,000			batches			160,000
Roasting	Roasting hours			120,000			roasting hours			1,200,000
Blending	Blending hours			80,000			blending hours			1,000,000
Packaging	Packaging hours			21,000			packaging hours			420,000
Total manufacturing overhead cost									$	4,500,000

Data regarding the expected production of Mona Loa and Malaysian coffee are presented below.
	Mona Loa		Malaysian
Expected sales	70,000	pounds	1,500	pounds

Data regarding the expected activities used by Mona Loa and Malaysian coffees are presented below.
	Mona Loa		Malaysian
Batches	10	batches	4	batches
Setups	30	setups	12	setups
Purchase order size	5	orders	4	orders
Roasting time per 100 pounds	700	hours	15	hours
Blending time per 100 pounds	350	hours	7.5	hours
Packaging time per 100 pounds	70	hours	1.5	hours