Analyze data in Excel with a case study of Dry Port to Dry Port (Part 2)

Published by Ngoc Tran on 7 October 20217 October 2021

We have discussed the two first steps of the data analysis process in Excel last week. Now, let’s continue with…

#3: Data analysis
Analysing data basically involves calculations and data reading skills. Excel offers a wide range of formulas in order to make our calculations more accurate and faster; however, which formulas are mostly common and useful? An creative tip is to try a combination of at least two formulas that you think it may provide some useful insights (see examples of formulas in the table above).
Another question for you is if there is any way that you can combine these formula in order to answer a question in our mind/ make things faster? I will reveal some suggestions together with the post next week, so stay tuned!
Hint for calculation: You need to define what is the input and output data. In other words, what do you have collected, and what do you want to know?
– The key input data is the recorded actual individual lead time of 35 shipments, and the cost breakdown of the 5 transport routes. Other input can be found in the Excel file in the first post of this series.
– The key output data is the (average) actual costs at each stage (it changes when occurring extra costs), (average) actual total cost and actual total (average) lead time for each side of the whole supply chain (in our case, Vietnam and Belgium). Trying to answer the suggested questions in the Excel file directs you to find your outputs.
These calculations are like a mining process. After this, you are one step closer to get your own diamond. In our case study, you somehow see what is the bottleneck in the DP2DP supply chain of company A, which is the significantly long dwell time at sea ports in Vietnam (longest dwell time was 15 days, and average dwell time was around 5.75 to 10 days depending on different transport routes, in comparison with only 2 to 4 days in normal situations).
Thinking with a logistics-oriented mindset, you link the outputs from the dataset with what has happened in the industry. Aha! The global supply chain disruption causes a delay in vessel scheduling, leading to longer dwell time at sea ports and longer total lead time. You move your binocular to another perspective: Aha! The longer lead time and increasing costs are also because of the severe lockdown in Vietnam. It is crucial to know what has been happening in the countries, or businesses that you are involved, because it may provide you useful insights connected with the outputs.
Other aspects are labour shortage due to quarantine and side-effect from vaccination, container shortage due to the imbalance of international trade, and disaster such as flood, storm, etc. However, in order to achieve all your diamonds and share them with your team, you should perform the last step, which is Data Visualization. We will come back to the last topic next week. Let me know if you have solved the problem, or have any question in the comment below.

Series Excel skills