Competition - Supply Chain Analytics

Supply Chain Analytics in Power BI

Executive Summary: Just In Time

Just In Time has achieved significant financial success over the past three years, with $6.18 millionin gross sales and a net profit of $1.65 million (profit ratio of 27%). The LATAM and Europe markets are major contributors to sales. Key departments driving success include Fan Shop, Apparel, and Golf. High-profit margin product categories present growth opportunities.

Certain products show promising potential due to their profitability and order volumes. Inventory management requires attention, particularly with high holding cost products and instances of overstock and understock. Stagnant products with no orders need action, either through liquidation or targeted marketing. Optimizing inventory management will enhance efficiency and maximize profitability.

Customer Geographical Analysis :

There are 7,987 unique customers across 5 markets, 23 regions, and 139 countries. Europe and LATAM are key markets, accounting for 20.78% and 20.13% of the customer base, respectively. Europe and LATAM together contribute over half of the total gross sales, amounting to $3.6 million. Major customer segments include the USA, France, Mexico, Germany, the UK, Australia, Brazil, China, Italy, and India. Customer lifetime value experienced a drop in 2017, due to lower orders from LATAM segment. Customer transportation preferences show a clear hierarchy, with standard class being the most popular choice. To sustain long-term customer relationships and maximize profitability, understanding the decline in customer lifetime value and addressing transportation preferences in specific regions are crucial areas of focus for Just In Time.

Orders and Shipments

The analysis of 30,900 orders and shipments for the Just in Time supply chain revealed two critical issues: 1,900 bottlenecks (orders delayed by over 60 days) and 2,700 systemic errors (shipments before order dates). The USA accounts for 62% of orders, with Standard Class being the most common (36%). The Puerto Rico warehouse receives 38% of orders, primarily in Standard Class (22%). Addressing these issues is vital for improving efficiency and customer satisfaction.

Delays

The analysis of shipment delays for Second Class and First Class orders in both the USA and Puerto Rico warehouses has revealed crucial insights. While Same Day shipments consistently arrive ahead of schedule, both Second Class and First Class experience delays, with median delays of 2 days and 1 day, respectively. Notably, Second Class delays vary seasonally, possibly due to holiday-related factors.

In the USA warehouse, Standard Class shipments demonstrate optimal performance with no delays, while Second Class orders experience a consistent 2-day delay in most top country segments, except for Honduras and China.

Similarly, in the Puerto Rico warehouse, Standard Class shipments perform well with no delays. However, Second Class orders show a 2-day delay in most top country segments, except for Mexico.

These findings highlight the need for targeted improvements in the shipment processes for Second Class and First Class orders in both warehouses. By addressing these delays, we can enhance supply chain efficiency, ensure timely deliveries, and improve overall customer satisfaction.

Bottlenecks & Errors

There are significant bottlenecks and systemic errors (or returns) in specific shipping options for certain countries, necessitating immediate attention to improve supply chain performance and customer satisfaction.

Over the years, Just in Time's efforts in streamlining operations have led to a positive trend in reducing bottlenecks. However, there has been a concerning upward trend in systemic errors or returns, which can impact customer satisfaction and increase costs associated with handling returns.

The USA, France, and Mexico are key countries experiencing both bottlenecks and systemic errors or returns, indicating the need for targeted solutions to enhance operational efficiency in these markets.

Standard Class shipping, being the most commonly used option, may contribute significantly to these issues, underscoring the importance of further optimizing and streamlining this shipping process to mitigate bottlenecks and systemic errors effectively. Addressing these challenges will strengthen the supply chain and enhance overall performance and customer experience.

Product Movement

The network analysis of product movement has revealed a decline in order volume for six product departments: Pet Shop, Health and Beauty, Discs Shop, Book Shop, Technology, and Fitness. Despite their lower performance compared to other products, these items have a low holding cost and continue to generate profit for Just in Time. Therefore, retaining them in inventory is recommended to maintain profitability and optimize overall product portfolio. Further details on the product analysis can be found in the hidden page (Product Analysis (3) in Power-Bi).

