Spreadsheet MILP
Transhipment problem
By Lance SO
CTA, MM SCM, SCMb 2019
The excel file I use
One Drive: https://1drv.ms/x/s!AhJtd6y5CclXgeldJQeaLpdvcahkEg Please download (it is read only)
https://docs.google.com/spreadsheets/d/15kQzextqhbzBmxY7zmK8DuPcawXE529GLA0Gpr7caTM/edit?usp=sharing (Google Drive)
Open Solver
This demo use Win10 Excel 365 and open solver
To download or learn more about open solver visit
https://opensolver.org/
OpenSolver has been developed for Excel 2007/2010/2013/2016 (including the 64bit versions) running on Windows, and supports Excel for Mac 2011 on Mac OS
X, with limited support for Excel for Mac 2016. We currently test against Excel 2010/2013/2016 on Windows 7 and Windows 10, and Excel 2011/2016 on OS X
10.7 through 10.11
Open Solver also available to Google Sheet as an add-on
Step 1: Set-up Data
Use Control C (copy); Control
V(paste) command;
Paste special - Transpose help
you change the layout of the
data to fit your need
Step 2: Set up variables
The problem ask the total cost from Factories to DC via Crossdock.
The decision variables are quantity flow through each transportation
arc
1. Between factory and Crossdock
2. Between Crossdock and DC
Tips: They should be same shape of your cost data!
Step 3 - Set-up objective functions
The objective of this problem is to MINIMIZE cost;
so the objective box is the total cost equation.
Which is the sum of the quantity in each
transportation arc times the respective cost
In spreadsheet, we could use sumproduct function
to construct this.
In complex problem you could breakdown your cost
into components equation for easier debugs
Step 4i Set-up Supply Constraints
The capacity, in totes, of the five factories are as follows
From the keyword Capacity, we know it is a maximum constraint (<=)
The LHS of the constraint is the sum of all transportation arc from each
factory to all CrossDocks
The RHS is given by the question as capacity limit of each factory
Step 4ii Demand Constraints
The demand, in totes, of each of the five regional DCs are as follows:
From the keyword demand, we know it is a minimum (or equal) constraint (>= or
=)
The LHS of the constraint is the sum of all transportation arc from each
crossdock to all DC
The RHS is given by the question as Demand of each DC
Step 4iii Conservation of flow constraint
In this single period Transhipment problem the inflow
must equal (=) to out flow of each transhipment
facilities (no left over to next period)
The LHS of the constraint is SUM of inflow from all
factory to each crossdock minus SUM of outflow
form each Crossdock to ALL factory
(the paste transpose of data in step 1 make this
operation easier)
RHS is 0
Step 5 config solver
You should installed Solver (I recommend Open solver)
Objective: is a SINGLE Cell contain the objective function is step2;
Variable: are the range of cells contain all variables defined in
step 3
Constraints: are all step 4 rules plus the integer rules, binary rules
and >=0 rule for the variables (apply common sense)
Sensitivity: If question ask sensitivity, output it to separate
worksheet - This only work for LP, not for MILP
Solver Engine: For MILP any linear engine will do
Save model to exit the config page (solve directly if you are using
excel solver)
Check your model and Solve
Open solver will color code your model for easy
checking if you made any mistakes
After checking press solve and you should have
your answer.
Read carefully if there is any error message
The excel file I use
One Drive: https://1drv.ms/x/s!AhJtd6y5CclXgeldJQeaLpdvcahkEg (it is read only, you could download)
https://docs.google.com/spreadsheets/d/15kQzextqhbzBmxY7zmK8DuPcawXE529GLA0Gpr7caTM/edit?usp=sharing
Other Topics not covered in this PP
- Binary variables
- Linking constraint
- Fix cost
Extra Topics: Binary variable and Link Constraint
The idea of a Link Constraint is to force the binary
to be 1 when the resources is being use.
Step 1 is to declare a set of binary variables
(yellow boxes) and Select “Bin” as a constraint for
this cell range.
Step 2 define constant M which is at least total
demand, our case is 740
Step 3 The constraint is Inflow-M*Bin<=0; When
there is no Inflow, the Binary could be 0, when
there is Inflow, the binary have to be 1
After define the binary and Linked constraint, this
could be use as other Constraint (like min max
facilities use)
Extra Topics: Fixed cost
For Fixed cost problem
You first define the fixed cost equation, it
would be SUMPRODUCT form the
fixcost*Bin variable.
Then your objective function cell should
include all cost from transportation and fix
cost from factory open.
Update your Solver config and solve with
the new solution.
Thank you for reading
Please post your questions or feedback to below post
https://courses.edx.org/courses/course-
v1:MITx+CTL.SC0x+3T2018/discussion/forum/80278941d0e7f0eb9b2431ff68adbac6c5287dcf/threads/5ba879a1a522030
9530036a6
