Looking for guidance on question two.

39 Required: 40 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. 41 The customer profibility analsysis should show column information for each customer along with a total column for the company. 42 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 43 44 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, 45 from the results of the customer profitability analysis. 46 Solution: Make sure you use cell references to make all calculations. 47 #1 Customer Customer 2 Customer 3 Customer 4 Customer 5 Total 48 Sales $3,000,000 $12,000,000 $3,000,000 $5,000,000 $2,000,000 $25,000,000 49 Cost of Goods Sold $2,460,000 $9,840,000 $2,460,000 $4, 100,000 $1,640,000 $20,500,000 50 Gross Profit $540,000 $2, 160,000 $540,000 $900,000 $360,000 $4,500,000 51 Less Expenses 52 Commission $120,000 $480,000 $120,000 $200,000 $80,000 $1,000,000 53 Sales Visits 137,800 169,000 67,600 44,200 20,800 439,400 54 Product Adjustments 28,750 45,000 12,500 7,500 6,250 100,000 55 Phone & Email Contracts 33,000 53, 100 27,000 20,700 15,600 149,400 56 Promotion & Entertainment Events 114,800 92.400 103,600 25,200 14,000 350,000 57 Corporate Jet 21,600 32,400 4,500 5,400 63,900 58 Total Expenses 455,950 871,900 335,200 297,600 142,050 2, 102,700 59 60 Net Profit $84,050 $1,288, 100 $204,800 $602,400 $217,950 $2,397,300 61 62 Customer Profitability Ratio 2.80% 10.73% 6.83% 12.05% 10.90% 9.59% 63 64 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, 65 from the results of the customer profitability analysis. 66 67 68 69 70 71 72 73 74 75