period solver to schedule lifts

[deano]

KISS : keep It Simple Sam. This is a revised request to help on a
period solver to schedule lifts

You have a tank that contains liquid. You make liquid each day at some
rate. The tank has to hold this daily make. Tank has a max and a min
volume of liquid to hold. You have to lift liquid out of tank so that
it stays within main & max. It costs $5 + $0.42/bbl to lift liquid out.
You know how much liquid you have in tank to start and how much liquid
make for the next 21 days. what is the optimal lift schedule. See the
solver & click show scenarios available here
http://availg.com/images/stories/tools/runout_1.xls

Problem: why is solver not finding the optimal schedule of lifts
regardless of initial guess?

For Example:
go to model tab:
click show scenarios
select base then close
go to Tools > solver
click solve
solve does not find optimal schedule.

click show scenarios
select optimal then close
That is the optimal solution.

Your help is greatly appreciated.

 

[Tom Ogilvy]

Doesn't sound like you need solver for this

You need to lift the total liquid produced in 21 days

that has a fixed cost of .42/bbl x amount of liquid (measured in bbl) you
produce in 21 days.

the only cost depending on when you lift the liquid is the $5 fixed cost.
So you obviously want to minimize the number of lifts - each one you
eliminate saves $5 or conversely, each one you perform costs $5.

Thus you lift Max - Min gallons each time. That would be Production in
21 days/(max - min) to identify how many lifts.

the only variability would be the start volumne and end volume. the first
lift would start at (Max-Start volumn)/production rate. then you would
space out the calculated number of lifts and deal with the last few days if
you have some target volumn you need to end with.

 

[Dana DeLouis]

Problem: why is solver not finding the optimal schedule of lifts

regardless of initial guess?

Hi. I may be wrong, but here is my opinion as to "why" Solver is not
working correctly.
The Solver code that I use quickly flagged your Target cell as a possible
problem.
The reason is that Solver can not handle functions that are discontinuous,
or "Jump."
You will notice that your optimal solution had just a few tanks that needed
"lifting." (4).
All the other cells need to be at 0! I mean at 0, and not 0.0001.
You will notice that in your bad scenario "base" that most of the solutions
are at the end of the list, with the last value 0.
This is not a fact, or anything one can prove, but what I believe Solver did
was start close to the solution you see here, and then tried a 0 at the end,
got confused, and then just Quit!!
Why? Let me use a different Target cell formula.
Suppose we use the following which is similar in concept to what you are
doing.
=IF(A1=0,0,5000+2*A1)

When A1 is 2, the output is 5004. For each unit change in A1, the output
changes by 2.
This is a form of Derivative that Excel uses to establish the next guess.
See Solver's Options for Estimates, Derivatives, & Search.
When A1 is 1, the output is 5002. And of course, when A1 is 0, the output
should be 5000. But wait. the output jumped by 5000 to 0, and Solver does
not know why !! It may have tried 0.000001 in A1, and got 5000.000002.
So, Solver does not know why a very small change made it jump by 5000.
Solver will usually quit immediately when just 1 cell exhibits this bad
behavior.

One usually has to use a model that does not use functions that Jump. (ie
Max, Min, If, ..etc)
One possible workaround here "might" be to use a helper column of "Binary"
constraints. The cell would be 1 if cost equation is used, and 0 if not
used.
Anyway, I hope this helps.

 

[Mike Middleton]

deano -

The "standard" Solver that is included with Excel does not usually perform
well with discontinuous functions.

You may have better results if you use the Premium Solver for Education
(included with many textbooks) or other more-advanced Solvers available from
http://www.solver.com.

- Mike
http://www.mikemiddleton.com

 

[Dana DeLouis]

