solver is not iterating to find optimal lift volume and frequency.

the solvers settings a

minimize cost of lifts across 21 days, cell $J$2,
=SUM(IF(J9:J290,1,0)*(5+0.42*(J9:J29)))
by changing daily vol of lifts across 21 days, cells $J$9:$J$29
subject to constraints:
1)daily tank4 inventory must be <= max vol , $F$9:$F$29 <= $F$5
2)daily tank4 inventory must be = heel vol , $F$9:$F$29 = $F$6
3)daily lift volume must be <= min lift vol , $J$9:$J$29 <= $J$5
4)daily lift volume must be = min lift vol , $J$9:$J$29 = $J$6

deano wrote:
Dear Martin,

A picture is worth a thousand words or in this case an excel file
located at http://availg.com/images/stories/tools/runout_1.xls is. As
you can see neither a minimize frequency of non zero lifts nor minimize
cost of lifts is getting solver to find the right answer. Plz help.

deano


Martin Fishlock wrote:
Hi Deano,

This is a classic situation where you have to maximise profit with multiple
constrainsts and you need to devise a method to combine the constaints to use
the solver

Generally make a cash flow or profit and loss statement with the relevant
costs and see if you can minimise them with the given constaints.
--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"deano" wrote:

Hi Martin,

ok, I added a lifting cost cell and set solver to minimize this cost.

if lift for the day 0, then cost = 5 + 0.42*lift volume. Minimize 21
day sum of lift cost.

The result is same as what I had when I set solver to minimize
frequency of lifts with =COUNTIF(J9:J29,"0") .

Any clues on how to get solver to span out lifts, increase lift size to
minimize fixed cost of 5 with each lift? Is solver the right approach
to this problem?


Martin Fishlock wrote:
You need to include the aspect of the cost of lifts and therefore minimise
the cost of lifts as well as the other constraints.
--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"deano" wrote:

a plant produces any 2 of 3 products (make1, make2, make3). Plant knows
how much to produce over a period of time of each product. normally
product 1 is set to zero. product 2 can be stored in any of three tanks
(tank1, tank2, tank3). product 3 can only be stored in tank4. Tanks
have minimum volumes known as heels and maximum volumes. Liftings of
products (2 & 3) need to be scheduled to maintain steady production
without "runout" of room. Liftings have maximum volume/lifting. Lifting
frequency should be minimized to save cost.

If production is known say for three weeks ahead, how best to estimate
when and how much to lift during this 21 day period? how to rotate
filling of tanks1,2, & 3 with product 2?

solver is one approach. counting number of lifts 0 with
=COUNTIF(J9:J29,"0") , and using solver to minimize this cell still
produces 21 daily lifts. How would you get solver to produce lifts
every 3-4 days or so as tanks working capacity offer 4+ days of
production before "runout" of room.

Is there a better way to do this than solver?

cheers,
deano