Shipping Optimization Tool

Hi everyone,

This is less so much of an issue with some code, but seeking some guidance to how best tackle a problem.

I am attempting to create a userform which takes product information (I sell flat steel plates) and generates an optimised load to ship to customers overseas. Currently this is done by hand and I believe can be improved upon. The problem is that I would like a form which essentially can look at possible ways to organise the products and select the cheapest way to organise the shipping containers. I am struggling to code in the logic that would determine which container to select to fill, that is, how can I code the program to look at what products are still to be loaded, and choose the best combination of shipping containers to accommodate them.

I apologise if this seems as though I asking for someone to make the program for me, but I am unsure as to how to solve this issue, I have had many attempts and each time I find some way in which the program cannot perform.

I have attached the program I currently have as well as an example of the kind of products that require shipping at one time, and how this would be organised. Any guidance is greatly appreciated.

Please let me know if I can provide anymore information, as I am having trouble explaining my situation.

Not really sure that solver is the right tool for you problem. I had a go at it but I had to set it up as a two stage "rocket".

First I had solver calculate the cheapest combination of all containers needed that could handle the total weight. In the second setup I used the selected containers and let solver distribute the load between the selected containers.

Cost vise I got a lower cost than you had in the uploaded file. I got 13633 versus the 14218 but that could also be caused by some additional factors that I don't know about.

Attachment 619896

Attachment 619897

Alf

Thank you both for your replies, my apologies for my late reply, flu season here and I've been home sick.

I have since been investigating the Solver add in and it may be able to solve my issue, or at least help point me in the right way.
Is there any good resources that help explain exactly its capabilities and how to best implement them? So far I have found there examples a bit confusing...

Thanks everyone!

Alf, would you be able to offer some guidance as to how you obtained such a number? As am I not able to open your attachments.

The uploaded file contain the solver models. First model sheet "Solver_container_selected". Then sheet "Solver_loading_containers" shows where the different plates should be loaded.

To find the cheapest container combination I let solver pick any number of containers that could handle the total load to the lowest price. To distribute that load in 5 containers the excel solver could not cope with this so I had to use the "OpenSolver". A bit more info in file.

Alf

Hi Alf,

That file you attached is fantastic! Really helped me get a feel for how to use the solver tool. My only question from here is that the containers have size restrictions as well, that is that container A can't hold any products that are larger than 2500 wide by 6100 long. Is it possible to further constrain solver to account for this as well? or would this be asking too much for it too handle?

A can't hold any products that are larger than 2500 wide by 6100 long. Is it possible to further constrain solver to account for this as well?

I'll have a go at it and see if I can set up a model that takes this in account. One question before I start tinkering, can the plates being rotated i.e. Container A has a maximum width of 2500 and if a plate has a width of 3050 can the plate be rotated 90 degrees so that width becomes length and as maximum length for A is 6100 that would then take two plates as 3050 *2 = 6100

I also think you should google for "container packing software" as I assume these programs should be better suited for what you need than solver. Also most of these programs comes with a free 10 days testing period so you have a chance to find the most suitable program for you needs.

Alf

My only question from here is that the containers have size restrictions as well, that is that container A can't hold any products that are larger than 2500 wide by 6100 long.

At the moment I can see the width constraint but unable to imagine the length constraint. Could you upload some example of this.

I've also set up an example of using a binary grid to ensure that plates are only loaded to to containers with equal or greater width than the plate.

This file is based on my earlier uploaded file as I still have not managed to build a model that solves everything in one go.

Alf

So I've been looking into solver and I think I'm close to a solution. I expanded on the sheet you uploaded for me Alf (which was very helpful, so thank you very much!)
and added the length and width constraints, however with the constraints I currently have solver almost perfectly optimises the loads. Everything except for one plate
is placed correctly and I cant see as to why this one is not. I believe it has to do with the settings but I am not sure what to change to remedy this. I have uploaded
the sheet I am working on and in the variable cells, the cell highlighted orange should be a 0 and the cell highlighted green should be a 1. Re-running solver after
clearing the orange cell fixes this issue so I am unsure as to why it gets stuck at this point.

Thank you for all your help, my apologies for taking so long to reply as I have been very busy with other tasks.

You are welcome and thanks for feedback and rep

my apologies for taking so long to reply as I have been very busy with other tasks

That's OK. I got no problem with that.

I believe it has to do with the settings but I am not sure what to change to remedy this

There are times when solver will not find the optimal solution after the first run but a rerun will then fix the problem. You ask what could be wrong. Frankly I'm puzzled that you got a solver solution with your setup in the uploaded file.

The excel solver (2010 and late) comes with three engines for solving problems, The linear engine, the GRG Nonlinear engine and the Evolutionary engine.

According to the makers of solver

Microsoft Excel provides a very rich formula language, including many built-in functions that are discontinuous or non-smooth. These functions cannot be used with the Simplex LP Solving method. Discontinuous functions cause considerable difficulty, and non-smooth functions cause some difficulty for the GRG Nonlinear Solving method. The Evolutionary Solving method can handle these functions, but you’ll “pay a price” in solution time and quality. Some models can only be expressed with the aid of these functions; in other cases, you have a degree of choice in how you model the real-world problem, and which functions you use.

By far the most common discontinuous function in Excel is the IF function where the conditional test depends on the decision variables, as in the example =IF(C1>10,D1,2*D1) where C1 is a variable. Here is a short list of common discontinuous Excel functions:

IF, CHOOSE

LOOKUP, HLOOKUP, VLOOKUP

COUNT

INT, ROUND

CEILING, FLOOR

Here is a short list of common non-smooth Excel functions:

ABS

MIN, MAX

Formulas involving relations such as <=, = and >= (on the worksheet, not in the Solver Parameters outlined list) and logical functions such as AND, OR and NOT are discontinuous at their points of transition from FALSE to TRUE values. Functions such as SUMIF and the database functions are discontinuous if the criterion or conditional argument depends on the decision variables.

as you use the discontinuous functions VLOOKUP and ROUNDUP I'm really impressed that you almost got an optimal solution on the first run.

If I clear the range G4:N16 and let solver run it takes a bit of time before it find the proper solution but it do find it so I get range G9:G16 = 1 and range N4N8 = 1.

Well I did'n think a setup like yours would work and I'm really pleased to see it really works

Conclusion: Solver works in mysterious ways it's miracles to preform.

Alf