Just In Time - Supply Chain Analytics

Just In Time - Supply Chain Analytics

📖 Background

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.

💾 The data

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

💪 Challenge

1. Use this Workspace to prepare your data (optional).
2. Visualize how shipments are delayed, by country, product, and over time.
3. Analyze products by their supply versus demand ratio.
4. Rank products by over or understock.
5. Use your skills to consolidate as much information as possible.
6. Summarize your findings in an executive summary.

🧾 Executive summary

• Prelminary Analysis

  Before commencing the analysis, a comprehensive review of the three datasets was performed, and essential cleanup tasks were executed:

  1. No missing values or duplicated rows were found in any of the datasets.
  2. Column names were examined and trailing spaces were removed for consistency.
  3. Non-alphanumeric characters were replaced to ensure uniformity.
  4. Date columns were converted to the DateTime data type for accurate temporal analysis.

  To assess the punctuality of shipments, two new features were derived from the 'orders_and_shipments.csv' dataset:

  1. Shipment Processing Days: This metric describes the time, in days, taken to process an order.
  2. Shipment Delay: This metric indicates whether an order was delivered on time or not, by comparing the scheduled days for dispatch with the actual days taken.

• Tableau Analysis

  • How to navigate: The Supply Chain Overview dashboard features a set of five orange buttons on its left side. Each button serves as a gateway to a distinct dashboard, delving into various related key aspects. Below these buttons, you'll find an array of filters that allow for in-depth analysis. For convenient access to quick information, look for the info icon situated in the upper right corner of each dashboard. Additionally, a backward icon is available to easily return you to the main dashboard whenever needed.

  The analysis indicates that during the period spanning from 2015 to 2017, the company achieved commendable sales, totaling approximately 66,000 units, and successfully yielded a profit of nearly $4M through 30,871 shipments, catering to nearly 8K distinct customers. Meanwhile, the inventory cost was estimated at approximately $85K.

  The Shipment Analysis reveals a substantial portion of delayed shipments, amounting to 44% of the total. Numerous contributing factors have been identified, notably instances where products are unavailable during order processing, leading to an average restocking time of 5 days, thus causing delays. Another notable observation is that the preferred shipment mode, Standard Class, is associated with the most significant shipment delays.

  The regions that experience the highest incidence of delayed shipments encompass LATAM, Europe, Pacific Asia, North America, and Africa, with specific countries like the USA, Mexico, and France being particularly affected. It is noteworthy to mention that there is a discernible decreasing trend in shipment delays during the analyzed period.

  The Inventory Analysis reveals that a substantial proportion of orders are sourced from the USA warehouse. Furthermore, there is generally little disparity in order quantities between the two warehouses across most customer regions, with the exception of Western Europe and South America. Overstocking is identified as a significant factor contributing to approximately 4.5% of the overall inventory cost, totaling nearly $4,000. Notably, three products consistently contribute to this overstocking issue, among which the Perfect Fitness Perfect Rip Deck stands out as the most impactful on the inventory cost. The other two products are the Under Armour Girl's Toddler Spine Surge Running shoe and the Nike Men's CJ Elite 2 TD Football Cleat.

  The Product Analysis has provided valuable insights into the company's product inventory, revealing the existence of 113 distinct product types, which have been organized into 11 departments. Among these, Fan Shop, Golf, and Apparel departments emerge as the most prominent, with a significant number of products sold, while Health and Beauty departments have relatively fewer products sold.

  On average, there are two products per order, indicating a balanced distribution of products across customer purchases. The top 10 best-selling products are as follows:

  1. Perfect Fitness Perfect Rip Deck
  2. Nike Men's Dri-Fit Victory Golf Polo
  3. O'Brien Men's Neoprene Life Vest
  4. Nike Men's Free 5.0+ Rurring Shoe
  5. Under Armour Girl's Toddler Spine Surge Runni
  6. Nike Men's CJ Elite 2 TD Football Cleat
  7. Field & Stream Sportsman 16 Gun Fire Safe
  8. Pelican Sunstream 100 Kayak
  9. Diamondback Women's Serene Classic Comfort Bi
  10. Team Golf Pittsburgh Steelers Putter Grip

  On the other hand, the analysis has revealed the top 10 over-stocked products, which may require further attention for proper inventory management:

  1. Perfect Fitness Perfect Rip Deck
  2. Under Armour Girls' Toddler Spine Surge Runni
  3. Nike Men's CJ Elite 2 TD Football Cleat
  4. Web Camera
  5. Toys
  6. Nike Men's Free 5.0+ Running Shoe
  7. Columbia Men's PFG Anchor Tough T-Shirt
  8. Smartwatch
  9. Under Armour Women's Ignite Slide
  10. Titleist Pro V1x Golf Balls

  Additionally, the top 10 under-stocking products have been identified, indicating the need for replenishment and careful monitoring to avoid any potential stockouts:

  1. O'Brien Men's Neoprene Life Vest
  2. Pelican Sunstream 100 Kayak
  3. Field & Stream Sportsman 16 Gun Fire Safe
  4. Nike Men's Dri-FIT Victory Golf Polo
  5. Adult dog supplies
  6. Lawnmower
  7. Industrial consumer electronics
  8. Fighting video games
  9. Team Golf Texas Longhorns Putter Grip
  10. Under Armour Women's Ignite PIP VI Slide

  The Sales Analysis indicates that the company has achieved a total gross sales figure of approximately $6 M, yielding a commendable profit of nearly $4M, with an average discount per order amounting to 10%. Notably, LATAM and Europe emerge as the most lucrative markets, while Africa lags behind in terms of profitability. Among the countries evaluated, the USA, France, Mexico, Germany, Brazil, the United Kingdom, and Australia rank as the most profitable based on their respective profits.

  Examining the trends over the analyzed period, there appears to be a general downward trajectory in both gross sales and profit. However, it is crucial to note that each year exhibits unique characteristics:

  • In 2015, a relatively modest decreasing trend in both gross sales and profit was observed.
  • In 2016, a slight reversal occurred, showing a modest increasing trend in both gross sales and profit.
  • In contrast, 2017 experienced a notable and sharp decline in both gross sales and profit.

  The top 10 sold products are as follows:

  1. Perfect Fitness Perfect Rip Deck
  2. Field & Stream Sportsman 16 Gun Fire Safe
  3. Nike Men's Free 5.0+ Running Shoe
  4. Nike Men's Dri-FIT Victory Golf Polo
  5. O'Brien Men's Neoprene Life Vest
  6. Diamondback Women's Serene Classic Comfort Bi
  7. Pelican Sunstream 100 Kayak
  8. Nike Men's CJ Elite 2 TD Football Cleat
  9. Under Armour Girls' Toddler Spine Surge Runni
  10. Web Camera

  The Customer Analysis identifies approximately 8K unique customers from 139 different countries. It is noteworthy that about 30% of these customers have placed multiple orders, indicating a certain level of loyalty. The Europe market constitutes the largest segment of our customer base.

  The countries with the highest number of customers and orders are the USA, France, Mexico, Germany, Brazil, the United Kingdom, and Australia, suggesting a significant presence in these regions.

  Regarding shipment preferences, Standard Class emerges as the most favored method among customers, followed by Second Class, First Class, and Same Day delivery options.

  Over the years, the customer count has demonstrated stability, with a minimum of 4,041 customers in 2015 and a maximum of 4,232 in 2016. This consistency in customer retention is a positive indicator of the company's appeal and reliability.
  

