Optimization problem for Logistic and Supply chain management

This article discusses optimization problems in logistic and supply chain management with solved examples. It covers the calculation of actual recycling capacity, distribution matrix, constraints for maximizing garbage capacity, and objective functions for minimizing cost. The article also suggests increasing plant capacity to maximize garbage recycling and provides solutions for MOLP.

Optimization problem
1 | P a g e
Logistic and Supply chain management

Optimization problem
Contents
Solution 1.........................................................................................................................................3
Solution1(a).................................................................................................................................3
Solution1(b).................................................................................................................................7
Solution1(c).................................................................................................................................8
Solution 2.........................................................................................................................................9
Solution 3.......................................................................................................................................10
Solution 4.......................................................................................................................................14
References......................................................................................................................................18
2 | P a g e

Optimization problem
Solution 1
Solution1(a)
As per given problem, the site where recycling is to be done, will be done according their
capacity at maximum stage and their efficiency at which the plant is running, as per given
question, the actual recycling capacity will be the multiplication of their capacity and efficiency
at which the plant is running, in this condition, the actual maximum capacity will be as given
Recycling Site
1 2 3 4 5
Capacity
(Megaton) 10 7 15 12 6
Efficiency 0.35 0.45 0.25 0.75 0.55
Actual capacity 3.5 3.15 3.75 9 3.3
Now Suppose the number of given sector is and site is j, and X,i,j is the amount of garbage
collected from sector I and disposed to the site j for recycling purpose.
The sector i lies between 1 ≤i ≤10
And similarly, site j lied between 1 ≤ j≤ 5,
In this condition, we must consider the following distribution matrix
Sector Recycled site
1 2 3 4 5
1 X11 X 12 X 13 X 14 X 15
2 X 21 X 22 X 23 X 24 X 25
3 X 31 X 32 X 33 X 34 X 35
4 X 41 X 42 X 43 X 44 X 45
5 X 51 X 52 X 53 X 54 X 55
6 X 61 X 62 X 63 X 64 X 65
7 X 71 X 72 X 73 X 74 X 75
8 X 81 X 82 X 83 X 84 X 85
9 X 91 X 92 X 93 X 94 X 95
10 X 101 X 102 X 103 X 104 X 105
3 | P a g e

