ANLY482 AY2017-18 T1 Group2 Project EZLin Project Data

HOME

ABOUT US

PROJECT OVERVIEW

PROJECT MANAGEMENT

DOCUMENTATION

 

Project Overview

Scope

Data

Methodology

Deliveries

We received various data files for our exploration and analysis. There are three main internal manufacturing plants and other plants are subcontracting plants that do not produce any goods in house.

Mapping of Data

The initial 20 excels sheets have different format and include various information. There are sheets on the exchange rate between different countries, BOM of the plants and markets in various APAC countries, and etc. In order to identify:
1) the flow of manufacturing and distributing,
2) the location of the manufacturing plant and markets and
3) the cost spending on raw materials and overhead,
we need to map all these sheets together, through identifying primary and secondary key in each sheet before linking all the sheets together.
Combine all excel files for BOM into one csv file

• Necessary for data extraction like getting data for different plant at one time.

Combine Purchase Information excel sheet with Purchase Information Linkage

• As there are too many useless columns in these two files, we decided to select the useful ones and combine them together

Combine material master excel sheet with Special procurement type description

• These two files are closely related. Adding the description of special procurement type to material master can help us to understand the data better.

Variable Transformation

Due to the large number of files received, we had to integrate all the files into a single master copy for our analysis and visualisation. The tool we used to consolidate the files is JMP.
Integrating the purchase information files

• Joined two purchase information files together. We join the tables through their private key attributes conditional record number using outer join.
• Remove the unnecessary columns and select the columns that identify the identical purchase information and the columns that provides the details for a certain purchase information.
  

Integrating the Vendor and Plant files

• Joined the consolidated PIR file with external vendor description file with Vendor number using inner join
• Join plant description file with plant number using left outer join to filter out the plants which do not produce goods
• The consolidated file consists of details of all the external vendors and the details of all the manufacturing plants, internal vendors and Asia Pacific Supply Chain(APSC) plants.
  

Integrating material master file

• Include material master file with inner join to understand which plants or subcontract the materials are produced internally or purchased from external vendor
• Identify the procurement type and special procurement type which will link the flow of the purchase of a material in our future visualization and analysis.
  

Integrating dimension conversion

• Before we standardize any unit of amount, we need to join the consolidated table with Dimension Conversion file to ensure the amount stands for the price of one single piece of material instead of multiple
• We will use outer join to join the table and retrieve the UoM value for each material in order to identify the amount for one piece of material.
  

Transform the value of Amount

• Transfer and standardize the Amount based on UoM, per and Currency
• Divide the Amount by UoM to get the cost per piece of material (Amount/UoM)
• Standardize all the per value to 100
• Standardize all the currency to USD to view the distribution of cost of materials across country or region
  

Integrating finishing products details

• Consolidate all the internal manufacturing and subcontracting plant data into a single file
• Consisted of the various cost and components for all levels of the Bills of Material (BOM), which together constitute the Finish Good that will be sent to the respective plant
  

Missing Variable

In order to track the relationship between excel sheets and help with further analysis.
Add Bom_Id, SerielCode column to all excel files about BOM.

• Adding Bom_Id: easier to extract one BOM for future analysis
• Adding SerialCode: To keep track of the BOM level relationship & For converting to JSON (explain later)

Add SubconOrNot column to all excel files about BOM

• Adding SubconOrNot: Easier to filter data for future analysis

Multi Entry Data

We identified that some excel sheets include multiple entry of the same product, with different variable values.
This occurs due to various issues such as human errors, or generating reports in new data format using the old systems in the company. After talking to the sponsors, it is believed that the multi entry of the data need to be combined before we start working on any analysis.

Data Visualization Transformation

Transformation of data from excel format to JSON format is needed in order to perform data visualization

• To use d3.js for data visualization, we need to transform the data to JSON format.
• JSON format is also good for dealing with hierarchical data.