Solver

Adrae

Active Member
Joined
Feb 19, 2002
Messages
306
Does anyone know how to make Solver take into account a ratio when determining profit maximization. I have four items and the ratio of Item 1 to 2 should be 7 to 5.

Any ideas?

Thanks :) Adrae
This message was edited by Adrae on 2002-10-24 10:18
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
You just integrate that ratio into your model. Care to share your model for discussion purposes?
 

Adrae

Active Member
Joined
Feb 19, 2002
Messages
306
Sure - here is an example:

1. Four juices are produced - OJ, GJ, PJ, All-in-One
2. The All-in-One contains equal parts of OJ, GJ, PJ
3.Contraints: 100 quarts of OJ; 75 quarts of GJ; 50 quarts of PJ
4. Cost per quart: $2 for OJ; $1.60 for GJ; $1.40 for PJ
5. Prices: $1 for OJ; $.90 for GJ; $.80 for PJ; $1.10 for All-in-One
6. GJ should be no more than 30% of total output
7. the ratio of the number of OJ quarts produced and number of PJ quarts produced should be 7 to 5.

It's this last that is giving me headaches. I'm not at all sure how to incorporate it.

Any ideas?

Thanks
 

Adrae

Active Member
Joined
Feb 19, 2002
Messages
306

ADVERTISEMENT

Oh right, the price and costs are on a per gallon.

Thanks :)
Adrae
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
Is the Cost per Quart or Gallon? See #4 above. What about the UOM of the constaints?
This message was edited by Mark W. on 2002-10-24 14:44
 

Adrae

Active Member
Joined
Feb 19, 2002
Messages
306

ADVERTISEMENT

Cost per gallon.
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
On 2002-10-24 14:35, Adrae wrote:
Oh right, the price and costs are on a per gallon.

Thanks :)
Adrae

Hey, with the Price and Cost listed above you'd be better off not producing anything!!! You're losing $$$ on everything you produce.
This message was edited by Mark W. on 2002-10-24 14:47
 

Adrae

Active Member
Joined
Feb 19, 2002
Messages
306
Sorry - I've gotten confused. I should have put everything in quarts.

As it is:

PRICE is per QUART
COST is per GALLON
UOM of constraints is QUARTS

In Solver, I had converted everything to quarts, so instead of what I listed above for cost, let's use the following:

$.50 for OJ
$.40 for GJ
$.35 for PJ

Do you know how I can incorporate the ratio of 7 to 5?

Thanks :) Adrae
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
I did this in a hurry... my stomach is growling. Your production appears to be constrained by GJ. Also, I wasn't sure how to interpet the constraint listed as #6 above since GJ is also a component of All-in-One. Use with caution!!! ...But this should get you started.
Juice.xls
ABCDEFGHIJKL
1JuiceLimitQuantityPriceCostProfitModel:
2OJ500$1.00$0.50$0.00$115.00
3GJ7525$0.90$0.40$12.503
4PJ500$0.80$0.35$0.00TRUE
5All-in-One150$1.10$0.42$102.50TRUE
6Total175$115.00TRUE
730%ofTotal52.5TRUE
8TRUE
9100
10
Sheet2

This message was edited by Mark W. on 2002-10-24 15:14
 

Watch MrExcel Video

Forum statistics

Threads
1,122,233
Messages
5,594,963
Members
413,954
Latest member
mrsandy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top