Optimization of trucks using VBA.

Topic

New Reply

Hello All,

I am looking for some VBA that will help me to make transportation planning. The range B9:E11 is defining the size of trucks in CFT and range B12:E14 is defining the loading capacity of those vehicles in CFT. My objective is ensuring the maximum capacity utilization (%) that will be reflected on cell E19 along with the rounded number of trucks required in range E15:E17. Here the input or required load CFT is reflected on cell E8. I hope that a VBA can ensure the optimization of the overall process as I failed applying some formulas.

Could you please check the attached file and help me to sort out the problem?
Thanks in advance for your time and cooperation.

Regards
Shazzad

Reply | Quote

Replies

Hi Shazzad

I will have a look at this and send a reply tomorrow.

zeddy

Reply | Quote

Hi Shazzad

We might need some further info from you.

Suppose we needed a required load of 1,140 CFT.
This could fit EXACTLY into 3 small trucks, i.e. 3 x 380 = 1,140 CFT, each with 100% capacity utilization.

But I suspect it would be cheaper to use 1 Big Truck (at 100% capacity, = 1,064 CFT), and put the rest (i.e. 1,140 – 1,064 = 76CFT) into a small Truck at 76/380 = 20.0% capacity utilization.

So the question is, are you looking for the best use of the Trucks by cost, or by capacity utilization???

In which case, we would need more info on the Truck costs-to-run.

For example, I suspect you would need to pay drivers of Big Trucks more than you would pay for drivers of Small Trucks.
And I suspect running/fuel/maintenance costs of Big Trucks are much higher than that of Medium Trucks etc etc etc

You might find running costs cheaper if you use your family as drivers. But your wife would need extra funds to buy proper safety clothing and hats. And your children could run up speeding-ticket costs for driving too fast. Just kidding..

zeddy

Reply | Quote

Hi Zeddy,

I thought while posting that these info are not sufficient. Please have a look the parameters-

• I need to prioritize on using Big Truck because they can carry more products and reduce the travelled distance to save fuel considering sustainability which is a vital issue in current age.
• Requirement of trucks should be followed from big truck to small trucks.

zeddy wrote:

So the question is, are you looking for the best use of the Trucks by cost, or by capacity utilization???

• Cost is not considerable here since if i ensure lowest distance to travel, cost will be reduced by own.

zeddy wrote:

But I suspect it would be cheaper to use 1 Big Truck (at 100% capacity, = 1,064 CFT), and put the rest (i.e. 1,140 – 1,064 = 76CFT) into a small Truck at 76/380 = 20.0% capacity utilization.

You are absolutely right.

