After graduation, you are
hired as environmental consultant, in charge of pollution control programs in
the state. Your first task is to decide how to reduce pollution in the Momiss
river at minimum cost.
There are three factories on
the Momiss river: 1, 2, 3. Each factory emits two type of pollutants labeled P1
and P2, into the river. If the waste from each factory is processed, the
pollution in the river can be reduced. It costs $150 to process a ton of
factory 1 waste, and each ton processed reduces the amount of P1 by 0.10 ton
and the amount of P2 by 0.45 ton. It costs $100 to process a ton of factory 2
waste, and each ton processed will reduce the amount of P1 by 0.20 ton and the
amount of P2 by 0.25 ton. It costs $120 to process a ton of factory 3 waste,
and each ton processed will reduce the amount of P1 by 0.40 ton and the amount
of P2 by 0.30 ton. The state wants to reduce the amount of P1 in the river by
at least 30 tons and the amount of P2 by at least 40 tons.
NOTE: The
pollutants must be reduced in whole tons. (fractional tons is not possible)
a. Set up the Excel model.
b. Enter your Name, date, and Assignment 3 at the top of
the worksheet.
c. Use Excel Solver to determine the minimum cost of reducing pollution by the desired amount.
d. Create an answer report, detailing the parameters of
the problem and the optimal solution.
Get Free Quote!
279 Experts Online