I am tyring to use Solver in excel to create a purchasing plan. I have a list of items, each with an order quantity and a lead time (in weeks) to receive the item. I want solver to tell me when (in what week) to place the order of each item so that I will receive a balanced quantity of items each week. This seems like a basic math balancing problem, but Solver is giving me a solution that does not appear optimal, and it will not work using the Simplex LP formula.
My model uses an order week for each item as the changing variable cells, and I am minimizing the standard deviation of the total quantity received each week as the objective cell. I use the following constraints:
1. The Order Week must be an integer.
2. The Order Week must be greater than 0.
3. The Receive Week (calculated as the order week plus the lead time) must be less than or equal to a deadline (in my model I have the deadline set to Week 7).
Any thoughts on what might be wrong/missing? Is solver the right tool for this problem? If there is different approach in Excel that would be great as well, because I need to do this for several thousand items but Solver has a limit of 200 decision variables.
Any help is greatly appreciated!
My model uses an order week for each item as the changing variable cells, and I am minimizing the standard deviation of the total quantity received each week as the objective cell. I use the following constraints:
1. The Order Week must be an integer.
2. The Order Week must be greater than 0.
3. The Receive Week (calculated as the order week plus the lead time) must be less than or equal to a deadline (in my model I have the deadline set to Week 7).
Any thoughts on what might be wrong/missing? Is solver the right tool for this problem? If there is different approach in Excel that would be great as well, because I need to do this for several thousand items but Solver has a limit of 200 decision variables.
Any help is greatly appreciated!
