CUSTOMER PROFITABILITY ANALYSIS

XYZ has 5 customers that represent all of its $20,000,000 in sales. Your activity-based costing system can allocate all costs to key activities incurred in connection with serving your customers, except $2,000,000 of general and administrative expenses that include, for example, the CEO's salary and bonus.

With profit margins declining in recent years, XYZ's CEO is concerned that the cost of winning contracts and maintaining relationships with its top five clients may be getting out of control. You have been hired to carry out a profitability analysis of the 5 clients.

The first cost associated with clients is the 5% commission paid to sellers based on the total sales of each client.

The rest of the customer service expenses are related to the following five activities:

ACTIVITY	TOTAL COST	CAUSE OF COSTS	TOTAL ACTIVITY
Client visits	$350,000	# of visits	350 visits
Product adjustments due to customer requests	$96,000	# of adjustments	60 adjustments
Contact by email or phone	$52,000	# of contacts	520 contacts
Promotions events	$252,000	# of events	84 events
Use of corporate jet to visit customers	$61,000	# flight hours	61 flight hours

This is the information related to the sales of the 5 clients:

CLIENT	SALES	COGS
ABC, Inc.	$5,500,000	$4,180,000
WAAC, Corp.	4,000,000	3,040,000
Corporation TAC	3,900,000	2,964,000
XZS, Co.	3,500,000	2,660,000
AAA, LLC	3,100,000	2,356,000

REQUIRED: (Answer the following four questions)

1. Prepare a profitability analysis for the 5 clients in Excel showing gross profits minus all other expenses that can be directly assigned to the five clients.

2. Arrange the 5 clients from most profitable to least profitable. Explain whether customers with the highest sales (purchases) are always the most profitable.

3. What is the main benefit of preparing a customer profitability analysis?

4. Based on your results, what recommendations would you give to the CEO of XYZ?