📷 Dashboard screenshot
Supply Chain Analytics in 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.
🌐 Upload your dashboard
Github Link: https://github.com/Mevhare/Supply-Chain-Analytics/
🧾 Executive Summary
🔍 Insights
💵 Sales
The dramatic decline in Sales revenue and quantity for Q4 2017, a 70% drop in revenue and 85% decrease in quantity sold compared to Q3 2017, was largely due to the unavailability of highest-selling products. A remarkable 90% of sales in Q4 2017 were made to new customers. Despite this, some markets did not place orders for the first time during the same period, including Africa, Latin America, North America, and Pacific Asia. Interestingly, the customer base was largely spread across Latin America, Europe, and North America. Field and Stream Sportsman 16 Gun Fire Safe stood as the most profitable product with a profit margin of 55%. The top selling departments were Golf, Fanshop, and Apparel. The top-performing products were Perfect Fitness Perfect Rip Deck, Nike Men’s Dri-Fit Victory Golf Polo, and O’Brien Men’s Neoprene Life Vest. However, products such as Dell laptop, SOLE E25 and E35, Bushnell Pro-X7 Rangefinder and Bowflex 1090 dumbbells did not record a single sale in the three-year period from 2015 to 2017.
📦 Inventory
The most expensive product departments in terms of inventory were Apparel, Fanshop, and Golf. In terms of turnover rates, High-value goods led the pace with 68.86, followed by Medium-value and Low-value goods. Stable demand products also had noteworthy turnover rates. The product departments with the highest number of backorders were Golf, Fan Club and Apparel.
🚢 Shipping
On-time delivery needs significant improvement, as only about 57% of orders were delivered within the stipulated timeline. The USA warehouse had majority of shipments serving 130 countries, compared to the Puerto Rico warehouse which shipped to 120 countries. There was no observable difference in shipping times between Second Class and Standard Class Shipping.
👨🏫 Recommendations
💵 Sales
Implement a solid inventory forecasting and replenishment system to prevent repeats of Q4 2017 Sales declines. For markets not placing orders over certain periods, targeted marketing strategies such as pop-up sales, discounts, or bundles can be deployed. In terms of profitability, more emphasis needs to be placed on the promotional strategies surrounding high-margin products like the Fire Safe, including product positioning, up-selling, cross-selling, and strategic pricing. Just In Time should consider the discontinuation of products that didnt record a single sale and other prodcuts that don't provide enough value.Given the high percentage of new customer sales, fostering customer loyalty and repeat business with an effective customer retention strategy would be beneficial. This could include personalized follow-up communications, loyalty programs or exclusive discounts for returning customers.
📦 Inventory
Rethink inventory management strategies with a focus on product demand. High-value goods with high turnover rates should be maintained in smaller, more frequent replenishment cycles. As for the high backorder product departments like Golf, Fan Shop and Apparel, historical sales data can be leveraged to anticipate demand and circumvent future backorders.
🚢 Shipping
The delivery process needs an overhaul to boost the on-time shipping performance. If in-house measures prove inadequate, outsourcing to a third-party logistics provider could be an alternative. Additionally, it would make sense to rebalance inventory locations between USA and Puerto Rico based on geographical demand for better delivery efficiency. To enhance the value of premium shipping options, a more distinctive difference should be created between different shipping classes, such as guaranteeing delivery periods for Standard Class Shipping.
⚙️ The Process
🧹 Data Cleaning and Transformation
After carefully investigating the data available; fulfilment, orders and shipment and inventory, some data issues were discovered and this is how they were addressed;
- Negative Shipment Time: Removed records of these transactions.
- Orders taking too long to ship: Given the shipment modes and reorder fulfilment times, it is highly unlikely that orders took more than 14 days to ship. Orders that took more than 14 days to ship were filtered.
- Correction of Country Names: Some country's names had special characters and these were corrected to ensure correct names.
- Discreptancecs between Inventory and Order records: Some sales were made on products that weren't recorded in the inventory. Assumption is made that this is an error in data compilation process.
📈 Measures Created
💵 Sales
- Net Sales
- Profit from Sales
- Profit after Inventory Costs
- Profit Margin
- Number of Orders
- Quantity Sold
- Number of Customers
📦Inventory
- Units Available
- Inventory Cost
- Inventory Turnover Rate
- Overstock/Backorder
- Reorder Level
- Inventory in Progress
🚢 Shipment
- Average Delivery Time
- Late Delivery Rate
ABC & XYZ Analysis
ABC/XYZ analysis is a tool for classifying items in a product catalogue. It can help improve inventory management, production planning, and customer satisfaction.
ABC analysis classifies products into three categories; High, Medium and Low based on revenue generated and XYZ classifies products into Stable, Flunctuant and Unstable based on their demand volatility.
Out of 118 products, 5 were classified as High-Value products, 3 were classified as Medium value products and 110 were classified as low-value products. On the other hand, 35 products were classified as Stable demand, 22 as Flunctuant demand and 66 as Unstable demand.
❌ Data flaws
- Profit figures aren't logical because higher profits were generated on prices eg $150 profit on a $100 sale. The assumption is made that there are different suppliers, multiple wholesale discounts or Just In Time is being paid by brands to sell their products.