Question

P 5.49 Customer Profitability analysis Ellie Mosk, CEO of X-Space Industries, decided to expand the company's product offering beyond the core model rocket business. After investigation, she decided to set up a separate division to design and manufacture products for the drone market. Several companies were interested in having X-Space develop these drones, and financial results, to date, have been encouraging. Revenue was $4 million, gross margins have been running about 40%, and the customer sales and support costs were $1 million. However, there is a growing concem that some customers require a disproportionate share of the sales and support resources, and the true profitability of the customers is unknown. Data were collected to support an analysis of customer profitability: Activity Cost Driver Total Cost Sales Visits Sales visit days S 486.000,00 Product Modifications Number of modifications s 260.000,00 Phone calls Number of Minutes S 92.000,00 e-mail electronic communicatio Number of Communications S 162.000,00 S 1.000.000.00

Answer 1

XIA 5 2020-09(2) SEP.xlsx - Microsoft Excel ? X FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW DEVELOPER Sign in H28 : X А B C D E F G H K L M N 3 Answer 1 4 A B Total $4,000,000 5 6 Customer Revenue Divided by: total sales % of total sales Multiply: Total selling expenses Allocated of selling expenses $400,000 $4,000,000 10% $1,000,000 $100,000 с $600,000 $4,000,000 15% $1,000,000 $150,000 $500,000 $4,000,000 13% $1,000,000 $125,000 D $1,000,000 $4,000,000 25% $1,000,000 $250,000 7 8 E $1,500,000 $4,000,000 38% $1,000,000 $375,000 9 $1,000,000 10 11 Total $1,590,000 12 Customer Gross profit Less: Allocated of selling exp. Customer margin A $150,000 $100,000 $50,000 B $200,000 $125,000 $75,000 с $230,000 $150,000 $80,000 D $420,000 $250,000 $170,000 E $590,000 $375,000 $215,000 13 14 $590,000 15 16 17 18 Answer 2 Divided by: Activity Costs Activity Level Cost driver rate $486,000 270 $1,800.00 $260,000 200 $1,300.00 $92,000 7,360 $12.50 $162,000 6,750 $24.00 Activity Centers Sales visits Products modifications Phone calls Email 19 20 21 22 23 24 25 26 Model Activity Centers Sales visits Products modifications Phone calls Email Allocated of selling exp. ... Sheet114 Sheet115 CIRCULAR REFERENCES Customer A Cost driver rate Multiply: Activity Allocated cost $1,800.00 15 27,000 $1,300.00 15 19,500 $12.50 1,030 12,875 $24.00 625 15,000 74.375 Sheet116 Sheet117 Sheet118 Sheet119 Sheet120 27 28 29 Activate Windows Go to Settings to activate Windows. . Sheet121 READY + + 100% Search the web and Windows O ENG US 09:33 21-09-20

XIA 5 2020-09(2) SEP.xlsx - Microsoft Excel ? X FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW DEVELOPER Sign in H56 X fi А B с D E F H K L M N 30 31 32 33 34 35 Model Activity Centers Sales visits Products modifications Phone calls Email Allocated of selling exp. Customer B Cost driver rate Multiply: Activity Allocated cost $1,800.00 25 45,000 $1,300.00 15 19,500 $12.50 1,120 14,000 $24.00 875 21,000 99,500 36 37 38 39 40 41 42 Model Activity Centers Sales visits Products modifications Phone calls Email Allocated of selling exp. Customer C Cost driver rate Multiply: Activity Allocated cost $1,800.00 40 72,000 $1,300.00 40 52,000 $12.50 1,370 17,125 $24.00 1,000 24,000 165,125 43 44 45 46 47 48 49 50 Model Activity Centers Sales visits Products modifications Phone calls Email Allocated of selling exp. Customer D Cost driver rate Multiply: Activity Allocated cost $1,800.00 90 162,000 $1,300.00 60 78,000 $12.50 1,720 21,500 $24.00 2,000 48,000 309,500 51 52 53 54 55 56 57 Model Activity Centers Sales visits ... Sheet114 Sheet115 CIRCULAR REFERENCES Customer E Cost driver rate Multiply: Activity Allocated cost $1.800.00 100 180.000 Sheet116 Sheet117 Sheet118 Sheet119 Sheet120 Activate Windows Go to Settings to activate Windows. . Sheet121 READY + + 100% 09:33 Search the web and Windows 0 ENG US 21-09-20

XIA 5 2020-09(2) SEP.xlsx - Microsoft Excel ? X FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW DEVELOPER Sign in H79 X fi А B с D E F H K L M N 53 Allocated of selling exp. 309,500 54 55 56 57 58 Model Activity Centers Sales visits Products modifications Phone calls Email Allocated of selling exp. Customer E Cost driver rate Multiply: Activity Allocated cost $1,800.00 100 180,000 $1,300.00 70 91,000 $12.50 2,120 26,500 $24.00 2,250 54,000 351,500 59 60 61 62 63 64 65 Total $1,590,000 Customer Gross profit Less: Allocated of selling exp. Customer margin Answer 2 B $200,000 $99,500 $100,500 А $150,000 $74,375 $75,625 с $230,000 $165,125 $64,875 D $420,000 $309,500 $110,500 E $590,000 $351,500 $238,500 66 67 $590,000 68 69 70 $170,000 Results from Answer 1 Results from Answer 2 Increase (Decrease) in results 71 Answer 3 $75,000 $100,500 -$25,500 $50,000 $75,625 -$25,625 $80,000 $64,875 $15,125 $110,500 $59,500 $215,000 $238,500 -$23,500 72 73 74 75 Management should be use ABC techniques to determine the pricing of products. ABC method (Results from Answer 2) is provide the accurate results than traditional costing approach. ABC method helps to understood the cost behaviour of each cost drivers. Management can be implement the ABC method to measure the performance. 76 77 78 79 80 Activate Windows Go to Settings to activate Windows. . Sheet114 Sheet115 Sheet116 Sheet117 Sheet118 Sheet119 Sheet120 Sheet121 READY CIRCULAR REFERENCES + + 100% 09:33 Search the web and Windows O ENG US 21-09-20