Question

Customer Profitability Analysis

   Rogers Aeronautics, LTD, is a British aeronautics subcontract company that designs and manufactures electronic control systems for commercial airlines. The vast majority of all commercial aircraft are manufactured by Boeing in the U.S. and Airbus in Europe; however, there is a relatively small group of companies that manufacture narrow-body commercial jets. Assume for this exercise that Rogers does contract work for the two major manufacturers plus three companies in the second tier.
Because competition is intense in the industry, Rogers has always operated on a fairly thin 20% gross profit margin; hence, it is crucial that it manage non-manufacturing overhead costs effectively in order to achieve an acceptable net profit margin. With declining profit margins in recent years, Rogers Aeronautics' CEO, Len Rogers, has become concerned that the cost of obtaining contracts and maintaining relations with its five major customers may be getting out of hand. You have been hired to conduct a customer profitability analysis.
Rogers Aeronautics' non-manufacturing overhead consists of $2.5 million of general and administrative (G&A) expense, (including, among other expenses, the CEO's salary and bonus and the cost of operating the company's corporate jet) and selling and customer support expenses of $3 million (including 5% sales commissions and $1,050,000 of additional costs). The accounting staff determined that the $1,050,000 of additional selling and customer support expenses related to the following four activity cost pools:

Activity	Activity Cost Driver	Cost per Unit of Activity
1. Sales visits	Number of visit days	$1,200
2. Product adjustments	Number of adjustments	1,500
3. Phone and email contacts	Number of calls/contacts	150
4. Promotion and entertainment events	Number of events	1,500

Financial and activity data on the five customers follows (Sales and Gross Profit data in millions):

			Quantity of Sales and Support Activity
Customer	Sales	Gross Profit	Activity 1	Activity 2	Activity 3	Activity 4
#1	$20.00	$4.00	106	23	220	82
#2	15.00	3.00	130	36	354	66
#3	6.00	1.20	52	10	180	74
#4	7.00	1.40	34	6	138	18
#5	6.00	1.20	16	5	104	10
	$54.00	$10.80	338	80	996	250

In addition to the above, the sales staff used the corporate jet at a cost of $800 per hour for trips to customers as follows:

Customer #1	24 hours
Customer #2	36 hours
Customer #3	5 hours
Customer #4	0 hours
Customer #5	6 hours

The total cost of operating the airplane is included in general and administrative expense; none is included in selling and customer support costs.

a. Prepare a customer profitability analysis for Rogers Aeronautics that shows the gross profits less all expenses that can reasonably be assigned to the five customers.
Enter figures as complete numbers (with all zeros). For example, 1 million is 1,000,000. Do not use negative signs with any answers.
Round return on sales to one decimal place. (Ex: 10.4%)

	Customer #1	Customer #2	Customer #3	Customer #4	Customer #5
Sales	Answer	Answer	Answer	Answer	Answer
Cost of goods sold	Answer	Answer	Answer	Answer	Answer
Gross profit	Answer	Answer	Answer	Answer	Answer
Less expenses
Sales commissions	Answer	Answer	Answer	Answer	Answer
Sales visits	Answer	Answer	Answer	Answer	Answer
Product adjustments	Answer	Answer	Answer	Answer	Answer
Phone and other remote contacts	Answer	Answer	Answer	Answer	Answer
Promotion and entertainment	Answer	Answer	Answer	Answer	Answer
Corporate jet expense	Answer	Answer	Answer	Answer	Answer
Customer profitability	Answer	Answer	Answer	Answer	Answer
Customer return on sales	Answer	Answer	Answer	Answer	Answer

b. Now assuming that the remaining general and administrative costs are assigned to the five customers based on relative sales dollars, calculate net profit for each customer.
Enter figures as complete numbers (with all zeros). For example, 1 million is 1,000,000.
Do not use negative signs with any answers.
Do not round during calculation G&A expenses. Round final G&A expenses to the nearest whole number.
Round return on sales to one decimal place. (Ex: 10.4

	Customer #1	Customer #2	Customer #3	Customer #4	Customer #5
Customer profitability	Answer	Answer	Answer	Answer	Answer
Less G & A expense	Answer	Answer	Answer	Answer	Answer
Net customer profitability	Answer	Answer	Answer	Answer	Answer
Net customer return on sales	Answer	Answer	Answer	Answer	Answer

Answer 1

• a. Prepare a customer profitability analysis for Rogers Aeronautics that shows the gross profits less all expenses that can reasonably be assigned to the five customers.

  Figures in $
  	Customer 1	Customer 2	Customer 3	Customer 4	Customer 5	Total
  Sales	20,000,000	15,000,000	6,000,000	7,000,000	6,000,000	54,000,000
  Cost of goods sold	16,000,000	12,000,000	4,800,000	5,600,000	4,800,000	43,200,000
  Gross profit	4,000,000	3,000,000	1,200,000	1,400,000	1,200,000	10,800,000
  Less expenses						-
  Sales commissions 5% of sales	1,000,000	750,000	300,000	350,000	300,000	2,700,000
  Sales visits	127,200	156,000	62,400	40,800	19,200	405,600
  Product adjustments	34,500	54,000	15,000	9,000	7,500	120,000
  Phone and other remote contacts	33,000	53,100	27,000	20,700	15,600	149,400
  Promotion and entertainment	123,000	99,000	111,000	27,000	15,000	375,000
  Corporate jet expense @$800 per hour	19,200	28,800	4,000	-	4,800	56,800
  Total Expenses	1,336,900	1,140,900	519,400	447,500	362,100	3,806,800
  Customer profitability	2,663,100	1,859,100	680,600	952,500	837,900	6,993,200
  Customer return on sales % (Profit/sales*100)	13.3	12.4	11.3	13.6	14.0

  Allocatiion of Sales and Support Activity to customer

  	Customer 1	Customer 2	Customer 3	Customer 4	Customer 5	Total$
  1. Sales visits	127200	156000	62400	40800	19200	405600
  	(106*$1200)	(130*$1200)	(52*$1200)	(34*$1200)	(16*$1200)
  2. Product adjustments	34500	54000	15000	9000	7500	120000
  	(23*$1500)	(36*$1500)	(10*$1500)	(6*$1500)	(5*$1500)
  3. Phone and email contacts	33000	53100	27000	20700	15600	149400
  	(220*$150)	(354*$150)	(180*$150)	(138*$150)	(104*$150)
  4. Promotion and entertainment events	123000	99000	111000	27000	15000	375000
  	(82*$1500)	(66*$1500)	(74*$1500)	(18*$1500)	(10*$1500)
  Total						1050000

  b. Now assuming that the remaining general and administrative costs are assigned to the five customers based on relative sales dollars, calculate net profit for each customer

  	Customer 1	Customer 2	Customer 3	Customer 4	Customer 5	Total
  Customer profitability	2,663,100	1,859,100	680,600	952,500	837,900	6,993,200
  Less G & A expense (in the ration of sales) (2,500,000-56,800 = 2,443,200)	904,889	678,667	271,467	316,711	271,467	2,443,200
  Net customer profitability	1,758,211	1,180,433	409,133	635,789	566,433	4,550,000
  Net customer return on sales %	8.8	7.9	6.8	9.1	9.4	8.4