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:

	Classic Margarita	Agave Sunrise	Brothers' Tommy
Forecast Sales (Units)	2,560	1,230	1,160
Selling price per unit	$80.00	$95.00	$82.00
Direct labour hours per unit (@ $35 per hour)	0.10 hours	0.15 hours	0.11 hours
Direct materials cost per unit	$35.00	$41.00	$38.00

The overhead costs for the month of January 2023 and related activity cost drivers were as follow:

OH Activity	OH Cost	Cost Driver	Amount of Cost Driver
			Classic Margarita	Agave Sunrise	Brothers' Tommy
Ingredient picking	$35,670	Number of batches	150	150	150
Ingredient packing	$28,400	Number of ingredients	15,360	9,840	8,120
Final box preparation	$19,300	Number of units	2,560	1,230	1,160
Inspection	$5,800	Inspection hours	128	115	200
Maintenance	$8,700	Maintenance hours	22	18	15

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.