Truck load capacity

[PuntingJawa]

Good afternoon MrExcel board!

I'm trying to make an excel sheet to create load capacity for shipments. I have started with basics but am having trouble bringing this to the next level. The main issue I am having is figuring out how many crates need to be placed on which row and how many can fit. There are 2 items that can only be placed on the bottom row and 3 items that can be placed on any of the 3 rows. I feel like I am on the right path but don't know how to go about working it.

I'll explain what I am attempting.

I want E12-E16, G14-G16, and I14-I16 to show how many crates can fit per row with length used.

A few issues I have a problem with are, I don't know how to formulate the above mentioned to allow use of 636. Example In D15 where we go into negative there are 314 inches left. If divided by D8 (length of DO crate) it comes to 4.8. Since we can't use a fraction of a location the .8 is gone. I would need it to reflect that. Also, Each row can hold 2 crates. I haven't figured out how to do 1/2 rows yet.
The next issue is F14-F16 and I14-I16. Each "Row" has only 636 inches. So my current formula won't work once the above is figured out. I would need to remove the length of crates already used.

Any help with this would be amazing. Please let me know if a better explantion is required. I'll have the board up when I return home and still continue to work on this on my own for now.

Truck load template.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3
4			Type / Max Stack	Length	Width	Height	Weight	QTY	Total (LBS)	Total Inch		Notes
5			Box Crate (1x Max)	42	57	43	850	6	5100	252		636" Per "Lane"
6			Car Kit (1x Max)	44	44	81	250	0	0	0		1xRow = 2x "Lane"
7			EDO Crate (3x Max)	70	37	25	450	1	450	70		1,272" Both Lanes
8			DO Crate (3x Max)	65	37	25	750	24	18000	1560		2,544" If Stacked
9	LE QTY	240	LE Pallet (3x Max)	96	48	16	960	1	960	96		32,000 (Max LBS set)
10												Actual Max 34k LBS
11			Load plan	1 Row	QTY 1	2 Row	QTY 2	3 row	QTY 3	Stackable		Based on 53'x8.2'x8'
12			Box Crate (1x Max)	384		1020		2292		No		636x98.4x96 inches
13			Car Kit (1x Max)	384		1020		2292		No
14			EDO Crate (3x Max)	314		950		2222		Yes
15			DO Crate (3x Max)	-1246		-610		662		Yes
16			LE Pallet (3x Max)	-1342		-706		566		Yes
17
18			Total Weight in LBS
19			24510
Sheet1

Cell Formulas
Range	Formula
I5:I9	I5	=H5*G5
J5:J9	J5	=D5*H5
G9	G9	=B9*4
D12	D12	=636-J5
D13:D16	D13	=D12-J6
F12	F12	=1272-J5
F13:F16	F13	=F12-J6
H12	H12	=2544-J5
H13:H16	H13	=H12-J6
C19	C19	=I9+I5+I7+I8+I6

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H12:I16	Cell Value	<0	text	NO
F12:G16	Cell Value	<0	text	NO
D12:E16	Cell Value	<0	text	NO
H12:I16	Cell Value	between 0 and 2544	text	NO
F12:G16	Cell Value	between 0 and 1272	text	NO
D12:E16	Cell Value	between 0 and 636	text	NO
C19	Cell Value	<32000	text	NO
C19	Cell Value	>32000	text	NO

 

[PuntingJawa]

Awesome, sorry for the delayed response, I want to make sure I get all the correct information first. I'll get it to you ASAP.

Thanks!!!

I had purchased 365 fort my personal computer a couple days ago and started rebuilding the foundation. Once I have the above information I can play around and do some fine tuning.

I left it at 3 high though.

 

[PuntingJawa]

This is what it looks like right now. There's a lot of calculation to lead up to this and a few simple rules. Input would be like you see here.
The first two "Lanes" under "ID Here" would be where you enter the ID of the item. Leave the third blank if not only 2 lanes. Up to 15 slots as much more than that is overkill.
It'll show you certain calculations and which "Lanes" need to be adjusted.
So far it can handle up to 35 different items but is expandable. I just need to figure how to to create a a self expanding Bill of lading, Packing slip, Commercial invoice, and USMCA. Once that is done, there is a lot more to do but I have the base already prepared.
Input belongs to BOL. Packing slip would translate to CI and USMCA via a helper tab with your shipping information.
Also added a little area as a help with a drop down of the most common types of trucks. I did not include box trucks as those are considered LTL and you should know if you need 12-15 feet or less easily.

 

[tmoney2011]

