Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
period solver to schedule lifts
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
period solver to schedule lifts
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. -- Regards, Tom Ogilvy "deano" wrote in message ups.com... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
period solver to schedule lifts
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. -- HTH :) Dana DeLouis Windows XP & Office 2003 "deano" wrote in message ups.com... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
period solver to schedule lifts
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 "deano" wrote in message ups.com... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
period solver to schedule lifts
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 a =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. -- HTH :) Dana DeLouis Windows XP & Office 2003 "Dana DeLouis" wrote in message ... 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. -- HTH :) Dana DeLouis Windows XP & Office 2003 "deano" wrote in message ups.com... 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
period solver to schedule lifts
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: D9:D29 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 wrote: 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 a =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. -- HTH :) Dana DeLouis Windows XP & Office 2003 "Dana DeLouis" wrote in message ... 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. -- HTH :) Dana DeLouis Windows XP & Office 2003 "deano" wrote in message ups.com... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
period solver to schedule lifts
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. -- HTH :) Dana DeLouis Windows XP & Office 2003 "deano" wrote in message oups.com... 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: D9:D29 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 wrote: 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 a =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. -- HTH :) Dana DeLouis Windows XP & Office 2003 "Dana DeLouis" wrote in message ... 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. -- HTH :) Dana DeLouis Windows XP & Office 2003 "deano" wrote in message ups.com... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 - Calculation production schedule using solver | Excel Worksheet Functions | |||
change the payment period of amortization schedule | Excel Worksheet Functions | |||
HOW DO I CHANGE A SCHEDULE USING SOLVER WITHOUT CHANGING EACH DAY? | Excel Worksheet Functions | |||
How to use solver to schedule part time and full time workforce? | Excel Discussion (Misc queries) | |||
Need an excel amortization schedule formula with a grace period | Excel Worksheet Functions |