Acctg331 Accounting Revenue and Cost Management

ACCTG 331 Accounting Revenue and Cost Management

XYZ Ltd is a New Zealand company that distributes pharmaceutical products through multiple channels. It buys a variety of over-the-counter drugs from pharmaceutical companies and resells them through supermarket chains, drug store chains, and independent chemists.

With the recent announcement that the Chemist Warehouse will be entering the marketplace XYZ Ltd is anticipating changes in the profitability of its market segments. Industry observers are anticipating that the opening of the Chemist Warehouse will lead to a bloodbath in the market and competitors will have to adapt or perish. Local pharmaceutical distributors are particularly concerned they will not be able to compete with Chemist Warehouse's pricing. Accordingly, XYZ Ltd wants to analyse its market segments in order to plan how to respond to the upcoming changes.

Prepare an Excel model to analyse market segment profitability using the following data.

	Supermarkets	Department chain stores	Chemists
Average sales per delivery	$92,500	$31,450	$6,000
Number of deliveries	2,470	960	8,570
Average cost of goods sold per delivery	$83,292	$28,302	$5,355
Rebate to customers: volume based	$205,628	$45,288	$5,142
Total number of orders	2650	1,000	9,500
Average number of cartons shipped per delivery	260	75	12
Customer order amendments	7,500	10,000	22,000

The following information is also available about the total costs and cost drivers for key activities.

Activity area	Cost driver	Total Cost $
Customer purchase order processing	Purchase order by customers	591,750
Customer store delivery	Number of deliveries	960,000
Cartons dispatched to customer stores	Number of cartons dispatched to customer stores	792,135
Customer service errors	Number of corrections	150,000
In-store promotions of XYZ brands	?	80,240

In the absence of driver information about in-store promotions, these costs are assigned based on percentage of sales.

Required

Prepare an excel-based model to analyze the profitability of XYZ Ltd.’s customer market segments. XYZ’s management team is particularly interested in identifying the margins earned (both gross and net) and cost to serve of each of its market segments.

Get instant help from 5000+ experts for