DEV Community

Cover image for Streamlining Transportation. Network design with Excel and Python
Bala Madhusoodhanan
Bala Madhusoodhanan

Posted on

Streamlining Transportation. Network design with Excel and Python

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.

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

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 :

i=14qtyicostj\sum_{i=1}^{4} qty_{i} *cost_{j}

where cost is the cost per trailer

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

Leg1qtyiColCapacityiLeg1_qty_{i} \leq Col_Capacity_{i}

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

Leg2qtyiPlantCapacityiLeg2_qty_{i} \geq Plant_Capacity_{i}

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

Leg1qtyi=Leg2qtyi\sum Leg1_qty_{i} = \sum Leg2_ qty_{i}

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

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()))

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

# Print the solution values
print('Objective value =', op.value(prob.objective))
print('x1 =', x1.varValue)
print('x2 =', x2.varValue)
print('x3 =', x3.varValue)
print('x4 =', x4.varValue)
Enter fullscreen mode Exit fullscreen mode

Solution: Objective value = 17249.75
x1 = 280, x2 = 350, x3 = 305, x4 = 325

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)

wyattdave profile image
david wyatt

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

balagmadhu profile image
Bala Madhusoodhanan

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