OOO AutoSave OFF Johnson Beverage Case fall 2020 Home Insert Draw Page Layout Formulas Data Review View Tell me Share 0 Comments Calibri (Body) 10.5 A A ' ' General Conditional Formatting Format as Table Insert v TX Delete 44 Y Paste Editing Ideas Sensitivity Cell Styles Format v 916 x fx A H H 1 J L M N N P D R S 5 7 8 Others B E F UNIT Devere en tu mewre Customers cu Lurerb Last year Revenue was $12 million - Sell Sport Drinks [Exhibit 1 Customer Profitability Saver Oscars Superstore Odd Lots Net Revenue $1,168,000 $1,192,000 Cost of Goods Sold 1,048,000 1,048,000 Gross Profit 120,000 144,000 Customer Service Costs 116,800 119,200 Customer Profit 3,200 24,800 Profit 0.3% 2.1% 9 10 11 12 Midwellon $121,520 104,800 16,720 12,152 4,568 3.8% Downtown Retail $454,500 393,000 61,500 45,450 16,050 3.5% $9,063,980 7,886,200 1,177,780 906,398 271,382 3.0% Total JBI $12,000,000 10,480,000 1,520,000 1,200,000 320,000 2.7% 13 Oscars Odd Lots $ Others $ Total JB $ 14 15 16 17 19 19 20 21 22 23 24 25 26 27 29 29 30 31 32 33 34 35 List price is $15.20 percase Cost is $13.10 per case Discounts vary by customer (Exhibit 2 Customer information! Saver Superstore Price per Case $ 14.60 Number of Cases 80,000 Number of Orders 16 Number of Deliveries 110 Miles per Delivery 5 Expedited Deliveries 10 Sales Visits 12 14.90 80,000 40 400 19 250 25 Midwellan $ 15.19 8,000 20 200 11 130 18 Downtown Retail $ 15.15 30,000 30 230 4 90 9 9 15.06 602,000 394 3,540 15.00 800,000 500 4,480 10 2,500 360 2,020 296 Customer Sever Superstore is not happy-they think they are paying too much Customer Service Cost Detail Cost In addition to COGS Johnson has Customer Service costs of $1.2 million per year-like overhead See Table 1 Currently allocated based on af revenue $ Product Handling Taking Orders from Customers Delivering the Product Expediting Deliveries Sales visits Total 672,000 100,000 140,000 198,000 90,000 1,200,000 They Run a report of Profitability by Customer See Exhibit 1 $ 36 37 38 39 40 41 Can you help them using Activity Based costing? Sheet1 Sheet2 Sheet3 + FEE W + 100% Calibri (Body) 10.5 A A General Il lil 101 Conditional Formatting Formatas Table Coll Styles Insert Delete Format B IU $ % ) $8-28 Editing le fx B D E F G H L M N 0 Others Total Johnson Beverage will to Retail store customers about 20 customers Last year Revenue was $12 million Sell Sport Drinks Exhibit 1 Customer Profitability Saver Oscars Superstore Odd Lots Net Revenue $1,168,000 $1,192,000 Cost of Goods Sold 1,048,000 1,048,000 Gross Profit 120,000 144,000 Customer Service Costs 116,800 119,200 Customer Profit 3,200 24,800 Profit 0.3N 2.15 Midwellon $121,520 104,800 16,720 12.152 4,568 3.3N Downtown Retail $454,500 393,000 61,500 45.450 16,050 3.5% $9,063,980 7,886,200 1,177,780 906,398 271,382 3.0N $12,000,000 10,480,000 1,520,000 1.200,000 320,000 2.7% Oscars Odd Lots $ List price is $15.20 percase Cost is $13.10 perc Discounts vary by customer Exhibit 2 Customer information) Saver Superstore Price per Case $ 14.60 Number of Cases 80,000 Number of Orders 16 Number of Deliveries 110 Miles per Delivery 5 Expedited Deliveries 10 Sales Visits 12 Others $ Tots $ 14.90 80,000 40 400 Midwellon $ 15.19 8,000 20 200 11 130 10 Downtown Retu $ 15.15 30,000 30 230 4 15.06 602.000 394 3,540 15.00 800,000 500 4480 90 250 25 2,020 296 10 2,500 360 9 Customer Saver Superstore is not happy they think they are paying too much Customer Service Com Detall In addition to COGS Johnson has Customer Service costs of $1.2 million per year like overhead See Table 1 Currently located based on of revenue Cost $ They Run a report of Profitability by Customer See Dhibiti Product Handling Taking Orders from Customers Delivering the Product Expediting Deliveries Sales visits Total 672.000 100,000 140,000 198,000 10,000 1,200,000 $ Can you help them using Activity Based costing! Sheett Sheet2 Sheet3 + OOO AutoSave OFF Johnson Beverage Case fall 2020 Home Insert Draw Page Layout Formulas Data Review View Tell me Share 0 Comments Calibri (Body) 10.5 A A ' ' General Conditional Formatting Format as Table Insert v TX Delete 44 Y Paste Editing Ideas Sensitivity Cell Styles Format v 916 x fx A H H 1 J L M N N P D R S 5 7 8 Others B E F UNIT Devere en tu mewre Customers cu Lurerb Last year Revenue was $12 million - Sell Sport Drinks [Exhibit 1 Customer Profitability Saver Oscars Superstore Odd Lots Net Revenue $1,168,000 $1,192,000 Cost of Goods Sold 1,048,000 1,048,000 Gross Profit 120,000 144,000 Customer Service Costs 116,800 119,200 Customer Profit 3,200 24,800 Profit 0.3% 2.1% 9 10 11 12 Midwellon $121,520 104,800 16,720 12,152 4,568 3.8% Downtown Retail $454,500 393,000 61,500 45,450 16,050 3.5% $9,063,980 7,886,200 1,177,780 906,398 271,382 3.0% Total JBI $12,000,000 10,480,000 1,520,000 1,200,000 320,000 2.7% 13 Oscars Odd Lots $ Others $ Total JB $ 14 15 16 17 19 19 20 21 22 23 24 25 26 27 29 29 30 31 32 33 34 35 List price is $15.20 percase Cost is $13.10 per case Discounts vary by customer (Exhibit 2 Customer information! Saver Superstore Price per Case $ 14.60 Number of Cases 80,000 Number of Orders 16 Number of Deliveries 110 Miles per Delivery 5 Expedited Deliveries 10 Sales Visits 12 14.90 80,000 40 400 19 250 25 Midwellan $ 15.19 8,000 20 200 11 130 18 Downtown Retail $ 15.15 30,000 30 230 4 90 9 9 15.06 602,000 394 3,540 15.00 800,000 500 4,480 10 2,500 360 2,020 296 Customer Sever Superstore is not happy-they think they are paying too much Customer Service Cost Detail Cost In addition to COGS Johnson has Customer Service costs of $1.2 million per year-like overhead See Table 1 Currently allocated based on af revenue $ Product Handling Taking Orders from Customers Delivering the Product Expediting Deliveries Sales visits Total 672,000 100,000 140,000 198,000 90,000 1,200,000 They Run a report of Profitability by Customer See Exhibit 1 $ 36 37 38 39 40 41 Can you help them using Activity Based costing? Sheet1 Sheet2 Sheet3 + FEE W + 100% Calibri (Body) 10.5 A A General Il lil 101 Conditional Formatting Formatas Table Coll Styles Insert Delete Format B IU $ % ) $8-28 Editing le fx B D E F G H L M N 0 Others Total Johnson Beverage will to Retail store customers about 20 customers Last year Revenue was $12 million Sell Sport Drinks Exhibit 1 Customer Profitability Saver Oscars Superstore Odd Lots Net Revenue $1,168,000 $1,192,000 Cost of Goods Sold 1,048,000 1,048,000 Gross Profit 120,000 144,000 Customer Service Costs 116,800 119,200 Customer Profit 3,200 24,800 Profit 0.3N 2.15 Midwellon $121,520 104,800 16,720 12.152 4,568 3.3N Downtown Retail $454,500 393,000 61,500 45.450 16,050 3.5% $9,063,980 7,886,200 1,177,780 906,398 271,382 3.0N $12,000,000 10,480,000 1,520,000 1.200,000 320,000 2.7% Oscars Odd Lots $ List price is $15.20 percase Cost is $13.10 perc Discounts vary by customer Exhibit 2 Customer information) Saver Superstore Price per Case $ 14.60 Number of Cases 80,000 Number of Orders 16 Number of Deliveries 110 Miles per Delivery 5 Expedited Deliveries 10 Sales Visits 12 Others $ Tots $ 14.90 80,000 40 400 Midwellon $ 15.19 8,000 20 200 11 130 10 Downtown Retu $ 15.15 30,000 30 230 4 15.06 602.000 394 3,540 15.00 800,000 500 4480 90 250 25 2,020 296 10 2,500 360 9 Customer Saver Superstore is not happy they think they are paying too much Customer Service Com Detall In addition to COGS Johnson has Customer Service costs of $1.2 million per year like overhead See Table 1 Currently located based on of revenue Cost $ They Run a report of Profitability by Customer See Dhibiti Product Handling Taking Orders from Customers Delivering the Product Expediting Deliveries Sales visits Total 672.000 100,000 140,000 198,000 10,000 1,200,000 $ Can you help them using Activity Based costing! Sheett Sheet2 Sheet3 +