ANLY482 AY2017-18T2 Group03 Project Overview

HOME

ABOUT US

PROJECT OVERVIEW

DATA ANALYSIS

PROJECT MANAGEMENT

DOCUMENTATION

MAIN PAGE

Previous		Current

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: OSA (Outbound Stock Adjustment – Usually when there is a shortage or excess physical goods in the warehouse, we need to perform this in the system to ensure that the quantity tallies between system and physical) OSO (For Outbound Shipment) OOT (Clarifying with sponsor) OTA (Clarifying with sponsor) OVT (Clarifying with sponsor)
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. Locations like 2R15C13 are the locations of our previous warehouse which has a different layout as the current warehouse.
Ship_To / TP_Full_Name	Outbound customer name.

Project Scope

The following describes the scope of work which the team will be working on across the span of the next few months till the final submission.

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: