**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**:

1.**Decision Variable**:

The aim is to find the quantity of trailer (positive integer) between each leg

where i - trailer count for every leg j

2.**Objective Function** :

where __cost__ 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

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

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

## 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

**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.

## Top comments (2)

With the new Excel and Python integration could you create a third hybrid approach?

Need to try the new Excel PY function. let me try and update if it works... But the point of Excel Solver is the low-code element for non-coder to proto-type the model and then pass for software delivery team to built to SCALE !!!