Best Product Combination to achieve 100% Order fulfillment

[aljrab]

OK here is the scope:

I have a warehouse and it currently it is at full capacity, I can not physically add more people to pick product it is already way too congested. In this warehouse they pick from roughly 400 different products.

I was given another warehouse to increase the picking capacity but its much smaller so I can only put 50 products in there. What 50 products do I put that will allow me to fulfill an order completely? At the moment we have been putting the 50 products that are picked the most, and we have been getting an additional 20% capacity but we need the capacity to be more around 30 – 40% so is there a way I can run an algorithm or an sql or something that will tell me out of this pool of orders this is the best 50 products to take out and replicate into the other warehouse?

Example:
I have 7 products; products A, B C, D, E, F & G. I can only replicate 3 of these products in the other warehouse.

There are 20 orders a day:
[TABLE="width: 256"]
<colgroup><col style="width:48pt" span="4" width="64"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]ABC
[/TD]
[TD="class: xl65, width: 64"]A[/TD]
[TD="class: xl65, width: 64"]EDG[/TD]
[TD="class: xl65, width: 64"]FAB[/TD]
[/TR]
[TR]
[TD="class: xl65"]DEF[/TD]
[TD="class: xl65"]BC[/TD]
[TD="class: xl65"]ABG[/TD]
[TD="class: xl65"]CAB
[/TD]
[/TR]
[TR]
[TD="class: xl65"]ABG[/TD]
[TD="class: xl65"]AG[/TD]
[TD="class: xl65"]ACE[/TD]
[TD="class: xl65"]DEF
[/TD]
[/TR]
[TR]
[TD="class: xl65"]CDE[/TD]
[TD="class: xl65"]FECG[/TD]
[TD="class: xl65"]DAB[/TD]
[TD="class: xl65"]GAC
[/TD]
[/TR]
[TR]
[TD="class: xl65"]F[/TD]
[TD="class: xl65"]ADE[/TD]
[TD="class: xl65"]BDE[/TD]
[TD="class: xl65"]AB
[/TD]
[/TR]
</tbody>[/TABLE]


By the logic we are currently using the top 3 movers that would become the pickfaces in the other warehouse are products A, B & E.

[TABLE="width: 129"]
<tbody>[TR]
[TD="colspan: 2"]Product Frequency
[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]

Products A, B & E would only allow me to fulfill 2 orders ( the 2^nd order and the last order) (10% increase) if I would have picked A,B & G I would have been able to fulfill 5 orders (25% increase) yet G was one of the least picked products….

I have attached this example below, If you can come up to something that I can apply to my warehouse orders that would be greatly appreciated

[TABLE="width: 163"]
<tbody>[TR]
[TD]Order Number[/TD]
[TD]Product[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]B[/TD]
[/TR]
</tbody>[/TABLE]


Kind Regards

Sara

 

[SydneyGeek]

Hi Sara,

Build a query that counts the number of times each product is ordered, and sort descending on the count.
If there is any seasonal variation in the ordering you will need to take that into account (a crosstab with months across the top, product as a row header and the count of orders in the Value area will help you to detect these patterns). You could also add the Year of the order in the row headers, to isolate patterns by year and see if they repeat.

Denis

 

[aljrab]

Hi Denis,

It is a fair bit more complex then that, I am after the top 50 products that will allow me to 100% fulfill orders, as per my above example and based on your suggestion the output of the data would say move A, B & E to the new warehouse, but if that was the case then only 10% of orders would have been fulfilled vs A, B and G or C where 25% of orders could have been fulfilled