ANLY482 AY2017-18 T1 Group2 Project EZLin Project Methodology
Current Approach
Currently J&J uses SAP software to manage its internal database. The data that they are able to provide for studying the supply chain includes the detailed cost components’ information for each product in each plant, plants’ description, raw materials’ information, the relationship of the shipments between different plant as well as the net trade sales data.
Before any further analysis on the data was able to be conducted, there were three main problems that we faced.
The first problem was that there was not a single excel file that contained all the necessary information, making it very difficult for us to initially use Tableau for our visualisation. For instance, the file containing material’s cost components are being separated into several excel files and in accordance to where they are manufactured or held in a distribution centre. In addition, to be able to trace the shipment flow with its respective information, the special procurement type excel file has to be referred to.
Secondly, due to JnJ’s internal data structure, the various cost components in the Bill of Materials (BOM) data are in hierarchical structure(Figure 1). If we use this hierarchical structure data to do the visualization directly, a lot of cost components will be double counted.
Figure 1 hierarchical structure data
Lastly, the table structure for storing the cost components is not very suitable in doing visualisation. This is because the origin structure has numerous rows of cost components and each rows contains all the cost components value for one material. This results in having numerous cells with 0 value as there is no cost under that particular cost component.
Given these, there was a need to process and transform the data in order to come up with some form of visualisation.
Data Transformation
Techniques we used for data transformation is python. Libraries we have used include pandas, numpy, itertools. Pandas contains a lot of functions which make data transformation easier. And itertools is for looping through the whole data set.
Firstly, we combined all excel files containing detailed cost component information(Figure 2,3). This included the Manufacturing BOM file, the Distribution Centre (DC) BOM file as well as the Subcontractor (Subcon) BOM file. Without combining all the excel file, it will be difficult to visualise all the information together.
Figure 2 add all excel files into a list
Figure 3 combine all excel files
Following the consolidation of the BOM files, in order to let each material maintain and keep track of its hierarchical relationship in one BOM, a SerialCode column and Bom_Id column was added(Figure 5,6). For example(Figure 4), if one Level 3 material’s material no is OHDIR and its parent material no is 8008608 for level 2, 28001426 for level 1, then the SerialCode for this specific material is 28001426/8008608/OHDIR. Its Bom_Id is 28001426, which is the Level 1 material’s material no of this bom.
Figure 4 example of SerialCode and Bom_Id
Figure 5 add Bom_Id and SerialCode
Figure 6 add Bom_Id and SerialCode
Once the BOM cost components has been properly structured, there was a need to map the shipment flow of the product by seeking the original plant for each material. To do so, we merged the combined excel file (containing all the cost components) with material master.xlsx file to get the Special Procurement Type (SPT) for each material(Figure 7). The SPT is a key denoted by alphabets and numeric value, each representing where was the product shipped from. With the SPT and plant of the material, we can join the table with Special procurement type description.xlsx to get the Long Text which contains the original plant no(Figure 7). Then, using some python string methods, we get the exact plant code from Long Text(Figure 8). (eg. Stock from(210A) -> 210A).
Figure 7 merge the combined excel file with material master.xlsx to get the SPT & get the long Text from Special procurement type description.xlsx
Figure 8 get the origin plant no from Long_Text
Given the variety in volume and types of product available, each material’s Putup (product volume) information and Variant (product type) information were also important for the comparison of the cost and components. As such, the consolidated table based on the above mentioned consolidation was also joined with the putup.xlsx and variant.xlsx(Figure 9).
Figure 9 join the combined excel with putup.xlsx and variant.xlsx
Based on the requirements of JnJ, it was essential that there was uniformity in the comparison which meant that the products had to be compared using the same unit. From the original BOM files, the raw data’s cost values were all in different currencies and in different product volume due to the large variety of products. The sponsor thought that it would be preferable to compare all the materials in either 1 Litre (L) or 1 Kilogram (KG) so that the comparison would be meaningful. Therefore, we transformed all the cost values to USD(Figure 10) and the volume into 1L / KG(Figure 11).
Figure 10 transform all cost values to USD unit
Figure 11 transform all cost values to Litre(or KG)
For the transposition of data, we needed to make all the materials that are in the same bom to have the same original plant, variant description and putup description(Figure 12).
Figure 12 add the same information for each material in one BOM
Following that all BOM materials to have the information, to avoid double counting materials, the rows that were not at the bottom level of the BOM had to be deleted(Figure 13). Only with this was it possible to transpose the data(Figure 14,15), which is to create rows reflecting the different cost components for one material.
Figure 13 drop rows that are not at the bottom level of the BOM
Figure 14 transpose for each material in the BOM
Figure 15 transpose for each material in the BOM
Lastly, to incorporate more information and to derive more insights about the packaging cost as well as the raw materials’ cost, the consolidated table above was merged with the Packaging Data.xlsx and ChemicalWithPlantCode.xlsx(Figure 16).
Figure 16 merge the combined file with packaging file and chemical file
