Hi,

I need to know to how determine unit sales, dollar sales and margin, and what data usually I need to have in order to calculate them?

The University of Melbourne Department of Computing and Information Systems Carlton Group Pharmacy Case Study Introduction Maynard Naes established Carlton Group Pharmacy in 1980, originally comprising one pharmacy store in Lygon Street Carlton. It has now grown to three stores, each located in different inner suburbs of Melbourne. The pharmacy sells a variety of products including prescription medications, beauty products, medical items for home use and health products. Each of these products is ordered and purchased centrally and distributed to the required location by the various suppliers. The stores are each opened from 8am until 9pm Monday through Saturday but only the original store opens on Sunday, from 9am until 6pm. Carlton Group Pharmacy has been very successful in recent years and is a highly profitable business. Maynard would like to expand the business further, by possibly adding a further two pharmacy stores and also by expanding the floor space in his existing pharmacies, opening all his pharmacies on Sundays and locating the most profitable products in prominent places in the stores. He is also considering introducing a loyalty program for his customers. However, in order to make these decisions he needs a better understanding of his products, sales and customers. He has heard from vendors that data warehousing and business analytics will provide him with the capability to make more informed decisions. You have been hired as a consultant to propose a data warehousing solution to Maynard. Two separate information systems are used within Carlton Group Pharmacy to support operations: the inventory system and the sales system. The inventory system is used to manage product inventory and order products from suppliers. Inventory management is the responsibility of Pearce Noj who has worked with Carlton Group Pharmacy for many years. Most reordering decisions are made automatically when stock levels reach predefined minimum levels, but Pearce also frequently uses intuition to make changes to these orders. The sales system is used to support sales and customer interactions. The group accountant, Milton Nomis, monitors sales figures. Milton has worked on a contract basis for Carlton Group Pharmacy for many years. He has a strong intuitive understanding of which products are the most profitable. Information from these systems is difficult to integrate and aggregate as they have different data formats and use different database management systems. The Insights Needed for DecisionMaking Decisions about expanding the business are made by Maynard, with input from Pearce and Milton. Although both Pearce and Milton have great experience and a strong \"gut feel\" about the business, Maynard wants evidence based on data to inform his decisionmaking. In particular, he needs insights about products, customers, store location and opening times. 1. Who are the key customers? The data warehouse should provide information about unit sales, dollar sales and margin for customers for various time periods (including year, month and quarter). It should also provide information about the suburbs (postcodes) in which the most profitable customers live. Note that customers move house occasionally and it is important to know the suburb in which they lived at the time of any particular sale. It should be possible to aggregate customer reports into age groups - 30 years and under, 3150, and over 50. [Cost = cost of the product on the most recent order before the sale] [Margin = Dollar sales - Cost] 2. Which products are the most profitable? The data warehouse should provide information about unit sales, dollar sales and margin for products for various time periods (including year, season and month). 3. Which store location is the most profitable? The data warehouse should provide information about unit sales, dollar sales and margin for each store location for each month of the previous year. 4. Which time periods are the most profitable? The data warehouse should provide information about the unit sales, dollar sales and margin for various time periods including Monthly, Quarterly, Yearly and Sundays. assume there are different location The Current Information Systems Data for the data warehouse will be sourced from two of the existing operational systems, the inventory system and the sales system. 1. The Inventory System The inventory system is a package based on an Access database and runs of a windowsbased computer. A 5digit product code is used to identify base level products. Product groups may also be defined. The system also supports product cost history. Examples of data in relevant Oracle tables are shown below. PRODUCT (sample data, incomplete) Code Description Group 12765 Aspirin Medical 12766 Shampoo Beauty 12767 Paracetamol Medical 12821 Vitamin C Health 14821 Antibiotics Prescription 14823 Conditioner Beauty 14827 Crutches Medical PRODUCT ORDER (sample data, incomplete) Code Description Date Quantity Cost per item 12765 Aspirin 19 Jan 2013 1000 $10 12765 Aspirin 24 Jan 2013 400 $10 12765 Aspirin 13 May 2013 2000 $12 14823 Conditioner 5 Mar 2013 100 $2 14823 Conditioner 7 July 2013 110 $3 14821 Antibiotics 4 Dec 2013 250 $12 14821 Antibiotics 5 Mar 2014 300 $12 14823 Conditioner 4 Apr 2014 120 $4 12767 Paracetamol 5 Jul 2013 400 $8 12767 Paracetamol 7 Dec 2013 450 $9 14827 Crutches 4 Apr 2014 3 $35 2. The Sales System The sales system is a package based on an SQLServer database also running on a windowsbased computer. The customer identifier includes both a customer number and a store. Sales order, Product and Customer data are included in the system. Examples of data in relevant SQLServer tables are shown below. CUSTOMER (sample data, incomplete) Customer ID Name Date of Birth Current Address Postcode C478Carl Brian Jones 10 Jan 1980 Lygon St., Carlton 3053 C567Carl Bill Nguyen 29 Mar 1982 Rathdowne St, Carlton 3053 C121Carl David Ong 14 May 1951 Main St., Fitzroy 3065 C478SthMelb Nancy Smith 12 Dec 1959 High Street, South Melbourne 3205 C128Will Tom White 17 Sep 1990 The Strand, Williamstown 3016 PRODUCT PRICE LIST (sample data, incomplete) Product ID Description Unit Price Valid until Date 12765 Aspirin $15 31 Dec 2014 14823 Conditioner $4 31 Dec 2014 14821 Antibiotics $20 31 Dec 2014 12767 Paracetamol $15 31 Dec 2014 14827 Crutches $70 31 Dec 2014 SALES ORDER (sample data, incomplete) Sales Order Customer ID Date Line Product Qty Sale Price S135 C478Carl 12/5/13 1 Aspirin 1 $15 S135 C478Carl 12/5/13 2 Conditioner 3 $5 S140 C478SthMelb 15/5/14 1 Crutches 1 $55 S168 C128Will 16/5/14 1 Antibiotics 2 $15 S168 C128Will 16/5/14 2 Paracetamol 2 $15 line= order inside the order. e,g: C478Carl ordered two items with the same Sales order