optimise trips

Hi guys,

Appreciate some help with the following.

In this exercise, my objective is to optimise the number of deliveries, based on the various-sized trucks that i have.
I have the raw data of 'total number of pallets' by row, and i need to know in each row, what is the optimal size and amount of trucks to use for delivery.


Assume I have the following delivery options:
-20 pallet truck (20P)
-10 pallet truck (10P)
-4 pallet truck (4P)
-2 pallet truck (2P)
-1 pallet truck (1P)

If I need to deliver 5 pallets, I will use a 4P+1P truck.
If I need to deliver 14 pallets, I will use a 10P+4P truck.
If I need to deliver 15 pallets, I will use a 20P truck.
If I need to deliver 25 pallets, I will use 20P+10P truck
If I need to deliver 35 pallets, I will use 2*20P truck

For 'total pallets' that are x5 and above (i.e. 15 pallets, 25 pallets, 35 pallets...), it is usually assumed to use a 10P or 20P truck (instead of a 4P+1P)

Goal is to place yourself as the business owner- you want the most efficient way to deliver goods. So ideally, I want to minimise the total number of trips.

Attached is a sample.


Thank you so much!!

For the 20 pallet truck try this in D4 and copy down;

Formula:

Please Login or Register  to view this content.

yes i debated that with myself as well- ideally we will use a right-sized truck whenever possible, while minimising the total number of trucks/ trips.

So at 14 pallets, i have a 10P+4P.
at 15 pallets, I use a 20P instead of a 10P+4P+1P (3 trips)

Originally Posted by kersplash

For the 20 pallet truck try this in D4 and copy down;

Formula:

Please Login or Register  to view this content.

great this works!! hoping that you are able to help with the remaining columns please!!

still need help please

bump

really really need some help here please

Hi

C3 must be 0 and D3:H3 has 20, 10, 4, 2, 1
Use in D4 the following formula to get a trivial solution
Here's a formula for finding a trivial solution to use in D4 and valid to drag down and forward to column I

Formula:

Please Login or Register  to view this content.

To set the relative cost I used (number of pallets to spare + $J$2 * the number of trips) where $J$2 is a fixed number (4, 5, ... to manipulate the cost)

Formula:

Please Login or Register  to view this content.

From Row 30 I make to areas two do simulations with different costs to try others solutions than the trivial

See the file for clarification.

Please see attached.

D4
=ROUND($C4/D$3-0.1,0)

E4
=MAX(ROUND(($C4-SUMPRODUCT($D$3:D$3,$D4:D4))/E$3,0),0)

F4 to H4
=MAX(ROUND(($C4-SUMPRODUCT($D$3:E$3,$D4:E4)-0.1)/F$3,0),0)

Originally Posted by Bo_Ry

Please see attached.

D4
=ROUND($C4/D$3-0.1,0)

E4
=MAX(ROUND(($C4-SUMPRODUCT($D$3:D$3,$D4:D4))/E$3,0),0)

F4 to H4
=MAX(ROUND(($C4-SUMPRODUCT($D$3:E$3,$D4:E4)-0.1)/F$3,0),0)

thank you so much!! this worked!!
could you explain why F4 to H4 required the -0.1?

been thinking about it but cant figure it out.

Thanks so much anyway!!! it really saved my project!

D and F to H use -0.1 because next of D4 20 is 10 is half of D4 same for F to H.
Without -0.1 round will round up when value is decimal is .5 so 10 Total pallets will go with 20 pallet truck

E.G
Total pallets is 10 10/20 is 0.5 it should go for 10 pallet truck need to round 0.5-0.1 to get 0 so 10 pallet can get 10 pallet
Total pallets is 10 10/20 is 0.5 it should go for 10 pallet truck need to round 0.55 -0.1 to get 0 so 10 pallet can get 10 pallet and another 1 will will go to 1 pallet

You may adjust this number to -0.05 if you want 11 pallet to go with 20 pallet truck.

same for F to H