runout - plant production scheduling tank inventory

[deano]

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

 

[deano]

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?

 

[Guest]

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.

 

[deano]

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

 

[deano]

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

the solvers settings are:

minimize cost of lifts across 21 days, cell $J$2,
=SUM(IF(J9:J29>0,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]

Merry Xmas...

A sharp pair of eyes may look at this period related scheduling solver
and catch wht it is not finding the optimal result. Thanks for the
link, it shows how to set up a solver.

if you provide solver a starting guess of 4 lifts costing $255 as in
{ 0, 0, 0, 141, 0, 0, 0, 136, 0, 0, 0, 0, 145, 0, 0, 0, 138, 0, 0, 0,
0 } in cells J9:J29, solver will come up with the optimal solution
which is { 0, 0, 0, 141, 0, 0, 0, 136, 0, 0, 0, 0, 124.05, 0, 0, 0,
117.05, 0, 0, 0, 0 } having the lowest cost of $238K and minimal
frequency of 4 lifts.

My problem is how do I get solver to find this optimal solution on its
own.......What am I missing????

 

[deano]

Guys, how do I get solver to find this optimal solution on its
own.......What am I missing ????