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

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

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

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

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

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????


Martin Fishlock wrote:
Dean,

I had a look at the file and I'm I could not seem to get any constraints
working.

You need to be able to pull it all together and get a p&l for the items

ie the cost of stock holding compared to the cost of production try and make
it clearer

It is a long time since I did linear programming but why not try looking at

http://www.economicsnetwork.ac.uk/ch...3/ch9_3p07.htm

and it may help.

Have a merry christmas.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"deano" wrote:

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

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

deano wrote:
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????


Martin Fishlock wrote:
Dean,

I had a look at the file and I'm I could not seem to get any constraints
working.

You need to be able to pull it all together and get a p&l for the items

ie the cost of stock holding compared to the cost of production try and make
it clearer

It is a long time since I did linear programming but why not try looking at

http://www.economicsnetwork.ac.uk/ch...3/ch9_3p07.htm

and it may help.

Have a merry christmas.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"deano" wrote:

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