Hi. On your model, you say that the optimum value is $212.52.
Using Solver, I get the same value. However, I had different "lift" amounts
on slightly different days.
This model appears to have multiple minimum solutions.
If you are interested in having Excel Solver do Fixed Cost problems, here is
just one method.
Using an "If" function would appear to be the only method to use. However,
as we have seen, it does not work. So, what's the key ?
One technique using Excel's Solver is to separate the Fixed costs, and the
Variable costs. You have limits on "lift" of 0 <= Lift <=150. The real
non-intuitive solution is to put a binary upper limit on Lift.
Here's what you do. Keep your "Lift" column. Make 3 additional columns.
First Column is named "Bin", and holds either 0 or 1.
Next is "UL" for Upper limit. The function here are =150*Bin. This is the
KEY. The upper limit on each cell is going to be either 0, or 150.
Third column is Variable cost ("VCost"). Equation is =0.42*Lift.

Fixed Costs are: =5*SUM(Bin)
Variable Costs : =SUM(V(Cost)
Target: Sum of these two. (Fixed + Variable)

So:
Minimize Target
Changing Cells: Lift, Bin
Tank >= 10
Tank <= 145
Bin = Bin (Binary)
Lift >=0
Lift <=UL (<- Key...)

Solver Options:
Precision: .0000001
Tolerance: 1%
Assume Linear Model: Yes
Assume Non-Negative: Yes.


One technique to list all the solutions is the following.
I usually do this via a macro.
Once a solution is found, the macro will clean up all the "near-zeros" (ie
1*10^-14 etc) and zero them out. It then scans the solution, and adds this
as a constraint for the next solution. For example, we have 4 Binary
solutions on the first run. The constraint added will be that the sum of
these 4 Binary constraints will be <= 3.5 (3, but we have to account for
precision). This insures that the next solution will not be the same 4
cells. Solver is run again in a loop.

Anyway, hope this helps. Good luck.

 

[deano]

Hi Tom, Dana, & Mike,

Mike, I looked at these prices & I said ouch
http://www.solver.com/pricexls.php , frankly, the scheduling
optimization here should be handled by standard Excel Solver Addin

Tom, yes, this is a simple representation, need to get it working for
one tank before I expand scope to multi tanks & multi grades of
products. Do you still think that solver is overkill with the greater
scope of more tanks, more grades, varying start inventory?

Dana, I added three columns: see here,
http://availg.com/images/stories/tools/runout_3.xls

column Bin, G9:G29=IF(D9=0,0,1) ' if lift = 0 then 0 else 1
column UL, H9:H29= 150* Bin ' not sure what this column does
column Vcost, I9:I29=0.42*D9 ' 0.42 * lift

total fixed cost , Cell G4=5*SUM(G9:G29)
total Var cost, Cell I4=sum(I9:I29)
total cost, Cell G2=G4+I4

Solver:
Minimize Cell G2
changing cells remain: Lift cells: D929
subject to the same constraints as before

Option: solver options says "The condition for assume linear model are
not satisfied."

Still no dice. I am not sure how to use column UL. How did you get it
to work?

thanks,
deano

 

[Dana DeLouis]

column Bin, G9:G29=IF(D9=0,0,1)' if lift = 0 then 0 else 1

Hi. You were so close.
(Remember...If's are bad!!)
I'm sending you a copy now.
The "Bin, or Binary cells are not a formula. They are set by Solver.
1.) You do this by adding a constraint that this Range is "Bin". (for
Binary)
2.) You have to add that this range are also "Changing Cells."

It took about 10 seconds of iteration to arrive at a solution.
Monitor the progress in the lower left corner of the screen.
Let me know if you get it working.

As a side note for later work...Excel's Solver can have only 200 adjustable
cells.

Also, I made a type. Solver's Optimum is the same as yours...$232.52, and
not $212.52.

 

[Tushar Mehta]

I assume from the scale of the numbers and the phrasing of the problem that
it is a homework assignment.

Yes, Tom is right. You don't need Solver for the problem.

Also, depending on how the fixed cost works across multiple tanks you may
not need Solver for the multiple tank problem either. But, since you
haven't provided enough information about the multi-tank problem there's not
much anyone can do about it.

Go over his explanation again. It's absolutely on the mark.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions