Supply Chain Analysis using Power BI

I had the pleasure of working on this project as part of Avery Smith's Data Career Jumpstart program. I have always been fascinated by the wide variety of products available in stores, whether they are food products, apparel, or cars. I used to check product labels. It is really surprising how products are manufactured in one part of the world and sold in another. This is done systematically using supply chain management. I have some experience in the supply chain industry myself, and I am eager to dig deeper into supply chain data to optimize the performance using Power BI.

Do you know? For many companies, supply chain and logistics are responsible for more than 10% of the overall cost. This also represents one of the greatest opportunities for both cost saving and faster, more reliable delivery to customers.

"Some of the important aspects of supply chain is the overall profit made and the customer satisfaction. Sometimes the delivery is delayed. I want to dig deep into the data and derive a meaningful insight about the different attributes that contribute to the success/failed/delayed delivery and the outcome of the business in terms of income."

Data set:

We are using real data for DataCo Global. The dataset was found here.

And The data description.

Power BI is my favorite business intelligence tool that helps you transform data from multiple sources into actionable insights. Its fascinating features, such as Power Query and DAX calculations, are used to turn data into a wide range of visuals, including pie charts, tree maps, monitor KPIs, combo charts, bar and column charts, maps, and many other options.

Data Cleaning and Transforming

There were columns with null values and a password column with special characters. These columns were removed as they cannot be used in our visuals. I changed the data type of the zip code from numbers to text. Using DAX calculations, I created a column for successful delivery.

Sucessful_delivery = IF(DataCoSupplyChainDataset[Late_delivery_risk]=0, 1,0)        

I am interested in some of the following:

• Where does the company make the most money?
• How can we have less late deliveries?

Some of the important KPI are ,

1. Total Sales made is $37 million.
2. Around $4 million is the profit made.
3. Total number of customers is 20.6K.

Here is the dashboard showing different visuals of our Analysis,

Let us get into our analysis,

Where does the company make the most money?

1.Profit and Sales across different geographical location:

• The profit from the Latin American and European regions is nearly equal, each accounting for around 30% of the total market profit, which collectively constitutes more than half of the total profit generated. In contrast, the profit from the African market is the lowest, at 7% of the total market.
• There is a significant opportunity in the African and USCA markets. Sales in Africa are currently very low, and profit could be maximized by increasing sales in this region.
• It is interesting to note that sales and profit are correlated: increasing sales leads to increased profit.

2. Profit made in different departments:

• There were 11 departments selling 50 product categories. I am very interested in knowing which product category generated the most and least profit.
• A slicer for different market sections provides insights into business performance and the actions that need to be taken.
• Overall, computer products in the technology department generated the most profit, totaling $1.5 million.

3. Profits made across different customer segment.

• The consumer segment generated the most profit, which is not surprising given that consumers have diverse product needs.
• In contrast, the home office segment generated the least profit. This is understandable, as home offices are not a necessity in every household. However, with the rising trend of remote work and study, this segment may see growth in the future.

How can we have less late deliveries?

Customer satisfaction is a crucial factor in determining the success of the supply chain industry. It focuses on product quality and timely delivery.

1. Shipping Delays Across Different Departments

The Fan Shop, followed by the Apparel Shop, is at risk of late delivery.

Products in high demand are more likely to experience delays in delivery.

2. Does number of days for delivery cause delay?

• In shipments where the delivery risk is zero, the number of days allocated for shipping exceeds what is actually needed in current settings(includes order processing, shipping). In these cases, deliveries could be made earlier or on time.

• For shipments with a late delivery status (1), the number of days allocated for shipping is 40% less than the actual time required in the current setup. To meet the target, either the shipping capacity should be scaled, the order processing should be improved. This is a primary reason for the late deliveries.

3. Is there any delays because of shipping mode?

• It is interesting to note that the delivery risk is higher for first-class and second-class shipping, while standard-class shipping has a lower delivery risk. Same-day delivery, on the other hand, carries no risk of failure.
• This disparity may be due to the higher volume of orders in standard-class shipping, which receives more focus and resources. In comparison, second-class and first-class shipping modes handle fewer orders, which might contribute to their higher delivery risks.

Actions to take:

Here are some of the actions to take to optimize the supply chain performance,

1. Profit and Sales across different geographical location

• In markets such as Africa and the USCA, offering more discounts and incentives could attract more customers, boost sales, and, consequently, increase profits. Tailoring marketing and sales strategies to fit each region is essential. For example, advertising through local media and providing discounts on products that are in high demand and significant to the region can be effective strategies.
• Adjusting prices based on local market conditions and consumer purchasing power is crucial. For example, in Africa, offering competitively priced footwear and apparel suited to the local climate can boost sales and profit.
• The children's clothing industry made $0.3 million in profit. To enhance this, it’s important to understand demand, customer preferences, and market trends in this product category and adjust strategies accordingly.

2. Understanding customer needs will contribute to increased profit.

a. Corporate Customers

• Bulk Purchasing: Corporate customers often buy in larger quantities. Offer volume discounts or tiered pricing to larger orders.
• Customization: Provide tailored solutions or products to meet specific corporate needs, such as customized packaging or branded items.
• Account Management: Assign dedicated account managers to handle corporate accounts, offering personalized service and support.

b. Home Office Customers

• Convenience: Focus on convenience and ease of ordering for home office customers, who may value quick delivery and user-friendly processes.
• Product Range: Offer a range of products suitable for home offices, including furniture, tech accessories, and office supplies.
• Flexible Delivery: Provide flexible delivery options, such as same-day or next-day delivery, and easy returns.

3. Shipping delays across different departments

There is high rate of late delivery risk if the demand is high. Here in fan shop, apparel department the late delivery risk can be managed by,

• Demand Forecasting: Use historical data, market trends to forecast demand accurately to reflect changing conditions.

• Safety Stock: Maintain safety stock levels to buffer against fluctuations in demand and potential supply chain disruptions.
• Reorder Points: Set reorder points based on lead times and demand patterns to prevent stockouts and ensure timely replenishment.

4. Does number of days for delivery cause delay?

Delivery can be done effectively on time by,

• Use route optimization software to plan the most efficient delivery routes and reduce transit times.
• Monitor traffic conditions and weather forecasts to avoid delays caused by unforeseen events.
• Partner with reliable carriers and regularly review their performance to ensure they meet delivery commitments.
• Have backup carriers or alternative transportation options in place to handle disruptions.

• Use real-time tracking systems to monitor shipments and identify potential delays early.

5. Is there any delays because of shipping mode?

Late delivery risk is comparatively very high in second class and first class shipping mode. some of the steps can be taken to reduce the delays include,

• Choose reliable carriers with a proven track record for on-time delivery for first class shipping and a good balance between cost and reliability for second class delivery.
• Establish clear service level agreements (SLAs) with carriers that define performance expectations, including on-time delivery guarantees.
• Implement efficient order processing procedures to reduce handling times and improve overall efficiency.

Feel free to comment or connect with me on LinkedIn if you have questions or suggestions, . Keep me in mind if you have any opportunities in the analytics field, as I am always looking for connections. Thank you, Kalaiselvi Kumar.