Exhibit 1, re-calculate with new numbers in the Customer Service Line, creating a new customer profit and % lines. Second, what should Johnson Beverage do? give some suggestions. Can you help them using Activity Based costing?

Johnson Beverage sell 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.3% 2.1% 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 Others $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% 3.5% List price is $15.20 per case Cost is $13.10 per case Discounts vary by customer (Exhibit 2 Customer information) Saver Oscars Superstore Odd Lots Price per Case $ 14.60 $ 14.90 Number of Cases 80,000 80,000 Number of Orders 16 40 Number of Deliveries 110 400 Miles per Delivery 5 19 Expedited Deliveries 10 250 Sales Visits 12 25 Downtown Retail $ 15.15 30,000 30 Midwellon $ 15.19 8,000 20 200 11 130 18 Others $ 15.06 602,000 394 3,540 Total JBI $ 15.00 800,000 500 4,480 10 2,500 360 230 44800 4 90 2,020 296 9 Customer Saver Superstore is not happy - they think they are paying too much Customer Service Cost Detail In addition to COGS Johnson has Customer Service costs of $1.2 million per year - like overhead See Table 1 Currently allocated based on % of revenue 800000 Cost $ 672,000 100,000 140,000 198,000 90,000 $ 1,200,000 Product Handling Taking Orders from Customers Delivering the Product Expediting Deliveries Sales visits Total 44800 They Run a report of Profitability by Customer See Exhibit 1 Can you help them using Activity Based costing? Johnson Beverage Problem - further information Johnson Beverage is trying to determine which of their customers create the best profit. Companies do this all the time. Many accounting systems in companies have a standard report called Customer Profitability Report. To do this, they have created on your Exhibit 1, a comparison of 4 of their customers. From that report you see that Midwellen is the most profitable. Here is the problem with that report. The 3 lines down to Gross Margin (Gross Profit) are fine. We know how much the revenue is by multiplying number of cases times their price, the cost of goods is just number of cases times $13.10, and the Gross Margin is the subtraction of revenue minus cost. But the next line is the problem. The customer service cost (THINK OF THIS AS OVERHEAD) is being allocated at 10% of the net revenue. Is that the best way to do that? KEEP IN MIND - THIS CUSTOMER SERVICE COST IS JUST FOR THE REPORT CALCULATION - THE CUSTOMERS ARE NOT BEING BILLED FOR THAT AMOUNT. Think of it like this. Two customers of Amazon. One buys products and pays, no problems, no returns. The other customer is always returning products, always calling and taking up a customer service person's time, just costing Amazon more money which results in less profit. This customer does not get billed for this extra customer service but is clearly a less profitable customer for Amazon. Your job is to allocate that customer service cost differently and see if it changes the profitability of customers. Table 1, page 1 lists the 5 customer service type costs, total $1,200,000. Exhibit 2 under the column Total for JBI, shows the number of each activity creating those costs. For example, Product Handling costs of $672,000 is based on a company total of 800,00 cases sold. Dividing $672,000 by 800,000 cases is $.84 per case. So Saver Superstore, who buy 80,000 cases would be allocated 80,000 times $.84 or $67,200 for that activity. Each of these 5 customer service costs has a cost per activity like that, and each customer will be allocated their amount based on their use of that activity. NOTE: Delivering the product is based on 2 things, number of deliveries and miles per delivery, or $140,000 divided by 44,800 (10 times 4,480) What I want is Exhibit 1, re-calculated with new numbers in the Customer Service Line, creating a new customer profit and % lines. DO THIS USING A SPREADSHEET PROGRAM LIKE EXCEL. SECOND, WHAT SHOULD JOHNSON BEVERAGE DO? GIVE SOME SUGGESTIONS. Johnson Beverage sell 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.3% 2.1% 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 Others $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% 3.5% List price is $15.20 per case Cost is $13.10 per case Discounts vary by customer (Exhibit 2 Customer information) Saver Oscars Superstore Odd Lots Price per Case $ 14.60 $ 14.90 Number of Cases 80,000 80,000 Number of Orders 16 40 Number of Deliveries 110 400 Miles per Delivery 5 19 Expedited Deliveries 10 250 Sales Visits 12 25 Downtown Retail $ 15.15 30,000 30 Midwellon $ 15.19 8,000 20 200 11 130 18 Others $ 15.06 602,000 394 3,540 Total JBI $ 15.00 800,000 500 4,480 10 2,500 360 230 44800 4 90 2,020 296 9 Customer Saver Superstore is not happy - they think they are paying too much Customer Service Cost Detail In addition to COGS Johnson has Customer Service costs of $1.2 million per year - like overhead See Table 1 Currently allocated based on % of revenue 800000 Cost $ 672,000 100,000 140,000 198,000 90,000 $ 1,200,000 Product Handling Taking Orders from Customers Delivering the Product Expediting Deliveries Sales visits Total 44800 They Run a report of Profitability by Customer See Exhibit 1 Can you help them using Activity Based costing? Johnson Beverage Problem - further information Johnson Beverage is trying to determine which of their customers create the best profit. Companies do this all the time. Many accounting systems in companies have a standard report called Customer Profitability Report. To do this, they have created on your Exhibit 1, a comparison of 4 of their customers. From that report you see that Midwellen is the most profitable. Here is the problem with that report. The 3 lines down to Gross Margin (Gross Profit) are fine. We know how much the revenue is by multiplying number of cases times their price, the cost of goods is just number of cases times $13.10, and the Gross Margin is the subtraction of revenue minus cost. But the next line is the problem. The customer service cost (THINK OF THIS AS OVERHEAD) is being allocated at 10% of the net revenue. Is that the best way to do that? KEEP IN MIND - THIS CUSTOMER SERVICE COST IS JUST FOR THE REPORT CALCULATION - THE CUSTOMERS ARE NOT BEING BILLED FOR THAT AMOUNT. Think of it like this. Two customers of Amazon. One buys products and pays, no problems, no returns. The other customer is always returning products, always calling and taking up a customer service person's time, just costing Amazon more money which results in less profit. This customer does not get billed for this extra customer service but is clearly a less profitable customer for Amazon. Your job is to allocate that customer service cost differently and see if it changes the profitability of customers. Table 1, page 1 lists the 5 customer service type costs, total $1,200,000. Exhibit 2 under the column Total for JBI, shows the number of each activity creating those costs. For example, Product Handling costs of $672,000 is based on a company total of 800,00 cases sold. Dividing $672,000 by 800,000 cases is $.84 per case. So Saver Superstore, who buy 80,000 cases would be allocated 80,000 times $.84 or $67,200 for that activity. Each of these 5 customer service costs has a cost per activity like that, and each customer will be allocated their amount based on their use of that activity. NOTE: Delivering the product is based on 2 things, number of deliveries and miles per delivery, or $140,000 divided by 44,800 (10 times 4,480) What I want is Exhibit 1, re-calculated with new numbers in the Customer Service Line, creating a new customer profit and % lines. DO THIS USING A SPREADSHEET PROGRAM LIKE EXCEL. SECOND, WHAT SHOULD JOHNSON BEVERAGE DO? GIVE SOME SUGGESTIONS