Question

This is a very competitive field that Aeronautics Company operates in.
It is imperative they manage the non-manufacturing overhead costs effectively in order to achieve an acceptable net profit margin.
With declining profit margins in recent years, the CEO has become concerned that the cost of obtaining contracts and maintaining relations with its five customers may be getting out of hand.
You have been hired to conduct a customer profitability analysis.
Below is applicable revenue and cost information you should include in your customer profitability analysis.
	Sales
Customer 1	$18,000,000
Customer 2	13,000,000
Customer 3	4,000,000
Customer 4	5,000,000
Customer 5	4,000,000
	$44,000,000
Cost of Good Sold (COGS) as a percentage of sales is the following:	80%	of Total Sales generated
Aeronautics Company selling and customer support team receives the following sales commissions on each customer account:	6%	Sales generated per customer
The accounting staff determined the additional selling and customer support expenses related to the following four activity cost pools and the cost per activity.
				Usage of cost driver per customer
Activity	Activity Cost Driver Data	Cost per unit of activity	Customer 1	Customer 2	Customer 3	Customer 4	Customer 5
1. Sales Visits	Number of visit days	$1,300	106	130	52	34	16
2. Product adjustments	Number of adjustments	1,250	23	36	10	6	5
3. Phone and email contracts	Number of calls/contracts	150	220	354	180	138	104
4. Promotion and entertainment events	Number of events	1,400	82	66	74	18	10
In addition to the above, the sales staff used the corporate jet for trips to customers at a cost per hour as stated below and jet hours used per customer as follows:
There is a cost of	$900	hour
	Hours used of jet
Customer 1	24
Customer 2	36
Customer 3	5
Customer 4	0
Customer 5	6
Required:
1. Develop a customer profitability analysis for Aeronautics Company that shows the sales, cost of goods sold, gross profit on sales, and all costs that can be assigned to the five customers.
Include the customer profitability ratio for each customer and the company. Make sure you use cell references to make all your calculations.
2. What type of actions might the company take as a result of this analysis? You need to specifically reference the different customers in the analysis you have performed in your answer to this question.
Solution:	Make sure you use cell references to make all your calculations.

Answer 1

Activity	Activity Cost Driver Data	Cost per unit of activity	Customer 1	Customer 2	Customer 3	Customer 4	Customer 5
1. Sales Visits	Number of visit days	1300	106	130	52	34	16
2. Product adjustments	Number of adjustments	1250	23	36	10	6	5
3. Phone and email contracts	Number of calls/contracts	150	220	354	180	138	104
4. Promotion and entertainment events	Number of events	1400	82	66	74	18	10
Cost = Cost per unit of activity * Activity of customer
Activity cost	Activity Cost Driver Data	Cost per unit of activity	Customer 1	Customer 2	Customer 3	Customer 4	Customer 5
1. Sales Visits	Number of visit days	1300	137,800	169,000	67,600	44,200	20,800
2. Product adjustments	Number of adjustments	1250	28,750	45,000	12,500	7,500	6,250
3. Phone and email contracts	Number of calls/contracts	150	33,000	53,100	27,000	20,700	15,600
4. Promotion and entertainment events	Number of events	1400	114,800	92,400	103,600	25,200	14,000
Total Activity cost			314,350	359,500	210,700	97,600	56,650
			314,350	359,500	210,700	97,600	56,650
In addition to the above, the sales staff used the corporate jet for trips to customers at a cost per hour as stated below and jet hours used per customer as follows:
There is a cost of	900	Hour
	Hours used of jet	Total cost of corporate jet for trips to customers (900 * Hours used of jet)
Customer 1	24	21,600
Customer 2	36	32,400
Customer 3	5	4,500
Customer 4	0	-
Customer 5	6	5,400
Profitability analysis	Sales	Cost of goods sold (Sales *80%)	Gross Profit
Customer 1	18,000,000	(14,400,000)	3,600,000
Customer 2	13,000,000	(10,400,000)	2,600,000
Customer 3	4,000,000	(3,200,000)	800,000
Customer 4	5,000,000	(4,000,000)	1,000,000
Customer 5	4,000,000	(3,200,000)	800,000
Profitability analysis	Sales commission selling and customer support team (Sales *6%)	Total cost of corporate jet for trips to customers	Activity cost	All cost assigned to customer
Customer 1	1,080,000	21,600	314,350	1,415,950
Customer 2	780,000	32,400	359,500	1,171,900
Customer 3	240,000	4,500	210,700	455,200
Customer 4	300,000	-	97,600	397,600
Customer 5	240,000	5,400	56,650	302,050
Profitability analysis	Gross Profit	All cost assigned to customer	Customer Profitability	Sales	Customer profitability ratio ( Customer Profitability / sales)
Customer 1	3,600,000	(1,415,950)	2,184,050	18,000,000	12.1336%
Customer 2	2,600,000	(1,171,900)	1,428,100	13,000,000	10.9854%
Customer 3	800,000	(455,200)	344,800	4,000,000	8.6200%
Customer 4	1,000,000	(397,600)	602,400	5,000,000	12.0480%
Customer 5	800,000	(302,050)	497,950	4,000,000	12.4488%
Customer profitability ratio of customer2 and 3 is lower Compare to other customer. So company charge extra price level compare to current price for those customers.