This is what it looks like right now. There's a lot of calculation to lead up to this and a few simple rules. Input would be like you see here.
The first two "Lanes" under "ID Here" would be where you enter the ID of the item. Leave the third blank if not only 2 lanes. Up to 15 slots as much more than that is overkill.
It'll show you certain calculations and which "Lanes" need to be adjusted.
So far it can handle up to 35 different items but is expandable. I just need to figure how to to create a a self expanding Bill of lading, Packing slip, Commercial invoice, and USMCA. Once that is done, there is a lot more to do but I have the base already prepared.
Input belongs to BOL. Packing slip would translate to CI and USMCA via a helper tab with your shipping information.
Also added a little area as a help with a drop down of the most common types of trucks. I did not include box trucks as those are considered LTL and you should know if you need 12-15 feet or less easily.

View attachment 106626

Again, sorry it's taken so long...

Here is the truck info I was given

Van Trailer: 8’x53’ Usable Space

Curtain Side: 8’x47’ and have one trailer that is 8’x53’

Box Truck:

Without Trim Rack: 8’x26’

With Trim Rack: 6.5’x26’

Box Truck Weight: 10,000 LBS of total GVW.

Van Trailer/Curtain Side Weight:

Our Trucks: 28,000 LBS of total GVW

GLM/Third Party: 54,000 LBS of total GVW

How many bunks of trim for curtain side:

Our truck: 5-6 Bunks (Pending Weight/Profile)

GLM/Third Party: 10-12 Bunks (Pending Weight/Profile)

How many hardware skids side by side in Van Trailer:

4’x4’ Skids: 12 long and 2 wide for a total of 48 skids. (Pending Weight)

How many skids of slabs on a trailer:

(20 Min) Fire Rated Slabs-3’0” Skids: 12 skids of 20 slabs per skid. (2,080 LBS a skid)

(1 3/8) Slabs-3’0” Skids: 24 Skids of 30 Slabs per skid (stack two high) (900lb per skid)

Van Trailer total of 240 Single Units Max based on 4 7/8 jamb.

Box Truck total of 100 single units max based on 4 7/8 jamb.

I'll work on getting a more precise item measurement for each possible loadable item as well.

 

[tmoney2011]

Well maybe this info is enough? Might be better to not have exact measurements, but rather how many of each items fits?

 

[PuntingJawa]

I've already set something up that may work well. I wanted to add a visual tab, but have been too tired after work to really dive into it. Honestly, I went ahead and made it to where you can add all the information yourself (truck capacity) and made sure that the input was expandable as well. I'll upload to my Google drive and share when I get home. It's not complete with everything I want to do in it just yet, but it's good enough for any experienced shipper to get a rough idea of your trucks capacities.

 

[tmoney2011]

I've already set something up that may work well. I wanted to add a visual tab, but have been too tired after work to really dive into it. Honestly, I went ahead and made it to where you can add all the information yourself (truck capacity) and made sure that the input was expandable as well. I'll upload to my Google drive and share when I get home. It's not complete with everything I want to do in it just yet, but it's good enough for any experienced shipper to get a rough idea of your trucks capacities.

That's amazing, I appreciate you!

 

[PuntingJawa]

That's amazing, I appreciate you!

Load planner personal project.xlsx

Try this. I haven't perfected it yet, but there are instructions. I plan on expanding it further when I finally get some energy back.
I have another one that I made for work with a BOL, Commercial invoice, and USMCA. I will be fixing a few things and adding to mine eventually when I figure out how to expand lines in the BOL to fit more items.

 

[tmoney2011]

Load planner personal project.xlsx

Try this. I haven't perfected it yet, but there are instructions. I plan on expanding it further when I finally get some energy back.
I have another one that I made for work with a BOL, Commercial invoice, and USMCA. I will be fixing a few things and adding to mine eventually when I figure out how to expand lines in the BOL to fit more items.

Thank you! I'll check it out, please keep me in mind as you make improvements!

 

[PuntingJawa]

Thank you! I'll check it out, please keep me in mind as you make improvements!

Just let me know how it works and any suggestions. I'll be more than happy to see what I can modify and add

 

[Mahesh5277]

Load planner personal project.xlsx

Try this. I haven't perfected it yet, but there are instructions. I plan on expanding it further when I finally get some energy back.
I have another one that I made for work with a BOL, Commercial invoice, and USMCA. I will be fixing a few things and adding to mine eventually when I figure out how to expand lines in the BOL to fit more items.

Hi PuntingJawa,

Can you share the file thro Google drive please? I work in Packaging & think this may be useful to me as well. The current link of One drive doesn't work for me to download (our company doesn't allow downloading from this source). Thanks

Mahesh