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?
Facility-Development.doc
IT2012Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
d-glitchCommented:
There is a tutorial on Linear Programming with Excel here:

     http://it.usu.edu/plugins/work/sitemaps/107/files/Linear_Programming_Using_Excel.pdf

I'm not sure what a QM template is.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jose ParrotGraphics ExpertCommented:
Hi,

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...

Jose
Jose ParrotGraphics ExpertCommented:
Hummm... Seems the file wasn't attached... lets try again.
Solver-Optimization.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
knspCommented:
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
knspCommented:
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?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Project Management

From novice to tech pro — start learning today.