Assignment number: 1 Profitability and customer segmentation Use cell references in your formulas instead of copying or copying values. DO NOT round decimal Excel results to whole units in ALL EXCEL CALCULATIONS!!! Solved problems Bulk Corporation Inc. is a wholesaler of various brands of beverages whose average catalog selling and buying prices are shown in the following table. Liqueurs Wines Water Selling price (average) 250 CZK/bottle 130 CZK/bottle 12 CZK/bottle Purchase price (average) 150 CZK/bottle 90 CZK/bottle 10 CZK/bottle Number of bottles per pack 2 bottles per pack 2 bottles per pack 6 bottles per pack weight of 1 pack (kg) 1.00 kg 1.40 kg 9.00 kg Bulk a.s. delivers the beverages in gift packs in which its employees repackage the purchased bottles. "Since its inception, Bulk a.s. has operated as the exclusive supplier to Large Customer A. Only the year before it significantly expanded its capacity and started to supply other customers as well:" Customer Liqueurs Wines Water A "238,000 bottles" "125,000 bottles" "505,000 bottles" B "40,000 bottles" "32,000 bottles" "16,000 bottles" C "19,000 bottles" "22,000 bottles" "55,000 bottles" D "40,000 bottles" "80,000 bottles" "276,000 bottles" Others "50,000 bottles" "20,000 bottles" "150,000 bottles" Total "387,000 bottles" "279,000 bottles" "1,002,000 bottles" "The expansion was accompanied by a rapid increase in the company's total sales to almost double. However, the same trend did not occur in the efficiency of the business. " "The return on sales (ROS) fell from the original values of around 15% to 6% after the expansion (before deducting administrative, tax and finance costs, which we will ignore). " Sales (gross) " 145,044,000.00 CZK " 100% Discounts "-8,181,000.00 CZK " 6% Purchase prices - direct "-93,180,000.00 CZK " 64% Gross profit margin " 43,683,000.00 CZK " 30% Overhead - Handling unit "-12,000,000.00 CZK " 8% Overhead - Trading unit "-7,440,000.00 CZK " 5% Overhead - Distribution "-15,000,000.00 CZK " 10% Operating profit " 9,243,000.00 CZK " 6% "Bulk a.s. decided to introduce Activity Based Costing (ABC). The following table shows the key activities, RCD and ACD for the ""Handling"" unit: " Activities ACD RCD (= numer of empl.) Repackaging Number of repackaging 15 Storage kg of products repackaged 5 1. "Using the past year's data, assign a cost to one bottle of each commodity group using the ABC model outlined above. " "The activities of the 'Sales' and 'Distribution' departments cannot be identified well enough by commodity group, but only by customer. " The 'Trade' unit is represented by the single activity 'Customer Care'. "The 'Distribution' unit carries out two activities, namely 'Order administration' (estimated at one fifth of the unit's time pool) and 'Delivery' (the rest of the time pool). " Department: Trading Distribution Activity: Customer Care Order administration Delivery ACD: Number of customer visits per year Number of items on orders per year Annual kilometres travelled when delivering to customers Customer A 6 "6,000" "36,000" Customer B 220 "6,000" "97,500" Customer C 4 "2,000" "21,000" Customer D 360 "15,000" "106,500" Others 30 "1,000" "39,000" Total ACD 620 "30,000" "300,000" 2. "Calculate the profitability of each customer. Calculate the cost of serving customers (= cost-to-serve, MSDA) as a separate balance. " Discounts and rebates were only given to customer A. 3. Does the 80:20 rule apply to revenues and profits? Demonstrate graphically. 4. "Interpret the graph of the zero profit line (X-axis = cost-to-serve to sales, Y-axis = gross profit.margin after rebate to sales, size of the gross sales circle). " How does your figure explain the paradox felt by the management? 5. Discussion with customer D revealed that he was unaware of the difficulty of his service. He is willing to significantly increase his orders if he receives a 3% discount on the price of all goods purchased. "By increasing orders, Customer D would reduce all ACDs (number of store visits, number of items on orders, number of miles driven) by 40% of the current level." Should Bulk Corporation Inc. agree to such an arrangement? What should it take into account? What impacts can be REALLY expected in the short and long term? Separate task 6. "Refine the profitability calculation for EACH (incl. E, F, G, H) customer based on the additional information provided." "The new arrangement with Customer D (see Task 5) failed. At the same time, Bulk a.s. worked to refine its ABC model, identifying the following more detailed information:" (a) "(a) The ""Handling"" unit actually performs three different activities, whose RCD and ACD are shown in the following table: " Activities ACD RCD (= numer of employees) Supply number of purchases 4 Repackaging number of repackaging 11 Storage kg of repackaged products 5 (b) The Bulk a.s. information system records the following numbers of purchases of individual types of commodity groups per warehouse: Liqueurs Wines Water Total number of purchases per year "1,260 purchases" 940 purchases 210 purchases (c) "The ""Other"" customer group was too heterogeneous. It was therefore split down to individual customers. The following table shows the quantities taken by them:" Customer Liqueurs Wines Water Other - Customer E "23,000 bottles" "3,000 bottles" "3,000 bottles" Other - Customer F "7,000 bottles" "9,000 bottles" "24,000 bottles" Other - Customer G "11,000 bottles" "3,000 bottles" "45,000 bottles" Other - Customer H "9,000 bottles" "5,000 bottles" "78,000 bottles" Others total "50,000 bottles" "20,000 bottles" "150,000 bottles" (d) "Following the division of the ""Other"" customer group, cost drivers were also discussed in detail:" Activity: Customer Care Order administration Delivery ACD: Number of customer visits per year Number of items on orders per year Annual kilometres travelled when delivering to customers Ostatní - Ostatní - Zákazník E 7 295 "4,000" Ostatní - Ostatní - Zákazník F 6 200 "3,000" Ostatní - Ostatní - Zákazník G 4 250 "21,000" Ostatní - Ostatní - Zákazník H 13 255 "11,000" Ostatní - Celkem ACD 30 "1,000" "39,000" ##### Sheet/List 2 ##### Bulk a.s. Task 1 "Allocation of direct costs and ""Handling"" unit" Handling "12,000,000 CZK" Phase 1 ABC - Identification of indirect costs of activities Activity RCD Activity cost Notes Repackaging 15 employees "9,000,000 CZK" Storage 5 employees "3,000,000 CZK" 4.00 CZK Total 20 employees "12,000,000 CZK" rate = " 600,000.00 CZK " per one employee Phase 2 ABC - Determination of product costs Liqueurs Wines Water Activity Total ACD Average purchase price 150.00 CZK/bottle 90.00 CZK/bottle 10.00 CZK/bottle Repackaging 9.00 CZK/bottle 9.00 CZK/bottle 3.00 CZK/bottle "9,000,000 CZK" "500,000 packs" sazba= 18.00 CZK/pack Storage 0.79 CZK/bottle 1.11 CZK/bottle 2.38 CZK/bottle "3,000,000 CZK" "1,891,800" sazba= 1.586 CZK/kg Cost of products 159.79 CZK/bottle 100.11 CZK/bottle 15.38 CZK/bottle Auxiliary: number of repackagings "193,500 packs" "139,500 packs" "167,000 packs" "500,000 packs" weight of packs sold "193,500 kg" "195,300 kg" "1,503,000 kg" "1,891,800 kg" Task 2 "ABC allocation of ""Trade"" and ""Distribution"" costs to customers " Phase 1 of ABC - identification of indirect costs of activities Activity RCD Activity cost Rate for ACD ACD "Trading - ""Customer Care""" "7,440,000 CZK" "12,000 CZK" per 1 visit at the customer " Distribution - ""Order Management""" 20% "3,000,000 CZK" 100 CZK per 1 item " Distribution - ""Delivery""" 80% "12,000,000 CZK" 40 CZK per 1 km Distribution - TOTAL "15,000,000 CZK" Total cost of service (=cost of service) "22,440,000 CZK" Phase 2 ABC - Identification of customer costs Unused capacity Customer A Customer B Customer C Customer D Ostatní Total Customer D Sales "81,810,000 CZK" "14,352,000 CZK" "8,270,000 CZK" "23,712,000 Kč" "16,900,000 Kč" "145,044,000 CZK" "23,712,000 Kč" Cost of goods "58,310,703 CZK" "9,841,297 CZK" "6,084,314 Kč" "18,645,038 Kč" "12,298,649 Kč" "105,180,000 CZK" "18,645,038 Kč" Discounts and rabates "8,181,000 CZK" "8,181,000 CZK" "711,360 Kč" Profit margin after Handling and rabates "15,318,297 CZK" "4,510,703 CZK" "2,185,686 CZK" "5,066,962 CZK" "4,601,351 CZK" "31,683,000 CZK" "4,355,602 Kč" as a percentage of sales 18.7% 31.4% 26.4% 21.4% 27.2% 21.8% 18.4% Customer care "72,000 CZK" "2,640,000 CZK" "48,000 CZK" "4,320,000 CZK" "360,000 CZK" "7,440,000 CZK" "2,592,000 Kč" "1,728,000 Kč" Administration "600,000 CZK" "600,000 CZK" "200,000 CZK" "1,500,000 CZK" "100,000 CZK" "3,000,000 CZK" "900,000 Kč" "600,000 Kč" Distribution "1,440,000 CZK" "3,900,000 CZK" "840,000 CZK" "4,260,000 CZK" "1,560,000 CZK" "12,000,000 CZK" "2,556,000 Kč" "1,704,000 Kč" Cost-to-Serve "2,112,000 CZK" "7,140,000 CZK" "1,088,000 CZK" "10,080,000 CZK" "2,020,000 CZK" "22,440,000 CZK" "6,048,000 CZK" "4,032,000 CZK" as a percentage of sales 2.6% 49.7% 13.2% 42.5% 12.0% 15.5% 25.5% Profit margin "13,206,297 CZK" "-2,629,297 CZK" "1,097,686 CZK" "-5,013,038 CZK" "2,581,351 CZK" "9,243,000 CZK" "-1,692,398 Kč" as a percentage of sales 16.1% -18.3% 13.3% -21.1% 15.3% 6.4% -7.1% " 13,206,297 Kč " -2629296.543 1097686.013 -5013037.742 2581351.094 9243000 Task 3 80:20 Rule % Customers cumul % revenues cumul Customers GRADUALLY by sales Sales Relatively in % 0% 0% Customer A " 81,810,000 CZK " 56% 20% 56% Customer D " 23,712,000 CZK " 16% 40% 73% Other " 16,900,000 CZK " 12% 60% 84% Customer B " 14,352,000 CZK " 10% 80% 94% Customer C " 8,270,000 CZK " 6% 100% 100% Total enterprise " 145,044,000 CZK " 100% % Customers cumul % profit cumul Customers GRADUALLY by profit margin Profit margin Relatively in % 0% 0% Customer A " 13,206,297 CZK " 143% 20% 143% Others " 2,581,351 CZK " 28% 40% 171% Customer C " 1,097,686 CZK " 12% 60% 183% Customer B "-2,629,297 CZK " -28% 80% 154% Customer D "-5,013,038 CZK " -54% 100% 100% Total 9243000 100% Task 4 Zero profit straight line Customer A Customer B Customer C Customer D Others Total Cost-to-serve on sales 2.6% 49.7% 13.2% 42.5% 11.9% 15.5% Gross profit margin on sales 18.7% 31.4% 26.4% 21.4% 27.2% 21.8% Revenue "81,810,000 CZK" "14,352,000 CZK" "8,270,000 CZK" "23,712,000 CZK" "16,900,000 CZK" "145,044,000 CZK" ##### Sheet/List 3 ##### Bulk a.s. - separately Task 6 "Allocation of direct costs and ""Handling"" unit" "Phase 1 Allocation of direct costs and ""Handling"" unit" Activity RCD Activity cost Pozn. Supply Changing Storage Total Phase 2 ABC - Determination of product costs Liqueurs Wines Water Activity Total ACD Average purchase price Supply Repackaging Storage Cost of products "ABC allocation of ""Trade"" and ""Distribution"" costs to customers " Phase 1 ABC - Identification of indirect costs of activities Activity RCD Activity cost ACD rate "Business - ""Customer care""" " Distribution - ""Order Administration""" " Distribution - ""Distribution""" Distribution - TOTAL Total cost of service (=cost-to-serve) Phase 2 ABC - Identification of customer costs Quantification of customer profitability Customer A Customer B Customer C Customer D Other-E customer Other-F customer Other-G customer Other-H customer Total Revenue Cost of goods Discounts and rebates Profit margin after Handling and rebates as a percentage of sales Customer care Administration Distribution Cost-to-Serve as a percentage of sales Profit margin as a percentage of sales ##### Sheet/List 4 ##### Bulk a.s. Task 1 "Allocation of direct costs and ""Handling"" unit" Phase 1 ABC - Identification of indirect costs of activities Activity RCD Activity cost Notes Repackaging 15 employees "9,000,000 CZK" Storage 5 employees "3,000,000 CZK" 4.00 CZK Total 20 employees "12,000,000 CZK" rate = " 600,000.00 CZK " per one employee Phase 2 ABC - Determination of product costs Liqueurs Wines Water Activity Total ACD Average purchase price 150.00 CZK/bottle 90.00 CZK/bottle 10.00 CZK/bottle Repackaging 9.00 CZK/bottle 9.00 CZK/bottle 3.00 CZK/bottle "9,000,000 CZK" "500,000 packs" sazba= 18.00 CZK/pack Storage 0.79 CZK/bottle 1.11 CZK/bottle 2.38 CZK/bottle "3,000,000 CZK" "1,891,800" sazba= 1.586 CZK/kg Cost of products 159.79 CZK/bottle 100.11 CZK/bottle 15.38 CZK/bottle Auxiliary: number of repackagings "193,500 packs" "139,500 packs" "167,000 packs" "500,000 packs" weight of packs sold "193,500 kg" "195,300 kg" "1,503,000 kg" "1,891,800 kg" Task 2 "ABC allocation of ""Trade"" and ""Distribution"" costs to customers " Phase 1 of ABC - identification of indirect costs of activities Activity RCD Activity cost Rate for ACD ACD "Business - ""Customer Care""" "7,440,000 Kč" "12,000 Kč" Numer of visits at customer per year " Distribution - ""Order Management""" 20% "3,000,000 Kč" 100 Kč Number of items within orders " Distribution - ""Delivery""" 80% "12,000,000 Kč" 40 Kč Annual kilometres travelled for customer deliveries Distribution - TOTAL "15,000,000 Kč" Total cost of service (=cost of service) "22,440,000 Kč" Phase 2 ABC - Identification of customer costs Task 5 """Unused capacity""" Customer A Customer B Customer C Customer D Ostatní Total Customer D Sales "81,810,000 Kč" "14,352,000 Kč" "8,270,000 Kč" "23,712,000 Kč" "16,900,000 Kč" "145,044,000 Kč" "23,712,000 Kč" Cost of goods "58,310,703 Kč" "9,841,297 Kč" "6,084,314 Kč" "18,645,038 Kč" "12,298,649 Kč" "105,180,000 Kč" "18,645,038 Kč" tzn. rozvázány ruce oběma útvarům Discounts and rebates "8,181,000 Kč" "8,181,000 Kč" "711,360 Kč" Obchodu a Distribuce odhadovaná Uvolněná Profit margin after Handling and rebates "15,318,297 Kč" "4,510,703 Kč" "2,185,686 Kč" "5,066,962 Kč" "4,601,351 Kč" "31,683,000 Kč" "4,355,602 Kč" kapacita as a percentage of sales 18.7% 31.4% 26.4% 21.4% 27.2% 21.8% 18.4% VH se PRAVDĚPODOBNĚ krátkodobě propadne o slevu! Customer care "72,000 Kč" "2,640,000 Kč" "48,000 Kč" "4,320,000 Kč" "360,000 Kč" "7,440,000 Kč" "2,592,000 Kč" "1,728,000 Kč" Oba útvary si musejí 23% Administration "600,000 Kč" "600,000 Kč" "200,000 Kč" "1,500,000 Kč" "100,000 Kč" "3,000,000 Kč" "900,000 Kč" "600,000 Kč" najít nové zákazníky 20% Distribution "1,440,000 Kč" "3,900,000 Kč" "840,000 Kč" "4,260,000 Kč" "1,560,000 Kč" "12,000,000 Kč" "2,556,000 Kč" "1,704,000 Kč" jinak budou v dlouhém období redukovány 14% Cost-to-Serve "2,112,000 Kč" "7,140,000 Kč" "1,088,000 Kč" "10,080,000 Kč" "2,020,000 Kč" "22,440,000 Kč" "6,048,000 Kč" "4,032,000 Kč" "kapacity a propuštěni, majetek prodán" 18% as a percentage of sales 2.6% 49.7% 13.2% 42.5% 12.0% 15.5% 25.5% Profit margin "13,206,297 Kč" "-2,629,297 Kč" "1,097,686 Kč" "-5,013,038 Kč" "2,581,351 Kč" "9,243,000 Kč" "-1,692,398 Kč" "-4,032,000 Kč" as a percentage of sales 16.1% -18.3% 13.3% -21.1% 15.3% 6.4% -7.1% Task 3 80:20 Rule % Customers cumul % revenues cumul Customers GRADUALLY by sales Sales Relatively in % 0% 0% Customer A " 81,810,000 Kč " 56% 20% 56% Customer B " 23,712,000 Kč " 16% 40% 73% Customer C " 16,900,000 Kč " 12% 60% 84% Customer D " 14,352,000 Kč " 10% 80% 94% Other " 8,270,000 Kč " 6% 100% 100% Total enterprise " 145,044,000 Kč " 100% % Customers cumul % profit cumul Customers GRADUALLY by profit margin Profit margin Relatively in % 0% 0% Customer A " 13,206,297 Kč " 143% 20% 143% Customer B " 2,581,351 Kč " 28% 40% 171% Customer C " 1,097,686 Kč " 12% 60% 183% Customer D "-2,629,297 Kč " -28% 80% 154% Others "-5,013,038 Kč " -54% 100% 100% Total " 9,243,000 Kč " 100% Task 4 Zero profit straight line Customer A Customer B Customer C Customer D Others Total Cost-to-serve on sales 2.6% 49.7% 13.2% 42.5% 11.9% 15.5% Gross profit margin on sales 18.7% 31.4% 26.4% 21.4% 27.2% 21.8% Revenue "81,810,000 CZK" "14,352,000 CZK" "8,270,000 CZK" "23,712,000 CZK" "16,900,000 CZK" "145,044,000 CZK"