Provide a thorough analysis that explains the result set from the Excel run. The detailed analysis should document each process (step) through the analysis and include responses to the following:
1
Binary Linear Optimization Analysis
When we perform a linear optimization analysis, if the value of decision variables can only be either 0 or
1, we call this analysis a binary linear optimization analysis. A binary linear optimization analysis consists
of four major steps: (1) identify the objective variable and decision variables; (2) create the objective
function; (3) find out the constraints; (4) specify the decision variables to be binary. I use an example to
show everyone how we use this technique in a logistics analysis process.
Example:
An online shopping website plans to build distribution centers to serve customers from 20 counties as
shown in the picture below. Each distribution center should serve customers either from the county OR
from the adjacent counties. What is the minimum number of distribution centers needed to cover all 20
counties?
2
To simply the analysis, we give each county an id number. For instance, 1 represents Ashtabula county;
2 represents Lake county, etc.
(1) Identify the objective variable and decision variables
Objective variable: Total number of distribution centers needed to serve these 20 counties. We use N to
denote this number.
Decision variables: To calculate the objective variable, we need to count how many counties have
distribution centers. There are two possibilities in each county: It either has a distribution center OR not
has one. Let
𝑥𝑖 : the number of distribution center in a county;
i: the id number of each county, i ∈ {1, 2, 3, …., 20}
Thus, 𝑥𝑖 = 1 if there is a distribution center in a county; otherwise, 𝑥𝑖 = 0 for that county. For instance, in
Ashtabula county (county id i = 1), 𝑥1 = 1 if Ashtabula county has a distribution center; 𝑥1 = 0 if
Ashtabula county does not have a distribution center. In Lake county (county id i = 2), 𝑥2 = 1 if Lake
county has a distribution center; 𝑥2 = 0 if Lake county does not have a distribution center, etc.
(2) Create the objective function
N = 𝑥1 + 𝑥2 + ⋯ + 𝑥20
Our objective is to minimize N
(3) Find out the constraints
The constraint in this case is that each distribution center must serve customers either from the county
OR from the adjacent counties. Because we don’t know which counties would have the distribution
centers yet, we should build the constraint for each county. For instance, for Ashtabula county, it is
adjacent to Lake county, Geauga county, and Trumbull county, as shown in the map on page 1. No
matter where the distribution center is finally built among these counties, there must be at least one
that serves Ashtabula county. Thus, the constraint for Ashtabula county is
𝑥1 + 𝑥2 + 𝑥12 + 𝑥16 ≥ 1
We should establish such a constraint for each county. For instance, for Lake county, it is adjacent to
Ashtabula county, Cuyahoga county, and Geauga county. Thus, the constraint for Lake county is
𝑥1 + 𝑥2 + 𝑥3 + 𝑥12 ≥ 1
3
For some counties that are adjacent to several other counties, the constraint could be stricter. For
instance, for Geauga county, it is adjacent to 6 counties (Ashtabula, Lake, Cuyahoga, Summit, Portage,
and Trumbull), we have
𝑥1 + 𝑥2 + 𝑥3 + 𝑥10 + 𝑥12 +𝑥13 +𝑥16 ≥ 1
In other words, no matter where the distribution centers are finally built, there must be at least one that
serves the Geauga county.
Using Solver
(1) Open the source data file “Binary in Logistic Management.xlsx”
In the data set, we first build a 20 by 20 matrix to indicate the counties locations in a mathematical way.
We mark 1 if two counties share a border (i.e., adjacent geographically). We mark 0 if two counties do
not share a border.
(2) Objective and Decision variables
We use cells from C32 to V32 to contain the decision variables. If there is a distribution center in a
county, the value for the decision variable will be 1; otherwise, it will be 0.
We use cell D29 to contain the objective variable (i.e., the total number of distribution centers needed).
In D29, we type in
=SUM(C32:V32)
Our goal is to minimize the value in cell D29.
(3) Set up constraints
In cell B35, we type in
=SUMPRODUCT($C$32:$V$32,C6:V6)
4
Note:
=SUMPRODUCT($C$32:$V$32,C6:V6) equals to =$C$32*C6 + $D$32*D6 + $E$32*E6 + … + $V$32*V6
We use this function (i.e., =SUMPRODUCT($C$32:$V$32,C6:V6)) to see if there is at least one
distribution center serving the Ashtabula county.
We then drag the autofill button of B35 to the rest of counties
(4) Set up Solver as shown below. See step (5) of how to set up the constraints.
5
6
(5) How to set up the constraints
Click Add next to the “Subject to the Constraints” box
In the left hand box, select cells from B35 to B54. In the dropdown list in the middle, choose >=, and
type in 1 in the right hand box, and click OK to add the first constraint.
7
Click Add next to the “Subject to the Constraints” box again.
In the left hand box, select cells from C32 to V32. In the dropdown list in the middle, choose bin, and
then the value in the right hand box will change to binary automatically. Click OK to confirm. By doing
so, we specify the decision variables can get either a 0 or a 1 as the value.
8
(6) After clicking the Solve button, choose “Keep Solver Solution”, and click OK.
From the analysis result, we find that the distribution centers should be built in Ashland county, Stark
county, and Geauga county. By doing so, we need a minimum of 3 distribution centers to serve all 20
counties.
As you can see in this example, the binary analysis is a very useful technique for logistic optimization,
location based service, and supply chain management. Please keep this tutorial for your advanced
analytics courses such as Project Management.