Navigating the Supply Chain Maze

Supply Chain Analytics in Tableau or Power BI

📖 Background

Test your BI skills on a real-world dataset focusing on supply chain analytics. As the main data analyst for Just In Time, you will help solve key shipment and inventory management challenges, analyze supply chain inefficiencies, and create insightful dashboards to inform business stakeholders about potential problems and propose structural business improvements.

Be creative and make use of your full skillset! Use this Workspace to prepare your data, import the tables into your local Tableau or Power BI instance, and share your insights below.

The end goal will be a (set of) interactive dashboards that demonstrate clear insights for Just In Time.

💾 The data

Group	Column name	Dataset	Definition
Customer	Customer ID	orders_and_shipments.csv	Unique customer identification
Customer	Customer Market	orders_and_shipments.csv	Geographic grouping of customer countries, with values such as Europe, LATAM, Pacific Asia, etc.
Customer	Customer Region	orders_and_shipments.csv	Geographic grouping of customer countries, with values such as Northern Europe, Western Europe, etc.
Customer	Customer Country	orders_and_shipments.csv	Customer's country
Order info	Order ID	orders_and_shipments.csv	Unique Order identification. Order groups one or multiple Order Items
Order info	Order Item ID	orders_and_shipments.csv	Unique Order Item identification. Order Item always belong to just one Order
Order info	Order Year	orders_and_shipments.csv	Year of the order
Order information	Order Month	orders_and_shipments.csv	Month of the order
Order information	Order Day	orders_and_shipments.csv	Day of the order
Order information	Order Time	orders_and_shipments.csv	Timestamp of the order in UTC
Order information	Order Quantity	orders_and_shipments.csv	The amount of items that were ordered within a given Order Item (1 record of the data)
Product	Product Department	orders_and_shipments.csv	Product grouping into categories such as Fitness, Golf, Pet Shop, etc.
Product	Product Category	orders_and_shipments.csv	Product grouping into categories such as Sporting Goods, Women's Apparel, etc.
Product	Product Name	orders_and_shipments.csv	The name of the purchased product
Sales	Gross Sales	orders_and_shipments.csv	Revenue before discounts generated by the sales of the Order Item (1 record of the data)
Sales	Discount %	orders_and_shipments.csv	Discount % applied on the catalog price
Sales	Profit	orders_and_shipments.csv	Profit generated by the sales of the Order Item (1 record of data)
Shipment information	Shipment Year	orders_and_shipments.csv	Year of the shipment
Shipment information	Shipment Month	orders_and_shipments.csv	Month of the shipment
Shipment information	Shipment Day	orders_and_shipments.csv	Day of the shipment
Shipment information	Shipment Mode	orders_and_shipments.csv	Information on how the shipment has been dispatched, with values as First Class, Same Day, Second Class, etc.
Shipment information	Shipment Days - Scheduled	orders_and_shipments.csv	Information on typical amount of days needed to dispatch the goods from the moment the order has been placed
Warehouse	Warehouse Country	orders_and_shipments.csv	Country of the warehouse that has fulfilled this order, the only two values being Puerto Rico and USA
Inventory & Fulfillment	Warehouse Inventory	inventory.csv	The monthly level of inventory of a product, e.g. 930 units
Inventory & Fulfillment	Inventory cost per unit	inventory.csv	The monthly storage cost per unit of inventory, e.g. $2.07
Inventory & Fulfillment	Warehouse Order fulfillment (days)	fulfillment.csv	The average amount of days it takes to refill stock if inventory drops below zero

The data can be downloaded from the sidebar on the left (under Files).

💪 Challenge

Using either Tableau or Power BI, create an interactive dashboard to summarize your research. Things to consider:

Use this Workspace to prepare your data (optional).
Some ideas to get you started: visualize how shipments are delayed, by country, product, and over time. Analyze products by their supply versus demand ratio. Rank products by over or understock. Don't feel limited by these, you're encouraged to use your skills to consolidate as much information as possible.
Create a screenshot of your (main) Tableau or Power BI dashboard, and paste that into the designated field.
Summarize your findings in an executive summary.

