In the water blending problem, the city is trying to determine how much water to
ID: 3208122 • Letter: I
Question
In the water blending problem, the city is trying to determine how much water to pump from each of the three sources to minimize cost while satisfying hardness standard. Using the Excel Solver sensitivity report (shown below) to answer the following questions:
Project
Department
Expected Profit
(x 100,000)
Engineers needed
1. Northampton water plant.
Environmental
$8
3
2. Blanford water plant.
Environmental
$2
2
3. Springfield compost facility.
Environmental
$1
1
4. Ludlow Bridge
Structures
$6
5
5. Longmeadow municipal building
Structures
$5
4
6. Ludlow traffic safety plan
Transportation
$3
2
7. Williamstown bike trail.
Transportation
$1.2
1
There are some constraints on the projects to be bid on:
a. A total of 10 engineers are available at the firm.
b. At least one project should be selected in each department (Environmental, Structures and Transportation).
c. At most one water plant can be bid on - either Northampton or Blanford.
d. One and only one project can be selected from Projects 1, 5, and 7.
e. The Ludlow traffic safety plan can be bid on only if the Ludlow Bridge is bid on.
f. The Blanford water plant can be bid on only if either the Ludlow Bridge or Longmeadow municipal building project is bid on.
Formulate a binary linear programming problem to select the mix of projects that will result in the maximum profits for the firm
Can someone help me set up the solver on excel for this problem?
Project
Department
Expected Profit
(x 100,000)
Engineers needed
1. Northampton water plant.
Environmental
$8
3
2. Blanford water plant.
Environmental
$2
2
3. Springfield compost facility.
Environmental
$1
1
4. Ludlow Bridge
Structures
$6
5
5. Longmeadow municipal building
Structures
$5
4
6. Ludlow traffic safety plan
Transportation
$3
2
7. Williamstown bike trail.
Transportation
$1.2
1
Explanation / Answer
Let us assume that x,y & z water pumps are needed from the three sources.
Objective function is :
Max Z = 11X + 11Y +4.2Z
Subject to the constraints,
x+y+z<= 10,
x<=3,
y<=2,
z<=2