AutoSaVe E [E3 COstilch Project Pt. 4 , Saved v tiistine Ostrich File Home Insert Page Layout Formulas Data Review View Help ,0 Search 15 Share 5' Comments '_' A t 5 v lie,-I L3" Arial 21 Wrap Text General v @ E ,6? $8 E 2 u o urn 9? p ; '3 v 7 , , (v Fill V . Paste B I U V :5 v v o (.11 ,go Conditional Format as Cell Insert Delete Format Sort 8( Find at Ideas v 6 7 ' Merge 8' center $ A ' - "\" Formatting v Table v Styles v v v v 0 Clear\" Filter v Select v Clipboard Isl Font I3 Alignment is Number Is Styles Cells Editing ideas A B47 v s x t/ fx v hi\"'l""lL F55FFFFFWFWF\" 21 22 Sales Customer 1 $2,000,000 Customer 2 11,000,000 Customer 3 3,000,000 Customer 4 4,000,000 Customer 5 2,000,000 $22,000,000 Cost of Good Sold (COGS) as a 7 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: 4% Sales generated per customer A B c i D E F c H I J K L A 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 eld that Aeronautics Company operates in. It is imperative they manage the non-manufacturing overhead costs effectively in order to achieve an acceptable net prot margin. With declining prot margins in recent years, the CEO has become concerned that the cost of obtaining contracts and maintaining relations with its ve customers may be getting out of hand, You have been hired to conduct a customer protability analysis. Below is applicable revenue and cost information you should include in your customer protability analysis. The accounting staff determined the additional selling and customer support expenses related to the following four activity cost pools and the cost per activity. 23 Usage of cost driver per customer a . . . . . Cost per unit 3 3 7 c 1 c | r: I g v 4 t l lnstructionsProject4 Part 1 Part 2 l (D E 4 r l E l El PTI ++ 100% ._. . - 6:02 PM I El n a 6' e A 0 \"X f\" a 10/31/2020 % l AutoSave On s C.Ostrich Project Pt. 4 - Saved cristine ostrich CO X File Home Insert Page Layout Formulas Data Review View Help 9 Search Share Comments Arial 11 ~ A A ab Wrap Text General Autosum AY O BIUVv VV Av Conditional Format as Cell Insert Delete Format Fill Paste Merge & Center $ ~ % 9 08 20 Sort & Find & Ideas Formatting Table Styles Clear Filter Select Clipboard Font Alignment Number Styles Cells Editing Ideas B47 X V fx A B C D E F G H K Aeronautics Company selling and customer support team receives the following sales 20 commissions on each customer account: 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. 23 Usage of cost driver per customer Activity Activity Cost Driver Data Cost per unit 24 of activity Customer 1 Customer 2 Customer 3 Customer 4 Customer 5 25 1. Sales Visits Number of visit days $1,200 106 130 52 34 16 26 2. Product adjustments Number of adjustments 1, 100 23 36 10 6 5 27 3. Phone and email contracts Number of calls/contracts 75 220 354 180 138 104 28 4. Promotion and entertainment events Number of events 1,500 82 66 74 18 10 29 30 31 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 $800 hour 33 Hours used of jet 34 Customer 1 10 35 Customer 2 30 36 Customer 3 37 Customer 4 38 Customer 5 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 profitability analysis 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 mal 43 InstructionsProject4 Part 1 |Part 2 + + 100% e W 6:02 PM 10/31/2020