3

I have been trying to find a way to create an efficient way of selecting a combination of packs based on the pack size and the amount of packs available.

For example a customer orders 370 widgets.

The supplier sells widgets in packs of 10, 30 and 50.

I know to meet the order efficiently I would need 7x of 50 pack and 2x of 10 pack.

But what if the supplier only had the following quantities of packs in their warehouse?

  • 100x of 10 pack
  • 30x of 30 pack
  • 5x of 50 pack

The most efficient for my purposes should be 5x of 50 pack and 4x of 30 pack.

How could I automate this type of order picking?

1

I suggest you to try Solver add-in. It uses linear and non-linear calculation algorithms to solve equations. It is a built-in add-in and you can activate it from File > Options > Add-Ins > Select Excel Add-ins > Click Go.

screenshot shows formulas and Solver settings

For more detailed information search for linear programming.

2
  • Thanks for pointing me to linear programming! It was very helpful as I was not sure where to start. It's not as efficient as I would like but it does the job and I'm happy with it. I did have to modify the constraints as per the following image Imgur
    – angelofdev
    May 21 '18 at 18:08
  • @angelofdev I thought you may need to prioritize your variables; so I updated my model a bit. I added weights to it and updated my goal to maximize the total weighted quantity. Also; I should have added customer order = total qty equation as a constraint. Here is a screenshot Imgur May 22 '18 at 5:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.