Question

Part 1
Aeronautics Company designs and manufactures electronic control systems for commercial airlines.
Aeronautics Company does contract work for the two major aircraft makers and three other companies that make the narrow-body commercial jets.
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

	Customer #1	Customer #2	Customer #3	Customer #4	Customer #5	Total
Sales	18000000	13000000	4000000	5000000	4000000	44000000
Less: COGS	14400000	10400000	3200000	4000000	3200000	35200000
Gross Profit	3600000	2600000	800000	1000000	800000	8800000
Less: Expenses
Sales Commission	1080000	780000	240000	300000	240000	2640000
Sales Visits	137800	169000	67600	44200	20800	439400
Product Adjustments	28750	45000	12500	7500	6250	100000
Phone and Emails	33000	53100	27000	20700	15600	149400
Promotions and Entertainment	114800	92400	103600	25200	14000	350000
Corporate Jet Expense	21600	32400	4500	0	5400	63900
Customer Profitability	2184050	1428100	344800	602400	497950	5057300
Return on Sales	12.13	10.99	8.62	12.05	12.45	11.49

Expense as % of Sales:

	Customer #1	Customer #2	Customer #3	Customer #4	Customer #5	Total
						% of Sales
Sales	100	100	100	100	100	100
COGS	80	80	80	80	80	80
Sales Commission	6	6	6	6	6	6
Sales Visits	0.77	1.30	1.69	0.88	0.52	1.00
Product Adjustments	0.16	0.35	0.31	0.15	0.16	0.23
Phone and Emails	0.18	0.41	0.68	0.41	0.39	0.34
Promotions and Entertainment	0.64	0.71	2.59	0.50	0.35	0.80
Corporate Jet Expense	0.12	0.25	0.11	0.00	0.14	0.15
Customer Profitability	12.13	10.98	8.62	12.04	12.45	11.49