Solver allows the user to set (1) an objective cell, (2) constraints, and (3) variable cells. In this model, we want to find the combination of planning areas whose waste should be sent to each region that would minimise the cost of transporting waste from the whole island to the selected region. The AreaIDs of the 53 planning areas were first entered into Excel as the first column.
Setting up the variable cells
Column A contains the variable cells in this model. They are binary cells (i.e. only values of 0 and 1) which will represent if waste from a planning area is sent to Region 1. A value of 1 would mean that waste generated in that planning area is to be sent to Region 1, and vice versa. Column F is the complementary binary of column A - that is, waste that is not sent to Region 1 must be sent to Region 2.
Weight, distance and weighted distance columns
Columns B and G contain the same values, which are the respective weight of waste generated in each planning area. Column C is obtained by multiplying the weight (B) with the least cost distance from each planning area to Region 1. Column H is analogous, but for Region 2 - that is, weight (G) multiplied by least cost distance from each planning area to Region 2.
Making constraints
Column D is the product of columns A and B. The sum of the cells in this column, SUM(D), gives the total amount of waste to be sent to Region 1. This sum is used to set the constraints, which is that the total waste sent to each region should not exceed the region's daily capacity by too much. For example, if the new incineration plant were to be sited in Region 1, giving it a total capacity of 6,200 t/d, the constraint that we set was 6050 < SUM(D) < 6350. This upper range was allowed as some excess waste, above a region's capacity, can still be accommodated by the incineration plants working slightly harder. The lower range was set to restrict the amount of waste that is sent to Region 2 - in other words, there must be a minimum amount that should be sent to Region 1. Column I - product of F and G - is analogous to column D, but for Region 2. SUM(I) gives the total amount of waste to be sent to Region 2.
Completing the optimisation problem with constraints
Column E is the product of columns A and C. The sum of the cells in this column, SUM(E), gives the total weighted cost to send waste from selected planning areas (defined by column A) to Region 1. Column J - product of F and H - and SUM(J) are analogous to column E and SUM(E) respectively, but for Region 2.
A special cell - [SUM(I) x 0.1 x Dist] - deserves special mention. This cell gives the weighted cost to send waste from Region 2 to Region 1, as the incineration ash from Region 2 - which should be 10% of the input waste amount, thus 0.1 - has to be sent to Region 1, where the Tuas Marine Transfer Station (TMTS) is located. It is at this TMTS where incineration ash is collected and subsequently ferried from the main island to the offshore Semakau Landfill. 'Dist' represents the least cost distance between Region 2 and Region 1.
The sum of the green cells SUM(E), SUM(J) and [SUM(I) x 0.1 x Dist] is the objective cell. This is marked in the above diagram as 'SUM OF GREEN CELLS'. It is the total weighted costs of transporting waste from the planning areas to the respective regions where their waste should be sent to, as defined by columns A and F. Solver was set to minimise the value in this cell.
Running the model
We let the model run under the "Evolutionary" Solving Method. There is no particular reason for choosing this method, except that the other two available options, GRG Nonlinear and Simplex Linear Problem, do not work [1]. When running the model, we encountered the problem of computational limitations. The total number of possible combinations that Solver has to test is in the order of 15, which takes too long to compute fully. We thus made a compromise and ran the model for two hours for both scenarios (identified in Part 1). The intermediate trial answer obtained for the cases tested within the two hours was used to prepare our final maps.
[1] If you are aware of why this is so, please do enlighten us! We can be reached here, thank you.
appendix
more about our Solver model...