Optimization problem
And as per above table the estimated recycled garbage, as per the plan, the garbage will be
recycled as per following constraint.
X11 +X12 + X13 + X14 +X 15 ≤ 4.6 …………(i)
X21 + X22 + X23 +X24 + X25 ≤ 4.6 …………(ii)
X31 + X32 + X33 +X34 + X 35 ≤ 4.7 …………(iii)
X 41+ X42+ X43+ X44 + X45 ≤ 4.2 …………(iv)
X51 + X52 + X53 +X54 + X 55 ≤3.8 …………(v)
X61 + X62 + X63 +X64 + X65 ≤ 3.9 …………(vi)
X71 + X72 + X73 +X74 + X 75 ≤3.4 …………(vii)
X 81+ X 82+ X 83+ X84+ X85 ≤ 3.3 …………(viii)
X 91+ X 92+ X 93+ X94 + X95 ≤3.9 …………(ix)
X101 + X102+ X103+ X104+ X105 ≤ 4.1 ……. (x)
Additionally, there are some other set of constraint is also conserved for maximising garbage
capacity, which is being recycled at each site.
X11 +X21 + X31 +X41 + X51 +X61 + X71 + X81 + X91 + X101 ≤ 3.5 ………(xi)
X12 + X22 + X32 +X42 + X52 + X62 +X72 + X82 + X92 + X102 ≤ 3.15 ………(xii)
X13 + X23 + X 33+X 43+ X53+ X63+ X73+ X83 +X93 + X103 ≤ 3.75 ………(xiii)
X14 + X24+ X34 + X 44+ X54 +X64 +X 74+ X84 +X94 + X104 ≤ 9 ……….…(xiv)
X15 + X25 + X 35+X 45+ X55+ X65+ X75+ X85 +X95 + X105 ≤ 3.3 ……….…(xv)
In this condition the objective function can be defined as per the following function.
Suppose first objective function is (F1) which is to be maximize garbage consumption.
F1 ( Max )= ( X11+ X12+ X13+ X14 + X15 ) + ( X 21+ X22 +X23 +X 24+ X25 ) + ( X31 + X32 + X33 + X34 +X35 ) + ( X41+ X42 + X43 + X 44+
…….(xvi)
4 | P a g e

Optimization problem
To find the optimise solution we must arrange all the data and run the excel solver which is as
follows.
Recycling Site
1 2 3 4 5
Capacity
(Megaton) 10 7 15 12 6
Efficiency 0.35 0.45 0.25 0.75 0.55
Actual capacity 3.5 3.15 3.75 9 3.3
By multiplying capacity with efficiency, we will calculate Actual capacity for each site.
1 1 2 3 4 5
Estimate
d
recycling
garbage
Sector 1 24 10 34 52 65 4.6
2 17 15 58 64 62 4.6
3 10 20 26 66 60 4.7
4 18 25 32 57 62 4.2
5 11 22 15 55 62 3.8
6 29 34 46 54 43 3.9
7 34 43 69 43 40 3.4
8 38 42 36 53 34 3.3
9 22 29 46 53 50 3.9
10 22 46 50 42 58 4.1
The distribution table as given as above.
1 2 3 4 5 SUM
Estimate
d
recycling
garbage
Daviatio
n
Sector 1 0 0 0 1.3 3.3 4.6 4.6 0
2 0 0 0 4.6 0 4.6 4.6 0
3 0 0 1.6 3.1 0 4.7 4.7 0
4 0 2.05 2.15 0 0 4.2 4.2 0
5 2.7 1.1 0 0 0 3.8 3.8 0
6 0.8 0 0 0 0 0.8 3.9 0.79487
5 | P a g e

Optimization problem
2
7 0 0 0 0 0 0 3.4 1
8 0 0 0 0 0 0 3.3 1
9 0 0 0 0 0 0 3.9 1
10 0 0 0 0 0 0 4.1 1
SUM 3.5 3.15 3.75 9 3.3
0.47948
7
The Objective function is being set with the use of SUM in excel sheet. Which is to be
minimised by changing the yellow cell. The excel solver is set as per following details.
After running the solver, the maximum value of garbage recycling occurred is 22.5 megaton. In
which sector 8,9, and 10 were not utilised. It means that the recycling of site is over capacities by
only seven sectors, for more garbage recycling we must increase the plant capacity.
For the same case the cost for recycling garbage were calculated as per following objective
function.
6 | P a g e

Optimization problem
Solution1(b)
The second objective function will be sum of each site multiplied by cost for each site and this
will be given as
F2 ( Min ) =109603∗( 24 X 11+10 X12 +34 X13 +52 X14 +65 X15 ) + ( X 1721 +15 X22+ 58 X23+ 64 X24 +62 X25 ) + ( 10 X31 +20
……(xvii)
After running the solver, the result is as follows
SUM
Estimate
d
recycling
garbage
Deviatio
n
Sector 1 0.690833 0.308333 0.375833 0.900833 0.330833 2.6066667 4.6 0.433333
2 0.008333 0.308333 0.375833 0.900833 0.330833 1.9241667 4.6 0.581703
3 2.7425 0.375 0.3675 0.8925 0.3225 4.7 4.7 0
4 0.008333 0.308333 0.375833 0.900833 0.330833 1.9241667 4.2 0.541865
5 0.008333 0.308333 0.375833 0.900833 0.330833 1.9241667 3.8 0.49364
6 0.008333 0.308333 0.375833 0.900833 0.330833 1.9241667 3.9 0.506624
7 0.008333 0.308333 0.375833 0.900833 0.330833 1.9241667 3.4 0.434069
8 0.008333 0.308333 0.375833 0.900833 0.330833 1.9241667 3.3 0.416919
9 0.008333 0.308333 0.375833 0.900833 0.330833 1.9241667 3.9 0.506624
10 0.008333 0.308333 0.375833 0.900833 0.330833 1.9241667 4.1 0.530691
SUM 3.5 3.15 3.75 9 3.3 0.444547
The optimized cost for running and transporting the garbage to the plant is calculated as $
104294470. The overall average deviation from estimated plan is around 44%, same data for
previous case was around 47%.
7 | P a g e

Optimization problem
Solution1(c)
MOLP
From both problems, the concern agency should increase the plant capacity, and in this case
when plant capacity in tripled. The calculated value from excel solver is as follows.
The % deviation will be calculated as D1= Target value−actual
target and d2= Actual−Target
target
The new constraints will d1 W 1 ≤ Q
d2 W2 ≤ Q
d2 W2=3 W 2
The calculated cost for given condition is $104294500, and deviation from estimated recycling is
around 0%. Almost all sites and plant were utilised in this calculation.
SUM
Tar
get
Val
ue
Deviatio
n
Wei
ght
Weigh
ted %
deviat
ion
Sector 1
0.3500
001
0.3
15 0.375 0.9
0.
33
2.2700
003 4.6
0.50652
1674 1
0.506
522
2
0.3500
001
0.3
15 0.375 0.9
0.
33
2.2700
003 4.6
0.50652
1674 1
0.506
522
3
0.3500
001
0.3
15 0.375 0.9
0.
33
2.2700
003 4.7
0.51702
1213 1
0.517
021
4
0.3500
001
0.3
15 0.375 0.9
0.
33
2.2700
003 4.2
0.45952
3738 1
0.459
524
5
0.3500
001
0.3
15 0.375 0.9
0.
33
2.2700
003 3.8
0.40263
15 1
0.402
632
6
0.3500
001
0.3
15 0.375 0.9
0.
33
2.2700
003 3.9
0.41794
8641 1
0.417
949
7
0.3500
001
0.3
15 0.375 0.9
0.
33
2.2700
003 3.4
0.33235
2853 1
0.332
353
8
0.3500
001
0.3
15 0.375 0.9
0.
33
2.2700
003 3.3
0.31212
1121 1
0.312
121
9
0.3500
001
0.3
15 0.375 0.9
0.
33
2.2700
003 3.9
0.41794
8641 1
0.417
949
10
0.3500
001
0.3
15 0.375 0.9
0.
33
2.2700
003 4.1
0.44634
139 1
0.446
341
SUM
3.5000
01
3.1
5
3.750
001
9.000
001
3.
3
22.700
003 40.5
0.43950
6099 3
1.318
518
8 | P a g e

Optimization problem
The current scenario of MOLP result concludes that, the combined effect of MOLP is not
changing from individual effect of running solver for Cost and tonnage. In this condition it can
be suggested that, we must increase the recycling capacity by three times. If we increase the
efficiency then we can fulfil the requirement without increasing he capacity.
Solution 2
The given problem for minimising distance with the help of coordinate system which is already
given, we must arrange all the station data as per in question. The distance between the ware
house and required point is calculated with the help of d= √ ( y2− y1 ) 2+ ( x2−x1 ) 2.
The total distance from the station will be the objective function for this problem. By changing
the cell value of ware1, 2 and 3. The given problem is arranged in excel sheet as follows
Suburb X Y
Warehous
-1
Warehous
-2
Warehous
-3 Distance
Ascot Vale 25 13.8 1 0 0 3.00 1
Avondale
Heights 19.7 14.2 1 0 0 5.06 1
Brooklyn 18.2 9.4 1 0 0 5.76 1
Burnside 10.7 16.2 0 0 1 7.96 1
Caroline Springs 9.7 16.8 0 0 1 8.37 1
Derrimut 10.7 10.2 0 0 1 2.62 1
Flemington 24.3 11.8 1 0 0 0.92 1
Footscray 22.4 11 1 0 0 1.30 1
Footscray 23.7 11.1 1 0 0 0.00 1
Hopper Crossing 6.3 4.7 0 0 1 4.49 1
Laverton north 13.5 7.2 0 0 1 4.95 1
Melbourne 28.6 8.9 1 0 0 5.37 1
Seabrook 10.9 2.3 0 0 1 6.56 1
Southbank 29.8 8.1 1 0 0 6.80 1
ST kilda 30.4 3.4 1 0 0 10.21 1
Sunshine west 16.6 10.2 1 0 0 7.16 1
Tarneit 5.2 8.1 0 0 1 3.54 1
tarneit 5.1 6.6 0 0 1 4.08 1
Werribee 0.5 0 0 1 0 0.96 1
Wyndham Vale 0 2 0 1 0 1.11 1
9 | P a g e

Optimization problem
Warehous-1 23.70 11.10 Distance 90.21366
Warehous-2 0.27 0.93
Warehous-3 8.72 8.49
The excel solver is applied as given below
After running the solver, the value calculated as 90.213 km.
Solution 3
The selection of by David Jones depends upon the four different criteria, which is price, safety,
economy and comfort, we must use analytical hierarchy process, which is as follows.
First, we must find the geometric mean of all the factors given in the question with the formula
G.M. = (factor1*factor2*…)1/n, or in excel we can use geomean function to find the
geometrical mean which is as follows.
10 | P a g e

Optimization problem
Price Safety
Econom
y
Comfor
t G.M.
Price 1
0.16666
7
0.33333
3 0.2
0.32466791
5
Safety 6 1 4 2
2.63214802
6
Economy 3 0.25 1
0.33333
3
0.70710678
1
Comfort 5 0.5 3 1 1.65487546
The priority vector is calculated based on geometrical mean with the help of formula GM factor /
total GM, which is as follows
Price Safety
Econom
y
Comfor
t G.M. PV
Price 1
0.16666
7
0.33333
3 0.2
0.32466791
5
0.06104
2
Safety 6 1 4 2
2.63214802
6
0.49487
6
Economy 3 0.25 1
0.33333
3
0.70710678
1
0.13294
5
Comfort 5 0.5 3 1 1.65487546
0.31113
7
We must check the consistency of the given data, i.e. it is valid or not. For this we must find the
consistency ratio by multiplying the sum of all factor by respective PV.
Price Safety
Econom
y
Comfor
t G.M. PV
Price 1
0.16666
7
0.33333
3 0.2
0.32466791
5
0.06104
2
Safety 6 1 4 2
2.63214802
6
0.49487
6
Economy 3 0.25 1
0.33333
3
0.70710678
1
0.13294
5
Comfort 5 0.5 3 1 1.65487546
0.31113
7
Sum 15
1.91666
7
8.33333
3
3.53333
3
5.31879818
2 1
SUM PV
0.91562
4
0.94851
3
1.10787
4
1.09935
1
11 | P a g e

Optimization problem
The sum of all PV value is known as lambda (Max) λ.
Further we must find consistency index with the formula CI = λ−n
n−1 , which is given below
Price Safety
Econom
y
Comfor
t G.M. PV
Price 1
0.16666
7
0.33333
3 0.2
0.32466791
5
0.06104
2
Safety 6 1 4 2
2.63214802
6
0.49487
6
Economy 3 0.25 1
0.33333
3
0.70710678
1
0.13294
5
Comfort 5 0.5 3 1 1.65487546
0.31113
7
Sum 15
1.91666
7
8.33333
3
3.53333
3
5.31879818
2 1
SUM PV
0.91562
4
0.94851
3
1.10787
4
1.09935
1
λ (max)
4.07136
2
CI
0.02378
7
CR 0.02643
The consistency ratio is decided for n = 4 as 0.9 from the table given below.
N RI
1 0
2 0
3 0.58
4 0.9
5 1.12
6 1.24
7 1.32
8 1.41
9 1.45
Now we must find the CR for all four criteria as given above, which will be look like as given
below
For price,
PRICE X Y Z G.M PV
X 1 0.33333 4 1.10064 0.26275317
12 | P a g e

Optimization problem
3 2 1
Y 3 1 7
2.75892
4
0.65862996
6
Z 0.25
0.14285
7 1
0.32931
7
0.07861686
3
SUM 4.25 1.47619 12
4.18888
3
Sum PV
1.11670
1
0.97226
3
0.94340
2
λ (max)
3.03236
7
CI
0.01618
3
RI 0.58
CR
0.02790
2
For Safety,
Safety X Y Z G.M. PV
X 1
0.33333
3 2 0.87358
0.21025155
5
Y 3 1 8
2.88449
9
0.69423533
8
Z 0.5 0.125 1 0.39685
0.09551310
7
Sum 4.5
1.45833
3 11 4.15493
Sum PV
0.94613
2
1.01242
7
1.05064
4
λ (max)
3.00920
3
CI
0.00460
1
RI 0.58
CR
0.00793
3
For Economy,
Economy X Y Z G.M. PV
X 1
0.16666
7
0.33333
3
0.38157
1
0.10252951
6
Y 6 1 0.33333 1.25992 0.33854500
13 | P a g e

Optimization problem
3 1 2
Z 3 3 1
2.08008
4
0.55892548
3
Sum 10
4.16666
7
1.66666
7
3.72157
6
Sum PV
1.02529
5
1.41060
4
0.93154
2
λ (max)
3.36744
2
CI
0.18372
1
RI 0.58
CR 0.31676
For Comfort,
Comfort X Y Z G.M. PV
X 1 0.125 0.25 0.31498
0.07892004
5
Y 8 1
0.33333
3
1.38672
3
0.34745099
7
Z 4 3 1
2.28942
8
0.57362895
8
Sum 13 4.125
1.58333
3
3.99113
1
Sum PV
1.02596
1
1.43323
5
0.90824
6
λ (max)
3.36744
2
CI
0.18372
1
RI 0.58
CR 0.31676
After calculating CR for all the 4 criteria, the summarised PV is as given below,
Price Safety
Econom
y
Comfor
t
Final
Score
PV of
Factors
0.06104
2
0.49487
6
0.13294
5
0.31113
7
X
0.26275
3
0.21025
2 0.10253 0.07892
0.15827314
2
Y 0.65863
0.69423
5
0.33854
5
0.34745
1
0.53687725
9
14 | P a g e

Optimization problem
Z
0.07861
7
0.09551
3
0.55892
5
0.57362
9
0.30484959
9
As we can see from the table that the comfort and economy, both has CR value greater than 0.1.
It means that the data for both the criteria is inconsistence and therefore, it is not considered as
decision making criteria. But rest two criteria are constant. After adding all the PV, it was found
that the Y model is most appropriate for his requirement criteria, therefore, David Jones must
choose Y model.
Solution 4
To find the best location for new warehouse with the help of TOPSIS in excel, we have to
proceed as given below.
The given data is as follows
Criteria Weight
Smithfield,
(NSW)
Eagle Farm,
QLD
Derrimut,
VIC Objective
Land Cost 4 5 7 6 Min
Labour Cost 6 7 6 6 Min
Labour
availability 8 6 7 5 Max
Construction cost 5 5 6 7 Min
Transportation 5 6 6 7 Max
Access to
customers 9 8 7 7 Max
Long Range goals 7 5 7 6 Max
The possible three location with seven criteria is as given below
Criteria
Lan
d
Cost
Labour
Cost
Labour
availabili
ty
Constructi
on cost
Transportati
on
Access to
customers
Long
Range
goals
Weight 4 6 8 5 5 9 7
Smithfiel
d, (NSW) 5 7 6 5 6 8 5
Eagle
Farm,
QLD 7 6 7 6 6 7 7
15 | P a g e

Optimization problem
Derrimut,
VIC 6 6 5 7 7 7 6
Objective Min Min Max Min Max Max Max
The number of ideal attributes which must be maximised and number of worst attributes which is
to be minimised is as given below.
Ideal 4 6 8 5 7 9 7
Worst 7 7 6 7 5 7 5
In this condition we must converts the minimization criteria as given below
Minimising criteria must be converted
Criteria
Lan
d
Cost
Labour
Cost
Labour
availabilit
y
Constructio
n cost
Transportatio
n
Access to
customer
s
Long
Range
goals
Smithfield
, (NSW) 2 0 6 2 6 8 5
Eagle
Farm,
QLD 0 1 7 1 6 7 7
Derrimut,
VIC 1 1 5 0 7 7 6
Now normalising all the matrix by dividing the square root of sum of square of all the cells as
given below.
Normalisi
ng
2.2360
68
1.4142135
62
10.488088
48
2.2360679
77 11
12.727922
06
10.488088
48
The normalised matrix is given as follows
Normed
matrix
Criteria
Land
Cost
Labour
Cost
Labour
availabili
ty
Construct
ion cost
Transporta
tion
Access to
customer
s
Long
Range
goals
Smithfie
ld,
0.8944
27
0 0.572077
554
0.8944271
91
0.54545454
5
0.628539
361
0.476731
295
16 | P a g e

Optimization problem
(NSW)
Eagle
Farm,
QLD 0
0.707106
781
0.667423
812
0.4472135
95
0.54545454
5
0.549971
941
0.667423
812
Derrimu
t, VIC
0.4472
14
0.707106
781
0.476731
295 0
0.63636363
6
0.549971
941
0.572077
554
Further we must multiply each of the criteria with related weight given on the question. After
this we will get ideal solution and negative ideal solution. The ideal solution we will get by
picking the max value and for negative ideal solution by picking the mean value.
Weighted Normed matrix
Criteria
Land
Cost
Labour
Cost
Labour
availabili
ty
Construct
ion cost
Transporta
tion
Access to
customer
s
Long
Range
goals
Smithfiel
d, (NSW)
3.5777
09 0
4.576620
428
4.472135
955
2.7272727
27
5.656854
249
3.337119
062
Eagle
Farm,
QLD 0
4.242640
687
5.339390
5
2.236067
977
2.7272727
27
4.949747
468
4.671966
687
Derrimut,
VIC
1.7888
54
4.242640
687
3.813850
357 0
3.1818181
82
4.949747
468
4.004542
875
Ideal
3.5777
09
4.242640
687
5.339390
5
4.472135
955
3.1818181
82
5.656854
249
4.671966
687
Worst 0 0
3.813850
357 0
2.7272727
27
4.949747
468
3.337119
062
From Ideal Condition
Criteria
Land
Cost
Labour
Cost
Labour
availabilit
y
Construct
ion cost
Transporta
tion
Access to
customer
s
Long
Range
goals
Smithfiel
d, (NSW) 0
4.242640
687
0.762770
071 0
0.4545454
55 0
1.334847
625
Eagle
Farm,
QLD
3.5777
09 0 0
2.236067
977
0.4545454
55
0.707106
781 0
Derrimut,
VIC
1.7888
54 0
1.525540
143
4.472135
955 0
0.707106
781
0.667423
812
From
worst
conditio
n
Criteria
Land
Cost
Labour
Cost
Labour
availabilit
y
Constructi
on cost
Transportat
ion
Access to
customers
Long
Range
goals
Smithfie
ld,
3.5777
09
0 0.7627700
71
4.4721359
55
0 0.7071067
81
0
17 | P a g e

Optimization problem
(NSW)
Eagle
Farm,
QLD 0
4.2426406
87
1.5255401
43
2.2360679
77 0 0
1.3348476
25
Derrimut
, VIC
1.7888
54
4.2426406
87 0 0
0.45454545
5 0
0.6674238
12
Now for each ideal and non-ideal condition, we must calculate the distance with the help of
square root of sum of all square,
di+
Criteria Ideal Rank
Smithfield, (NSW)
4.53544
4 2
Eagle Farm, QLD
4.30193
1 3
Derrimut, VIC
5.14516
5 1
di-
Criteria
Non-
Ideal Rank
Smithfield, (NSW)
5.82080
9 3
Eagle Farm, QLD
5.20663
9 2
Derrimut, VIC
4.67461
9 1
After ranking the closeness in both criteria, it was found that, Derrimut, VIC is the appropriate
location for given condition.
18 | P a g e

Optimization problem
References
Anon, (2010), Business Statistics. 5th ed. New York: Barron's .
Anon, (2010), Contemporary decision making. 7th ed. Houstan: Wiley.
Reeve, J, M, (2012), Business analysis using excel. 2nd ed. Mason, Ohio: South-Western
Cengage Learning.
Shuqin, Y, (2005) Applications of Excel in teaching Statistics. Cal-laborate, 1(1), pp. 1-4.
Ward, J, (2010), Data analysis and reporting. 1st ed. Birmingham, UK: Packt Pub..
19 | P a g e