Creating Optimal Pallets from Sales Orders

[AdamLeonardo]

Hi All,

I have a problem I could use some help with. I am trying to divide sales orders into pallets and haven’t been able to figure this out. I have a decent amount of experience with Excel, but I am by no means an expert.

Our company produces 10 different products that we ship out. Product A can fit 48 units per pallet. Product B can fit 72. Product C can fit 40, etc… We use percentages to determine how much of each product can fit on a pallet. For instance, if we are shipping 16 units of Product A and 12 units of Product B, we would be able to put both on one pallet (16/48 + 12/72 = 50% of a full pallet). We can’t divide the remainder of products between multiple pallets; for example, if we are shipping 142 units of Product A and 76 of Product B, we would have 2 full pallets of 48 units of Product A, 1 pallet of 46 units of A, 1 full pallet of 72 units of B and 1 pallet of 4 units of B. We couldn’t put 1 unit of B with the 46 units of A and then the remaining 3 units of B alone. The B units would have to stay together.


[TABLE="width: 505"]
<tbody>[TR]
[TD]Sales Order[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5210[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Qty[/TD]
[TD]Per Pallet[/TD]
[TD]Qty/Plt[/TD]
[TD]Full Pallets[/TD]
[TD="colspan: 2"]Remainder (Qty-Full*Per Pallet)[/TD]
[TD="colspan: 2"]% of Pallet
(Remainder/Per Pallet)[/TD]
[/TR]
[TR]
[TD]Product A[/TD]
[TD]110[/TD]
[TD]48[/TD]
[TD]2.29[/TD]
[TD]2[/TD]
[TD="colspan: 2"]14[/TD]
[TD="colspan: 2"]29%[/TD]
[/TR]
[TR]
[TD]Product B[/TD]
[TD]30[/TD]
[TD]72[/TD]
[TD]0.42[/TD]
[TD]0[/TD]
[TD="colspan: 2"]30[/TD]
[TD="colspan: 2"]42%[/TD]
[/TR]
[TR]
[TD]Product C[/TD]
[TD]98[/TD]
[TD]40[/TD]
[TD]2.45[/TD]
[TD]2[/TD]
[TD="colspan: 2"]18[/TD]
[TD="colspan: 2"]45%[/TD]
[/TR]
[TR]
[TD]Product D[/TD]
[TD]216[/TD]
[TD]100[/TD]
[TD]2.16[/TD]
[TD]2[/TD]
[TD="colspan: 2"]16[/TD]
[TD="colspan: 2"]16%[/TD]
[/TR]
[TR]
[TD]Product E[/TD]
[TD]72[/TD]
[TD]72[/TD]
[TD]1.00[/TD]
[TD]1[/TD]
[TD="colspan: 2"]0[/TD]
[TD="colspan: 2"]0%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


So A, B and C couldn’t go on one pallet because their % adds up to 116%. A, (B or C) and D can go together because they total to less than 100%. We may have a situation where 3 products each make up more than 50% of a pallet and can't be combined.

I need to figure out how to grab a sales order number, populate the required amounts of each product and then automatically palletize the order. I can generate the quantities needed, but haven’t been able to figure out the best way to use Excel to create ideal pallet optimization. I tried the Solver extension, but had issues when the remaining quantities were less than 100% of a pallet. I’ve also run into problems getting multiple products on one pallet where their percentages sum up to less than 100% (1 unit of A, 3 of B, 6 of C…) I really hope I haven’t made this too confusing and any suggestions would be greatly appreciated!

 

[yky]

I have a partial solution. Maybe some one can come up with a full solution.

1. Let's say your data is in A6:G10. In H11, enter the formula "=SUMPRODUCT(G6:G10,H6:H10)"

2. Highlight H11 and click at Solver. You want to maximize H11 by changing H6:H10.

3. Enter 11 conditions. Five for H6 to H10 are integers. Six for H6 to H11 are less than or equal to 1.

Now, solver can pick up the combination of the remaining product to maximize the pallet usage.

I said this is a partial solution, not a full solution because it only works if the total remaining percentage is less than 200%. If it is greater than 200%, you need to repeat the above process for each 100%. That is, if the total remaining percentage is 512%, you need to do it a total of four times.

 

[yky]

It seems solver can take array constrains. So, you only need to enter two conditions. H6:H10 are integers and H6:H11 are equal to or less than 1.