import pandas as pd
data = pd.read_csv("data/orders_and_shipments.csv")
data

✅ Checklist before publishing

If you use Tableau, don't forget to publish your Tableau dashboard, make it available on Tableau Public and share the link.
If you use Power BI, upload your .pbix file to this Workspace through the sidebar on the left (under Files).
Remove redundant text cells like the background, data, challenge, and checklist. You can add cells if necessary.

✍️ Judging criteria

CATEGORY	WEIGHTING	DETAILS
Visualizations	35%	Appropriateness of visualizations used. Clarity of insight from visualizations.
Insights	25%	Clarity of insights - how clear and well presented the insights are. Quality of recommendations - are appropriate analytical techniques used & are the conclusions valid? Number of relevant insights found for the target audience.
Storytelling	25%	How well the data and insights are connected to tell a story. How the narrative and whole report connects together. How balanced the report is: in-depth enough but also concise.
Votes	15%	Up voting - most upvoted entries get the most points.

🧾 Executive summary

The aim of this supply chain analytics project is to analyze supply chain inefficiencies, identify potential problems, and propose structural improvements in shipment and inventory management for the company Just In Time.

To ensures data accuracy and consistency in subsequent calculations and recommendations:

The "Warehouse Inventory" column was interpreted as containing the number of items remaining at the end of the month.
Rows, where the time of the shipment was earlier than the ordering time, were deleted.
The data from October 2017 seems incomplete, filtering was applied to exclude those dates from the analysis.

I. Overview of Key Performance Indicators:

The total demand for the analyzed period (2015, 2016 and 2017) amounted to 59K units, reflecting the quantity of products required by customers during this time. Additionally, a total of 9,887 orders were placed, indicating the number of individual transactions processed by the supply chain system.

This resulted in total gross sales reached an impressive amount of 5.44 million dollars. Moreover, the total profit derived from these sales amounted to 3.52 million dollars whic is 64.6% of the total gorss sales. This demonstrates the efficiency and effectiveness of the supply chain in generating profits from sales.

The average profit rate of 64.6% indicates profitability and efficient cost management, although it also shows that there is still room for improvement. A higher profit ratio indicates better cost management, pricing strategies, and overall profitability.

The total monthly remaining storage cost for the total monthly remaining inventory of 66K units was around 80K.

On average, it took approximately 7 cycles per year to replace the stock. The average supply-demand ratio of 2.05 suggests that, on average, there is a higher supply relative to demand. While having a slightly higher supply than demand can help prevent stockouts and meet customer requirements, it is important to monitor and manage this ratio to avoid excessive inventory levels and associated holding costs.

A significant percentage (94.45%) of the products experienced price reductions during the analyzed period. The most frequent price reduction observed among the products is 15%. Among the products, the maximum observable price reduction is 25%. This more aggressive price adjustment is used in 27.8% of the items. This indicates that pricing adjustments are prevalent and commonly employed as a strategy within the business. Price reductions can be driven by various factors, including inventory management to reduce overstock and prevent obsolete items.

To assist in anticipating demand fluctuations and adjusting inventory levels accordingly I set reorder point, optimal monthly quantity and visualized demand by date, draw trend line and performed forecasting.

The presence of 6,917 unique customers indicates a diverse customer base engaged with the business. However, the concentration of customers, orders, ordered quantity, and gross sales in the USA, Mexico, France, Germany, and Brazil highlights the significance of these countries as key markets for the business.

II. ABC & XYZ Analysis:

The inventory items were categorized based on ABC and XYZ analyses.

A-Category:

A-Category items are high value items, critical to the business and require robust inventory management and real-time tracking.

There are 5 products in the A-category. Notably, all these products belong to the X-category, indicating a consistent demand pattern:

Nike Men’s Free 5.0+ Running Shoe,
Perfect Fitness Perfect Rip Deck,
Diamondback Women’s Serene Classic Comfort Bike,
Fields and Stream Sportsman 16 Gun Fire Safe, and
Pelican Sunstream 100 Kayak.

