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.
suppressPackageStartupMessages(library(tidyverse))
data <- readr::read_csv("data/orders_and_shipments.csv", show_col_types = FALSE)
data🧾 Executive summary
In a couple of lines, write your main findings here.
Overview
This report is to inform you on Just In Time's state regarding delayed orders, supply versus demand of products, and ranking products that are overstocked and understocked.
Overall, Just In Time have received over 30,000 orders. Out of those orders, 43.54% were delayed.
Just In Time's overall supply demand ratio is 1.07. With product categories such as sporting goods, toys, and books with the highest supply demand ratio.
Finally, ranking the top 5 products that we have overstocked and understocked.
Top 5 Overstocked:
- Perfect Fitness Perfect Rip Deck
- Under Armour Girls' Toddler Spine Surge Runni
- Nike Men's CJ Elite 2 TD Football Cleat
- Web Camera
- Toys
Top 5 Understocked
- O'Brien Men's Neoprene Life Vest
- Pelican Sunstream 100 Kayak
- Field & Stream Sportsman 16 Gun Fire Safe
- Nike Men's Dri-FIT Victory Golf Polo
- Adult Dog Supplies
Shipment Delays
One of the main focus of this investigation is to determine how shipments are being delayed.
NOTE: The dataset contained various data entry errors where the shipment date was listed before the order date. One issue that needs to be resolved is ensuring the order date and shipment date are accurate.
From 2015 through 2017, Just In Time has had 13,442 delayed orders. Over time, the number of delayed orders has decreased to about 400 a month with a sharp decline at the end of 2017.
The majority of delayed orders came from orders that were categorized as standard class and first class. First class shipments should only take two days to process but are a quarter of total delayed orders.
Our warehouses in the USA have had 62.48% of their orders delayed, while our warehouses in Puerto Rico have had 37.52% of their orders delayed.
Globally, our delayed orders have primarily affected our customers in the USA, Mexico, France, Germany, and Brazil.
Our most delayed product is the Perfect Fitness Perfect Rip Deck with a total of 1,904 delayed orders.
Supply Demand Ratio
As stated before, Just In Time's supply demand ratio is at 1.07. From 2015 to 2017 Just In Time has so far sold 66,367 products while our total supply is 71,014.
Products such as Smart Watch, Toys, and Rock Music have the highest supply demand ratio while products such as Industrial Consumer Electronics, Lawn Mower, and Diamondback Girls' Clarity 24 Hybrid Bike 201 have the least supply demand ratio.