|
|
| Line 40: |
Line 40: |
| | | | |
| | <div style="margin:0px; padding: 10px; background: #f2f4f4; font-family: Century Gothic, Open Sans, Arial, sans-serif; border-radius: 7px; text-align:left; font-size: 15px"> | | <div style="margin:0px; padding: 10px; background: #f2f4f4; font-family: Century Gothic, Open Sans, Arial, sans-serif; border-radius: 7px; text-align:left; font-size: 15px"> |
| − | The data provided by the sponsor is in Microsoft Excel format for each 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.
| |
| | | | |
| − | </div>
| + | The data provided by the sponsor is in Microsoft Excel format for each outlet by month. For now, they have provided the data for a total of 12 months from Dec 2016 to Nov 2017. The data that was given to us are Inventory Data, Monthly PLU (Programmable Logic Unit) and Sales Data. 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. Below is a short description of the each dataset: |
| − | | |
| − | <br/>
| |
| − | | |
| − | ==<div style="background: #ffffff; padding: 17px;padding:0.3em; letter-spacing:0.1em; line-height: 0.1em; text-indent: 10px; font-size:17px; text-transform:uppercase; font-weight: light; font-family: 'Century Gothic'; border-left:8px solid #1b96fe; margin-bottom:5px"><font color= #000000><strong>Metadata (Inventory)</strong></font></div>==
| |
| − | <div style="margin:0px; padding: 10px; background: #f2f4f4; font-family: Century Gothic, Open Sans, Arial, sans-serif; border-radius: 7px; text-align:left; font-size: 15px">
| |
| − | <span>For each month and every outlet, the sponsor uses an Excel file to record various information of the inventory. These include:</span>
| |
| | | | |
| | <table border=1> | | <table border=1> |
| − | <tr><th>Column Name</th><th>Description</th></tr> | + | <tr><th>Dataset Name</th><th>Dataset Description</th></tr> |
| − | <tr><td>Outlet</td><td>Outlet ordering the product</td></tr> | + | <tr><td>Inventory</td><td>Describes the inventory order for each outlet the data is updated daily.</td></tr> |
| − | <tr><td>Supplier</td><td>Supplier from which product is being ordered from</td></tr>
| + | <tr><td>Sales</td><td>Describes the sales for each outlet for each month daily.</td></tr> |
| − | <tr><td>Product Code</td><td>Product unique identifier</td></tr>
| + | <tr><td>MonthPLU</td><td>Describes the number of patrons for each outlet according to the type of meal daily. </td></tr> |
| − | <tr><td>Product Name</td><td>Name of product</td></tr>
| |
| − | <tr><td>Packaging</td><td>How product is packaged and sold. E.g. by KG, PKT or GRAMS</td></tr>
| |
| − | <tr><td>Case</td><td>Packet, Container or Box</td></tr> | |
| − | <tr><td>CaseBal</td><td>Balance of Cases</td></tr>
| |
| − | <tr><td>Unit</td><td>KG, PKT or GRAM</td></tr>
| |
| − | <tr><td>UnitBal</td><td>Balance of individual units</td></tr>
| |
| − | <tr><td>PriceByCash</td><td>Unit cost for product</td></tr>
| |
| − | <tr><td>LastMthValue</td><td>Dollar value of product from previous month quantity</td></tr>
| |
| − | <tr><td>LastMthCase</td><td>Dollar value of product from previous month quantity</td></tr> | |
| − | <tr><td>CaseOpnStk</td><td>Amount of product from previous month in terms of case</td></tr>
| |
| − | <tr><td>CasePurQTY</td><td>Amount of cases bought in current month</td></tr>
| |
| − | <tr><td>UnitBalCal</td><td>Combined balanced in terms of unit</td></tr>
| |
| − | <tr><td>CaseBalCombine</td><td>Combined quantity from both cases and individual units</td></tr>
| |
| − | <tr><td>CaseUsageQTY</td><td>Quantiy of cases used in month calculated by [CaseOpnStk+CasePurQTY-CaseBalCombine]</td></tr>
| |
| − | <tr><td>CaseUsageAmt</td><td>Amount used in dollars</td></tr>
| |
| − | <tr><td>AcctTerm</td><td>Category of product mainly for accounting purposes (Beverage, Food, etc)</td></tr>
| |
| − | <tr><td>Counter</td><td>Category of product (Vegetable, Sauce, Sushi)</td></tr>
| |
| − | <tr><td>Type</td><td>OutType of product (Vegetable, Sauce, Read-to-eat)let</td></tr>
| |
| − | <tr><td>CaseBalCombine$</td><td>CaseBalCombine in terms of dollars</td></tr>
| |
| | </table> | | </table> |
| | + | |
| | </div> | | </div> |
| | + | |
| | <br/> | | <br/> |
| | | | |
| − | ==<div style="background: #ffffff; padding: 17px;padding:0.3em; letter-spacing:0.1em; line-height: 0.1em; text-indent: 10px; font-size:17px; text-transform:uppercase; font-weight: light; font-family: 'Century Gothic'; border-left:8px solid #1b96fe; margin-bottom:5px"><font color= #000000><strong>Metadata (Sales)</strong></font></div>==
| |
| − | <div style="margin:0px; padding: 10px; background: #f2f4f4; font-family: Century Gothic, Open Sans, Arial, sans-serif; border-radius: 7px; text-align:left; font-size: 15px">
| |
| − | 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:
| |
| − | <table border=1>
| |
| − | <tr><th>Column Name</th><th>Description</th></tr>
| |
| − | <tr><td>Sales_dt</td><td>the sales date in format (dd/mm/yyyy)</td></tr>
| |
| − | <tr><td>Sales_day</td><td>the day of of the sales</td><tr>
| |
| − | <tr><td>Loc_id</td><td>the location ID</td></tr>
| |
| − | <tr><td>Loc_name</td><td>the location name</td></tr>
| |
| − | <tr><td>G_sales</td><td>gross sales</td><tr>
| |
| − | <tr><td>Discount</td><td>discount amount</td></tr>
| |
| − | <tr><td>Nett_sales</td><td>the total nett of sales (nett_sales = income - service - tax)</td></tr>
| |
| − | <tr><td>Service</td><td>the amount of service charge borne by the customer</td></tr>
| |
| − | <tr><td>Tax</td><td>the amount of goods & services tax paid by the customer</td></tr>
| |
| − | <tr><td>Income</td><td>the total amount of income collected (income = cash + cards + nets + voucher)</td></tr>
| |
| − | <tr><td>Cash</td><td>the amount of income from cash gained</td></tr>
| |
| − | <tr><td>Cards</td><td>the amount of income from cards gained</td></tr>
| |
| − | <tr><td>Nets</td><td>the amount of income from nets gained</td></tr>
| |
| − | <tr><td>Voucher</td><td>the amount of income from voucher gained</td></tr>
| |
| − | <tr><td>Pax</td><td>the number of people served</td></tr>
| |
| − | <tr><td>Tv_count</td><td>transaction void count</td></tr>
| |
| − | <tr><td>Tv_amt</td><td>transaction void amount</td></tr>
| |
| − | <tr><td>Country</td><td>the country of the sale</td></tr>
| |
| − | </table>
| |
| − | </div>
| |
| − | <br/>
| |
| − | ==<div style="background: #ffffff; padding: 17px;padding:0.3em; letter-spacing:0.1em; line-height: 0.1em; text-indent: 10px; font-size:17px; text-transform:uppercase; font-weight: light; font-family: 'Century Gothic'; border-left:8px solid #1b96fe; margin-bottom:5px"><font color= #000000><strong>Metadata (Monthly Programming logic unit)</strong></font></div>==
| |
| − | <div style="margin:0px; padding: 10px; background: #f2f4f4; font-family: Century Gothic, Open Sans, Arial, sans-serif; border-radius: 7px; text-align:left; font-size: 15px">
| |
| − | The monthly programming logic unit displays the number of people who patronize XYZ Company by month and by location, below is the metadata for this data:
| |
| − | <table border=1>
| |
| − | <tr><th>Column Name</th><th>Description</th></tr>
| |
| − | <tr><td>Sales_dt</td><td>sale</td></tr>
| |
| − | <tr><td>Outlet</td><td>the outlet number</td></tr>
| |
| − | <tr><td>Outletdesc</td><td>the name of the outlet</td></tr>
| |
| − | <tr><td>Transtype</td><td>Transaction Type (Dine-in, Takeaway, HouseAccount)</td></tr>
| |
| − | <tr><td>Group_id</td><td>grouping of type of PLU, setting product purposes</td></tr>
| |
| − | <tr><td>Dept_id</td><td>grouping PLU for POS interface</td></tr>
| |
| − | <tr><td>Shiftcode</td><td>the shift of the day that the transaction took place in</td></tr>
| |
| − | <tr><td>Plu</td><td>the lookup code for the transaction</td></tr>
| |
| − | <tr><td>Desc</td><td>the description of the transaction</td></tr>
| |
| − | <tr><td>Totalqty</td><td>the sum of the total of the type of transactions for the whole year</td></tr>
| |
| − | <tr><td>Unitprice</td><td>the price charged for one unit of a type of transaction</td></tr>
| |
| − | <tr><td>Totalamt</td><td>the product of totalqty and unitprice</td></tr>
| |
| − | <tr><td>Country</td><td>the country the outlet is located in</td></tr>
| |
| − | <tr><td>Concept</td><td>the type of restaurant concept</td></tr>
| |
| − | <tr><td>OutletName</td><td>the name of the outlet</td></tr>
| |
| − | <tr><td>Company</td><td>the parent company of the outlet</td></tr>
| |
| − | <tr><td>Shift</td><td>description of the shiftcode</td></tr>
| |
| − | <tr><td>Dept_Type</td><td>Department type</td></tr>
| |
| − | <tr><td>Dept_Type2</td><td>Product type</td></tr>
| |
| − | <tr><td>Dept_Type3</td><td>day type</td></tr>
| |
| − | <tr><td>Shift2</td><td>lunch / dinner shift</td></tr>
| |
| − | </table>
| |
| − | </div>
| |
| | <!-- End Body ---> | | <!-- End Body ---> |
