Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Linear Programming

Posted on 2012-04-03
12
Medium Priority
?
989 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:IT2012
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
12 Comments
 

Author Comment

by:IT2012
ID: 37805260
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?
0
 

Author Comment

by:IT2012
ID: 37805371
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.
0
 
LVL 27

Assisted Solution

by:d-glitch
d-glitch earned 1000 total points
ID: 37805949
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

Expert Comment

by:Jose Parrot
ID: 37875692
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
0
 
LVL 18

Accepted Solution

by:
Jose Parrot earned 1000 total points
ID: 37875702
Hummm... Seems the file wasn't attached... lets try again.
Solver-Optimization.xlsx
0
 
LVL 5

Expert Comment

by:knsp
ID: 37897374
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
0
 
LVL 5

Expert Comment

by:knsp
ID: 37897454
Sorry I misread your question and thought that you wanted an answer for your issue.
0
 
LVL 18

Expert Comment

by:Jose Parrot
ID: 37910563
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?
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
Article by: evilrix
Looking for a way to avoid searching through large data sets for data that doesn't exist? A Bloom Filter might be what you need. This data structure is a probabilistic filter that allows you to avoid unnecessary searches when you know the data defin…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Starting up a Project

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question