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]

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

Load planner

I never used Google drive before. Let me know if this works. Also, any improvements or additions that you may have would be great. I haven't touched it in a while as I'm refreshing at the moment and collecting data to hyper fixate on. I want to perfect this to be all inclusive for shipping capacity.

My future plans to include are as follows.
Add Bill of lading
Add packing list
Add commercial invoice
Add USMCA
Add capacity visual

I've already designed the extra paperwork for my current job, but haven't implemented on this personal project due to company information being used.

 

[Mahesh5277]

Hi PuntingJawa,

Many thanks for sharing the file. I will try to use this whenever i get an opportunity to do so. Also, will try to add if possible from my side. This is wonderful work by you. Again thanks.

Mahesh

Load planner

I never used Google drive before. Let me know if this works. Also, any improvements or additions that you may have would be great. I haven't touched it in a while as I'm refreshing at the moment and collecting data to hyper fixate on. I want to perfect this to be all inclusive for shipping capacity.

My future plans to include are as follows.
Add Bill of lading
Add packing list
Add commercial invoice
Add USMCA
Add capacity visual

I've already designed the extra paperwork for my current job, but haven't implemented on this personal project due to company information being used.

 

[PuntingJawa]

Hi PuntingJawa,

Many thanks for sharing the file. I will try to use this whenever i get an opportunity to do so. Also, will try to add if possible from my side. This is wonderful work by you. Again thanks.

Mahesh

Not a problem at all. It's an accumulation of sources and formulas I have learned from this very board thrown together. I did an update last night with minor changes and fixes.
Expanded on the quantity of pallets/trucks that can be added.
Expanded on the quantity of custom Trucks.

 

[PuntingJawa]

I have made another rather large update to this file. Please let me know if there is anything I can add or errors/bugs that I should fix.

Additions made.
PDF macro button on BOL, PS, and USMCA/Autohide macro button on BOL
Color coding of tabs to show what interacts most with one another.
  Input to BOL/Minor information such as address BOL number (as packing slip number) Ship to, and date all transferred to Packing slip, Commercial Invoice, and USMCA.
Major information such as the load plan.

Added Bill of lading that pulls the truck loads based on your input of which truck you fill the information in.
  You can change truck via a dropdown menu. After which, click on the "Hide/Expand Rows" button. It'll remove blank rows and shorten the size of the BOL. Everything is automatically handled based on the information you placed in the load plan under the "Input" sheet.

Added Packing slip.
  Just add your own information to the Bill of lading. Add your own logo to the top left.
  When setting up your packing slip, make sure to first place all information in the "H.S.Codes" sheet. This will only need to be done once per item. You should never have to revisit unless the cost of the item has changed or you increased your revision and the part number changed. Otherwise it's a "One and done" scenario.

Added Commercial Invoice.
  This pulls information from your Packing slip. Just be sure to add all relevant information the the "H.S.Codes" sheet or it won't be able to pull the information to calculate.

Added USMCA
  All of this information is pulled from the "H.S.Codes" area as well. So long as you filled in all information, you're good.

Load planner personal project Google Drive

 

[PuntingJawa]

I've changed a few functions and the way that addresses behave. Like with all update, check notes (Cells with upper right red mark) for notes. I really do hope this helps some shippers out there that have to keep doing all this manually.

Removed Hiderows button on BOL
  This is now automated when you change which truck is shipping.

Changed behavior of addresses.
  The only manual input is now on the "Address" sheet. Dropdowns will allow access.
  Addresses still function the same. BOL address controls the "ship from" and "ship to" addresses on the Packing slip, Commercial invoice, and USMCA.

USMCA
  Just like with other address changes, you'll need to add the producers address to the Address sheet. All addresses that are changeable will now have a dropdown menu.

Load planner personal project Google Drive