Loading optimization for warehouse

**spatnala** · 05-24-2017, 01:04 PM

Hello All,

I have a question to see if this is possible in excel and some direction. I'm not asking for my problem to be solved more so just if this actually possible in excel.
Also, if you guys have any good ideas on other programs that can do this please suggest.

I have datasets that I want to implement the following business rules.

The topic is warehouse pallet optimization for logistics.
There are a few business rules to abide by:
1. Trucks can hold a max of 45,000 LBS and 30 Pallets.
Optimally they will be 38,000-42,000 LBS and 26-28 pallets.

2. Each DC(Distribution Center) sends multiple POs(Purchase Orders):
Each PO can be one or multiple items
Each PO can be one of two types.

3. More specifics about pallets:
Goal weight is for them to average around 1,500 each.
Each pallet can only contain one type of PO.
Certain items cannot be mixed such as chemicals and food.
Certain items can only be stacked with themself such as rice or fig bars
Certain items cannot be stacked on top of food such as fish.
Max height of a pallet is around 100 inches.

**MrShorty** · 05-24-2017, 01:24 PM

Short but useless answer -- yes it is possible to build a spreadsheet to solve this "knapsack" problem. Perhaps the place to start is here (by the providers of Excel's built in Solver utility): http://www.solver.com/optimization-s...stics-examples Download the example file, then click through to the discussion of the knapsack problem.

After that, you can easily put "knapsack problem" or "knapsack problem excel" into your favorite search engine and find all kinds of discussions around this common and long studied optimization problem. From there, it would be about how to program your specific requirements into a spreadsheet and incorporate them into the optimization algorithm.

**Alf** · 05-25-2017, 02:40 AM

I would first try to setup a pallet loading model as certain items can't be loaded on top of each other.

A proroblem of this kind should be possible to solve as a linear problem. The excel solver is not very powerful as the makers of solver have limited the size of problems to be solved by maximising the number of constraits to 100.

There is a freebie called OpenSolver developed by the University of Auckland, integrates nicely with excel and has no constraints limits you could use if the excel solver is not powerful enough.

As a number of forum member do not have English as their mother tongue (myself included) I would try not to use acronyms as DC and PO as their meaning is not always obvious.

Alf

**spatnala** · 05-25-2017, 08:40 AM

Thank you both for the feedback, helped a lot.
I will edit my post so it does not use any acronyms.

**Alf** · 05-26-2017, 12:44 PM

Some time ago I build a model for container loading. This is not exactly what you asked for as it stacks pallets to a maximum weight but perhaps it will give you an idea how solver could be used.

First of all you must have the solver add-in installed in excel. Check under the data tab and you if you don't see the solver icon you need to install it

As this solver setup is run by starting the macro "Solver_container" you first of all need to set a reference to solver in VBA.

Click on the "Developer" tab -> Click "Visual Basic" icon -> Click "Tools" -> Click "References"

Find and tick box marked "Solver" then click "OK". now you are ready to run macro.

After some seconds the "Show trial solution" windows pops up with the text "The maximum time limit was reached, continue anyway?"

Click "Stop", and solver start another run. After having clicked "Stop" 4 times solver finds the final solution.

Alf