ANLY482 AY2017-18 T1 Group2 Project EZLin Midterm
ER Diagram
ER diagram is used to present the relationship between the excel sheets that we have received.
Data Transformation
The transformation in data that we have performed are listed below.
- Integrating the purchase information files
- Integrating the Vendor and Plant files
- Integrating material master file
- Integrating dimension conversion
- Transform the value of Amount
- Integrating finishing products details
More details will be presented in 'Data' under 'Project Overview' part!
Visualization
- Exploratory Data Analysis was conducted to understand which variables were meaningful variables to be included in the analysis and that will allow us to achieve the objective of mapping out the end-to-end supply chain. Some of the meaningful variables included the vendor, vendor country, CnTy, number of level, number of Finished Goods (Level 1), number of finished goods per plant, aggregated cost for each component (e.g. raw materials, packaging, O/H etc.) for each plant.
- From the summary and initial exploratory data analysis, it can be seen that Adult Wash has 6 different BOL levels and is being supplied from 10 different countries (based on the PIR records) with 3 countries being the company’s internal manufacturing site.
- In total, there are 158 Level 1 Finished Products coming out from the internal manufacturing plant and subcontracting plant. However, each L1 finished product does not equate a unique product. Some L1 products may consist of other L1 product from another plant that has been repackaged into a new product, with a new Material No, either for promotional or repackaging purpose.
- Based on the Net Trade Sales data, initial exploratory data analysis was also done. As seen from the chart above, China has the largest Net Trade Sales (NTS) by dollar value for the period from January to August 2017. Even though no in-depth analysis has been done yet, the chart shows a clear representation of the top countries by NTS value.
- We used JMP and Tableau as the tools for visualization, in order to see the relationships between products of different levels and the manufacturing plant or distribution centers.
Challenges
There were numerous challenges faced as we dive deeper into this project.
1. As we received the data late, it took us more time to understand the structure of the finish product supply chain.
2. Even though the number of records for each file is not extremely large, there were many different files highlighting different variables that were important in understanding the end-to-end process. Initially, it was challenging to understand which information to retrieve from which spreadsheet. Furthermore, there were constant updates from the company as to how to read the different files, causing much confusion in the initial phase.
3. For the cost, that’s related to the purchase information, there are many different methods to calculate the total cost for different products. This makes the entire data transformation more complex and changes every time there’s an update from the company.
4. In the BOM data file, certain rows reflect the sum up data while others reflect the cost for different levels. This required us to which need to be transformed for data visualization in Tableau.
5. Based on the data we have now, it is hard to envision if we are able to develop any useful analysis. Our current data processing and transformation seems to be just basic visualization of the different cost and process. Hence, we need to get a better understanding of the data.