Recommendations:

• Optimize inventory management to balance stock levels and minimize holding costs.
• Focus on profitable departments like Fan Shop, Apparel, and Golf for further investment.
• Promote high-profit margin products such as Kids' golf clubs and women's clothing.
• Utilize forecasting models to plan inventory more effectively.
• Improve shipment performance and address delays in First Class and Second Class shipments.
• Analyze and improve customer lifetime value to enhance retention.
• Target specific markets like the USA, France, and Mexico for supply chain enhancements.
• Explore opportunities in declining product departments.
• Review hidden page analysis (Product Analysis in Power BI) for low order volume, low holding cost, and profitable products.

📷 Dashboard screenshot (1st page)

Remaining Pages

Detailed Analysis

Background

As the main data analyst for Just In Time, the objective is to address key shipment and inventory management challenges, analyze supply chain inefficiencies, and create insightful dashboards for business stakeholders. The goal is to provide clear insights and propose structural improvements for Just In Time.

Data Structure

The analysis involves three primary tables:

1. orders_and_shipments,
2. inventory, and
3. fulfillment.

These tables contain essential information for analyzing customer behavior, order details, product performance, sales revenue, and inventory management. The tables are linked through the "Product Name" field in the fulfillment table, establishing a one-to-many relationship between fulfillment and inventory, as well as fulfillment and orders_and_shipments.

Here is a graphical representation of the above:

ER Diagram

Methods

The preliminary data transformation included several essential steps:

• Checking for missing values and duplicates in the datasets to ensure data quality.
• Conducting summary statistics to understand the dataset's characteristics, such as variable types, data dispersion, and formatting.
• Aggregating data by grouping variables, joining tables, and mutating variables.
• Merging datasets to facilitate subsequent analysis.

Analysis

The analysis is divided into three parts:

1. Exploratory Data Analysis (EDA) in Power BI and dashboard creation to provide visual insights and understand the current supply chain performance.
2. Forecasting Sales and Demand for the next six months of 2018 to help with inventory planning and management.
3. Network Analysis to understand product movement and customer preferences, complementing the findings from the Power BI dashboard.

Analysis Highlights

Over the span of three years, Just In Time has achieved significant financial success, with gross sales totaling 6.18 million and a net profit (after discounts) of 1.65 million, resulting in an impressive profit ratio of 27%. The majority of sales are generated from the LATAM and Europe markets. However, there is a concerning downward trend in sales, particularly observed in the fourth quarter, which clearly has to do with the decline in orders from LATAM segment.

Department and Product Category Insights

Out of 118 products and 30,871 orders, the Fan Shop, Apparel, and Golf departments have played a crucial role in Just In Time's success, contributing a total of $5 million in gross sales. Notably, the Fan Shop department has brought in approximately 2.9 million in gross sales. These three departments together generate around 1.4 million in net profit (net profit = profit after discount), constituting approximately 70% of the total net profit for Just In Time.

Certain product categories, including fishing, water sports, men's footwear, camping, and hiking, stand out for their high-profit margins, averaging around 41%. These categories offer potential opportunities for further investment and growth.

Promising Products

Several products have shown promising potential due to their high-profit margins and substantial order volumes, indicating existing demand in the market. Noteworthy mentions include:

• Outdoors category - Kids' golf clubs and garden products with profit margins of 52% and 43%, respectively.
• Apparel category - Women's clothing with a profit margin of 46%. Additionally, some individual products like the:
• Garmin Forerunner 910XT GPS Watch with an impressive 84% profit margin,
• Garmin Forerunner 910XT GPS Watch with a 74% profit margin,
• Cleveland Golf Women's 588 RTX CB Satin Chrome with a 71% profit margin,
• Garmin Approach S4 Golf GPS Watch with a 64% profit margin have demonstrated exceptional profitability and deserve attention for their potential to drive revenue growth.

Inventory Management

In the realm of inventory management, Just In Time's current total inventory stands at 71,000 units, incurring a holding cost of approximately 82,100$. A comprehensive breakdown of the inventory distribution can be found on Inventory Management (1) page.

