MSC 615 Spring 2021 Assignment 6 1. A metropolitan area has three reservoirs A, B, and C. The water is sent through a network to a water treatment plant, T, before being distributed to customers. Following table shows the daily capacity of (one way) pipelines between connection points on the network. a. The water company managing the network wants to know the maximum daily flow that can be sent through the network from three reservoirs to the water treatment plant. (1) What model can be used to answer the companys question? (2) Describe the model by doing the following: first identify source, sink, and transshipment nodes of the network; then draw a complete network showing the capacity of each arc following the notations used in the chapter. (Use the shapes function in Excel to do the drawing.) b. Formulate the problem above on a spreadsheet; Using an appropriate analytic tool to solve the problem optimally. What is the amount of flow sent from each reservoir in your optimal solution? c. Since the daily demand for water is usually less than the maximum capacity of the network, the company wants to use an economic water distribution plan to node T when the demand is low. Water demand of the area varies between 200 and 300 units. Following table shows the unit cost of shipping each unit of flow through each arc. From\To D E F G H I T A 12 4 15 B 3 14 3 12 C 10 9 18 D 8 E 12 12 F 3 7 G 18 H 15 I 4 From\To D E F G H I T A 75 65 30 B 40 50 60 40 C 80 70 60 D 45 E 55 45 F 100 90 G 120 H 190 I 130 (1) What would you recommend to the company as to how to supply the water from reservoirs to the treatment plant? To answer the question, analyze the optimal plans and costs when the demand is at different levels: 200, 220, 240, 260, 280, and 300. This requires modeling and solving multiple problems, and summarizing the results. (2) Bonus: should the company charge higher or lower per unit usage of water when demand is higher? Justify your answer by analyzing the work done in question (1). d. If the daily cost for shipping water to the treatment plant is budgeted at (no more than) $5,000, What is the maximum amount of water that the treatment plant can receive each day? Formulate the problem as an appropriate model and solve it using appropriate tool; make a recommendation to the company.