CIVE 202 Introduction to Numerical Modeling and Optimization
Purpose: The purpose of this project is to demonstrate the concept of optimization modeling by minimizing the cost associated with maintaining a healthy fish population in a river. Participants will gain experience in designing and implementing optimization models using spreadsheet software. Participants will use the Solver tool in Excel as well as VBA programming to create custom macros and userforms.
Problem Description: Suppose you work for an international development agency that is trying to improve river water quality in countries with emerging economies such as China and India. Many cities in such countries do not have wastewater treatment systems, so they discharge untreated sewage directly into rivers. This wastewater uses dissolved oxygen (DO) in the river as it decomposes. Fish also need DO to survive. If the DO becomes very low, then the fish will die.
Two options are available to improve the DO in rivers. The first option is to augment the flow. The city can lease water rights to ensure that a certain flow is always available in the river. Higher flows dilute the pollution and help maintain higher DO levels. The other option is to treat the wastewater. Wastewater treatment plants partially decompose the waste, which lowers the “biochemical oxygen demand” (BOD) of the wastewater being discharged in the river. The BOD is the DO that is required to decompose the wastewater. Of course, as more water is leased and more treatment is performed, the costs increase.
You have been asked to develop an optimization model in Excel that determines the minimum cost solution for this type of problem. Your model should be applicable to cities with different populations and growth rates, and it should consider both current and future conditions. Your model should also be applicable to rivers with different lengths, and it should be able to accept a specified minimum acceptable DO value and the minimum acceptable flow in the river. Any VBA code (macros and functions) you use in your model must be appropriately documented with comment statements.
The objective function for the model is the total cost, which is the sum of the leasing cost and the treatment cost. Formulas for the costs are given in the companion PowerPoint presentation. The treatment cost depends on the percent treatment and the wastewater flow rate (the wastewater flow rate depends on the current population). The leasing cost depends on the amount of water that is being leased. The control variables in the optimization problem are the amount of water that is leased and the percentage treatment of the wastewater. Constraints for the optimization problem include the following:
(1) The amount of water leased cannot exceed 150 cfs. That is the maximum available water.
(2) The total flow in the river must not drop below 50 cfs. This minimum flow is required to maintain a healthy habit for the fish.
(3) The minimum DO in the river must not drop below the specified lower limit (usually 6 mg/l).