Solved

Linear Programming

Posted on 2012-04-03
12
955 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
 
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

A short article about problems I had with the new location API and permissions in Marshmallow
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

757 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

19 Experts available now in Live!

Get 1:1 Help Now