Solved

LP Model help!

Posted on 2011-02-13
11
194 Views
Last Modified: 2012-05-11
I've been having a difficult time with figuring out a problem using solver to figure our the following and come up with a graph.

First product pound contains 20% cp, 20% rb and 15% mb
Second product pound contains 30% cp, 25% rb and 10% mb

First product costs $90 for each pound
Second product costs $120 for each pound

I want to buy enough of both products to get at least 8 pounds of cp, 6 pounds of rb, and 5 pounds of mb while paying the least. For this, I'm using an LP model and trying to sketch a region but it's not making much sense as well as the optimal price.

Thank you in advance.
0
Comment
Question by:faithless1
  • 5
  • 4
  • 2
11 Comments
 
LVL 32

Accepted Solution

by:
phoffric earned 400 total points
Comment Utility
Could you make this two questions - one for the minimization, and one for the graph? I'll then try to see if I can help with one of these questions. Also, since this question appears to be academic in nature, I will try to provide as much guidance as I am allowed to per EE guidelines.

Does 28 and 8 pounds for first and second items seem right to you?
0
 

Author Comment

by:faithless1
Comment Utility
That is precisely what it's supposed to be. Unfortunately I am having difficulties with properly utilizing excel to produce this information with a graph. Thank you in advance.
0
 
LVL 32

Assisted Solution

by:phoffric
phoffric earned 400 total points
Comment Utility
Sorry, I haven't figured out how to create a graph yet. Perhaps you could ask this graph as a separate question. I only figured out the answer using solver. Or, if that is the primary requirement, then you can leave this as the question. What kind of graph are you looking for?

BTW - is this for academic or self-study purposes?
0
 

Author Comment

by:faithless1
Comment Utility
Perfect, thanks. Solver usage was my main problem so thanks for that. I've been out of prison for some time now, just need this for a business plan I'm working on. For the graph I just need optimums for both amounts along with constraints. I should be able to figure the graph so no worries if it requires a lot more time. Thanks again.
0
 
LVL 32

Assisted Solution

by:phoffric
phoffric earned 400 total points
Comment Utility
Here is the form of the spreadsheet in preparation in using the Solver:

Purchase of cp, rb, and mb products                        

Objective

    A                B           C          D
Minimize Cost        0 

Open in new window

             
Decision Variables                        
Pounds of first      0                
Pounds of second     0 

Open in new window

             
Constraints                        
Product Name        % cp        % rb        % mb
Actual              0.00        0.00        0.00
Required at least   8.00        6.00        5.00


Pounds of 1st >= 0:    0           0        
Pounds of 2nd >= 0:    0           0 

Open in new window

           
Facts
Product Name        % cp        % rb        % mb
First               0.20        0.20        0.15
Second              0.30        0.25        0.10

Open in new window

                       
                       
Breakdown of components in Pounds                        
Product Name        # cp        # rb        # mb
First               0.00        0.00        0.00
Second              0.00        0.00        0.00
Total               0.00        0.00        0.00

Open in new window


Any non-zero numbers are taken from your OP. The 0's in the 3 fields in Objective and Decision Variables are just numbers - not formulas. The two right-most 0's in the last two constraints of the 5 shown are fixed numbers (and the left-most 0's are just the actual pounds purchased which is set to the Decision Variables). The Actual line are formulas based on Decision Variables and the Facts. The Breakdown of components in Pounds are formulas.

The first step in using the Solver is to make sure you have the formulas correct. So, just put in arbitrary values such as 1 and 10 and verify that all the 0's are computed correctly. If you have problems doing that, attach your spreadsheet and indicate which cell is causing the incorrect numbers to appear.

I assume that since you wish to use the Solver, then you are already familiar with Excel formulas.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 32

Assisted Solution

by:phoffric
phoffric earned 400 total points
Comment Utility
>> already familiar with Excel formulas.
should be: already familiar with Excel formulas (Sum) and simple equations. I'll be back in about an hour to check up on the spreadsheet. The gist of using the solver is to select the Minimize Cost cell having the 0, and then hit the Solver button.

If you set up the form as above, then you can then hit the Guess button, and you should see something like:  $B$9:$B$10 corresponding to the two Decision Variables.

Then you have to add the constraints by hitting the Add button (5 times for the 5 constraints). You set the Cell Reference (by pointing to the cell), the equality/inequality operator (by pull-down menu), and the Constraint (by pointing to the cell). For each constraint, hit the Add button except for the last constraint, where you hit the OK button. Then you will see the 5 constraints listed, where you can double-check your work.
0
 
LVL 37

Assisted Solution

by:TommySzalapski
TommySzalapski earned 100 total points
Comment Utility
For the chart I would use an XY scatter plot with lines (this is the best way to get excel to do normal xy plots). You could chart the needed pounds of product 2 given the pounds of product 1 purchased. Then you could plot all the costs.
Something like this (posted Excel file). I assumed you had to purchase whole pounds.

LP.xls
0
 
LVL 37

Expert Comment

by:TommySzalapski
Comment Utility
Note: From a mathematical standpoint, the way to calculate the optimum analytically would be to simply get the cost at each critical point (All product 1, all product 2, and all the intersections of the other lines). In this specific instance you only have one intersection in the possible range (where P1,P2 >=0). So you would only need to check three points.
0
 

Author Comment

by:faithless1
Comment Utility
Thank you!!!! I made a few minor adjustments but essentially this is exactly what I needed! Thanks again, very much appreciated.
0
 

Author Closing Comment

by:faithless1
Comment Utility
Thanks!
0
 
LVL 32

Expert Comment

by:phoffric
Comment Utility
You're welcome. Good luck with your business plan.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
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 simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

763 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

11 Experts available now in Live!

Get 1:1 Help Now