A city must increase its water supply by 150 million gallons/day (mgd) in response to popu...

A city must increase its water supply by 150 million gallons/day (mgd) in response to population growth. Three alternative sources of supply are available A river flowing through the city provides a low-cost, good-quality source of supply However cause the city is already obtaining most of its existing supply from this source, themaximum available additional supply is limited to 25 mgd Up to 120 mgd may may beobtained from a groundwater aquifer, but the quality is not as good as surface water. The hardness of the ground water is 2,300 pounds/million gallons (lbs/mgal.) A river/reservoir system located a significant distance from the city provides a third altentative source, which is very expensive. The maximum amount of water available, unit cost, and hardness for each of the three alternative sources are provided below.  Hardness consists primarily of calcium and magnesium. Water quality standards limit the maximum allowable hardness to 1,200 lbs/mgal.

Formulate an LP model to determine the amount of water to obtain from each of the three alternative sources that minimizes costs while limiting the hardness of the combined supply to not exceed 1,200 lbs/mgal. Use Excel or another software package to obtain the optimum solution.