The county of Orchard is growing in population, and the public works department predicts the need for an increased water supply. There are five towns in the county, named Appletown, Berrytown, Cherrytown, Grapetown and Mangotown.
Currently, the water is drawn from two reservoirs (Majorlake and Minorlake) on local rivers of good quality but insufficient quantity. Future sources, the ones to be tapped next, have various problems. Water from a nearby aquifer is available in large quantities, but its hardness is too high unless it is blended with a lower hardness source. The maximum allowed total mass of hardness in kilograms per million liters delivered to each town is 1,200 kg/ML.
• •
• •
•
Water from a distant stream is of adequate quality, but a pipeline has to be built, which results in a high cost to pump the water to the treatment plant.
The city is planning for the predicted demand ten years from the present. Table 1 lists the expected daily water needs of the five towns at that time in million liters (ML):
Table 1: Expected Daily Water Needs (in ML) in ten years Town Appletown Berrytown Cherrytown Grapetown Mangotown Water Needs 30 10 50 20 40
The four sources are numbered as follows: 1- Majorlake, 2- Minorlake, 3- Aquifer and 4- the distant stream. The supply limits (i.e. max) in millions of liters per day, and the hardness in kilograms per million liters are given in Table 2:
Table 2: Characteristics of Water depending on the Source Source 1 Source 2 Source 3 Source 4 Supply Limit per day (ML) 15 10 60 80 Hardness (kg/ML) 250 200 2300 700
PAGE 1Table 3 provides the costs to obtain water in dollars per million liters, depending on the source and the town. For example, the cost of providing water from Source 3 to Berrytown is 715 $/ML (in bold in Table 3).
Table 3: Cost of providing water, depending on the source and the town ($/ML) Appletown Berrytown Cherrytown Grapetown Source 1 360 370 350 355 Source 2 420 425 430 435 Source 3 700 715 685 720 Source 4 2000 2015 1995 1985
Mangotown 365 415 725 2020
Figure 1 shows the layout of the pipes between the sources and the towns:
Figure 1: Layout of Pipes between Sources and Towns (not to scale).
Note that the pipes linking source 1 and source 2 to the supply network merge downstream of sources 1 and 2. For structural reasons, flow in the common pipe must not exceed 20 ML.
Questions
1. Using your own engineering intuition, guess how much water should be distributed from each source to each town to minimize cost? Does your guess satisfy all the constraints? Note: Your answer is expected to be incorrect! Do not waste too much time on this question (max five minutes). The point is to see how much better you can do with optimization tools. Full credit is given for any solution with a reasonable explanation. (2 Mark)
2. Formulate a Linear Program (LP) that the county of Orchard can use to minimize the costs of obtaining its water needs in ten years, while satisfying the supply capacity, demand, hardness, and structural constraints described above. (a) Define your mathematical notation. Be precise. (2 Mark) (b) Using this notation, formulate (i) the objective function and (ii) all the constraints. Should the supply constraints be equalities or inequalities? Explain and formulate accordingly. (3 Marks)
PAGE 2(c) Solve the LP that you have formulated using Excel Solver. Provide the value of the objective function (total cost) and the value of the decision variables (flows from sources to towns) at the optimum. Describe qualitatively the optimal solution and compare to your guess. (4 Marks)
The purpose of the following questions is to study the influence of changes in the parameter values or system structure compared to the original Linear Program solved in the previous question. Each question and subquestion is independent from the other ones, i.e. the changes are not cumulative. Where applicable, provide the value of the objective function as well as the value of the decision variables.
3. The actual demand and supply limits may differ from the forecasts mentioned earlier. (a) Solve the Linear Program with the demand of water for each town 5% lower than the forecast described earlier. Explain what happens (no more than three sentences). (1 Mark) (b) Solve the Linear Program with the demand of water for each town 5% higher than the forecast described earlier. Explain what happens. (1 Mark) (c) Consider when the daily supply limits for sources 3 and 4 are 95 and 75 ML respectively (instead of 60 and 80 ML). Solve the Linear Program and explain what happens. (1 Mark)
4. Kiwi Inc., a large factory, is outside the county of Orchard but close to Berrytown. It does not currently receive any water from the county of Orchard. The factory would like to buy water from the county of Orchard in the future (ten years). The factory accepts to be responsible for the investment to join the water supply network of the county of Orchard, and is ready to sign a contract with the county to buy 1 ML for the price of $1,500 per day. The county of Orchard expects to incur the same costs to supply the water to the factory as it does to supply the water to Berrytown. Should the county of Orchard accept these conditions? We assume that the County does not want to lose money when supplying water. (2 Marks)
5. The county of Orchard has the option to increase the supply limit of Source 1 by adding a small supporting structure. The small structure needed to increase the limit by 2 ML per day would have to be replaced every year and costs $5,000. We can assume that this yearly cost is equivalent to a daily cost of 5000/365 ˜ $14. Should the county choose this option? Note that this small structure does not change the flow limit in the common pipe downstream of Sources 1 and 2. (2 Marks)
6. The county of Orchard has the option to increase the supply limit of Source 3. What is the maximum daily cost the county should be willing to pay in order to increase the supply limit of Source 3 (in the same manner as in the previous question, we can assume that the cost for upgrading the facility can be viewed as a daily cost)? (2 Marks)
Deliverables
Submit the following on Blackboard. Zip your files. Be sure that the function files are named exactly as specified (including spelling and case). EXAM1_Lastname_G#.PDF EXAM1_Lastname_G#.XLSX