Streamlining Transportation. Network design with Excel and Python
Tags: machinelearning, msexcel, python, problemsolving
Published: 2023-08-28 08:02:46

**Intro**:
The transportation problem, a classic optimization challenge, revolves around determining the optimal distribution of goods from multiple sources to numerous destinations while minimizing overall transportation costs. 

**Scenario**:
In the Dusty Outpost, Second Chance Recycling has two major collection centers, A and B, each with different maximum capacities for trailers in a week. Collection center A can hold up to 280 trailers, while collection center B can accommodate up to 360 trailers.

The process starts with trailers from the collection points moving to a sortation station, where they will be sorted and re-consolidated. The sorted materials will then be sent to two recycling plants: Revive plant and Reborn plant. The capacity of Revive plant is 305 trailers, and the capacity of Reborn plant is 325 trailers.

To optimize the flow of goods, we need to consider the cost of moving trailers between each location. The costs are as follows:

| Transportation leg         	| Cost / Trailer 	|
|----------------------------	|----------------	|
| Collection A to Sortation  	| $ 13.58        	|
| Collection B to Sortation  	| $ 16.54        	|
| Sortation to Revive        	| $ 7.57         	|
| Sortation to Reborn        	| $ 16.46        	|

Our goal is to find the most cost-effective way to transport the trailers from the collection centers to the two recycling plants through the sortation station. However, it's essential to remember that the sortation station cannot hold any trailers.

By optimizing this flow efficiently, Second Chance Recycling can minimize costs, reduce transportation inefficiencies, and contribute to a more sustainable recycling process in the Dusty Outpost.

**Mathematical Problem**:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4auc27u29eh6gz9kfc7p.png)


1.**Decision Variable**:
The aim is to find the quantity of trailer (positive integer) between each leg 

{% katex %} qty_{i} {% endkatex %}

where i - trailer count for every leg j

2.**Objective Function** :

{% katex %} \sum_{i=1}^{4} qty_{i} *cost_{j} {% endkatex %}

where <u>cost</u> is the cost per trailer 

3.**Constraints**:
a. The first leg of the network is to ensure that maximum number of trailers from the plants would be capped to the capacity of the collection plant

{% katex %} Leg1_qty_{i} \leq Col_Capacity_{i} {% endkatex %}

b. The second leg of the network is to ensure that maximum number of trailers from the sortation center to  plant should  the capacity of the collection plant

{% katex %}  Leg2_qty_{i} \geq Plant_Capacity_{i} {% endkatex %}

c. Flow capacity is to enforce that what sum of trailers inbound to sortation plant should be sum of the trailers leaving / outbound of the sortation plant

{% katex %}  \sum Leg1_qty_{i} = \sum Leg2_ qty_{i} {% endkatex %}

## Method 1: Excel Solver

The Decision variable are # of Trailer for each transportation leg. (Highlighted in Yellow cells)

The object function is to minimise the total Cost of transportation (i.e, # trailers * cost for the individual leg)

The constraint 1: The Trailer for each leaving collection point  < Capacity of each collection point (Supply capacity constraint)

The constraint 2: The Sum of all trailer for each recycling plant should be > Capacity of recycling plant (Demand)

The constraint 3: The flow constraint is Sum of all Trailer inbound to sortation = Sum of all trailer outbound 


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3kzojkli455q36yf3r9n.png)

**Result of Solver**:
Objective Functions: 17249.75

| Transportation leg         	| Trailer 	|
|----------------------------	|---------	|
| Collection A to Sortation  	| 280     	|
| Collection B to Sortation  	| 350     	|
| Sortation to Revive        	| 305     	|
| Sortation to Reborn        	| 325     	|


## Method 2: Python PULP 

```
import pulp as op

# Create the problem
prob = op.LpProblem("TrailerOptimization", op.LpMinimize)

# Decision variables
x1 = op.LpVariable("x1", lowBound=0, upBound=None, cat='Integer')
x2 = op.LpVariable("x2", lowBound=0, upBound=None, cat='Integer')
x3 = op.LpVariable("x3", lowBound=0, upBound=None, cat='Integer')
x4 = op.LpVariable("x4", lowBound=0, upBound=None, cat='Integer')

# Set the objective function
prob += 13.58 * x1 + 16.54 * x2 + 7.57 * x3 + 16.46 * x4

# Add constraints to the environment
prob += x1 <= 280
prob += x2 <= 360
prob += x3 >= 305
prob += x4 >= 325
prob += x1 + x2 == x3 + x4

# Solve the problem (other solvers: prob.solve(op.SOLVERNAME()))
prob.solve()

# The status of the solution
print("Status:", op.LpStatus[prob.status])

# Print the solution values
print('Solution:')
print('Objective value =', op.value(prob.objective))
print('x1 =', x1.varValue)
print('x2 =', x2.varValue)
print('x3 =', x3.varValue)
print('x4 =', x4.varValue)
```

`INTEGER OPTIMAL SOLUTION FOUND Status: Optimal
Solution: Objective value = 17249.75
x1 = 280, x2 = 350, x3 = 305, x4 = 325`

**Summary**:
Each tool brings its own set of advantages, and your choice will ultimately depend on the complexity and scale of your transportation optimization needs.