Optimizing the gifting distribution

[Raviprmr]

Hi I am looking after distribution
Just has this querry. If stock is greater than to be supplied quantity than Allocation Logic is equal to be supplied quantity

In case to be supplied quantity is greater than Supply Stock,

Can I have some sort of optimization formula to do the following

1. Dispatch should be based on the Priority - Black, Red, Yellow, Green
2. Within Black first priority should be with the "Location and type of customer" ending with "-C", than "-M" and the rest
3. Allocation to be done based on following logic, If supply location stock is not sufficient to meet all orders:

1. Fill all Blacks orders (for all customer type) - to at least 1/3 of order qty with the priority mentioned in 2 above
2. Fill all Red orders (for all customer type) - to at least 1/3 of order qty with the priority mentioned in 2 above

If there is still stock left

1. Fill all Blacks orders (for all customer type) - to at least 2/3 of order qty with the priority mentioned in 2 above
   
2. Fill all Red orders (for all customer type) - to at least 2/3 of order qty with the priority mentioned in 2 above
   
3. Fill all Yellow orders (for all customer type) - to at least 2/3 of order qty with the priority mentioned in 2 above

Than the balance qty should not be dispatched

 

[Nebu]

Hi:

I do not have time to build a model for you, but this situation is ideal for exploring building a solver model. find the following link to read more about solvers.

http://www.solver.com/solver-tutorial-using-solver

Thanks

 

[Raviprmr]

Hi Nebu,

Thanks for the information. I need to know can this be done using (IF, SUMIF, Vlookup) or any other functions. Not familiar with using solver.
However, I will give the above link a try and understand how to build my query.