Logistics Consolidation Problem

jbecks

New Member
Joined
Feb 28, 2019
Messages
2
I need to find a way in excel to show me the lowest amount shipments it would take to send out all of these orders, without splitting individual orders, by adding together rows that could fit under the volume threshold of 3400. Any help would be appreciated.
https://imgur.com/a/X8AXTZO image is attached of the problem.
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,216
Welcome to the Board!

This is a variation of the Knapsack Problem. You can Google that, or check Wikipedia, but the upshot is that it's really a tricky problem. There are algorithms to do this, but it'd take more time than I have to adapt one of them to your particular problem. Excel has a tool called Solver that might work for you however. It's not installed by default though. If you don't have it on your Data tab, click File > Options > Add-ins > Go (on the bottom next to Excel Add-ins) > check the Solver box.

Now set up a new spreadsheet and set it up like this:


ABCDEFGHIJ
1OrderVolumeShipment1234
211450000
32135
43846Std. Dev.
548370
65349
76161
87549
981595
1091595
11101908
12111895
13121991
14
15Max Per shipment3400
16Minimum Orders3.531176

<tbody>
</tbody>
Sheet2

Worksheet Formulas
CellFormula
F2=SUMIF($D$2:$D$13,F1,$B$2:$B$13)
G2=SUMIF($D$2:$D$13,G1,$B$2:$B$13)
H2=SUMIF($D$2:$D$13,H1,$B$2:$B$13)
I2=SUMIF($D$2:$D$13,I1,$B$2:$B$13)
F5: =STDEV.P(F2:I2)
C16=SUM(B2:B13)/C15

<tbody>
</tbody>

<tbody>
</tbody>



The formula in C16 will tell you the minimum number of shipments you'll need (rounded up). So in F1:I1 put the numbers 1 to 4. Then put the formula in F5.

Now from the Data tab, click Solver (far right). Set up the parameters like this:

Set Objective: F5
To: Min
By Changing Variable Cells: D2:D13
Subject to the Constraints:
D2:D13<=4
D2:D13 = integer
D2:D13 >=1
F2:I2<=C15
Solving Method: Evolutionary

and click Solve. It'll ponder for a while (you can see the stats in the status bar) and hopefully come up with a solution. If it doesn't, and it may not, don't give up hope yet. The algorithm is very dependent on starting conditions. Change D2:D13 all to 4 and try again. The numbers in column D will be the shipment that the order on that line should go in. If you still don't get a good solution after a few tries, there may not be a solution, or this algorithm might not find it. Then you might have to assume that you need 5 shipments. You'd just add a 5 in J1, the formula in J2, and update the formula in F5. Then try Solver again, changing the constraints I marked in red.

Incidentally, I chose Std. Dev. as the formula since that will have the effect of balancing the shipment sizes as much as possible. Other options are possible.

Hope this works for you!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,816
Messages
5,525,054
Members
409,617
Latest member
Lenaf

This Week's Hot Topics

Top