1. Develop a customer profitability analysis for Aeronautics Company that shows the sales, cost of goods sold, gross profit on sales, and each cost as a line item that can be assigned to each of the five customers.

The customer profibility analsysis should show column information for each customer along with a total column for the company.

Include the customer profitability ratio for each customer and the company. The customer profitability ratio should be shown in percentage terms taken out to 2 decimal places. Make sure you use cell references to make all your calculations.

2. Provide a detailed recommendation to management on what type of actions the company should take as a result of this analysis. In your recommendation you need to specifically identify the different customers, for example Customer 1,

from the results of the customer profitability analysis.

Solution:

Make sure you use cell references to make all calculations.

#1

Customer 1

Customer 2

Customer 3

Customer 4

Customer 5

Total

2. Provide a detailed recommendation to management on what type of actions the company should take as a result of this analysis. In your recommendation you need to specifically identify the different customers, for example Customer 1,

from the results of the customer profitability analysis.

Part 1 2 Aeronautics Company designs and manufactures electronic control systems for commercial airlines 3 Aeronautics Company does contract work for the two major aircraft makers and three other companies that make the narrow-body commercial jets 4 This is a very competitive field that Aeronautics Company operates in. 5 It is imperative they manage the non-manufacturing overhead costs effectively in order to achieve an acceptable net profit margin. 6 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. 7 You have been hired to conduct a customer profitability analysis. 8 9Below is applicable revenue and cost information you should include in your customer profitability analysis. 0 Sales 12 Customer 1 13 Customer 2 4 Customer 3 15 Customer 4 6 Customer 5 17 Cost of Good Sold (COGS) as a 18 ipercentage of sales is the following: 9 Aeronautics Company selling and customer support team receives the following sales commissions on each 20 customer account $3,000,000 12,000,000 3,000,000 5,000,000 2,000,000 $25,000,000 82% of Total Sales generated 4% Sales generated per customer 21 22 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 23 Cost per unit of activity $1.300 1,250 Activity 24 25 1. Sales Visits 26 2. Product adjustments 27 3. Phone and email contracts 28 4. Promotion and entertainment events Customer 5 Customer 1 Customer 2 Activity Cost Driver Data Customer 3 Customer 4 16 51 104 10 Number of visit days 106 130 52 34 Number of adjustments 23 36 10 6 Number of calls/contracts 150 220 354 180 138 Number of events 1,400 82 66 74 18 30 1 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 32 There is a cost of B3 34 Customer 1 35 Customer 2 36 Customer 3 37Customer 4 $900 hour Hours used of jet 24 36 5 6 Part 1 2 Aeronautics Company designs and manufactures electronic control systems for commercial airlines 3 Aeronautics Company does contract work for the two major aircraft makers and three other companies that make the narrow-body commercial jets 4 This is a very competitive field that Aeronautics Company operates in. 5 It is imperative they manage the non-manufacturing overhead costs effectively in order to achieve an acceptable net profit margin. 6 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. 7 You have been hired to conduct a customer profitability analysis. 8 9Below is applicable revenue and cost information you should include in your customer profitability analysis. 0 Sales 12 Customer 1 13 Customer 2 4 Customer 3 15 Customer 4 6 Customer 5 17 Cost of Good Sold (COGS) as a 18 ipercentage of sales is the following: 9 Aeronautics Company selling and customer support team receives the following sales commissions on each 20 customer account $3,000,000 12,000,000 3,000,000 5,000,000 2,000,000 $25,000,000 82% of Total Sales generated 4% Sales generated per customer 21 22 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 23 Cost per unit of activity $1.300 1,250 Activity 24 25 1. Sales Visits 26 2. Product adjustments 27 3. Phone and email contracts 28 4. Promotion and entertainment events Customer 5 Customer 1 Customer 2 Activity Cost Driver Data Customer 3 Customer 4 16 51 104 10 Number of visit days 106 130 52 34 Number of adjustments 23 36 10 6 Number of calls/contracts 150 220 354 180 138 Number of events 1,400 82 66 74 18 30 1 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 32 There is a cost of B3 34 Customer 1 35 Customer 2 36 Customer 3 37Customer 4 $900 hour Hours used of jet 24 36 5 6