Solver? to determine sequencing in inventory management problem

There is no straight forward mathematical way to perform such calculations.

The only way out is using permutations even though it will result in a extremely huge number of permutations.

There are two approaches to tackle permutations.

1. Go through all possible permutations and get the sequence with least wastage.

2. Generate a random permutation and calculate its wastage. When macro is triggered again generate random combinations until the previous wastage is beaten. If triggered again, it will again generate random combinations until the previous low is beaten. User can keep pressing it until he is satisfied with the results.

One logical approach can be -
Start from wastage of 5 units and batch 1(smallest batch).
Macro generates combinations of orders and calculates wastage. If wastage is not more than 5 it allocates those orders to batch 1 and moves on, else it keeps on generating combinations until this wastage is not more than 5. If no such combination is found it takes up the next batch. It will keep on allocating orders to batches fitting within 5 units range of wastage. After all batches are done, it increases the wastage range to 10 units and the smallest batch which is still unallocated. It will again start allocating orders fitting in 10 units range of shortage. Then moves on to next unallocated batch. Then after all batches are done increases wastage range to 15 units, and so on......

Originally Posted by Alf

I've set up a solver model based on your uploaded file. There are certainly more combinations but in the end it boils down to the fact that you have orders
for 32962 units and you production is 35000 so all the best (min waste) solution will have an excess of 2038 units.

Advantage with the solver setup is that solver found a solution in less than 10 seconds.

Alf

Whoa! That was a cool setup

I just changed the Batch 1 quantity to 4000 or change 10000 to 9000 I was wondering why solver says "could not find a feasible solution". Weird
Also, if I change 10000 to 9999 it takes a lot more time to process. Again weird

Batch 1 quantity to 4000 or change 10000 to 9000 I was wondering why solver says "could not find a feasible solution".

You've changed the production from 5000 to 4000 but I assume the customer demand stays the same i.e. 32962.

So with only 4000 in production the only orders that can be filled is A, D, F, G and H this adds up to 2949

As you've now used batch1 completely as you can't use balance 4000-2949 to partly fill another order you only have batch2, batch3 and batch4 left i.e. 30000 units and your customer demand is B, C, E, I and J and the total of these orders are 30013 units so as you can see solver can't fulfill a demand like this.

But only changing one of the 10000 batches to 9000 and keeping 5000 batch unchanged will let solver find a solution.

Alf

Ps Thanks for feedback and rep

You are welcome and thanks for feedback and rep

Discovered Excel is constrained to 200 variables.

Yes it tends to put a spoke in the wheel at times. But as you problem in the uploaded file is a "Simplex LP" problem you could try the OpenSolver a freebie developed by the university of Auckland NZ. Their linear model does not have constrains and the solver integrates nicely with excel.

http://opensolver.org/

Another option is to run solver in a loop. Based on my model solver would first solve range D5:D15, E5:E15, and so forth to range G5:G15. I did a test like that and I still got the same excess 2038 units.

Problem with this sub optimizing approach is that the total result may not be as good as solving all parameters at one go.

Alf

Just for my amusement I've added the solver loop. See sheet "Solve_setup2".

To run the macro you first need to set a reference to solver in Visual Basic.

Click on "Developers" tab, click on Visual Basic icon, click "Tools" -> "References" and tick box marked Solver. Clear range "D6:G15" and run
macro "SolvLoop"

You still get the same total waste looping as solving it in one go. This is because there is quite a slack between production and demand. If the difference between these two values was less this would increase the probability that the later solution will not be optimal since solver will have less chance of picking the best values.

But most of the time you really don't know unless you can test both methods and see if there is a difference and one solution is better than the other. The advantage with this approach is of cource that you can solve quite a big model without reaching the excel solver constraint.

Alf