Supply Chain Data Analysis

6 min readAug 17, 2023

Introduction:

In today’s competitive business landscape, the effectiveness of the supply chain plays a pivotal role in meeting customer demands and expectations. A well-optimized supply chain not only ensures timely delivery of products but also enhances customer satisfaction by offering seamless and efficient service.

Data:

The dataset was gotten from STATSO.

I’ll be using Excel to perform the exploratory data analysis and visualization.

Business Task:

The objective of this data analysis is to delve into the intricacies of our supply chain processes, leveraging data-driven insights to identify potential areas for improvement.

Business Questions:

In order to achieve my objective, I came up with the following questions which I will use to gain insight into the dataset and offer recommendations.

1. Which product types have the highest revenue generation, and how does price and availability impact their sales?

2. What are the lead times for different suppliers, and can we optimize these lead times to improve delivery times?

3. How do shipping costs vary across different locations, and are there opportunities for cost optimization and faster delivery?

4. What are the manufacturing lead times and defect rates for various production volumes, and how can we reduce defects to improve supply chain efficiency?

5. Can we identify any patterns in customer demographics that influence their buying behavior, and how can we tailor our supply chain strategies to meet their specific needs?

6. Are there any transportation modes or routes that consistently incur higher costs, and how can we optimize transportation to create more value for customers?

Analysis:

Which product types have the highest revenue generation, and how does price and availability impact their sales?

Insight:

Skincare products generated the highest revenue ($241,628.16).

After checking to see if price and availability impact revenue, the analysis revealed that there is no meaningful relationship between product availability, price, and the revenue generated, indicating that these factors do not significantly impact sales performance for the respective product types.

2. What are the lead times for different suppliers, and can we optimize these lead times to improve delivery times?

Insight:

The suppliers with the best lead time among the five suppliers are “Supplier 1” and “Supplier 4” since both of them have the shortest lead time of 15 days. They provide the quickest delivery or fulfillment of orders compared to the other suppliers in the list.

3. How do shipping costs vary across different locations, and are there opportunities for cost optimization?

Insights:

Kolkota has the highest average shipping cost of $144.04, while Delhi is the cheapest with $76.05.

4. What are the manufacturing lead times and defect rates for various production volumes?

Insights:

Production volumes between 100–200 have an average lead time of 12 hours, while those between 801–1000 have an average lead time of 18 hours. The larger the volume, the longer the lead time.

All production volumes have the same average defect rate.

5. Can we identify any patterns in customer demographics that influence their buying behavior, and how can we tailor our supply chain strategies to meet their specific needs?

Insights

Females spend more on the products, with the average revenue they generated being $6460.58. Followed by males and non-binary folks. Non-binary people, on the other hand, have the highest order quantity. Followed by females and males.

This indicates that non binary people buy cheaper products at a larger volume, while females buy products that are a bit more expensive.

6. Are there any transportation modes or routes that consistently incur higher costs, and how can we optimize transportation to create more value for customers?

Insights

By sea, Route B has the highest cost, followed by Route C then Route A.

By road, Route C has the highest cost, followed by Route B then Route A.

By rail, Route B has the highest cost, followed by Route A then Route C.

By air, Route B has the highest cost, followed by Route C then Route A.

Besides road, all other transport modes for Route B have the highest cost of the 3 routes. Also, besides rail, all other transport modes for Route A have the lowest cost of the 3 routes.

Route B is the most expensive, while Route A is the least expensive.

Recommendations:

Since skincare products have generated the highest revenue, it’s advisable to focus on further promoting and optimizing these products. Consider introducing new variants, collaborating with influencers or experts in the skincare field, and ensuring consistent availability.
Optimize haircare products and cosmetics by creating product bundles that combine them with skincare products. Design bundles that offer a holistic solution to customers. For example, a bundle could include a skincare serum, a haircare shampoo, and a complementary lipstick. This encourages customers to explore multiple categories and potentially increases their overall spending.

Given that Supplier 1 and Supplier 4 have the best lead times of 15 days, it’s recommended to prioritize these suppliers when fulfilling orders. Their quick delivery can help in meeting customer demands promptly.
While Supplier 1 and Supplier 4 offer the best lead times, it’s important to have backup plans in case of unforeseen disruptions. Maintain open communication with these suppliers and have alternative options in case their lead times are compromised.

Since Kolkata has the highest shipping cost, engage in negotiations with carriers operating in that region. Bulk shipping agreements or long-term contracts could potentially lead to more favorable rates.
Investigate whether there are any surcharges or additional fees contributing to the higher shipping costs to Kolkata. Addressing or minimizing these surcharges could result in cost savings.

Plan production schedules with the understanding that higher production volumes result in longer lead times. Allocate sufficient time for production to accommodate the increasing lead time trend.
For production volumes that require longer lead times, consider adjusting the timing of order placement to account for the extended production duration. Avoid last-minute orders for large volumes.

Tailor marketing campaigns and product recommendations based on gender and non-binary preferences. Highlight products that align with the spending patterns of each group, focusing on affordability for non-binary customers and value for females.
Create bundle offers that appeal to non-binary customers who prefer buying in larger volumes. Provide cost-effective bundles that align with their spending patterns.

Learn from the low-cost transportation modes of Route A. Identify the factors contributing to their efficiency and apply those best practices to other routes, where feasible.
Given that Route B consistently incurs the highest costs, explore options to optimize this route. Evaluate alternative routes or transportation modes that could potentially reduce costs while maintaining delivery timelines.