Skip to content

Warehouse Capacity Utilization

Warehouse Optimization and Cost Minimization Analysis

Project Overview

This project aimed to optimize warehouse distribution and minimize transportation costs using Excel Solver, VBA automation, and Tableau for visualization. I was given 100 different scenarios of warehouse supply and store demand, and I applied Solver in Excel to generate optimized warehouse allocations for each scenario. I then automated this process using VBA, extracting key metrics such as total cost, capacity utilization, and demand distribution. The final analysis and visualization were conducted using Tableau.

Findings and Insights

Through my analysis of the 100 scenarios, I identified key relationships that influenced warehouse efficiency and cost management:

  1. Total Cost Variance Across Scenarios

    • The scenario with the highest cost exceeded $7,500 (Scenario 80), while the lowest cost scenario was approximately $3,500 (Scenario 82).
    • Poor capacity utilization led to inefficiencies and higher costs, while balanced warehouse utilization distributed costs effectively.

  2. Capacity Utilization’s Impact on Cost
    • Over-utilization of certain warehouses resulted in increased costs due to inefficiencies.
    • In Scenario 80, Warehouse 3 reached 100% utilization, leading to bottlenecks, while in Scenario 82, a balanced utilization of 42% in Warehouses 1 and 3 and 89% in Warehouse 2 resulted in lower costs.

    3. Warehouse and Store Allocation Patterns
    • No single warehouse consistently supplied all stores; however, Warehouse 2 played a crucial role in fulfilling demand for multiple stores.
    • Warehouse 3 was the primary supplier for Store 1, while Warehouse 2 handled most of the demand for Stores 2, 3, and 4.


    4. Total Demand and Cost Correlation

    • Higher total demand typically led to increased costs, though efficient warehouse management mitigated some of the cost impact.
    • Poor capacity utilization combined with high demand resulted in significantly higher costs.


    5. Cost Variance Analysis

    • A direct correlation between transportation cost and cost variance was observed. Higher transportation costs resulted in greater cost variance due to inefficiencies in warehouse distribution.

Methodology

I structured my analysis using a combination of Excel Solver, VBA, and Tableau for data extraction, processing, and visualization:

  1. Solver Optimization in Excel

    • Solver was used to minimize total transportation cost while meeting store demand and warehouse supply constraints.
    • Constraints included ensuring total warehouse supply did not exceed capacity and that store demands were met efficiently.

  2. VBA Automation

    • VBA was implemented to run Solver across all 100 scenarios, automating the optimization process.
    • The script extracted key metrics such as total cost, demand fulfillment, cost variance, and capacity utilization.

  3. Data Visualization in Tableau

    • Horizontal Bar Charts were used to compare total costs across scenarios and analyze capacity utilization among warehouses.
    • Scatter Plots illustrated relationships between transportation costs, cost variance, and total demand.
    • Warehouse-to-Store Allocation Charts helped identify trends in store reliance on specific warehouses.

Business Recommendations

Based on my findings, I propose the following recommendations to optimize warehouse operations and minimize costs:

  1. Improve Warehouse Utilization Balance

    • Redistribute demand across warehouses to prevent overutilization of a single facility, ensuring more balanced workload distribution.
    • Implement dynamic inventory management strategies, such as cross-warehouse transfers, to optimize capacity usage.

  2. Mitigate Risks from Warehouse Over-Reliance

    • Since Stores 2, 3, and 4 heavily depend on Warehouse 2, an operational disruption in this facility could significantly impact supply chains.
    • Diversify store reliance by redistributing inventory across multiple warehouses.

  3. Optimize Transportation Costs through Route Planning

    • Group shipments for stores in close proximity to reduce the number of transportation trips.
    • Pre-position inventory near high-demand locations during peak seasons to minimize transportation delays and costs.

  4. Analyze High-Cost Scenarios to Identify Inefficiencies

    • Conduct root cause analysis on scenarios where costs were unexpectedly high despite similar demand levels.
    • Investigate warehouse inefficiencies, such as delays in processing and reliance on costly transportation methods.

Conclusion

This project provided valuable insights into warehouse efficiency, transportation cost minimization, and demand fulfillment. Through my Solver-based optimization, VBA automation, and Tableau visualizations, I identified cost-saving opportunities and inefficiencies in warehouse utilization. Implementing my recommendations can enhance operational efficiency, reduce costs, and improve overall supply chain performance.