ANLY482 AY2017-18T2 Group03 Project Overview
| HOME | ABOUT US | PROJECT OVERVIEW | DATA ANALYSIS | PROJECT MANAGEMENT | DOCUMENTATION | MAIN PAGE |
| Previous | Current |
|---|
Contents
Motivation
The company has stayed ahead of their competition by embracing technology in the early days and now, they are starting to dabble into analytics in order to help make use of the large amount of data that they have generated throughout the course of their business. Currently, these information is being stored in their proprietary Warehouse Management System (WMS) which captures transaction information whenever goods are moving in, out or within the warehouse.
The amount of information being managed by the system is massive and it is very complicated, thus managers have to spend time to use pivot tables in excel to better visualize the information being generated in order to get a better view of how the current operations is being run. This uses up valuable time which could have been used for other more important operational matters.
One important information that is very useful to a warehouse manager is an ABC analysis which is an inventory categorization technique. The purpose of this analysis is to classify the inventory into 3 categories. One possible way of doing categorization is shown below.
- ‘A’ items – 20% of the items accounts for 70% of the annual consumption value of the items.
- ‘B’ items - 30% of the items accounts for 25% of the annual consumption value of the items.
- ‘C’ items - 50% of the items accounts for 5% of the annual consumption value of the items.
Not only that, in order to provide even more better insight for their customers, they would also like to be able to have a better view of the seasonality patterns of the products, as well as an affinity analysis of the products to further improve the way the products can be organized in the warehouse to increase the efficiency of the warehouse operations.
Objective and Goals
The main objectives of the project would be to do the following:
1. Create a Operations Dashboard to visualize the following information
- Inbound / Outbound Volume Time Series Chart
- The purpose of this chart is to visualize the operations performance at various time slices so as to allow operations to have a clear view of the inventory movement patterns for the various SKUs
- X-axis: Time unit (day, week, month, quarter, year)
- Y-axis: No. of units of product
- Lines: Each line would symbolize the trend for a different SKU.
- Product Ranking Chart
- The purpose of this chart is to find out which are the best-selling items and which are the least popular items.
- Treemap view of the products filtered by month / year
- Product Seasonality Chart
- The purpose of this chart is to see the seasonality factors of the different products.
- X-axis: Month of the year
- Y-axis: Number of units of product
- Lines: Each line would symbolize the trend for a different SKU.
2. Conduct Associative Rule learning so as to attempt to see if there are affinities within the products within the warehouse so that inventory can be better organized within the warehouse.
Provided Data
The data provided by our sponsor are obtained from their proprietary Warehouse Management System (WMS) which is developed in-house. The Warehouse Management System is used to keep track of the movement of the goods coming in, moving out and within the warehouse itself. The process of goods coming into the warehouse is known as the Inbound process while the process of goods moving out of the warehouse is known as the Outbound process.
For this project, our sponsor has provided us with data for one of their client, which is a company selling cables and connectors. We will be provided with 3 years worth of data from year 2015 to 2017. There are 2 Excel workbooks provided, one for the Inbound goods and the other for the Outbound goods. A more in-depth explanation of the Excel workbooks will be explained below.
Inbound Report
There are a total of 308,730 rows of data spanning across January 2015 to December 2017 for the Inbound orders for this client. In 2015, there were 99,586 rows of data and this increased in 2016 to 102,379. In 2017, the number of rows of data was 106,765.
The report shows the order level where each row represents an order by the client.
Metadata Dictionary
| Terminology | Description |
|---|---|
| Doc_Num | Unique identifier for an inbound order. |
| Product_Code | Unique identifier of a product. |
| ASN Date | Advanced Shipping Notice. ASN date represents the date when the ASN is downloaded. |
| GRN Date | Goods Received Note. The date when the GRN job is created. |
| GRN Post Date | The date when the GRN job is completed. |
| PA Created Date | The date when the putaway job is created. |
| PA Post Date | The date when the putaway job is completed. |
| No. Of Line | The number of lines in the storing list. |
| Count by LPN | Number of cartons. |
| Owner_Short_Name | The short name of the client. |
Outbound Report
The number of Outbound orders for this client from January 2015 to December 2017 is approximately 657,578 rows of data. In 2015, there were 220,387 rows of data and this increased in 2016 to 221,498. In 2017, the number of rows of data was 215,667.
The report shows the order level where each row represents an order by the client.
Metadata Dictionary
| Terminology | Description |
|---|---|
| Doc_Num | Unique identifier for an outbound order. |
| Trans_Code | There are several trans code, but we will only focus on:
|
| Product_Code | Unique identifier of a product. |
| Qty | The total quantity of products needed for the order. |
| No of CTN | Number of cartons for the order. |
| SO Created Date | The date when the sales order is downloaded. |
| SO Post Date | (Clarifying with sponsor) |
| Expected Delivery Date | The date which the goods is expected to be shipped out. |
| PlannedPGIDate | Planned DP post date. |
| DP Creation Date | The date when the picking list is printed for the picker to pick the goods in warehouse. |
| DP Commit Date | The date when the picker has finished picking. |
| DO Created Date | The date which the delivery order is created (this indicates that we are ready for shipment and waiting for transporter to load our goods). |
| DO Commit | The date when the delivery order is committed (this happens after the goods left the warehouse). |
| LocName | The name of the location in our warehouse.
|
| Ship_To / TP_Full_Name | Outbound customer name. |
Limitations
As with any other real life projects, there are limitations which we face especially with regards to the availability of data.
| S/N | Limitation | Conclusion |
|---|---|---|
| 1 | There is no way to accurate link an Outbound shipment to an Inbound shipment. We can only link the Outbound product and Inbound product but not the exact shipment where the product is being brought into the warehouse. | We will assume a First In First Out (FIFO) order of shipment out of the warehouse as that is usually how logistics company operates. This means that the first Outbound in 2015 will be linked to the first Inbound in 2015, unless there are excess goods brought over from 2014. If so, calculations will be done based on the quantity to determine the number of each products left after Outbound shipment. |
| 2 | The current data provided only consists of information from 1 company, which cables and connectors, that our sponsor company is handling. | We assume that the same analysis can be replicated for the logistic warehousing of other companies’ products which are similar in nature. |
| 3 | The data provided from WMS might not contain all the transactions that happened due to human errors such as missed barcode scanning. | With a large enough dataset, it can be assumed that the results found by our team can be representative and considered accurate. |
Important Information about the Business Process
(Input Warehouse Business Process Diagram)
Project Scope
1. Data Gathering and Scoping
Our sponsor has given us various excel spreadsheets, both Inbound and Outbound, with data from 2015 to 2017, for a client selling cables and connectors.
Our project scope includes analysing the products ranking, products seasonality and products inbound and outbound volumes. Additionally, we will also look at the affinity between products to see if there are trends of any products frequently being shipped together. With the data, we will perform Exploratory Data Analysis using JMP Software.
2. Tools to be Used
We will be utilising several tools to aid us in the development of our dashboard. This includes:
| Technology | Description |
|---|---|
| Highcharts and JavaScript | Highcharts is a simple-to-use charting library written in JavaScript which allows us to add interactive charts to our web application for the dashboard. It will be used to develop our dashboard for the various visualizations mentioned above. |
| Python and Jupyter | Python is a programming language which we will use to create a script using Jupyter meant for cleaning the dataset when it is uploaded. It is also used to handle the missing and duplicated data. |
| PostgreSQL | PostgreSQL is an object-relational database management system which will be used to store the data from the CSV files. |
| Tableau Software | Tableau will be utilized for Exploratory Data Analysis. |
| Microsoft Excel | Microsoft Excel will be utilized for Exploratory Data Analysis. |
3. Data Cleaning, Wrangling and Restructuring
Our team aims to combine the 3 years data for Inbound products into 1 excel spreadsheet. This will be the same for the Outbound report. We will also try to combine the Inbound and Outbound reports by using their unique identifier to link the data together. Data cleaning will be done using JMP Software to handling missing values and duplicate data, after integrating the data.
The team will also use Python to write a script which will combine the various excel spreadsheets and extract the relevant fields before consolidating them into a single spreadsheet that will be used as an input for the analysis and the Operations Dashboard. All the data that is uploaded will be stored into a PostgreSQL database, which serves as the database of information for the dashboard.
4. Data Visualization and Reporting
Once the data is cleaned and combined, we will extract the data and create a dashboard with various visualizations as mentioned earlier. This will be done using Highcharts and JavaScript. The interactive dashboard will allow managers to have easy access to visualizations showing the performance of the current operations as compared to past operations.
On top of the visualizations, we will also do Market Basket Analysis to find out the affinity between products. Also, we will report all our findings to our sponsor and propose how the goods should be arranged in the warehouse based on their categories (A, B or C).
