Whisky Brothers has recently launched a line of agave spirit cocktail kits. These kits are hand-packed at the firm's Adelaide plant using agave distilled by Whisky Brothers and other items secured through external suppliers.

 

Whisky Brothers currently allocates overhead costs to cocktail kits based on direct labour hours. The firm is, however, considering a switch to activity-based-costing (ABC). This is to support an enhanced focus on a cost-reflective product pricing. To facilitate the transition to ABC, the management accounting team has prepared the following preliminary information to support this transition for the month of January 2023:

  

Required:

• Using Excel and the current costing method of applying overhead based on direct labour hours, develop a spreadsheet to calculate for each kit the expected:
• Gross Profit per unit,
• Gross Profit margin ($GP/$Sales),
• Total Gross Profit per Model, and
• Total Firm Gross Profit.
• Using Excel and the overhead activity and cost data provided, conduct the same analysis ABC to allocate activity-based costs and again calculate for each kit the expected:
• Gross Profit per unit,
• Gross Profit margin ($GP/$Sales),
• Total Gross Profit per Model, and
• Total Firm Gross Profit.

 

Classic Margarita Forecast Sales (Units) Selling price per unit Direct labour hours per unit (@$35 per 0.10 hours hour) Direct materials cost per unit 2,560 $80.00 $35.00 Agave Sunrise 1,230 $95.00 0.15 hours $41.00 Brothers' Tommy 1,160 $82.00 0.11 hours $38.00 The overhead costs for the month of January 2023 and related activity cost drivers were as follow: