1. Project Title & Company Review

Streamline Logistics Solution: Eradicating Order Backlogs; Builing an Interactive Excel Dashboard for Real Time Order Optimization.
INDUSTRY: Supply Chain/Logistics Industry.
Streamline Logistics Solutions is a growing and distinguished leader in the supply chain and logistics industry. Since its inception in 2023, the company has established a reputation for providing reliable and efficient logistics services across a wide range of industries.
Streamline specializes in ensuring the swift and dependable delivery of products to customers nationwide, consistently maintaining high standards of service excellence.

ROLE: Operation Analyst/Supply Chain Analyst.

2. Business Problem

- Mounting Order Backlogs: Despite efforts to streamline routing and resource allocation, the company continues to face growing backlogs, resulting in delayed deliveries. These backlogs compromise delivery timelines and, ultimately, customer satisfaction.
- Customer Frustration: Increasing customer complaints about delayed deliveries and poor communication channels have begun to tarnish the company reputation for reliability. Clients expect transparency and timely updates on their orders.
- Escalating Operational Costs: Rising operational expenses, including overtime payments and the need for expedited shipping, are negatively impacting profitability. The ongoing backlogs force the company to rely on costly last-minute shipping and additional resources to meet delivery expectations.
- Visibility Gap: Customers often lack real-time updates on the progress of their orders, which leads to gaps in communication and rising dissatisfaction. The lack of visibility into delivery timelines increases the frustration of customers and reduces overall trust in the service.

3. Business Objectives

- Efficient Resource Allocation: Efficiently allocate delivery resources based on order volume and location to ensure that vehicles and drivers are used optimally, reducing operational inefficiencies and ensuring timely deliveries.
- Monitor Order Progress: Continuously track and monitor order progress in real-time, identifying potential delays and proactively addressing them before they impact delivery timelines.
- Enhance Customer Communication: Improve communication with customers by providing real-time delivery status updates, ensuring transparency enhancing and the customer experience.
- Reduce Backlogs and enhance the customer Operational experience. Costs: Identify and resolve bottlenecks in the order fulfillment process, reducing backlogs and minimizing additional costs associated with overtime, expedited shipping, and inefficient routing.
- Elevate Customer Satisfaction and Reputation: Improve overall customer satisfaction by ensuring timely and reliable deliveries, safeguarding Streamline's reputation as an industry leader in logistics and supply chain management.

4. My Approach

- Data Collection.
- Data Preprocessing.
- Exploratory Data Analysis (EDA).
- Forecasting Analysis.
- Documentation & Recommendations.

TOOL USED: MS Excel

5. Key Deliverables

Delivered a MS Excel dashboard to improve data-driven decision-making.

6. Insights

- Average Delivery Time (min) by Resources: In comparison to Standard and Custom orders, Expedited orders take longer time . This is operationally backward.
- Average Delay Time (min) by Resources: Standard & Custom Orders took an average of 14 mins while Expedited Rules took 15 mins. No improvement for expedited orders again - a pointer to operational challenges.
- Average Delivery Time by Driver (Overall Top 10 Performing Drivers):Best performer: D88 with 1hr 54mins (114 mins) average delivery time.
- Orders Trend By Day: Orders fluctuate between 177 and 210 Orders, but we noticed a decline in the total orders from the 6th of September towards the 8th of September.
- Orders by Resources: Distribution is balanced, but expedited orders being almost equal to standard orders suggests dependency.
- Average Delay Time (min) by Zone: Zone 1: 14mins, Zone 2 & 3: 15mins . The Average Delays time is almost consistent across zones with little difference.
- Customer Sentiment: Negative: 35%, Neutral: 33% & Positive: 32%. Majority of the customers have negative compared to the positive ones— directly linked to high backlog & delay.
- Orders by City: City B & D have highest demand (320, 312): High-demand cities need more resources. City E is lowest (272).
- Average Delivery|Delay Time (min) by Route: Route 4 is fastest with 2hrs 25mins average delivery time and 15 mins average delay time. Route 1 is slowest with 2hrs41mins average delivery time and 15 mins average delay time.
- Forecast Trend by Orders: There is a Projected downward trend in the orders volume over the next 5 days.

7. Recommendations

- Orders should be assigned to Drivers in the same Delivery address of the customers to cut down the delay caused by distance (Route/Zone). They should also be trained on the best route.
- Implement GPS-based route auto-assignment for transparency on live-location of customers orders.
- Streamline Logistics should look into employing temporary drivers for days/ city with high volume of orders.
- Audit the expedited orders process: There is possibly insufficient resources allocated for expedited deliveries.
- Improve delivery time and cut down delay on Standard delivery performance to limit the processing of expedited orders and reduce negative feedback from customers.
- Based on the Forecast Analysis: Streamline Logistics should prioritize improving on their operations to prevent a longtime effect of the predicted outcome.