Results 1 to 15 of 22
Thread: Multicondition formula (2000)

20020612, 11:16 #1
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Multicondition formula (2000)
Problem: I would like to choose a minimum value among 12 in a list, but only up to a number that corresponds to the price in question. For example assume I have 12 prices for a commodity that correspond to the twelve months JanuaryDecember. Assume further I have a budget of a certain amount of money each month with which to buy the commodity. This amount varies each month, as does the monthly prices. Finally, I have a beginning "bucket" of money with which I can purchase the commodity. This "bucket" may or may not equal the sum of the monthly budgets. I would like to choose, in order, the cheapest monthly price and buy as much as that month's budget allotment will allow, and then progress on to the next cheapest month until my original "bucket" has been spent.
Any help?
Thanks,
Jeff

20020612, 11:50 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Multicondition formula (2000)
If your goal is to maximize the quantity of the commodity you buy in a year, I would suggest using the Solver addin.
(You can install it if necessary using Tools/Addins...)
I have attached a demo workbook. If you open it, and select Tools/Solver..., you will see:
The cell whose value must be maximized,
The cells that can be modified,
The restrictions that have been imposed.
If you click Solve, Excel will determine the optimum solution.

20020612, 12:59 #3
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Multicondition formula (2000)
Thanks, Hans, I'll try it!

20020613, 09:58 #4
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Multicondition formula (2000)
Hans,
Can you help me with the attached?
Thanks,
Jeff

20020613, 16:08 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Multicondition formula (2000)
I don't have time today; perhaps tomorrow. Sorry. If anyone else cares to take a look ...

20020614, 06:13 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Multicondition formula (2000)
Hello Jeff,
One of the constraints you put in is
$D$28 >= $F$27
I think that should be
$D$27 >= $F$27
Then there is something which I'm not sure about, so I may be completely off.
In most of your calculations, you are effectively multiplying the monthly purchases by a factor based on retainage before adding it to the SCQ balance.
In cell F30, however, you are adding the monthly purchases straight to the beginning balance. This amount is then compared to the minimum inventory level for the end of October. I don't pretend to understand what you are doing in this worksheet, but my intuition says that for consistency the monthly purchases should be multiplied by the retainage factor before adding them to the beginning balance.
In that case, the Solver is able to find a workable solution.
See the attachment. Sheet1 is your worksheet with the modifications proposed above. I have added a new Sheet2 with essentially the same calculations, but laid out slightly differently  it helped me to understand what was going on.
Regards,
Hans

20020614, 07:04 #7
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Multicondition formula (2000)
Hans,
Thanks a bunch for "solving" this thing for me. Actually, the 11,600,000 should be the "Maximum" inventory level on hand at the end of October, and the sum of the (1) beginning inventory level on hand at 5/31/2002, and, (2) the purchases "made" throughout the months of MayOctober, must be less than 11,600,000.
By the way, how do you reset the problem back to zero?
Thanks,
Jeff

20020614, 07:37 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Multicondition formula (2000)
Jeff,
If you mean "How can I reset the Solver Parameters", there is an "Initial values" (or similar, don't know the exact wording in English) button in the Solver dialog.
If you mean "How can I reset the starting values", select the monthly purchases cells (I5:I10) and erase the contents (or enter 0).
Regards,
Hans

20020614, 07:39 #9
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Multicondition formula (2000)
Thanks, I just noticed that. Did you get the problem to work with the change I mentioned in my last email? When I tried to do it, it gave me an infeasible solution!
Thanks,
Jeff

20020614, 08:07 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Multicondition formula (2000)
Jeff,
In fact, the change you mentioned makes the problem trivial <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.
By turning around the condition on the Inventory level at the end of October, ALL restraints are now "keep something below a certain level". Your present goal is to minimize total cost. The solution is simple: buy nothing! All conditions are met, you spend $ 0,00. Cheapskate!
Why doesn't the Solver come up with this trivial solution? Because there is no lower limit to the monthly purchases. So Excel tries negative values and concludes that the whole business gets out of hand. If you add the restraint that the monthly purchases should be >= 0, the Solver will find the trivial solution.
You will have to rethink the problem. What is your real goal?
You can strive to realize an Inventory level of 11,600,000 by the end of October and minimize the costs.
Or you can strive to maximize the number of purchases while not exceeding the Inventory level of 11,600,000.
But trying to keep the Inventory level below 11,600,000 AND minimize costs is selfdefeating  it leads to 0 purchases.

20020614, 08:12 #11
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Multicondition formula (2000)
Hans,
I apologize. Upon reflection, the minimum inventory level at the end of October should be no less than 11,600,000, and no greater then 12,025,138 (Cell H2, the maximum space available for the inventory).
How stupid of me!
Take another stab?
Thanks, and again I apologize for my hasty observation!
Jeff

20020614, 08:39 #12
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Multicondition formula (2000)
Jeff,
In fact, the condition that inventory level shouldn't exceed 12,025,138 is not necessary  trying to minimize costs ensures that you will end up with an inventory level as near 11,600,000 as possible.
In fact, the workbook I attached earlier today does just that, I think. The solution is as you would expect since prices increase steadily:
Buy as much as allowed by the maximum monthly injection in May, June and July.
Then in August, buy less to avoid exceeding the maximum inventory level for that month.
In September, do as in MayJuly again.
Finally, in October buy the bare minimum to reach the minimum inventory level for that month.
Regards,
Hans

20020614, 14:01 #13
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Multicondition formula (2000)
Hans,
Still having a problem with the attached. May I impose a little more on you?
Thanks in advance,
Jeff

20020614, 14:21 #14
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Multicondition formula (2000)
In D24 and D27 (the realized inventory levels in June and August), you include (add) the beginning level.
In F24 and F27 (the maximum inventory levels for those months), you exclude (subtract) the beginning level.
This makes the comparisons D24 <= F24 and D27 <= F27 invalid, I think.
If you remove
BeginningSCQBalance
from the formulas F24 and F27, the Solver will find a solution.

20020614, 17:48 #15
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Multicondition formula (2000)
Hans,
One more time please? The two worksheets shoul yield the same result.
Thanks,
Jeff