Suppose, required load CFT is 2500. Here we can use two big trucks (1064*2=2128) and for the rest volume(2500-2128=372) one small truck is perfect which can take load of 380 CFT. Here capacity utilization is 2500/((1064*2)+(380*1)= 99.70% whereas if we use two big truck and one medium truck capacity utilization will be 2500/((1064*2)+(504*1))= 95%. But we need to focus on maximum utilization of the capacity.

zeddy wrote:

For example, I suspect you would need to pay drivers of Big Trucks more than you would pay for drivers of Small Trucks. And I suspect running/fuel/maintenance costs of Big Trucks are much higher than that of Medium Trucks etc etc etc

True that cost of big trucks are higher but considering load-ability it charges me lower. Hope, i can make you understand the scenario but please revert more query.

zeddy wrote:

You might find running costs cheaper if you use your family as drivers. But your wife would need extra funds to buy proper safety clothing and hats. And your children could run up speeding-ticket costs for driving too fast. Just kidding..

No matter who is doing anything wrong or right but i must need to do insurance for such situation 😝

Shazzad

Reply | Quote

Hi Shazzad

To clarify my example I gave previously with a Required Load of 1,140 CFT : although this will fit EXACTLY into 3 Small Trucks (with a 100% capacity utilization), it is better to use FEWER Trucks i.e. use 2 Trucks instead of 3.

So the objective Rule 1 will always be to use as few Trucks as possible.

On this basis, in all cases, no matter what the Required Load is, you would NEVER need more than one Small Truck, and you would NEVER need more than one Medium Truck.

Also, in all cases, you would NEVER need BOTH a Small Truck AND a Medium Truck, since instead of using these TWO Trucks, you would just put that Load into ONE Big Truck (using Rule 1 : using 1 Truck is better than using 2 Trucks).

So here is my logic:
1. Check if Required Load <= Small Truck CFT.
>If the required load will fit into a Small Truck, then you just need 1 Small Truck, otherwise..
2. Check if Required Load <= Medium Truck CFT.
If the Required load will fit into a Medium Truck, then you just need I Medium Truck, otherwise..
3. You need to use Big Trucks.
(For example, although a Required Load of 884 CFT would fill a Small Truck + a Medium Truck, instead of using these TWO Trucks we could just use ONE Big Truck)
Number of Big Trucks needed N = INT((Required Load/Big Truck CFT))
4. Keep filling the Big Trucks until they are full.
5. After you have filled the last Big Truck full, check what is left over of the required Load
6. If there is no leftover load, you don’t need any more Trucks, otherwise..
7. If the leftover amount is > Medium Truck CFT, you will need to put it into another Big Truck, otherwise..
8. If the leftover load is > Small Truck CFT, you just need to use a Medium Truck, otherwise..
9. you will need to put the leftover load into a Small Truck.

So now you can implement these rules using Excel formulas – you don’t need to use VBA.
Although we love using VBA and can make Excel do anything we want with VBA, it is more efficient to use Excel’s formulas whenever this is possible.

So I have added formulas to give you the required MINIMUM number of Trucks for any given Required Load.
I have left you the task of putting in the formula for Capacity utilization into cell [E19]
..we can do this for you but I am sure you can do it!

zeddy

Vehicle-Optimization-zeddy

 

1 user thanked author for this post.

shazzad.arla

Reply | Quote

Dear Zeddy,

YOU ARE JUST MORE THAN AWESOME.

Thank you THE ANGEL EXCEL.

 

BTW, I was thinking to share with you some relevant sort of issues. Should I post here or create a new post?

Again Thanks a lot for your value-able cooperation.

Shazzad

Reply | Quote

Hi Shazzad

Well thank you for your kind words. But there are plenty of willing helpers here who just love helping out.

If you have additional questions related to your Vehicle Optimization issue, my preference is to keep them together here in this thread, but if the AskWoody managers say ‘start a new thread’ then it shouldn’t be a problem.

zeddy

1 user thanked author for this post.

shazzad.arla

Reply | Quote

zeddy wrote:

Hi Shazzad

Well thank you for your kind words. But there are plenty of willing helpers here who just love helping out.

If you have additional questions related to your Vehicle Optimization issue, my preference is to keep them together here in this thread, but if the AskWoody managers say ‘start a new thread’ then it shouldn’t be a problem.

zeddy

Hi Zeddy and Paul,

Actually the topic is about route optimization. Vehicle optimization is one of the major parameters of route optimization. No issue to create a new thread but I am bit confused whether it will violate the norms of the forum as I am respectful towards its rules.

Shazzad

 

Reply | Quote

If the question is not about optimizing trucks then you should probably start a new thread. Makes it easier for others to find issues they may be having.

cheers, Paul

1 user thanked author for this post.

shazzad.arla

Reply | Quote

shazzad.arla wrote:

the topic is about route optimization

Then it’s still nicely on topic.  🙂

cheers, Paul

Reply | Quote

Paul T wrote:

shazzad.arla wrote:

the topic is about route optimization

Then it’s still nicely on topic.  🙂

cheers, Paul

Hi Paul,

I have post in this thread but it is not updated with the attached excel file. Could you please help in detecting the wrong I am doing or have done?

Thanks.

Shazzad.

Reply | Quote

Hi,

Usually I receive orders in the attached file. The orders are placed from distributors of specific regions. Distributors and region details along with the Latitude and Longitude are briefed in the sheet named DB. There are around 15 to 17 distributors of each regions. just after receiving the orders from a region, I check the sheet named RP that stands for route plan. And then, I need to plan the route of vehicles considering the LCFT (Required Load CFT) which is in column J and very shortly briefed in this opening post. Since manual route plan may not be effective to optimize the routes and vehicle capacity utilization, I am trying to have a VBA to generate the optimized route plan.

For example, in RP sheet of attached file, total LCFT (Required Load CFT) is 7,722 and SendMail sheet is suggesting that I need 7 vehicles of L-23 (aka BIG TRUCK) and 1 vehicle of S-12 (aka SMALL TRUCK) to have the best capacity utilization (target is 80%) against the Hired CFT. At this point, I need to plan for the routes of the vehicles where lowest amount of distance to be travelled to deliver the orders of respective distributors. And here a VBA (using the google distance API, I guess) would suggest the best routes that I am actually looking for.
Please let me know if any parameter is need to clarify more.
Thanks.

Shazzad

Reply | Quote

Hi Shazzad

For the benefit of others, we should start by saying you are using Excel 365.

Now, first of all, are all the trucks loaded at the same start position?

If yes, where is this start location??

Are there any other restrictions e.g. are all locations always open to accept deliveries?

If this was Transylvania and you were delivering blood supplies, all your deliveries would have to be at night – i.e. no daylight delivery allowed!

zeddy

Reply | Quote

Hi Zeddy.

zeddy wrote:

For the benefit of others, we should start by saying you are using Excel 365.

You are absolutely right. It was my bad that I didn’t mention it.

zeddy wrote:

Now, first of all, are all the trucks loaded at the same start position?

Yes, all vehicles will be start from the same position. Lat Lon of the position is 24.006772, 90.313201.

zeddy wrote:

Are there any other restrictions e.g. are all locations always open to accept deliveries?

Mostly all the points are open to receive the products except night time (9PM to 8AM).

All the above info, I should mention earlier as all are very crucial for the planning.

Thanks Man.

Shazzad

Reply | Quote

anonymous wrote:

Hi Zeddy.

zeddy wrote:

For the benefit of others, we should start by saying you are using Excel 365.

You are absolutely right. It was my bad that I didn’t mention it.

zeddy wrote:

Now, first of all, are all the trucks loaded at the same start position?

Yes, all vehicles will be start from the same position. Lat Lon of the position is 24.006772, 90.313201.

zeddy wrote:

Are there any other restrictions e.g. are all locations always open to accept deliveries?

Mostly all the points are open to receive the products except night time (9PM to 8AM).

All the above info, I should mention earlier as all are very crucial for the planning.

Thanks Man.

Shazzad

Really Sorry for responding anonymously as I was not signed it during replying.

Shazzad

Reply | Quote

Hi Shazzad

Last night I imagined I was driving a 7.5 ton 23ft-long Big Truck fully loaded to deliver goods to your Distributors in Area 09. When I got to the first drop-off, I opened up the back of the truck and found all the stuff for this particular drop-off location was loaded onto the truck first, and so to get to it I would need to first unload lots of boxes to reach the stuff at the front of the lorry.

Does this mean we need to consider how the Orders are loaded onto the Trucks??? i.e. the particular loading-order sequence so that stuff for the first drop-off is loaded LAST???

Or do your Trucks have racks that allow you to walk down the middle to fetch stuff from the front???

Some time ago we helped someone here with a pallet-loading problem for aircraft – our Excel solution had to consider the weight-distribution of the pallets as loaded to make sure the aircraft was properly balanced to fly! At least you don’t have this problem!!

What happens when one of your Trucks has finally delivered ALL its load, i.e. is now empty? Does it drive all the way back to base with an empty load? Does it pick up an items to be returned? Just asking.

zeddy

Reply | Quote

Hi Zeddy,

Sorry for late response since was a bit sick last two days.

zeddy wrote:

Does this mean we need to consider how the Orders are loaded onto the Trucks??? i.e. the particular loading-order sequence so that stuff for the first drop-off is loaded LAST???

Usually, we develop the route plan according to drop sequence such as DP1-DP2-DP3-DP4-DP5-DP6-DP7. DP stands for Drop Point Location. Based on the drop sequence, warehouse load from reverse like they will load DP7 at first and then DP6, DP5, DP4 and finally DP1.

zeddy wrote:

Some time ago we helped someone here with a pallet-loading problem for aircraft – our Excel solution had to consider the weight-distribution of the pallets as loaded to make sure the aircraft was properly balanced to fly! At least you don’t have this problem!!

That could be really a great work I guess ♥. You have a huge reply portfolio. Could you please share the topic title of that solution?

zeddy wrote:

What happens when one of your Trucks has finally delivered ALL its load, i.e. is now empty? Does it drive all the way back to base with an empty load? Does it pick up an items to be returned? Just asking.

Since all the vehicles are hired for a the specific trip and routes, vehicle owners charge me accordingly. However, usually they get some other orders during returning to base.

Please let me know if you have any other query.

Shazzad

Reply | Quote

Hi Shazzad

Could you please share the topic title of that solution?

It was around 10 years ago, the post number is #1334221

I’m not sure how to put a link to that post – maybe someone can help here?

I am still looking at your Drop Points but I have a few jobs to do this week, so please be patient.

zeddy

Reply | Quote

Hi Zeddy

I have found that post. It was a huge conversation indeed. I always try to pick some unique learning from your post.

However, i am not in hurry for the route planning. So you can take time and solve at your convenience.

Thanks 😊

Shazzad

 

 

 

Reply | Quote

Hi Shazzad

I’m still calculating the best way to deal with the routes.

The shortest-distance ought to take account of existing physical roads.

It’s not like you are delivering via drone!

In London, you cannot assume that the distance from A to B is the same as the distance from B to A, since there are lots of one-way-roads and traffic systems (no-entry roads etc etc). But I am ignoring this issue for your situation.

Still working on it.

zeddy

Reply | Quote

Hi Zeddy.

I believe that you will solve it like a magician. Doesn’t matter how long it would take time.

zeddy wrote:

The shortest-distance ought to take account of existing physical roads.

You are right. The shortest distance is usually calculated based on distance in Google Map.

If I am not wrong, a distance matrix is required to calculate the best route. If yes, Please let me know. I will build it in a sheet and will share the file with you again.

Again, thanks for your effort and Happy Valentine Day to you ♥

Take Care.

Shazzad

Reply | Quote

Hi Shazzad

My heart has been misbehaving. Been in hospital twice.  Just back today. Not quite sorted out yet.

I think your idea of using a distance matrix would be very useful.

Please post a sheet and I will certainly be looking at it soon.

zeddy

 

Reply | Quote

Hi Zeddy.

At first, take rest and have proper treatment to make sure that your heart behave in the way you want. Since the case is cardio related, please do not ignore any abnormality.

As you said, I will work on the distance matrix and will share you shortly.

But you please take care and recover soon.

Shazzad

Reply | Quote

Hi Shazzad

I’m enjoying my rest and taking it easy.

Actually, I’ve been doing that for as long as I can remember.

I’m ready to have a look at that distance matrix to see what we can do.

zeddy

Reply | Quote

Hi Zeedy,

I am actually working on it. It is taking a bit more time. No worry. I will back to you as soon as I can prepare the data.

Shazzad.

Reply | Quote

Hi Shazzad

Well it seems I’m back in circulation (excuse my phlebotomy pun).

I was thinking your route-planning question could be considered something like a railway-network with your delivery-destinations as ‘train stations’.  You could schedule your lorries as the ‘trains’. Note that some trains could be considered as ‘express trains’ i.e. not stopping at ALL stations etc etc etc.

So your distance-matrix would be something like a train-route-map. Just saying.

zeddy

Reply | Quote

zeddy wrote:

Hi Shazzad

Well it seems I’m back in circulation (excuse my phlebotomy pun).

I was thinking your route-planning question could be considered something like a railway-network with your delivery-destinations as ‘train stations’.  You could schedule your lorries as the ‘trains’. Note that some trains could be considered as ‘express trains’ i.e. not stopping at ALL stations etc etc etc.

So your distance-matrix would be something like a train-route-map. Just saying.

zeddy

Hi Zeddy,

It’s really great to have you back. I am sorry for responding late as I was too much busy with some of my relatives’ medical treatment. And finally, I am back to my field. However, i am also working with the distance matrix. It is totally manual and taking more time. However, I am sharing part of my workings so that it would help you to put some thoughts.

Shazzad

Reply | Quote

Hey Shazzad, have you considered using the google maps API to get your travel distances via road networks. I’ve had a play with the idea today and I’ve made a very basic spreadsheet using the google API as a from web data source, combined with minimal VBA to have a sheet that updates the distance and time taken to traverse between 2 locations. I’m sure this can be worked on further to be used with a route eg. DP1-DP2-DP3 etc.

Hopefully this useful to you and saves you from manually creating a distance matrix.

-Ricky

Reply | Quote