ANLY482 AY2017-18 T1 Group2 Project EZLin Midterm
Contents
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.
Lesson Learned
There were many lessons that we have learned till the mid-term review that we believe we are able to continue to apply for the remaining of this project.
1. In a real business problem, data sets are not all stored in one database or server. Different data has to be extracted from different sites. Even after the data has been successfully extracted, not everyone in the organisation understands how the data works. As such, it is important to find the right personnel on board to explain the data and provide a right context for the data.
2. It can be challenging to fully understand an external client’s final objective. For this project, there were different stakeholders involved, namely the technology team and the business team. Due to certain differences in objectives and understanding of the business requirements, both may not fully agree or understand the other’s perspective. This can pose as a challenge for our team in ensuring that we are able to deliver results that’s in line with the client’s final objective.
3. From the mid-term review, we are able to see the importance of collaboration and communication. For the second sprint presentation, our team was clearly not as ready as the other team and we failed to communicate with the other team in ensuring that both utilise the same logic in our data processing. Taking the perspective of the client, they see us as one team, that’s just working on different product line. However, it’s still the same team and the logic in preparing and consolidating the data should not differ greatly. Moving forward, we will seek to collaborate and communicate with the other team as much as possible.
4. We are able to fully understand and appreciate the time required for data pre-processing and cleaning. With the numerous files and constant updates, we had to constantly clean the data even before we were to work on it fully. We also had to constantly email the company and check with them the meaning behind certain data. This verification and liaising does require effort and time as it’s not just a one-off episode.
Future Work
In the next sprint, we will first focus on further visualization using Tableau, and understand the data and correlation between files better. We will be following the Adult Wash End-to End flow to draw the flow of materials, the cost spent in each tasks in the whole production chain, and how products are bundled and sold in the last distribution center. We will analysis from there and identify any waste or inefficiency in the flow.
In the future, we are going to create a cost analysis dashboard system using html and javascript. The main page will consist of a map which can show the relationship between each plant and user can click to see more details for each product. And the reactive dashboard can let users to easily drill down from the finishing products to see the components of the products and cost of the materials. Also, there is sort function for users to see the top five cost under different categories. The purpose of creating this dashboard is to let users to see cost details of cost in the End-to-End flow from the raw material vendor to the markets. Users can compare the cost of products across the plants and drill down the cost of one product to identify the outlier. Some of the possible visualization is shown below.
