Streamlining Transportation. Network design with Excel and Python

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

$$qty_{i}$$

where i - trailer count for every leg j

2.Objective Function :

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

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

$$Leg1_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

$$Leg2_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

$$\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

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.

Comments

[david wyatt]

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

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