Question

400 Problem 3: Softtowel Ltd manufactures towels for department stores and residential hotels. The financial controller is concerned that the profits for last year were 15 per cent below the previous year and plans to calculate the profitability of the three main customer groups: department stores, small hotels, and large hotels. Last year the selling price and manufacturing costs for the three major products - bath sheets, bath towels and hand towels - and sales to customer groups, were as shown below. Bath sheets Bath towels Hand towels Selling price $40 $35 $20 Manufacturing cost $20 $15 $7 Sales units: Department stores 500 1,000 300 Small hotels 300 300 Large hotels 800 1,200 850 The customer-driven activities, cost per unit of activity driver, and activities for each customer group were as follows: Activities Cost per unit of activity driver Department stores Small hotels Large hotels Process sales order $80 per order 40 60 25 Deliver order $150 per delivery 60 60 28 Invoice customer $30 per invoice 38 60 30 Process payment $20 per payment 45 58 25 Handle complaint $40 per complaint 10 7 8 Investigate bad debt $50 per investigation 6 4 10 Visit customers $120 per visit 30 26 20 Advertise in hotel $50,000 (to be shared equally magazines between small and large hotels) Required: 1. Calculate the customer-driven costs for each customer group. 2. Prepare a profitability report in order to determine the contribution of the three customer groups to company profits. Within your report, include percentage contributed by each customer group to sales revenue, gross margin, and contribution to company profits. 3. Contrast the relative profitability of the three customer groups and provide suggestions for improving performance.

Answer 1

2020-09(2) SEP.xlsx - Microsoft Excel ? X FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW DEVELOPER Sign in N6 х fi A B D E F G K L M N 1 Answer 1 H Answer 2 Departmental Small hotels Large hotels stores Customer group : Total 2 3 4 800 5 Costs 3,200 9,000 1,140 900 400 500 40 $ 20,000 $ $ $ 40 400 40 $ 16,000 $ 6 32,000 Customer group :- Departmental stores Activities Cost driver rate Activity level Processing order $ 80 40 $ Deliver order $ 150 60 $ Invoice customer $ 30 38 $ Process payment $ 20 45 $ Handle complaint $ 40 10 $ Investigate bad debt $ 50 6 $ Visit customer $ 120 30 $ Advertise in magazines $ 50,000 $ Customer driven cost allocated to Departmental stores $ 7 8 Bath sheets Unit sold Multiply: Unit selling price Sales revenue of Bath sheets Bath towels Unit sold Multiply: Unit selling price Sales revenue of Bath towels Hand towels Unit sold Multiply: Unit selling price Sales revenue of Hand towels 9 300 3,600 $ $ 1,000 35 $ 35,000 $ 300 35 $ 10,500 $ 1,200 35 42,000 10 11 12 18,540 13 $ $ 300 20 $ 6,000 $ 300 850 20 $ 20 6,000 $ 17,000 14 15 16 17 Sales revenue of Bath sheets Sales revenue of Bath towels Sales revenue of Hand towels Total sales revenue Customer groups Small hotels Activities Cost driver rate Activity level Costs Processing order $ 80 60 $ 4,800 Deliver order $ 150 60 $ 9,000 Invoice customer $ 30 60 $ 1,800 Process payment $ 20 58 $ 1,160 Handle complaint $ 40 7 $ 280 Investigate bad debt $ 50 4 $ 200 Visit customer $ 120 26 $ 3,120 Advertise in magazines $ 50,000 1/2 $ 25,000 Customer driven cost allocated to Small hotels $ 45,360 $ $ $ $ 20,000 $ 35,000 $ 6,000 $ 61,000 $ 16,000 $ 10,500 $ 6,000 $ 32,500 $ 32,000 42,000 17,000 91,000 $ 18 19 184,500 20 21 22 800 23 500 20 $ 10,000 $ $ $ 400 20 $ 8,000 $ 20 24 16,000 Bath sheets Unit sold Multiply: Unit cost Cost of Sales - Bath sheets Bath towels Unit sold Multiplv: Unit cost Sheet19 Sheet20 Sheet21 25 26 300 1,200 27 Customer group : Activities ... Sheet13 Large hotels Cost driver rate Activity level Costs Sheet14 Sheet15 Sheet16 Sheet17 S 1,000 15 : S 15 S 15 Activate Windows Go to Settings to activate Windows Sheet18 READY + 100% i Search the web and Windows 0 ENG US 05:53 15-09-20

