Question

Ellie Mosk, CEO of X-Space Industries, decided to expand the company’s product offering beyond the core model rocket business. After investigation, she decided to set up a separate division to design and manufacture products for the drone market. Several companies were interested in having X-Space develop these drones, and financial results, to date, have been encouraging. Revenue was $4 million, gross margins have been running about 40%, and the customer sales and support costs were $1 million. However, there is a growing concern that some customers require a disproportionate share of the sales and support resources, and the true profitability of the customers is unknown. Data were collected to support an analysis of customer profitability:

Activity	Cost Driver	Total Cost
Sales visits	Sales visit days	$	482,000
Product modifications	Number of modifications		264,000
Phone calls	Number of minutes		92,400
E-mail/electronic communications	Number of communications		166,000
		$	1,004,400

Customer	Revenue				Gross Profit				Visit Days				Modifications				Phone Minutes				Electronic Communications
A	$	396,000			$	146,000				15				15				1,070				625
B		496,000				196,000				25				15				1,160				875
C		596,000				226,000				40				40				1,410				1,040
D		1,040,000				416,000				90				60				1,760				2,040
E		1,460,000				586,000				100				70				2,160				2,290
Totals	$	3,988,000			$	1,570,000				270				200				7,560				6,870

Required:

1. Management felt the easiest way to allocate the sales and support costs was based on the total revenue. Using total revenue as the allocation base, determine the profitability of each of the five customers.

2. Management felt that because the data revealed some customers require a disproportionate share of sales and support resources, activity-based costing should be used to determine customer profitability. Use ABC to prepare a customer profitability analysis.

Answer 1

Activity	Cost Driver	Total Cost	expected activity	cost driver rate (Total Cost / expected activity)
Sales visits	Sales visit days	482,000	270	1785.18519
Product modifications	Number of modifications	264,000	200	1320.000
Phone calls	Number of minutes	92,400	7,560	12.2222222
E-mail/electronic communications	Number of communications	166,000	6,870	24.1630277
		1,004,400

Customer	A	B	C	D	E	Total
Revenue	396,000	496,000	596,000	1,040,000	1,460,000	3,988,000
allocated overhead (1004400* sales of customer/3988000)	99,735	124,920	150,106	261,930	367,709	1004400
Gross Profit	146,000	196,000	226,000	416,000	586,000	1570000
Less: allocated overhead	99,735	124,920	150,106	261,930	367,709	1004400
customer profitability	46,265	71,080	75,894	154,070	218,291	565600

Answer 2

Customer	A	B	C	D	E	Total
Visit Days	15	25	40	90	100	270
Sales visits (1785.18519 * Visit Days)	26778	44630	71407	160667	178519	482000
Modifications	15	15	40	60	70	200
Product modifications (Modifications * 1320)	19800	19800	52800	79200	92400	264000
Phone Minutes	1,070	1,160	1,410	1,760	2,160	7560
Phone calls (Phone Minutes * 12.2222222)	13078	14178	17233	21511	26400	92400
Electronic Communications	625	875	1,040	2,040	2,290	6870
E-mail/electronic communications (24.1630277 * Electronic Communications)	15102	21143	25130	49293	55333	166000
Sales visits	26778	44630	71407	160667	178519	482000
Product modifications	19800	19800	52800	79200	92400	264000
Phone calls	13078	14178	17233	21511	26400	92400
E-mail/electronic communications	15102	21143	25130	49293	55333	166000
allocated overhead	74757	99750	166570	310670	352652	1004400
Gross Profit	146,000	196,000	226,000	416,000	586,000	1570000
Less: allocated overhead	74,757	99,750	166,570	310,670	352,652	1004400
customer profitability	71,243	96,250	59,430	105,330	233,348	565600