ANLY482 AY2017-18T2 Group03 Project Overview
| HOME | ABOUT US | PROJECT OVERVIEW | DATA ANALYSIS | PROJECT MANAGEMENT | DOCUMENTATION |
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.
Also, manhour management is also very important as the amount of volume that the material handlers can handle is limited to 7 containers for inbound and 7 containers for outbound. Anymore than this and overtime will be required. Currently, material handlers are the ones that request for overtime hours whenever the volume of work exceeds the current baseline threshold. Thus, it is up to the judgement of the manager on the ground to make the final call on how long the material handlers need to overtime. As a result, there are cases of material handlers taking more overtime than necessary leading to increased cost which leads to the company having their margins beings reduced due to this inefficiency as the client would only pay for a certain number of overtime hours based on the agreed service level agreement.
The main objectives of the project would be to do the following:
1. Create a Operations Dashboard to visualize the following KPIs
- Operations Productivity Chart
- The purpose of this chart is to visualize the operations performance at each hour of the day and see if the drop in productivity is line with the break timings and if there are additional timings which are unproductive.
- X-axis: Hour of the day
- Y-axis: Number of pallets successfully serviced
- 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 pallets
- Actual Overtime Hours Chart (Dual-axis chart), these charts will be made up of 2 lines, one for the total number of overtime hours incurred and another being the total number of containers that were completed for the day. One chart will be used for the morning shift and another chart will be used for the night shift.
- The purpose of this chart is to visualize the number of overtime hours for the month in a timeline view so that managers can see the overtime performance of the operations in relation to the number of additional containers that need to be serviced for the day
- X-axis: Date of the month
- Y-axis: Number of overtime hours
- Y-axis: Number of containers
- Overtime Performance Analysis Chart
- The purpose of this chart is to use the historical timings to come-up with a baseline overtime hour required for varying number of additional containers.
- X-axis: Number of containers over the threshold limit
- Y-axis: Average time taken to complete the additional order
2. Validate if the current operation performance is in-line with the baseline performance of 7 containers of inbound and 7 containers of outbound per working shift.
The data provided by our sponsor are obtained from 3 of their in-house systems:
- Warehouse Management System (WMS) - The warehouse management system is used to keep track of the movement of the goods coming in, going out and within the warehouse itself. The WMS is a proprietary system developed in-house.
- Reporting System (RS) - This system provides the company with performance management systems which allow the company to manage their KPIs. Data from the WMS is being fed into the RS in order to generate summary reports.
- Human Resources System (HRS) - This system provides information on the permanent employees’ working hours and overtime hours.
For the purpose of this project, our sponsor has provided us with data for one of their client, which is a company selling powdered milk cans. This includes the inbound and outbound products for the year 2017. There are several excel workbooks provided which we will explain below.
Contents
From Warehouse Management System
Handling In Report
There is a total of around 140,000 rows of data spanning across January to December 2017 for the Inbound orders for this client. Each row represents a pallet.
*The description is blanked out because it contains information about the client’s products
| Terminology | Description |
|---|---|
| STO No | Unique identifier for a particular inbound order. It is the same as the Customer Ref No in Inbound Order Status Summary Report from RS |
| Trans Code | There are several trans code, but we will only focus on:
|
| GRN Created Date | Goods Received Note. Once GRN is recorded, it means that inbound process is completed. |
| PA Created Date | The date when the putaway job is created. |
| PA Post Date | EThe date when the putaway job is completed. |
| Description | The product which will be stored in the warehouse e.g. Brand X 850g powdered milk cans or Brand X 400g powdered milk cans. |
Handling Out Report
The number of Outbound orders for this client from January 2017 to December 2017 is approximately 81,000 rows of data. Each row represents a pallet.
*The description is blanked out because it contains information about the client’s products
| Terminology | Description |
|---|---|
| SO Doc Num | Unique identifier for a particular outbound order. It is the same as the Order Num in Outbound Order Status Summary Report from RS. |
| Trans Code | There are several trans code, but we will only focus on:
These 3 trans codes are to be taken into consideration because there is a physical movement of goods. |
| DO Created Date | The date which the delivery order is created. |
| DO Post Date | The date which the delivery order is completed. |
| Product Code | Unique identifier of a product. |
| Prod Description | The product which will be stored in the warehouse e.g. Brand X 850g powdered milk cans or Brand X 400g powdered milk cans. |
From Reporting System
Inbound Order Status Summary Report
There is a total of around 3,000 rows of data spanning across January to December 2017 for the Inbound orders for this client from RS. This number is significant lesser than the Handling In Report as it is an aggregation of the data from WMS. Each row represents a container, which contains 22 pallets.
A snippet of the data for a Inbound Order Status Summary Report is shown below.
| Terminology | Description |
|---|---|
| Customer Ref No | Unique identifier for a particular inbound order. It is the same as the STO No in Handling In Report from WMS. |
| ASN Date/Time | Arrival Shipment Notice. It refers to the timing when the goods leave the production plant. |
| GRN Date/Time | Goods Received Note. Once GRN is recorded, it means that inbound process is completed. |
| Putaway Date/Time | The putaway timing refers to the time after which processing of a pallet is complete. It is usually 15 minutes after the reporting of GRN. |
The difference between ASN Date/Time and GRN Date/Time is the total time taken for goods to finish its inbound process after leaving the production plant. This includes the travelling time and the handling in time. However, we are only interested in the handling in time which is the time taken for the workers to complete the handling in. Yet, this timing is not available because the time which the truck arrives at the warehouse for handling in is not recorded. Therefore, we will have to make an assumption on the average time taken to travel from the production plant to the warehouse, which is from Tampines to Tuas. To ensure a more accurate timing of the actual handling in time, we will recommend our sponsor to start collecting this information as well.
Outbound Order Status Summary Report
There is a total of around 4,000 rows of data spanning across January to December 2017 for the Outbound orders for this client from RS. This number is significant lesser than the Handling Out Report as it is an aggregation of the data from WMS. Each row represents a container, which contains 22 pallets.
A snippet of the data for a typical Outbound Order Status Summary Report is shown below.
| Terminology | Description |
|---|---|
| Order Num | Unique identifier for a particular outbound order. It is the same as the SO Doc Num in Handling Out Report from WMS. |
| Trans Code | There are several trans code, but we will only focus on:
These 3 trans codes are to be taken into consideration because there is a physical movement of goods. |
| Qty in PLT | Quantity in Pallet, which will be rounded up to whole number. |
| DP Start Date | DP (direct picking) start refers to the operational trigger time when operation starts. |
From Human Resources System
Overtime Hours Report
The report on the number of working hours and overtime hours for the permanent staff can be retrieved from the HRS. This information helps the Operations Manager to get a sense of the estimated number of overtime hours required for a client storing this type of products.
Given the nature of a warehousing business, a client typically releases a Request For Quotation (RFQ) and a logistics provider will bid for the project. The contract will consist of a service level agreement which determines the number of workers that can be used per shift, the total number of overtime hours that the client agrees to pay, the amount of transactions that the client must provide each month, and etc.
Thus, since the amount of overtime that the client is willing to pay is fixed, overtime must be properly planned so that the company will not exceed the agreed amount of overtime as this would lead to reduced margins from the client.
1. Exploratory analysis
We will be doing a horizontal analysis to understand what is the ratio of each cost component in a particular unit of finished good across different locations.
2. Correlation
We will be doing a correlation analysis to identify variables that correlate with one another. We will also be identifying the variables that affect the total cost of goods produce which affects the total gross profit, such as the correlation between different cost components and the finished good cost. We will also be checking for multicollinearity between different cost components for complementary materials.
3. Regression
We will see if the total cost to produce a finished good can be expressed as a linear/polynomial equation of certain influential cost components. This is to identify which materials are the bottlenecks as well as weightage importance.
1. Data Gathering and Scoping
Our sponsor has given us the data in Excel file format for the baby oil product family in the year 2017. Geographically, the data involves three manufacturing plants in China, Thailand and India, and multiple distribution centres across over ten cities. From the supply chain perspective, the project will investigate the total costs associated with a finished good from the manufacturing plant to the wholesalers.
2. Tools to be Used
Our team has researched and discussed with the sponsor regarding the tools used in analyzing and presenting the data.
We will be using JMP Pro to perform exploratory data analysis. For visualization, we will be presenting our analytical results and dashboard using Tableau as XYZ is comfortable with using this software. It also fulfills their requirement of a dashboard that is highly interactive and allows them to drill down from the overview to the root causes.
We will also research R libraries to find suitable methods that perform analysis and data wrangling.
3. Data Cleaning, Wrangling and Restructuring
The team wants to perform horizontal analysis on the cost of one material across the three locations. However, the same material have different material numbers in different manufacturing location. Thus, we can only compare based on the material’s textual description, which are recorded differently in the different locations.
Therefore, the team will need to reconcile these name differences, either through manual reparsing or by using scripts that perform approximate string matching. Our preliminary research points us towards the R library stringdist, which can match two approximately similar strings based on their string distance. (https://cran.r-project.org/web/packages/stringdist/stringdist.pdf)
The team will also use R to write a script that will extract and join the relevant fields from the different Excel files into a consolidated spreadsheet, which will then be used as input for analysis and report generation.
4. Data Visualization and Reporting
Once the data is cleaned and collated, we can begin to extract the data and load onto a data visualization template in R that will facilitate reporting and upper management review of the company’s performance. The template will accept certain excel file inputs regarding XYZ’s yearly, monthly or daily operations and will produce an output, either in the form of an interactive dashboard or a visual presentation. There will also be statistical relations so that management can more easily discern trends and discrepancies in their operations.
Armed with these functions, XYZ can determine where to first look into to find redundant costs in their supply chain.