High Holding Cost Products

Among the inventory, specific products have notably high holding costs. Topping the list are:

• Perfect Fitness Perfect Rip Deck - 18,000$
• Nike Men's Dri-FIT Victory Golf Polo - 13,200$
• O'Brien Men's Neoprene Life Vest - 12,000$ ... and several others (see Inventory Management (1) page).

Overstock and Understock Observations

Analyzing our inventory levels, we have identified instances of both overstock and understock.

Overstocked Items

The following products have accumulated excessive stock levels, leading to potential holding cost concerns:

• Perfect Fitness Perfect Rip Deck - 10,189 units
• Nike Men's Dri-FIT Victory Golf Polo - 7,084 units
• O'Brien Men's Neoprene Life Vest - 6,201 units

Understocked Items

On the other hand, we have encountered instances of understocked products, resulting in unfulfilled customer demand:

• Field & Stream Sportsman 16 Gun Fire Safe - 248 pending orders with zero available units (either out of stock or used up)
• Pelican Sunstream 100 Kayak - 329 pending orders with depleted inventory

Products with No Orders

Furthermore, we have observed certain products that are currently in stock but have not received any orders. These products, with their respective holding costs, are as follows:

• Bowflex SelectTech 1090 Dumbbells - Total holding cost of 1200 euros
• Dell Laptop - Total holding cost of 1000 euros
• Bushnell Pro x7 Jolt Slope Rangefinder - Total holding cost of 800 euros
• Sole Ellipticals - Total holding cost of 1400 euros

To address this situation, it is recommended to consider either liquidating these products or launching targeted marketing campaigns to stimulate sales and clear the inventory.

By managing our inventory more effectively and addressing the overstock, understock, and stagnant product concerns, we can enhance overall efficiency and maximize profitability. These actions will align with our objectives of optimizing inventory planning and management.

Customer Geographical Analysis

The analysis of customer geographical data reveals the following insights:

• There are a total of 7,987 unique customers across 5 markets, 23 regions, and 139 countries.
• Europe holds the largest share of customers, with a strong presence in Western Europe accounting for 20.78% of the customer base, followed closely by LATAM (Central America) with 20.13%.
• Pacific Asia, North America, and Africa have the least number of customers in comparison.

Sales Distribution by Geographical Region

Both Europe and LATAM have played a significant role in Just In Time's financial success, as they have each brought in approximately 1.8 million in gross sales. Together, these two markets contribute to over half of the total gross sales, which amounts to 3.6 million.

Major Customer Segments

Among the various regions, the following customer segments have emerged as the major contributors to Just In Time's profitability:

• USA - With 1,365 customers, the USA is the most significant customer segment, generating the highest profit for Just In Time.
• France - Following the USA, France holds a notable position with 859 customers contributing to sales and profitability.
• Mexico, Germany, the UK, Australia, Brazil, China, Italy, and India - These countries are also noteworthy contributors to Just In Time's customer base and profits.

Customer Trends

While the analysis indicates a steady increase in the number of customers over the years, a concerning trend has been observed in the customer lifetime value. In 2017, the customer lifetime value experienced a significant drop. Understanding the factors contributing to this decline will be critical to sustain long-term customer relationships and ensure continued profitability.

Customer Transportation Preferences w/ Network Analysis

The analysis of customer transportation preferences using network diagrams reveals a clear hierarchy in their choices. Customers across all markets prefer standard class transportation, followed by the second class, while the first class is also popular but ranks slightly lower. The same-day transportation option has the least frequency of use, indicating lower demand for immediate services. The African Market shows a lower demand for orders compared to other regions, presenting potential areas for improvement or opportunities for Just In Time to explore. By examining customer transportation preferences in specific regions, we find that the standard class is the most commonly used method in Central America, Western Europe, and South America. These regions show a preference for this transportation option compared to other classes.

Orders & Shipments

Our analysis of orders and shipments reveals crucial insights into the performance and efficiency of Just in Time supply chain.

Order Overview

