XYZ’s management team is particularly interested in identifying the margins earned and cost

1 1 1 1 MicrosoftInternetExplorer4 0 2 Document Not Specified 7.8 磅 Normal 0 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 analyze its market segments in order to plan how to respond to the upcoming changes.

Prepare an Excel model to analyze 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.