I have the attached file. I want to use a tool that will find what the optimal number should be in cell B7 to obtain 0 or closet to 0 in cell K8.
I thought SOLVER would have worked, but it is not getting cell K8 as to close to 0 as possible.
Any suggestions would be greatly appreicated.
If you suggest a Macro,I would need what it is to copy it, I am not an avid user of Macro's, thus I struggle with them.
Thanks.Solve B7 to Get 0 in K8.xlsx
Any Suggestions Out There?
I think you are going to need to think through the model more. It appears to me that K8 is a "step" function of B7. For example, as B7 crosses 3402, K8 stops decreasing and jumps up dramatically. Newton Raphson type algorithms (like those used by Solver) have real trouble working with "step" and other discontinuous functions.
If you have access to Excel 2010 or 2013, you might try using the evolutionary algorithm available in the newer versions of Solver. Though it is computationally much more intense, an evolutionary algorithm may succeed where the NR type algorithms fail.
Just trying some random values in B7, I could not see any trends in the values for K8. You might just need to rethink the entire spreadsheet to get a more workable "objective function."
For that matter, a Monte Carlo type algorithm (try as many random entries as you can and record results) might be a suitable approach.
Not much help, I know.
Originally Posted by shg
I agree with you that there is no reason or rhyme to the formula because it takes our inventory in to account along with min order quantities, thus when one inventory runs out it will add in another order quantity. The amount in B7 is used as a mulitiplier in other cells. K8 is the sum of the new values in column V which is taking into account what was entered into B7. I do have 2010 Excel at home. I will give it a try at home and let you know.
What I am trying to accomplish is - what is the amount of items should I make that will end up with the least amount of excess inventory once the product life cycle is done.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks