Supply Chain Optimization
for a FMCG Company

Background Information

FMCG companies that have only been in the instant noodle business for two years are experiencing a mismatch between demand and supply of products in various warehouses. When demand is high, supply is low, and vice versa. This causes significant inventory cost losses. Therefore, management wants to understand more deeply the demand patterns and warehouse conditions in various regions in order to optimize product distribution.

Project Objective

Perform historical data analysis to:
• Identify product demand patterns by region, location type, and warehouse characteristics.
• Diagnose factors causing demand and supply mismatch.
• Provide data-driven recommendations to optimize supply allocation and support managerial decision making through interactive dashboards.

Business Questions & Analysis Method

A. Zone and Location 🗺️
• Q: Which zone or region most often experiences mismatch between warehouse capacity and product weight shipped?
• Q: Does the location of the warehouse in a village or city affect the amount of product distribution?

B. Warehouse Capacity & Utilization 🏛️
• Q: How optimally is the warehouse utilized (calculated from the ratio of product_wg_ton to WH_capacity_size)?
• Q: Which warehouses are overused (exceeding ideal capacity) or underused?

C. Logistics and Transportation 🚚
• Q: Do warehouses with a history of transportation issues (transport_issue_l1y) have lower shipments?
• Q: How much does dist_from_hub affect the weight of products shipped?

D. Environment & Infrastructure 🏢
• Q: Do flood-impacted or non-flood-proof warehouses tend to have lower shipments?
• Q: Is the presence of electric_supply or temp_reg_mach related to warehouse performance in product distribution?

E. Warehouse Activity 🏪
• Q: Do warehouses that experience frequent refills (num_refill_req_l3m) also receive greater supplies?
• Q: Does the number of distributors (distributor_num) have an impact on the total shipments of products to the area?
• Q: How much influence does wh_breakdown_l3m have on the decline in supplies to the warehouse?

F. Market Competition 🛒
• Q: Does the number of competitors (Competitor_in_mkt) affect the number of products shipped to an area?
• Q: Which areas have market potential (few competitors, many retailers) but supply is still low?

G. Certification and Compliance 📜
• Q: Do warehouses with a certain approved_wh_govt_certificate have higher distribution performance?
• Q: Does the number of government inspection visits (govt_check_l3m) affect the supply to that warehouse?

H. Warehouse Eligibility & Age 📆
• Q: Do warehouses built longer (wh_est_year) tend to have more problems and lower deliveries?

I. Combination of Factors 🕵
• Q: What combination of factors (location, capacity, number of distributors, transportation) is most ideal to support high shipments?
• Q: Zones with ideal conditions (safe from flooding, adequate electricity, smooth distribution) but supply is still low – why?

Technical Section

For technical parts such as Data Understanding, EDA to find problems in the dataset, Data Cleaning and Transformation, EDA to find business insights, and visualization strategies I save them in the Github Readme File.
Here is the link: Github Repositories | Jupyter Notebook

Insight & Recommendation

CARD:
🔷 1. Total Product Distributed: 553M
Rural: 92% | Urban: 8%

📊 Insight:

The majority of product distribution occurs in rural areas (92%), indicating a high dependence on the rural distribution network.
✅ Recommendation:

- Evaluate distribution efficiency in rural areas, as they may be more prone to logistical challenges.
- Optimize urban infrastructure to increase distribution in high-growth potential areas.

🔷 2. Avg. Storage Issue (3 Mo): 17.13
50% Warehouses Above Avg.

📊 Insight:

Storage issues are relatively high, with half of the warehouses experiencing issues above the average.
✅ Recommendation:

- Conduct audits to identify warehouses with high storage issues.
- Improve SOPs for storage handling and schedule regular maintenance.

🔷 3. Avg. Warehouse Breakdown (3 Mo): 3.48
Rural: 3.46 | Urban: 3.75

📊 Insight:

Urban warehouses show a slightly higher frequency of breakdowns compared to rural ones.
✅ Recommendation:

- Conduct more frequent inspections in urban warehouses.
- Assess if operational loads in urban warehouses are too high, causing faster breakdowns.

🔷 4. Avg. Warehouse Age: 15.80 Years
18% Warehouses > 20 Years

📊 Insight:

Warehouses are relatively old, with nearly 1 in 5 being over 20 years old—posing operational risks (see storage issues).
✅ Recommendation:

- Consider modernizing or relocating warehouses over 20 years old.
- Explore building new warehouses with modern storage technology.

🔷 5. Total Transport Issue (1 Yr): 19K
Avg. 0.77 per Warehouse

📊 Insight:

Transport issues are still significant, with nearly one issue per warehouse annually.
✅ Recommendation:

- Audit distribution routes and transport vendors.
- Add buffer time, training, or logistic backups for risk-prone areas.

🔷 6. % Certified Warehouses: 96%
+21% vs Target (75%)

📊 Insight:

The percentage of certified warehouses has exceeded the target significantly, which is very positive.
✅ Recommendation:

- Leverage this as a competitive advantage (useful for branding/logistics partnerships).
- Shift focus to next-level performance metrics such as efficiency or distribution speed.


CHART:
📊 Insight 1:
Even though warehouse breakdowns are increasing, shipping volume remains high. Urban areas tend to be more frequently involved in breakdowns with higher shipment volumes.

💡 Business Recommendation:
Conduct more frequent operational audits in urban areas with high breakdowns to prevent long-term logistic inefficiencies.

📊 Insight 2:
The more storage issues reported, the higher the shipping volume. This suggests that warehouses with large capacities tend to experience more storage problems.

💡 Business Recommendation:
Invest in inventory management and smart storage systems in large warehouses to reduce issue potential.

📊 Insight 3:
Warehouses with official certification (C to A+) contribute significantly higher shipping volumes compared to uncertified ones.

💡 Business Recommendation:
Encourage warehouse certification to increase trust in handling large volumes. Uncertified warehouses should be evaluated and prioritized for standard improvements.

📊 Insight 4:
Most certified warehouses are located in rural areas, while uncertified ones are also only found in rural regions. Certification is not evenly distributed to urban areas.

💡 Business Recommendation:

- Expand the certification program to urban areas.
- Review whether uncertified rural warehouses have high capacity, as they may pose a risk in the supply chain.
📊 Insight 5:
Newer warehouses (built after 2010) report fewer storage issues than older ones.

💡 Business Recommendation:
Focus on renovating or upgrading infrastructure in warehouses built before 2010. Alternatively, direct more distribution to newer warehouses to minimize storage issues.

📊 Insight 6:
The average shipping volume is lower in newer warehouses, indicating that older warehouses still dominate in handling large volumes, despite having more issues.

💡 Business Recommendation:
Redistribute shipping loads to newer and more efficient warehouses to avoid overburdening older ones. Build the operational capacity of newer warehouses to take over distribution loads.

📊 Insight 7:
The more transport issues encountered, the lower the shipping volume. Warehouses or routes with fewer transport problems dominate in shipping volume.

💡 Business Recommendation:
Evaluate and optimize areas or routes with many transport issues. Improvements may involve better road infrastructure, transportation modes, or changing logistics vendors.

Below are highlights from the dashboard or click this link for complete interactive Power BI dashboard.

Note: Zoom-out its dashboard for a better experience. 40% zoom percentage is ideal. The zoom slider is in the lower right corner of the dashboard.