Linear Programming

I would like advise on approach, excel QM template using Sover:

Kent County plans to develop several new recreational facilities that must be completed within the $3.5-million budget. A survey of county residents has given information about the type of facilities that county residents would like to see built. The information is described in the following table. Specifically, this table provides the cost to construct and maintain each facility, the acres each facility will require, and the average monthly use of each facility. The county has decided that at least 15 facilities will be built and has set aside 55 acres for construction.

Table Horizotal Headings
1. Facility      
2. Cost per Facility      
3. Acres per Facility      
4. Use in People per Month      
5 Annual Maintenance

1. Basketball Courts      
2. $300,000      
3. 3      
4. 700      
5. $3,000

1. Baseball Fields      
2. $250,000      
3. 5      
4. 1,000      
5. $6,000

1. Playgrounds      
2. $75,000      
3. 2      
4. 800      
5. $3,000

1. Soccer Fields      
2. $175,000      
3. 3      
4. 1,200      
5. $7,000

The county has also established the following list of prioritized goals:

P1: The county would like to spend the entire budget.

P2: The county would like to build enough facilities so that 15,000 people or more each month can use them.

P3: The county does not want to use more than the 55 acres set aside for the project.

P4: The county does not want to spend more than $80,000 per year on maintenance costs for the new facilities.

How many of each type of facility should be constructed?
Who is Participating?
Jose ParrotConnect With a Mentor Graphics ExpertCommented:
Hummm... Seems the file wasn't attached... lets try again.
IT2012Author Commented:
Hello Aikimark, I'm not sure what your comment means. It is my first posting to the site is there a problem with my posting?
IT2012Author Commented:
Airimark, Oh Great! Yes solver is a big part of the question. In fact if I knew what template to use I could probably do the question. Thank you for your quick response, I did think I did something wrong.
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

d-glitchConnect With a Mentor Commented:
There is a tutorial on Linear Programming with Excel here:

I'm not sure what a QM template is.
Jose ParrotGraphics ExpertCommented:

Follows an Excel approach with the solver.
First, lets create the problem table, as in A3:E7.
The decision cell will be calculated Total Cost (cell G12)
Then input the constraints.
Here I have include upper and lower limits, such that the solver will work in pre-defined ranges. This is a hint to the optimization of the solution as you decide.
For exemple, you have results like to support 17K users, by spending $78K/year or to attend 16K users with $74K/year. You can refine the solution by narrowing the ranges...

The optimized is  5 Basketball Courts  & 4 each of the rest.
The  Annual Maintenance is 1000 less and People per Month is 500 more to what the forecast is.

                                                                           Totals       Difference
2. Cost per Facility            1500000      1000000      300000      700000      3500000        0
3. Acres per Facility            15      20      8      12      55                  0
4. Use in People per Month     3500      4000      3200      4800      15500
5 Annual Maintenance      15000      24000      12000      28000      79000
Sorry I misread your question and thought that you wanted an answer for your issue.
Jose ParrotGraphics ExpertCommented:
Basketball :6      Baseball:2      Playgrounds :9           Soccer: 3
is one of the possible solutions which satisfies the conditions. It has the advantege of requiring $ 2000 less for maintanance and to support 1000 more people monthly. As told before, the more we narrow the ranges, the better the solution.

Is the proposed solver parameters/constraints what you are looking for?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.