Solver Excel Problem - How to create the optimal boxes mix to respect the constrains

The picture would reqiure rewriting into Excel to suggest possible solver solution. Please consider publishing a workbook as the attachment. See yellow banner above and .... just before posting scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.


I expect that the variables will form a matrix 14 (as products number) by at least 16 (to avoid tax, with total value of $761.69 and single pack below $50 you need at least 16 packs). So we are probably above upper limit of available standard solver variables (which is around 200).

In case solver fails because of too many variables, you can consider some other solutions like opensolver, or may be limit the number of items and packs to be completed by manually completing some packs before solver is applied. Probably the wise strategy would be to start from most expensive items (#4,5,9) so you will have 9 packs with single expensive item and "filled with less expensive" to fit total below $50 and weight almost a pound.

60% tax is making the cost of the postage less important, but of course it would be better to use heavier packs in general case. But in your case, you have at least 16 packs and only 14.939 pounds. So keepeng all packs under a pound will end up with less than $320 for delivery. While 15 below 1 pound and one below 2 pound makes already almost $330 and so on.

Originally Posted by Kaper

The picture would reqiure rewriting into Excel to suggest possible solver solution. Please consider publishing a workbook as the attachment. See yellow banner above and .... just before posting scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.


I expect that the variables will form a matrix 14 (as products number) by at least 16 (to avoid tax, with total value of $761.69 and single pack below $50 you need at least 16 packs). So we are probably above upper limit of available standard solver variables (which is around 200).

In case solver fails because of too many variables, you can consider some other solutions like opensolver, or may be limit the number of items and packs to be completed by manually completing some packs before solver is applied. Probably the wise strategy would be to start from most expensive items (#4,5,9) so you will have 9 packs with single expensive item and "filled with less expensive" to fit total below $50 and weight almost a pound.

60% tax is making the cost of the postage less important, but of course it would be better to use heavier packs in general case. But in your case, you have at least 16 packs and only 14.939 pounds. So keepeng all packs under a pound will end up with less than $320 for delivery. While 15 below 1 pound and one below 2 pound makes already almost $330 and so on.

Thanks a lot! I will try to understand all the information you gave me and still trying to learn through tutorials.

I attached the spreadsheet, I tried it in the first post but did not work. Maybe it was because I did not use the "manage attachments".

It looks like you can use 16 boxes, all with a weight less than 1 pound.

As Kaper wrote, there are too many variables for the standard Solver (I doubt it would find a solution, anyhow). You will need to install OpenSolver, a free add-in.

HTH,

Francesco

Hi Francesco

Tried your solution but on my PC in ran for 120 seconds and then told me it could not find a proper solution (uploaded fractional value instead of integers). My setup looks
like yours and OpenSolver found a solution after 1,96 seconds so I'm puzzled why your file did not run properly on my PC. Any ideas about that?

Just to see fract costs I setup a small excel solver model but found all boxes could be shipped in four lots with weights from 3,61 to 3,87.

Alf

Originally Posted by Alf

My setup looks like yours and OpenSolver found a solution after 1,96 seconds so I'm puzzled why your file did not run properly on my PC. Any ideas about that?

Hi Alf,

I think I was too optimistic with maximum solution time.

Running again the model, the first time I got no solution, the second had to wait 100s, so I think it may be safe to set 300s.

It seems to me you haven't added the constraint on max weight in your setup. It is quite taxing for the engine.

HTH,

Francesco

It seems to me you haven't added the constraint on max weight in your setup.

So right you are and now I realize I've not understood the OP's request so I've deleted files and comments.
Alf

I have given macro code. It results in 16 boxes. It does not take any time, instant. This may be helpful until you get SOLVER help.

Please Login or Register  to view this content.

I don't know what happened to my file, Alf. When I tried to change it, OS gave me a funny error message I could not get rid of, and forced me to rebuild the model.

Anyhow, it may not be a bad thing, since I have decided we should seek to minimize the sum of shipping costs and extra-fees, if a box has a value > 50$. The optimal number of boxes is found by OS.

There are some bin vars, to chose the correct class, and some real vars (>0), to calculate the extra-fees. I have assumed that the fees are paid on the fraction of value that is over 50$.
After a few runs, I have seen that OS will never fill a box up to more than 2 pounds, therefore I have removed some vars.

The optimal solution is still 16 boxes, for a total cost of 303.68$ (all shipping costs), found in less than 3 minutes.

HTH,

Francesco

I also tested your new file, nice setup by the way. I did a number of runs with different max time settings. 600 s, 450 s and 900 s. All runs stopped on max time and said a solution was found but when I clicked the "OK" button was told that the solution was not optimal but it was loaded into the spreadsheet.

Looking at the results I could not see any problem with them. All constraints was met. Checking the output from OpenSolver I found that it took my PC 566 seconds to reach the lowest amount 303,68 and that did not change during the 600 and 900 runs. I got the impression that OpenSolver was aiming for a result around 289??? But I'm not sure I read the output from OpenSolver right.

The only difference was 450 second run where amount was only 304,82 but them my PC needed 566 second to reach its lowest value.

Alf

Originally Posted by kvsrinivasamurthy

I have given macro code. It results in 16 boxes. ...

but 4 of them weight more than 1 pound
11 ITEM #5 , ITEM #14 , ITEM #3
12 ITEM #3 , ITEM #3 , ITEM #8 , ITEM #8 , ITEM #8 , ITEM #10
13 ITEM #10 , ITEM #10 , ITEM #10 , ITEM #10 , ITEM #6 , ITEM #6
14 ITEM #6 , ITEM #6 , ITEM #6 , ITEM #6 , ITEM #6 , ITEM #6 , ITEM #1
(box 12 even above 2 pounds)
so the total cost is $50 more than possible best solution (16 boxes, each below $50 and each below 1 pound)

All boxes should be less than $50 to avoid import tax fee, which is 60%.

Op's condition is Cost should be <$50

He did not explicit ask that every box should be equal to or less than 1 pound in weight but he asked for minimum costs. And freight cost must be at minimum as this can be influenced by the box weight.

So if one could replace two 1 pound boxes with one 2 pound box for instance one would save the difference between 2*18.98 and 28.98 you could then save 8.98 but as model is set up to maximize box value this is not possible as all boxes are to close to their maximum value.

So 16 boxes with weight equal to or less than 1 pound makes a freight cost of 303,68.

Your solution makes 3 boxes above 1 pound and one box above 2 pounds. This makes a total freight cost of 353,68

Alf

Let us wait for solver solution with 16 boxes with less than 1 pound.

@Alf
Thank you

No problem. We learn something new.