Question

A community council must decide which recreation facilities to construct in its community. Four new recreation facilities have been proposed a swimming pool, a tennis center, an athletic field, and a gymnasium. The council wants to construct facilities that will maximize the expected daily usage by the residents of the community subject to land and cost limitations. The expected daily usage and cost and land requirements for each facility follow.

Recreation Facility	Expected Usage (people per day)	Cost ($)	Land Requirement (Acres)
Swimming pool	300	35,000	4
Tennis center	90	10,000	2
Athletic field	400	25,000	7
Gymnasium	150	90,000	3

The community has a $120,000 construction budget and 12 acres of land. Because the swimming pool and tennis center must be built on the same part of the land parcel, only one of these two facilities can be constructed. The council wants to know which of the recreation facilities to construct to maximize the expected daily usage.

1. Write the Integer programming model for the above problem
2. Solve the problem and give your answers.

Answer 1

Let S, T, A and G be binary decision variables which can take the value of either 1 or 0 for swimming pool, tennis center, athletic field, gymnasium. If the value is 1, that means the facility is constructed and if it is 0, then the facility is not constructed.

We need to maximize the daily usage which is the objective function. Hence the objective function Z is as below

Z = 300S+90T+400A+150G

Now we will model for constraints

As the total budget is 120000, we have the below constraint

35000S+10000T+25000A+90000G $\leq$ 120000

Also as total area is 12 acres, we have the below constraint

4S+2T+7A+3G $\leq$ 12

Also because the swimming pool and tennis center must be built on the same part of the land parcel and only one of these two facilities can be constructed we have the below constraints

S+T $\leq$ 1

This the complete Integer programming model is as below

Maximize Z = 300S+90T+400A+150G

subject to constraints

35000S+10000T+25000A+90000G $\leq$ 120000

4S+2T+7A+3G $\leq$ 12

S+T $\leq$ 1

The problem is formulated in excel as below. The below 2 images show the formulation as well as the formulas used

| B | C | D | ETFIGH STAG 1 Decision Variables 2 Variable 3 Value 4 5 Objective Function 300 90 400 500 35000 10000 25000 90000 0 <= 120000 7 Constraints 8 Budget 9 Land 10 Only swimming pool or tennis center

I STAG 1 Decision Variables 2 Variable 3 Value 4 5 Objective Function 300 400 =SUMPRODUCT(B5:E5,$B$3:$E$3) 25000 120000 7 Constraints 8 Budget 35000 9 Land 10 Only swimming pool or tennis center 1 =SUMPRODUCT(B8:E8, $B$3:$E$3) =SUMPRODUCT(B9:E9, $B$3:$E$3) =SUMPRODUCT(B10:E10, $B$3:$E$3)

Post these the solver parameters need to be entered as per the below image.

Solver Parameters Set Objective: $F$5 To: Max Min O Value Of: By Changing Variable Cells: $B$3:$E$3 Subject to the Constraints: SBS3:SES3 = binary $F$8:$F$10 <= SH58:SH$10 Add Change Delete Reset All Load/Save Make Unconstrained variables Non-Negative Select a Solving Simplex LP Method: Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help Solve Close

Once this is done, click on solve. The solver will solve the problem and show the message stating the same. Click on ok. The excel will now have the final solution as per below image.

BCD E F G H 1 Decision Variables 2 Variable 3 Value 4 5 Objective Function - 300 90 400 150 700 35000 10000 25000 90000 60000 <= 120000 7 Constraints 8 Budget 9 Land 10 Only swimming pool or tennis center 10 only swimming pool or tennis center i of i l le

Thus we can see that S=1 and A=1. Hence the solution is to construct Swimming Pool and Athletic Field.