XIA 5 2020-09(2) SEP.xlsx - Microsoft Excel ? X FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW DEVELOPER Sign in N49 : Х f A D E F к L M N B с Customer driven cost allocated to Small hotels Н. 10,000 $ 24 $ 45,360 $ 8,000 $ 16,000 25 26 1,200 27 G Cost of Sales - Bath sheets Bath towels Unit sold Multiply: Unit cost Cost of Sales - Bath towels Hand towels Unit sold Multiply: Unit cost Cost of Sales - Hand towels 1,000 15 $ 15,000 $ $ $ 15 300 15 $ 4,500 $ 28 18,000 29 850 Customer group : Large hotels Activities Cost driver rate Activity level Costs Processing order $ 80 25 $ 2,000 Deliver order $ 150 28 $ 4,200 Invoice customer $ 30 30 $ 900 Process payment $ 20 25 $ 500 Handle complaint $ 40 8 $ 320 Investigate bad debt $ 50 10 $ 500 Visit customer $ 120 20 $ 2,400 Advertise in magazines $ 50,000 1/2 $ 25,000 Customer driven cost allocated to Large hotels $ 35,820 30 31 300 7 $ 2,100 $ $ $ 300 7 $ 2,100 $ 7 32 5,950 33 34 35 Cost of Sales - Bath sheets Cost of Sales - Bath towels Cost of Sales - Hand towels Total cost of sales $ $ $ $ 10,000 $ 15,000 $ 2,100 $ 27,100 $ 8,000 $ 16,000 4,500 $ 18,000 2,100 $ 5,950 14,600 $ 39,950 $ 36 37 81,650 38 Total 39 40 41 Customer group :- Answer 3 Main contrast of relative profit theory: It may possiable that operating Total sales revenue profit might not linked with business activity. No one can control the Less: Total cost of sales outside factors of business. For example, Rising in the raw matrerial Gross margin prices that not controlled by the manager. This resuls in the company may Less: Customer driven cost incurred loss. Contributed to profits Departmental Small hotels Large hotels stores $ 61,000 $ 32,500 $ 91,000 $ $ 27,100 $ 14,600 $ 39,950 $ $ 33,900 $ 17,900 $ 51,050 $ $ 18,540 $ 45,360$ 35,820 $ $ 15,360 $ (27,460) $ 15,230 $ 42 184,500 81,650 102,850 99,720 3,130 43 44 45 46 47 From the percentage analysis indiactes that small hotels have lower gross profit and sales contribution towards the total amounts. Large hotels includes the 50% sakes and gross margin towards business. Percentage of contribution for sales Total sales revenue $ Divided by: total $ Customer wise Sales (%) 48 49 50 61,000 $ 32,500 $ 91,000 184,500 $ 184,500 $ 184,500 33.06% 17.62% 49.32% Company needs to avoid the small hotels groups because their operated Sheet13 Sheet14 Sheet15 Sheet16 Sheet17 Sheet18 100.00% Activate Windows Go to Settings to activate Windows. Sheet19 Sheet20 Sheet21 READY 3 1 + 100% i Search the web and Windows O ENG US 05:53 15-09-20

XIA 5 2020-09(2) SEP.xlsx - Microsoft Excel ? X FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW DEVELOPER Sign in N61 : X foc A D F H K L M N B с C Customer driven cost allocated to Large hotels E 35,820 36 $ Cost of Sales - Hand towels Total cost of sales $ $ 2,100 $ 2,100 $ 5,950 27,100 $ 14,600 $ 39,950 $ 37 81,650 38 Total 39 40 41 Customer group: Answer 3 Main contrast of relative profit theory: It may possiable that operating Total sales revenue profit might not linked with business activity. No one can control the Less: Total cost of sales : outside factors of business. For example, Rising in the raw matrerial Gross margin prices that not controlled by the manager. This resuls in the company may Less: Customer driven cost incurred loss. Contributed to profits Departmental Small hotels Large hotels stores $ 61,000 $ 32,500 $ 91,000 $ $ 27,100 $ 14,600 $ 39,950 $ $ 33,900 $ 17,900 $ 51,050 $ $ 18,540$ 45,360 $ 35,820 $ $ 15,360 $ (27,460) $ 15,230 $ 42 184,500 81,650 102,850 99,720 3,130 43 44 45 46 47 From the percentage analysis indiactes that small hotels have lower gross profit and sales contribution towards the total amounts. Large hotels includes the 50% sakes and gross margin towards business. Percentage of contribution for sales Total sales revenue $ Divided by: total $ Customer wise Sales (%) 48 61,000 $ 32,500 $ 91,000 184,500 $ 184,500 $ 184,500 33.06% 17.62% 49.32% 49 100.00% 50 Company needs to avoid the small hotels groups because their operated at loss. 51 52 Percentage of contribution for gross margin Gross margin $ 33,900 $ 17,900 $ 51,050 Divided by: total $ 102,850 $ 102,850 $ 102,850 Customer wise gross margin (%) 32.96% 17.40% 49.64% 53 54 100.00% 55 56 57 Percentage of contribution for profits Contributed to profits $ Divided by: total $ Customer wise profits (%) 58 15,360 $ (27,460) $ 3,130 $ 3,130 $ 490.73% -877.32% 15,230 3,130 486.58% 59 100.00% 60 61 62 Activate Windows Go to Settings to activate Windows. Sheet13 Sheet14 Sheet15 Sheet16 Sheet17 Sheet18 Sheet19 Sheet20 Sheet21 + + READY + 100% i Search the web and Windows O ENG US 05:53 15-09-20