ANLY482 AY2017-18 T2 Group10 Project Overview: Data
Contents
Data Overview
The data provided by Zingrill is in Microsoft Excel format for each Seoul Garden outlet by month. For now, they have provided the data for a total of 12 months from Dec 2016 to Nov 2017. One limitation is that the company has recently changed the format of the inventory data, and thus we would be working with 2 different formats of inventory data.
Metadata (Inventory)
For each month and every outlet, Zingrill uses an Excel file to record various information of the inventory. These include:
| Column Name | Description |
|---|---|
| Outlet | Outlet ordering the product |
| Supplier | Supplier from which product is being ordered from |
| Product Code | Product unique identifier |
| Product Name | Name of product |
| Packaging | How product is packaged and sold. E.g. by KG, PKT or GRAMS |
| Case | Packet, Container or Box |
| CaseBal | Balance of Cases |
| Unit | KG, PKT or GRAM |
| UnitBal | Balance of individual units |
| PriceByCash | Unit cost for product |
| LastMthValue | Dollar value of product from previous month quantity |
| LastMthCase | Dollar value of product from previous month quantity |
| CaseOpnStk | Amount of product from previous month in terms of case |
| CasePurQTY | Amount of cases bought in current month |
| UnitBalCal | Combined balanced in terms of unit |
| CaseBalCombine | Combined quantity from both cases and individual units |
| CaseUsageQTY | Quantiy of cases used in month calculated by [CaseOpnStk+CasePurQTY-CaseBalCombine] |
| CaseUsageAmt | Amount used in dollars |
| AcctTerm | Category of product mainly for accounting purposes (Beverage, Food, etc) |
| Counter | Category of product (Vegetable, Sauce, Sushi) |
| Type | OutType of product (Vegetable, Sauce, Read-to-eat)let |
| CaseBalCombine$ | CaseBalCombine in terms of dollars |
Metadata (Sales)
Sales, which is exclusive of tax, is calculated using the formula, "Sales = Net Sales + Service Charge". For their sales value, they have the following data:
| Column Name | Description |
|---|---|
| Sales_dt | the sales date in format (dd/mm/yyyy) |
| Sales_day | the day of of the sales |
| Loc_id | the location ID |
| Loc_name | the location name |
| G_sales | gross sales |
| Discount | discount amount |
| Nett_sales | the total nett of sales (nett_sales = income - service - tax) |
| Service | the amount of service charge borne by the customer |
| Tax | the amount of goods & services tax paid by the customer |
| Income | the total amount of income collected (income = cash + cards + nets + voucher) |
| Cash | the amount of income from cash gained |
| Cards | the amount of income from cards gained |
| Nets | the amount of income from nets gained |
| Voucher | the amount of income from voucher gained |
| Pax | the number of people served |
| Tv_count | transaction void count |
| Tv_amt | transaction void amount |
| Country | the country of the sale |
Metadata (Monthly Programming logic unit)
The monthly programming logic unit displays the number of people who patronize Seoul Garden by month and by location, below is the metadata for this data:
| Column Name | Description |
|---|---|
| Sales_dt | sale |
| Outlet | the outlet number |
| Outletdesc | the name of the outlet |
| Transtype | Transaction Type (Dine-in, Takeaway, HouseAccount) |
| Group_id | grouping of type of PLU, setting product purposes |
| Dept_id | grouping PLU for POS interface |
| Shiftcode | the shift of the day that the transaction took place in |
| Plu | the lookup code for the transaction |
| Desc | the description of the transaction |
| Totalqty | the sum of the total of the type of transactions for the whole year |
| Unitprice | the price charged for one unit of a type of transaction |
| Totalamt | the product of totalqty and unitprice |
| Country | the country the outlet is located in |
| Concept | the type of restaurant concept |
| OutletName | the name of the outlet |
| Company | the parent company of the outlet |
| Shift | description of the shiftcode |
| Dept_Type | Department type |
| Dept_Type2 | Product type |
| Dept_Type3 | day type |
| Shift2 | lunch / dinner shift |
