Supply Chain Optimization

Project Overview

This project focuses on optimizing supply chain management by minimizing the difference between scheduled delivery dates and actual delivery dates. The primary objective is to select the optimal shipment mode and vendor for each brand while adhering to constraints related to shipment weight and freight costs for each country. The entire solution is built using Django and the PuLP library for linear programming optimization.

Key Features

Data Cleaning: Automated preprocessing of raw shipment data to ensure consistency and accuracy.
Optimization: Implementation of a mathematical model to optimize shipment routes using the PuLP library.
Result Presentation: Exporting optimized results to an Excel sheet with proper formatting for easy analysis.

Technical Stack

Backend Framework: Django
Optimization Library: PuLP
Database: MySQL
Data Processing: Pandas, Django ORM
Excel Export: OpenPyXL

Project Details

1. Data Cleaning

Before running the optimization algorithm, it was crucial to clean the data to ensure that all necessary fields were in the correct format:

Freight Cost Adjustments: Rows where the weight or freight cost was listed as "Freight Included in Commodity Cost" were converted to 0.
Row Deletions: Any rows containing "Invoiced Separately" or "Weight Captured Separately" were removed from the dataset.
ID Replacement: In cases where the weight_kg or freight_cost_usd fields contained "ID#:", the corresponding numeric values were extracted and matched with the correct rows.

# Example code snippet for data cleaning

ShipmentRoute.objects.filter(weight_kg__contains="Freight Included in Commodity Cost").update(weight_kg='0')

# Further data cleaning logic...

2. Data Conversion

The cleaned data was then converted to ensure that all fields were in numeric format, which is necessary for the optimization algorithm:

routes = ShipmentRoute.objects.annotate(weight_kg_num=Cast('weight_kg', FloatField()),

freight_cost_usd_num=Cast('freight_cost_usd', FloatField())).filter(weight_kg_num__isnull=False,

freight_cost_usd_num__isnull=False)

3. Optimization Algorithm

The core of this project is a linear programming optimization model that aims to minimize the difference between scheduled and actual delivery dates. The model includes constraints to ensure that the weight of shipments and freight costs for each country stay within specified limits:

# Example code snippet for the optimization process

prob = LpProblem("Shipment Optimization", LpMinimize)

# Objective function and constraints definition...

prob.solve()

4. Result Export

The optimized results are exported to an Excel sheet for further analysis, with proper formatting to enhance readability:

# Example code snippet for exporting to Excel

df = pd.DataFrame(optimized_results)

df.to_excel(output_file, index=False)

# Formatting the Excel sheet...

Challenges and Solutions

Data Inconsistency: The raw data contained inconsistent formats and non-numeric values, which required thorough cleaning and transformation.
- Solution: Implemented a robust data cleaning pipeline using Django ORM to filter, update, and convert data to the appropriate types.
Optimization Complexity: Balancing multiple constraints while minimizing the objective function was computationally challenging.
- Solution: Utilized PuLP for linear programming, efficiently solving the optimization problem while adhering to all constraints

Final Output

The final output was an optimized set of shipment routes, which were exported to an Excel sheet with clear formatting, making it easy for stakeholders to interpret the results.

Conclusion

This project demonstrates the power of combining Django with mathematical optimization libraries like PuLP. It showcases how complex real-world problems, such as supply chain management, can be effectively tackled using a structured approach to data processing, algorithm design, and result presentation.