Supply Chain Analysis with Excel

A supply chain is a network of production and logistics involved in producing and delivering goods to customers. Supply chain analysis evaluates every stage of a supply chain, from acquiring raw materials or supplies to delivering final products to customers. The goal is to identify areas for improvement to deliver products more quickly and efficiently.

OBJECTIVE:

The primary goal of this project is to analyze supply chain data, focusing on extracting key insights and identifying opportunities for improvement. I aim to explore various stages of the supply chain to develop a deeper understanding of these processes and propose potential strategic improvements.

---

QUESTIONS:

1. What is the top-performing product category by revenue?

2. What are the costs associated with different stages of the supply chain?

3. What are the most popular products among different customer demographics?

4. What is the lead time from order placement to delivery for different products?

5. How do different suppliers and transportation modes impact overall supply chain efficiency?

DATA:

The dataset is from a fashion and beauty start-up and is based on the supply chain of personal care products. Explore the dataset here.

Below is the initial overview of the dataset.

---

DATA ANALYSIS OVERVIEW:

1. Define clear objectives

2. Data import and inspection

   1. Import the supply chain dataset in CSV format and perform an initial review to understand its structure and content.

3. Data cleaning and validation

   1. Identify and fix missing data in key fields like Price, Availability, and Number of Products Sold.

   2. Verify that each column is assigned the appropriate data type.

   3. Confirm that SKU identifiers are unique and correctly match their respective Product Types.

4. Analysis

5. Findings and Recommendations

---

ANALYSIS:

• The total revenue generated by the company was $577,604.82

• The company generates the most revenue from skincare products.

• The company uses three carriers for transportation. Shipping Carrier B generates the most revenue for the business.

• Female and non-binary individuals purchased more skin care products.

• 45% of the business sales come from skincare products, 29.5% from haircare, and 25.5% from cosmetics.

  

• Skincare products have the lowest average stock levels due to higher sales

  

---

Analysing SKU’s:

There’s a column in the dataset called SKUs. You might be hearing this term for the first time. SKU stands for Stock Keeping Units. They are special codes that help companies keep track of all the different items they have for sale. Imagine you have a large toy store with many toys. Each toy is different, with its name and price. To know how many are left, you need a way to identify them. You give each toy a unique code, known as an SKU

• SKU51 generated the highest revenue for the business.

• SKU12 is the largest stock in the company

---

Lead Time:

Lead time is the amount of time that passes from the start of a process until its conclusion. Companies review lead time in supply chain management during pre-processing, processing, and post-processing stages. By comparing results against established benchmarks, they can determine where inefficiencies exist. Investopedia

• Cosmetic products had the largest profit margin of $372.02. Haircare products had a negative profit margin of -$83.08.

• Supplier 3 spends the least on manufacturing costs with $654.51.

  

• The company spends more on road and rail transportation for moving products. Over 50% of the company’s transportation costs come from these two modes

  

• Shipping lead times are much shorter than manufacturing lead times for the products.

  

• Cosmetic products had the shortest lead times overall.

• The company's shipping lead times are significantly better than its manufacturing lead times.

• Route C is the most cost-effective route across all transportation modes while Route B is the most expensive.

---

FINDINGS:

1. Higher revenue generation was often linked to lower stock levels, indicating efficient inventory turnover

2. Female and non-binary individuals purchased more skin care products. Further data collection and analysis are necessary for better insights into the 'Unknown' customer demographic segment.

3. Cosmetic products emerged as the most profitable segment, suggesting a strategic focus area for the business.

4. Skincare products had longer lead times, while haircare products had longer manufacturing times. This suggests supply chain complexities that need further investigation.

5. Supplier 3 demonstrated the highest profitability due to the lowest shipping and manufacturing costs. This highlights the importance of cost management in enhancing profit margins.

6. Certain transportation modes and routes proved more cost-effective, suggesting potential strategic adjustments in logistics planning.

---

RECOMMENDATIONS:

1. Create and implement marketing campaigns tailored to the insights from customer demographic analysis. Customize product offerings and marketing messages to align with the preferences of different customer segments.

2. Optimize inventory levels to align with current sales trends and customer preferences, ensuring efficient stock turnover. Evaluate the performance of various products and consider phasing out or revising strategies for underperforming items.

3. Investigate strategies to reduce manufacturing and shipping costs. This could include renegotiating contracts with existing suppliers or exploring new supplier relationships that offer more favorable terms.

4. Implement a regular review process for logistics operations to ensure they remain efficient and cost-effective. This may involve adapting to changing market conditions or leveraging new logistics technologies and methodologies.

   
   

Happy Learning!!!