Question

Problem 5-49 Customer Profitability Analysis [LO 5-1, 5-6]

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	$	487,000
Product modifications	Number of modifications		271,000
Phone calls	Number of minutes		93,100
E-mail/electronic communications	Number of communications		173,000
		$	1,024,100

Customer	Revenue				Gross Profit				Visit Days				Modifications				Phone Minutes				Electronic Communications
A	$	401,000			$	151,000				15				15				1,140				625
B		501,000				201,000				25				15				1,230				875
C		601,000				231,000				40				40				1,480				1,110
D		1,110,000				421,000				90				60				1,830				2,110
E		1,510,000				591,000				100				70				2,230				2,360
Totals	$	4,123,000			$	1,595,000				270				200				7,910				7,080

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.

Problem 5-49 Customer Profitability Analysis [LO 5-1, 5-6]

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	$	487,000
Product modifications	Number of modifications		271,000
Phone calls	Number of minutes		93,100
E-mail/electronic communications	Number of communications		173,000
		$	1,024,100

Customer	Revenue				Gross Profit				Visit Days				Modifications				Phone Minutes				Electronic Communications
A	$	401,000			$	151,000				15				15				1,140				625
B		501,000				201,000				25				15				1,230				875
C		601,000				231,000				40				40				1,480				1,110
D		1,110,000				421,000				90				60				1,830				2,110
E		1,510,000				591,000				100				70				2,230				2,360
Totals	$	4,123,000			$	1,595,000				270				200				7,910				7,080

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.

Problem 5-49 Customer Profitability Analysis [LO 5-1, 5-6]

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	$	487,000
Product modifications	Number of modifications		271,000
Phone calls	Number of minutes		93,100
E-mail/electronic communications	Number of communications		173,000
		$	1,024,100

Customer	Revenue				Gross Profit				Visit Days				Modifications				Phone Minutes				Electronic Communications
A	$	401,000			$	151,000				15				15				1,140				625
B		501,000				201,000				25				15				1,230				875
C		601,000				231,000				40				40				1,480				1,110
D		1,110,000				421,000				90				60				1,830				2,110
E		1,510,000				591,000				100				70				2,230				2,360
Totals	$	4,123,000			$	1,595,000				270				200				7,910				7,080

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

Req 1.
Customer	Revenue	Gross profit	% Total Revenue	Total OH	Allocated OH	Net income
A	401000	151000	9.73%	1024100	99603	51397
B	501000	201000	12.15%	1024100	124442	76558
C	601,000	231,000	14.58%	1024100	149281	81719
D	1,110,000	421,000	26.92%	1024100	275710	145290
E	1,510,000	591,000	36.62%	1024100	375065	215935
Total	4,123,000	1,595,000	100.00%	1024100	1024100	570900
Req 2.
Activity		Driver		Total Cost	Total Driver		Rate
Sales visit		Visit days		487,000	270		1803.70
Product Modification		Modification		271,000	200		1355.00
Phone calls		Minutes		93100	7910		11.77
Email		Communication		173000	7080		24.44
Overheads allocation:
		Rate	Customer A		Customer B		Customer C		Customer D		Customer E
Activity			Activity	OH	Activity	OH	Activity	OH	Activity	OH	Activity	OH
Sales visit		1803.7	15	27055.5	25	45092.5	40	72148	90	162333	100	180370
Product Modification		1355	15	20325	15	20325	40	54200	60	81300	70	94850
Phone calls		11.77	1140	13417.8	1230	14477.1	1480	17419.6	1830	21539.1	2230	26247.1
Email		24.44	625	15275	875	21385	1110	27128.4	2110	51568.4	2360	57678.4
				76073		101280		170896		316741		359145
Customer	Revenue	Gross profit	Allocated Oh	Net income
A	401000	151000	76073	74927
B	501000	201000	101280	99720
C	601,000	231,000	170896	60104
D	1,110,000	421,000	316741	104259
E	1,510,000	591,000	359145	231855
Total	4,123,000	1,595,000