Solved

Linear Programming

Posted on 2012-04-03
12
962 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
  • 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 250 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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
LVL 18

Expert Comment

by:JoseParrot
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:
JoseParrot earned 250 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:JoseParrot
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

815 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now