Streamlining Transportation. Network design with Excel and Python

Optimization Simplified: Excel Solver and Python Illustrated (6 Part Series)

1 Team Selection Strategy: FPL Manager
2 Cracking the Scheduling Puzzle: Solving Scheduling Problems with Operations Research
… 2 more parts…
3 Navigating the Maze: Solving Shortest Path Challenges with Dijkstra’s Algorithm
4 Streamlining Transportation. Network design with Excel and Python
5 Balancing Wants and Constraints: Navigating the Knapsack Problem
6 Leveling Up in OR: Excel Solver vs. 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:

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

q t y i qty_{i} qtyi​

where i – trailer count for every leg j

2.Objective Function :

∑ i = 1 4 q t y i ∗ c o s t j \sum_{i=1}^{4} qty_{i} *cost_{j} i=1∑4​qtyi​∗costj​

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

L e g 1 q t y i ≤ C o l C a p a c i t y i Leg1_qty_{i} \leq Col_Capacity_{i} Leg1q​tyi​≤ColC​apacityi​

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

L e g 2 q t y i ≥ P l a n t C a p a c i t y i Leg2_qty_{i} \geq Plant_Capacity_{i} Leg2q​tyi​≥PlantC​apacityi​

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

∑ L e g 1 q t y i = ∑ L e g 2 q t y i \sum Leg1_qty_{i} = \sum Leg2_ qty_{i} ∑Leg1q​tyi​=∑Leg2q​tyi​

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

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

Enter fullscreen mode Exit fullscreen mode

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.

Optimization Simplified: Excel Solver and Python Illustrated (6 Part Series)

1 Team Selection Strategy: FPL Manager
2 Cracking the Scheduling Puzzle: Solving Scheduling Problems with Operations Research
… 2 more parts…
3 Navigating the Maze: Solving Shortest Path Challenges with Dijkstra’s Algorithm
4 Streamlining Transportation. Network design with Excel and Python
5 Balancing Wants and Constraints: Navigating the Knapsack Problem
6 Leveling Up in OR: Excel Solver vs. Python

原文链接：Streamlining Transportation. Network design with Excel and Python