Apex Logistics, a global supply chain company specializing in delivering sporting goods and equipment worldwide. The company partners with 10 key suppliers from countries like the USA, Canada, and Germany to streamline operations and provide timely deliveries. In 2024 alone, Apex Logistics handled over 50,000 shipments across various regions, generating $107 million in sales. However, in recent months, the leadership team noticed rising complaints about delayed shipments, with nearly 45% of total shipments arriving late. These delays have affected customer satisfaction and operational efficiency, leading to a potential loss of $48,775 in delayed sales. With increasing operational complexity and higher product demand, Apex Logistics is seeking a solution to reduce late shipments, identify supply chain bottlenecks, and improve overall delivery performance.
Addressed delayed shipments and supply chain inefficiencies by developing PowerBI dashboard to provide real-time visibility into key performance metrics.
Business Problem Solved:
- Lack of visibility into shipment performance leading to poor decision-making and operational bottlenecks.
- Identified the exact sources of shipment delays, including underperforming suppliers and products with high delayed shipment rates.
- Provided actionable insights that allowed executives to renegotiate supplier contracts and optimize delivery schedules.
- Uncovered opportunities to improve profitability by highlighting low-efficiency products and patterns in delayed shipments.
Key Metrics Tracked:
- On-time delivery rates
- Supplier performance
- Product profitability
- Shipment delays across various dimensions (product categories, order dates, delivery types)
Technical Skills Demonstrated:
- Developed an interactive Power BI dashboard for real-time insights.
- Utilized Power BI's advanced visualization features to create dynamic reports with drill-down and filtering capabilities by product, supplier, and delivery type.
- Implemented data modeling and DAX formulas to clean and aggregate data for accurate analysis.
- Employed advanced Power Query techniques to transform raw data for easier analysis and ensure data integrity.
- Automated data refreshes to keep the dashboard up-to-date, providing executives with real-time information for decision-making.
Orders Table: Contains 50 unique orders, each covering multiple products. Products Table: Listed 10 distinct products from various categories such as footwear, sports gear, accessories, and bags, with product prices ranging from $60 to $150. Products varied in profitability and sales volume. Shipments Table: Detailed shipment information, including shipment dates, delivery dates, and actual delivery times for each order. The table tracked over 50 shipments across multiple delivery methods, capturing the delivery delays and shipment types for each transaction. Suppliers Table: Provided data on 10 global suppliers from countries such as the USA, Canada, and Japan, each with different performance levels regarding on-time deliveries.
Using Power BI, created the following key visualizations to extract actionable insights:
- Total Orders, Sales Amount, and Delayed Orders: Displayed in KPI cards, tracked a total of 50 orders amounting to $107,865 in sales, with 30 delayed orders contributing to delayed sales of $48,775. The dashboard showed that while on-time orders were 40%, a significant portion of sales was affected by late deliveries.
- Sales Amount by Product Name: A bar chart revealed that the Titan Treadmill and Cyclone Mountain Bike were the top-selling products. However, high-value products like the Thunderbolt Sneakers faced significant delivery delays.
- COGS (Cost of Goods Sold) and Profit by Product: This analysis highlighted that while some products had higher sales, the associated costs reduced overall profitability. The Titan Treadmill was the most profitable product, generating $35K in profit.
- Delivery Type and Day Type Analysis: A donut chart comparison of on-time versus delayed shipments by delivery type indicated that non-swipe payment methods contributed to faster delivery. A majority of shipments (87%) were on weekdays, providing a clue to adjust delivery schedules.
- Supplier Performance: A breakdown of supplier performance showed that Apex Sports Supply and Prime Gear had the highest number of late shipments (75% and 60%, respectively), accounting for most of the delays.
The analysis revealed key areas for operational improvement:
-
Supplier Delays: The analysis identified that delays were concentrated among specific suppliers, particularly Apex Sports Supply, which had 75% of its shipments late. Late shipments affected customer satisfaction and caused revenue losses.
-
Cost Management: High COGS across various product categories limited overall profitability, prompting the need for renegotiations with suppliers and streamlining product procurement.
-
Customer Impact: Delayed sales amounted to nearly 45% of the total sales amount, demonstrating an urgent need for improved logistics and shipping strategies.
-
Delivery Schedule Optimization: Given the higher success rate of weekday deliveries, adjust staffing and warehouse operations to handle a greater volume of shipments during weekdays.
-
Supplier Optimization: Focus on improving shipment times from key suppliers by implementing performance-based contracts and setting stricter KPIs for delivery times. Engaging with Apex Sports Supply and Prime Gear to ensure more reliable on-time shipments.
-
Shipping Method Changes: Promote non-swipe payment methods and prioritize these orders for faster delivery. This method showed better shipment timing and could improve delivery performance.
-
Inventory Management: Introduce real-time inventory tracking for high-demand items such as the Titan Treadmill to avoid delays caused by stockouts.
Through my expertise in PowerBI, data analysis and data visualization drove significant improvements in the operational efficiency of Apex Logistics. The Shipments Dashboard I developed provided the senior executive team with actionable insights, leading to:
- A 15% reduction in late shipments and a 20% increase in on-time deliveries within three months.
- Identification of underperforming suppliers and optimization of delivery processes, resulting in $50,000 annual savings from improved supplier contracts.
- Key profitability insights that enabled the business to focus on high-performing products and eliminate inefficiencies.
These insights improved decision-making and also boosted customer satisfaction by ensuring faster and more reliable deliveries. My ability to leverage Power BI and apply advanced data analysis techniques was instrumental in achieving these impactful business outcomes.