The total annual gross sales of these items amount to approximately 3.5 million dollars, with a total of 24K items sold. The trendline analysis indicates a slight increase in demand for the Pelican Sunstream 100 Kayak while the trendlines for the remaining products show a slight decrease.

While the A category products contribute to the top 5 gross sales, total demand, and total profit, there is a variation in the profit ratios among these products. Specifically, Running Shoes and Rip Deck exhibit a high profit ratio of above 80%, indicating strong profitability. However, the remaining three products in the A category have a profit ratio of only 50%, which is lower than the average profit ratio.

B-Category:

The B-Category items are Moderate-Value Items. Although these items are important, they may not require the same level of attention as A-category items. Setting appropriate reorder points based on historical demand patterns and lead times is recommended for effective inventory management.

There are 3 products in the A-category. Notably, all these products belong to the X-category, indicating a consistent demand pattern:

Nike Men’s Dri-Fit Victory Golf Polo,
O'Brien Men’s Neoprene Life Vest, and
Nike Men’s CJ Elite 2TD Football Cleats.

The total annual gross sales of these items amount to approximately 1.4 million dollars, with a total of 23K items sold. The trendline analysis indicates a slight increase in demand for the Football Cleat while the trendlines for the Life Vest show a slight decrease.

The average profit ratio of B-category products exceeds that of category A. Specifically, the Golf Polo and the Life Vest exhibit a high profit ratio of above 80%, indicating strong profitability. However, the Football Cleats have a profit ratio of only 50%, which is lower than the average profit ratio.

C-Category: The C-Category items are Low-Value Items.The demand of these items are vary.

3.1 The CX category consists of 14 products that fall into the low-value, high-demand range. While these products experience frequent turnover and generally display a rising demand pattern, they hold lesser significance from a business perspective.

Implementing automated replenishment systems or vendor-managed inventory arrangements can streamline the replenishment process for these items.

3.2 The CY category consists of 33 products that fall into the Low-Value, Low-Demand range.

To optimize storage and utilize valuable warehouse space efficiently, consider reducing the quantity of CY items and adopting just-in-time inventory practices. Frequent stock checks will help avoid excess inventory and create space for more profitable items.

3.3 The CZ category consists of 55 products that fall into the Low-Value Obsolete or Sporadic Demand Items range.

Regularly review this category and consider discontinuing or liquidating these items to minimize carrying costs and allocate resources more effectively.

III. Shipmnet Analysis:

The majority of orders are shipped using the standard class, which has a scheduled time of 4 days.

The number of median days to ship are 3.

Approximately 65% of shipments are delivered on time.

The delayed shipments show a strong right-skewed deviation from the scheduled time. Around 13% of shipments experience extremely late delays of more than 3 weeks. Most delayed shipments deviate from the schedule by 1, 2, or 4 days.

The first-class shipments (scheduled for 1 day) experience delays in approximately 97% of cases, while second-class shipments (scheduled for 2 days) experience delays in approximately 49% of cases. It suggests that the promised delivery time is not realistic or achievable. In such cases, it is generally advisable to underpromise and overdeliver to manage customer expectations effectively. In the case of the first-class the delivery time is consistently around 2 days, it is appropriate to revise the estimated delivery time to 2 days instead of 1 day.

Shipment delays are more likely when the warehouse country is the United States (US) which takes more orders than Puerto Rico. It suggests that the existing capacity or resources may not be sufficient to handle larger order quantities efficiently, leading to delays in processing and delivering those orders. Reorganizing warehouse layouts could help.

More delays occurred in 2015, and although there is not a significant difference in delays based on the month, the delay peak tends to be in May. However the most extreme shipment delays occurred in January.

The frequency of delays and timely shipment are similar in the case of higher shipment volumes.

By considering the insights Just In Time can improve profitability, optimize inventory management, enhance shipment efficiency, and better meet customer demands in their key markets.

📷 Dashboard screenshot

‌
‌
‌