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 brings the best $\%$ profit for Johnson.

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\mathrm{.}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 $$\mathrm{.}84$ per case. So Saver Superstore, who buy $80,000$ cases would be allocated $80,000$ times $$\mathrm{.}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. I WANT ORIGINAL SUGGESTIONS!

Do it on excel