In total, we have processed 30,900 orders. Among these, 1,900 orders encountered bottlenecks, and 2,700 orders experienced systemic errors (check below detailed assumptions).

Assumptions:

1. Bottleneck - Orders where the difference between the shipment date and the order date is more than 60 days (2 months).

• This means that if it takes more than 60 days for the company to ship the product after the customer places the order, it is considered a bottleneck.

2. Systemic Error - orders where the shipment date is before the order date."

• This means that the company ships the product before the customer even places the order. Orders with a negative time difference between shipment and order date are illogical and indicative of systemic errors or data entry mistakes in the business process.

Geographic Distribution of Orders

The majority of our orders, approximately 62%, come from the USA. Within the USA, order classes are distributed as follows:

• Standard Class: 36% of all orders
• Second Class: 12% of all orders
• First Class: 10%
• Same Day: Less than 5%

The Puerto Rico warehouse receives 38% of the orders, with class distribution as follows:

• Standard Class: 22%
• Second Class: 8%
• First Class: 6%
• Same Day: 2%

Delays and Shipment Durations

We have identified delays in both Second Class and First Class shipments, with median delays of 2 days and 1 day, respectively, for both USA and Puerto Rico warehouses. However, it's worth noting that Same Day shipments display a median of -2 days, indicating faster than scheduled arrivals.

Focusing on Second Class and First Class

Upon deeper examination of Second Class and First Class shipments, we observe the following:

1. First Class delays consistently span 1 day throughout all periods.
2. Second Class experiences variations in delays, particularly 2-day delays, occurring from March to September (coinciding with a higher shipment volume) and again in November and January, possibly due to holiday-related factors.

Delays on Country Segments

Upon further investigation into the warehouses and top country segments, we have discovered intriguing patterns related to shipment delays.

USA Warehouse Delays

In the USA warehouse, we have observed the following:

• First Class: As mentioned earlier, delays in First Class shipments consistently span 1 day, indicating a standard performance across our top country segments.
• Same Day: Notably, Same Day shipments in the USA warehouse arrive, on average, 2.5 days before the scheduled shipment time, showcasing excellent efficiency.

Second Class Delays in Top Country Segments (USA Warehouse)

With respect to Second Class shipments in the USA warehouse, we have observed that nearly all top 10 major country segments experience a 2-day shipment delay, except for Honduras and China. This highlights the need for targeted improvements in the shipment process for Second Class orders in these segments.

Standard Class Performance (USA Warehouse)

Standard Class shipments, on the other hand, display optimal performance, with no observed shipment delays across the board.

Puerto Rico Warehouse Delays

Similarly, we have analyzed the delays in the Puerto Rico warehouse:

• First Class: As previously mentioned, First Class shipments in the Puerto Rico warehouse demonstrate standard performance across our top country segments, as delays consistently span 1 day.
• Same Day: The efficiency of Same Day shipments in the Puerto Rico warehouse is commendable, with shipments arriving, on average, 2.5 days before the scheduled shipment time.

Second Class Delays in Top Country Segments (Puerto Rico Warehouse)

In the case of Second Class shipments in the Puerto Rico warehouse, most top 10 major country segments experience a 2-day shipment delay, with the exception of Mexico, which shows no observed delays. This underscores the opportunity for targeted improvements in Second Class shipment processes for these segments.

Standard Class Performance (Puerto Rico Warehouse)

Similar to the USA warehouse, Standard Class shipments in the Puerto Rico warehouse also demonstrate strong performance, with no recorded shipment delays.

In conclusion, it is evident that both First Class and Second Class shipment categories require attention and optimization. By fine-tuning the processes associated with these classes, we can further enhance our supply chain efficiency, reduce delays, and ensure timely deliveries. Addressing these areas will contribute to an overall improvement in customer satisfaction and strengthen our position in the market.

Bottlenecks & Systemic Errors (or Returns)

Our analysis has revealed significant bottlenecks and systemic errors (or returns) in certain shipping options for specific countries. Addressing these issues is crucial to improve overall supply chain performance and customer satisfaction.

