*** Please show excel formulas, thanks ***

Babaloo leather company produces briefcases from leather, fabric, and synthetic materials in a single production department. The basic product is a standard briefcase made from leather and lined with fabric. Babaloo has a good reputation in the market because the standard briefcase is a high-quality item that has been produced for many years.

Last year, the company decided to expand its product line and produce specialty briefcases for special orders. These briefcases differ from the standard in that they vary in size, contain both leather and synthetic materials, and are imprinted with the buyers logo (the standard briefcase is simply imprinted with the Babaloo name in small letters). The decision to use some synthetic materials in the briefcase was made to hold down the materials cost. To reduce the labor costs per unit, most of the cutting and stitching on the specialty briefcases is done by automated machines, which are used to a much lesser degree in the production of the standard briefcases. Because of these changes in the design and production of the specialty briefcases, Babaloo management believed that they would cost less to produce than the standard briefcases. However, because they are specialty items, they were priced slightly higher; standards are priced at $30 and specialty briefcases at $32.

After reviewing last months results of operations, Babaloos president became concerned about the profitability of the two product lines because the standard briefcase showed a loss while the specialty briefcase showed a greater profit margin than expected. The president is wondering whether the company should drop the standard briefcase and focus entirely on specialty items. Units and cost data for last months operations as reported to the president are as follows:

Units produced	10,000.00	2,500.00
Direct materials
Leather (1 sq. yd. x $15.00; 112 sq. yd. x $15.00)	15.00	7.50
Fabric (1 sq. yd. x $5.00; 1 sq. yd. x $5.00)	5.00	5.00
Synthetic		5.00
Total materials	20.00	17.50
Direct labor (1/2 hr. x $12.00, 1/4 hr. x $12.00)	6.00	3.00
Manufacturing overhead (1/2 hr. x $8.98; 1/4 hr. x $8.98)	4.49	2.25
Cost per unit	30.49	22.75

Factory overhead is applied on the basis of direct labor hours. The rate of $8.98 per direct labor hour was calculated by dividing the total overhead ($50,500) by the direct labor hours (5,625). As shown in the table, the cost of a standard jacket is $0.49 higher than its $30 sales price; the specialty jacket has a cost of only $22.75, for a gross profit per unit of $9.25. The problem with these costs is that they do not accurately reflect the activities involved in manufacturing each product. Determining the costs using ABC should provide better product costing data to help gauge the actual profitability of each product line. The manufacturing overhead costs must be analyzed to determine the activities driving the costs. Assume that the following costs and cost drivers have been identified:

The Purchasing Department's cost is $6,000. The major activity driving these costs is the number of purchase orders processed. During the month, the Purchasing Department prepared the following number of purchase orders for the materials indicated:

Leather	20
Fabric	30
Synthetic Material	50

The cost of receiving and inspecting materials is $7,500. These costs are driven by the number of deliveries. During the month, the following number of deliveries were made:

Leather	30
Fabric	40
Synthetic Material	80

Production line setup cost is $10,000. Setup activities involve changing the machines to produce the different types of jackets. Each setup for production of the standard jackets requires one hour; each setup for specialty jackets requires two hours. Standard jackets are produced in batches of 200, and specialty jackets are produced in batches of 25. During the last month, there were 50 setups for the standard item and 100 setups for the specialty item.

The cost of inspecting finished goods is $8,000. All jackets are inspected to ensure that quality standards are met. However, the final inspection of standard jackets takes very little time because the employees identify and correct quality problems as they do the hand cutting and stitching. A survey of the personnel responsible for inspecting the final products showed that 150 hours were spent on standard jackets and 250 hours on specialty jackets during the month.

Equipment-related costs are $6,000. Equipment-related costs include repairs, depreciation, and utilities. Management has determined that a logical basis for assigning these costs to products is machine hours. A standard jacket requires 1/2 hour of machine time, and a specialty jacket requires two hours. Thus, during the last month, 5,000 hours of machine time relate to the standard line and 5,000 hours relate to the specialty line.

Plant-related costs are $13,000. These costs include property taxes, insurance, administration, and others. For the purpose of determining average unit costs, they are to be assigned to products using machine hours.

Required:

a. Using activity-based costing concepts, what overhead costs should be assigned to the two products?

b. What is the unit cost of each product using activity-based costing concepts?

c. Reevaluate the president's concern about the profitability of the two product lines.

d. Discuss the merits of activity-based management as it relates to Babaloo's ABC cost system. Please do not state the obvious. Use this example to identify the merits and compare it with the traditional system. How your decision would be affected in either case

*** Please show excel formulas, thanks ***