Problem:
The borough of Millersville has received a federal grant of $600,000 to expand its public recreation facilities. City council representatives have demanded four different types of facilities- gymnasiums, athletic fields, tennis courts, and swimming pools. In fact, the demand by various communities in the city has been for 7 gyms, 10 athletic fields, 8 tennis courts, and 12 swimming pools. Each facility costs certain amount, requires a certain number of acres, and is expected to be used a certain amount, as follows:
The Parks and Recreation Department has located 50 acres of land for construction (although more land could be located, if necessary). The department has established the following goals, listed in order of their priority:
|
Facility
|
Cost
|
Required Acres
|
Expected Usage
(people/week)
|
|
Gymnasium
|
$80,000
|
4
|
1,500
|
|
Athletic field
|
$24,000
|
8
|
3,000
|
|
Tennis court
|
$15,000
|
3
|
500
|
|
Swimming pool
|
$40,000
|
5
|
1,000
|
(1) The department wants to spend the total grant because any amount not spent must be returned to the government.
(2) The department wants the facilities to be used by a total of at least 20,000 people each week.
(3) The department wants to avoid having to secure more than the 50 acres of land already located.
(4) The department would like to meet the demands of the city council for new facilities. However, this goal should be weighted according to the number of people expected to use each facility.
Required:
Question 1) Formulate a goal programming model to determine how many of each type of facility should be constructed to best achieve the city's goals.
Question 2) Solve this model by using the computer so that the solution values are integers.
Describe your problem fully and attach your Excel worksheet. Provide correct solution of the given problem with step by step calculations.