• Key Suggestions

  • Implement a Real-Time Inventory Tracking System: By integrating a real-time inventory tracking system, the company can monitor stock levels more accurately, identify potential stockouts, and streamline the restocking process. This will help reduce delays caused by products being out of stock during order processing.
  • Optimize Shipment Mode Selection: Analyze the shipment delay data for different shipment modes to identify the root causes of delays associated with the shipment mode. Consider optimizing the selection of shipment modes for different regions based on historical performance data to reduce delays and improve customer satisfaction.
  • Establish Safety Stock Levels: Identify critical products and establish safety stock levels based on historical demand variability and lead times. Having safety stock in place can help mitigate unexpected demand spikes and minimize delays caused by stockouts.
  • Implement Just-In-Time (JIT) Inventory Approach: Evaluate the feasibility of adopting a JIT inventory approach for fast-moving products with consistent demand. JIT can help reduce inventory carrying costs and minimize the risk of overstocking or understocking.
  • Monitor and Optimize Reorder Points: Continuously monitor sales trends and adjust reorder points accordingly. By setting appropriate reorder points, the company can ensure that orders are placed in a timely manner, avoiding delays caused by delayed reorder decisions.
  • Use Predictive Analytics for Demand Forecasting: Utilize predictive analytics and machine learning algorithms to forecast demand more accurately. Accurate demand forecasting can enable the company to optimize inventory levels, preventing both overstocking and understocking situations.
  • Collaborative Planning with Suppliers: Strengthen communication and collaboration with suppliers to ensure a smooth supply chain flow. Share relevant sales and inventory data with key suppliers to enable them to plan production and deliveries more effectively.
  • Continuous Process Improvement: Establish a culture of continuous process improvement to identify bottlenecks and inefficiencies in the supply chain and inventory management. Regularly review and refine processes to enhance overall efficiency and reduce delays.
  • Customer Segmentation and Targeted Promotions: Conduct customer segmentation based on ordering patterns, location, and preferences. By understanding customer behavior, the company can offer targeted promotions and incentives to encourage more frequent orders and increase customer loyalty, leading to better inventory management.

orders_and_shipments.csv

inventory.csv

fulfillment.csv