Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Multi-condition 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 January-December. 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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multi-condition formula (2000)

    If your goal is to maximize the quantity of the commodity you buy in a year, I would suggest using the Solver add-in.

    (You can install it if necessary using Tools/Add-ins...)

    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.
    Attached Files Attached Files

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Multi-condition formula (2000)

    Thanks, Hans, I'll try it!

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Multi-condition formula (2000)

    Hans,
    Can you help me with the attached?
    Thanks,
    Jeff
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multi-condition formula (2000)

    I don't have time today; perhaps tomorrow. Sorry. If anyone else cares to take a look ...

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multi-condition 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
    Attached Files Attached Files

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Multi-condition 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 May-October, must be less than 11,600,000.
    By the way, how do you reset the problem back to zero?
    Thanks,
    Jeff

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multi-condition 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

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Multi-condition formula (2000)

    Thanks, I just noticed that. Did you get the problem to work with the change I mentioned in my last e-mail? When I tried to do it, it gave me an infeasible solution!
    Thanks,
    Jeff

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multi-condition 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 self-defeating - it leads to 0 purchases.

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Multi-condition 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

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multi-condition 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 May-July again.
    Finally, in October buy the bare minimum to reach the minimum inventory level for that month.

    Regards,
    Hans

  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Multi-condition formula (2000)

    Hans,
    Still having a problem with the attached. May I impose a little more on you?
    Thanks in advance,
    Jeff
    Attached Files Attached Files

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multi-condition 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.

  15. #15
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Multi-condition formula (2000)

    Hans,
    One more time please? The two worksheets shoul yield the same result.
    Thanks,
    Jeff
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •