ANLY482 AY2017-18T2 Group03 Project Overview

HOME

ABOUT US

PROJECT OVERVIEW

DATA ANALYSIS

PROJECT MANAGEMENT

DOCUMENTATION

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.

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.

Provided Data

It comes in the form of 14 Excel sheets, notably the Bill of Materials (BOM) files for the manufacturing plants in China, India and Thailand. The BOM includes the individual components and costs involved to manufacture XYZ Baby oil. The costs include but are not limited to: packaging, labour, direct overhead, indirect overhead, depreciation, freight, duty, toll fees, markup. Other data also include plant description, material master as well as procurement information records.

The data provided is mainly in string and numerical format.

Objective and Goals

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), this chart will be made up of 2 lines, one for the morning shift workers and one for the night-shift workers.
  • 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.

Analysis Methods

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.

Project Scope

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.