Optimizing Transportation Cost and Recycled Garbage Amount

Solution
Q1)
a) Formulate an multiple-objective linear programming (MOLP) model for this problem in a
Word file with a brief description of an equation, and implement the MOLP model in an
Excel spreadsheet.
Objective
10X1 + 7X2 + 15X3 + 12X4 + 6X5
Constraints
24*$109,603X1 + 10*$109,603X2 + 34*$109,603X3 +52*$109,603 X4 + 65*$109,603X5
≤4.6
17*$109,603X1 +15*$109,603X2 + 58*$109,603X3 + 64*$109,603X4 + 62*$109,603X5
≤ 4.6
10*$109,603X1 + 20*$109,603X2 + 26*$109,603X3 + 66*$109,603X4 + 60*$109,603X5
≤ 4.7
18*$109,603X1 + 25*$109,603X2 + 32*$109,603X3 + 57*$109,603X4 + 62*$109,603X5
≤ 4.2
11*$109,603X1 + 22*$109,603X2 + 15*$109,603X3 + 55*$109,603X4 + 62*$109,603X5
≤ 3.8
29*$109,603X1 + 34*$109,603X2 + 46*$109,603X3 + 54*$109,603X4 + 43*$109,603X5
≤ 3.9
34*$109,603X1 + 43*$109,603X2 + 69*$109,603X3 + 43*$109,603X4 + 40*$109,603X5
≤ 3.4
38*$109,603X1 + 42*$109,603X2 + 36*$109,603X3 + 53*$109,603X4 + 34*$109,603X5
≤ 3.3
22*$109,603X1 + 29*$109,603X2 + 46*$109,603X3 + 53*$109,603X4 + 50*$109,603X5
≤ 3.9
22*$109,603X1 + 46*$109,603X2 + 50*$109,603X3 + 42*$109,603X4 + 58*$109,603X5
≤ 4.1

Total Estimated Recycleble Garbage
x1 x2 x3 x4 x5
Objective Decision 1.28E-07 8.93E-08 1.91E-07 1.53E-07 7.65E-08
Capacity 10 7 15 12 6 7.06647E-06
constrains
sectors
1 2630472 1096030 3726502 5699356 7124195 2.563980263 4.6
2 1863251 1644045 6356974 7014592 6795386 3.194490132 4.6
3 1096030 2192060 2849678 7233798 6576180 2.491282895 4.7
4 1972854 2740075 3507296 6247371 6795386 2.643667763 4.2
5 1205633 2411266 1644045 6028165 6795386 2.126398026 3.8
6 3178487 3726502 5041738 5918562 4712929 2.969407895 3.9
7 3726502 4712929 7562607 4712929 4384120 3.4 3.4
8 4164914 4603326 3945708 5808959 3726502 2.871546053 3.3
9 2411266 3178487 5041738 5808959 5480150 2.864555921 3.9
10 2411266 5041738 5480150 4603326 6356974 2.997368421 4.1
b) Determine the optimal value for each objective in the problem.
X1 = 1.28 * 10-7
X2 = 8.93 * 10-8
X3 = 1.91 * 10-7
X4 = 1.53 * 10-7
X5 = 7.65 * 10-8
c) Suppose the management considers maximising the amount of recycled garbage to be
three times as important as minimising the transportation cost. Formulate a GP model to
optimise both objectives simultaneously with a brief description of an equation in a Word
file, and implement the MOLP model in an Excel spreadsheet. What do the results
suggest?
Objective
10X1 + 7X2 + 15X3 + 12X4 + 6X5

Constraints
24*$109,603X1 + 10*$109,603X2 + 34*$109,603X3 +52*$109,603 X4 + 65*$109,603X5
≤13.8
17*$109,603X1 +15*$109,603X2 + 58*$109,603X3 + 64*$109,603X4 + 62*$109,603X5
≤ 13.8
10*$109,603X1 + 20*$109,603X2 + 26*$109,603X3 + 66*$109,603X4 + 60*$109,603X5
≤ 14.1
18*$109,603X1 + 25*$109,603X2 + 32*$109,603X3 + 57*$109,603X4 + 62*$109,603X5
≤ 12.6
11*$109,603X1 + 22*$109,603X2 + 15*$109,603X3 + 55*$109,603X4 + 62*$109,603X5
≤ 11.4
29*$109,603X1 + 34*$109,603X2 + 46*$109,603X3 + 54*$109,603X4 + 43*$109,603X5
≤ 11.7
34*$109,603X1 + 43*$109,603X2 + 69*$109,603X3 + 43*$109,603X4 + 40*$109,603X5
≤ 10.2
38*$109,603X1 + 42*$109,603X2 + 36*$109,603X3 + 53*$109,603X4 + 34*$109,603X5
≤ 9.9
22*$109,603X1 + 29*$109,603X2 + 46*$109,603X3 + 53*$109,603X4 + 50*$109,603X5
≤ 11.7
22*$109,603X1 + 46*$109,603X2 + 50*$109,603X3 + 42*$109,603X4 + 58*$109,603X5
≤ 12.3

Total Estimated Recycleble Garbage
x1 x2 x3 x4 x5
Objective Decision 0 2.68E-07 4.93E-07 8.91E-07 2.3E-07
Capacity 10 7 15 12 6 2.13476E-05
constrains
sectors
1 2630472 1096030 3726502 5699356 7124195 8.847800131 13.8
2 1863251 1644045 6356974 7014592 6795386 11.3888379 13.8
3 1096030 2192060 2849678 7233798 6576180 9.951034251 14.1
4 1972854 2740075 3507296 6247371 6795386 9.593112499 12.6
5 1205633 2411266 1644045 6028165 6795386 8.390731074 11.4
6 3178487 3726502 5041738 5918562 4712929 9.842830166 11.7
7 3726502 4712929 7562607 4712929 4384120 10.2 10.2
8 4164914 4603326 3945708 5808959 3726502 9.212986667 9.9
9 2411266 3178487 5041738 5808959 5480150 9.774482212 11.7
10 2411266 5041738 5480150 4603326 6356974 9.616331893 12.3
For effective maximising the amount of recycled garbage to be three times as important as
minimising the transportation cost the total capacity should be 2.13476 *10-5