Over the years, our analysis reveals a positive trend in reducing bottlenecks within the supply chain:

In the year 2015, the total number of bottlenecks was approximately 1000. By the year 2017, this number significantly decreased to around 200 bottlenecks. This decline in bottlenecks indicates successful efforts in streamlining our operations, optimizing shipping processes, and addressing potential areas of congestion.

On the other hand, we have observed an increasing trend in systemic errors or returns:

In the year 2015, there were approximately 470 instances of systemic errors or returns. By the year 2017, this number escalated to over 900 instances. This upward trend in systemic errors or returns requires immediate attention, as it can lead to dissatisfied customers and increased costs associated with handling returns and resolving errors.

Bottlenecks:

USA: The USA country segment experiences the highest number of bottlenecks, reflecting operational challenges that need to be addressed.

France: Following the USA, France also encounters a notable number of bottlenecks, signaling the need for improvements in the shipping process.

Mexico: Mexico is another country with a significant occurrence of bottlenecks, requiring targeted solutions to enhance efficiency.

Systemic Errors (or Returns):

USA: Similar to bottlenecks, the USA country segment also has the highest instances of systemic errors or returns, demanding immediate attention to resolve the underlying issues.

France: France follows closely behind the USA in terms of systemic errors or returns, indicating the importance of rectifying problems in this market.

Mexico: As with bottlenecks, Mexico also experiences a considerable number of systemic errors or returns that need to be addressed promptly.

It is expected that Standard Class shipping contributes to the most instances of bottlenecks and systemic errors or returns, given its widespread usage as the most common form of transport. However, this highlights the need to further optimize and streamline our Standard Class shipping process to reduce these instances and improve overall performance.

Product Movement w/ Network Analysis

Based on our findings, there is a notable decline in order volume for six product departments: Pet Shop, Health and Beauty, Discs Shop, Book Shop, Technology, and Fitness. Even though these products perform poorly compared to the others, their holding cost is really low and they still bring profit to Just in Time, it may be prudent to consider keeping them in inventory (view hidden page (Product Analysis (3) in Power-Bi))

Recommendations

Optimize Inventory Management: Identify and address overstocked and understocked items to minimize holding costs and prevent unfulfilled customer demand. Consider liquidating stagnant products or launching targeted marketing campaigns to stimulate sales.

Focus on Profitable Departments and Categories: Continue to invest in departments like Fan Shop, Apparel, and Golf, which have contributed significantly to Just In Time's success. Also, pay attention to high-profit margin product categories like fishing, water sports, men's footwear, camping, and hiking for further growth opportunities.

Promote High-Profit Margin Products: Highlight and promote products like Kids' golf clubs, garden products, women's clothing, Garmin Forerunner 910XT GPS Watch, Cleveland Golf Women's 588 RTX CB Satin Chrome, and Garmin Approach S4 Golf GPS Watch, as they have shown exceptional profitability.

Forecast Sales and Demand: Utilize forecasting models to predict sales and demand for the next six months, enabling better inventory planning and management.

Improve Shipment Performance: Address delays and inefficiencies in First Class and Second Class shipments. Analyze bottlenecks and systemic errors to streamline operations and enhance supply chain efficiency.

Customer Lifetime Value Analysis: Investigate the factors contributing to the decline in customer lifetime value in 2017. Identify opportunities to improve customer retention and satisfaction.

Targeted Improvements in Specific Markets: Focus on optimizing supply chain performance in key markets like the USA, France, and Mexico, which experience higher instances of bottlenecks and systemic errors.

Hidden Page Analysis: Review the hidden page (Product Analysis (3) in Power BI) to further explore the performance of products with low order volumes but low holding costs and profit, to decide whether to keep them in inventory.

Explore Opportunities in Declining Product Departments: While some product departments show a decline in order volume, consider conducting further analysis to explore any potential opportunities for improvement or identify reasons for the decline.

By incorporating these recommendations into the supply chain management strategy, Just In Time can drive structural improvements, optimize inventory planning, and position itself for continued success in the market.

Appendix

Time Series analysis and forecasting - GROSS SALES