Global Freight Forwarding & Supply Chain
Air Cargo vs Ocean LCL Chargeable Weight Formula
Calculate IATA Air Volumetric & Chargeable Weight MAX(W_gross, V_cbm * 167), Air Freight Cost, Ocean LCL W/M Revenue Tons MAX(W_gross / 1000, V_cbm), and Modal Freight Savings.
Prevent 40%+ air-freight invoice shocks on bulky, low-density shipments!
Importers quoting air vs Less-than-Container-Load (LCL) ocean freight on Actual Scale Weight W_gross (kg) alone routinely suffer huge Dimensional-Weight surcharges! Under IATA Resolution 502 (6,000 cm^3 per kg = 166.67, rounded commercially to 167 kg/CBM), airlines bill Chargeable Weight W_chg_air = MAX(W_gross, V_cbm * Dim_Factor_air)—so any shipment lighter than 167 kg/m^3 "cubes out" on aircraft belly space! Meanwhile, Ocean LCL consolidators price per "Revenue Ton" (Weight-or-Measurement, W/M) at 1,000 kg = 1.00 CBM: Billable_WM = MAX(W_gross / 1000, V_cbm) (frequently floored at a 1.00 W/M minimum)!
Verify on Total_Shipment_Volume V_cbm = 12.00 CBM (B2), Actual_Scale_Gross_Weight W_gross = 1,400 kg (1.40 MT, density 116.7 kg/CBM < 167, B3), IATA_Dim_Factor = 167 kg/CBM (B4), Air_Freight_Rate = $4.50/kg (B5), and Ocean_LCL_Rate = $85.00 per W/M ton (B6): Air Volumetric Weight is 12.00 * 167 = 2,004 kg, so Air Chargeable Weight is MAX(1,400, 2,004) = 2,004 kg (+604 kg above scale weight, E2!), costing 2,004 * $4.50 = $9,018.00 (E3 vs $6,300 naive scale-weight quote!); Ocean LCL Billable W/M is MAX(1.40, 12.00) = 12.00 Revenue Tons (E4), costing 12.00 * $85.00 = $1,020.00 (E5)! Test dense steel valves (V_cbm = 2.00, W_gross = 3,000 kg): Air bills 3,000 kg ($13,500) and Ocean LCL bills MAX(3.00, 2.00) = 3.00 W/M tons ($255.00)!
| Freight Forwarding Metric (12.0 CBM, 1,400 kg Gross, Air $4.50/kg, LCL $85/WM) | Formula | Verified Output |
|---|---|---|
| IATA Air Chargeable Weight (kg) | MAX(W_gross, V_cbm * 167) | 2,004.00 kg (+604 kg Volumetric) |
| Total Air Freight Charge ($) | MAX(W_gross, V_cbm * 167) * Rate_air | $9,018.00 |
| Ocean LCL Billable W/M Tons | MAX(1, MAX(W_gross / 1000, V_cbm)) | 12.00 W/M Revenue Tons |
| Total Ocean LCL Freight Charge ($) | Billable_WM * Rate_lcl | $1,020.00 ($7,998 Saved vs Air) |
IATA Air Chargeable Weight & Freight Cost Formula
Keep Dim_Factor parameterized in cell B4 so express couriers using 200 kg/CBM (5,000 cm^3/kg divisor) can be audited in the exact same template!
=LET(v_cb, B2, w_gr, B3, dm_f, B4, r_ar, B5, r_oc, B6, IF(OR(v_cb <= 0, w_gr <= 0, dm_f <= 0, r_ar <= 0, r_oc <= 0), "Invalid Shipment Specs", ROUND(MAX(w_gr, v_cb * dm_f) * r_ar, 2)))Ocean LCL W/M Revenue Ton & Minimum-1-CBM Floor Formula
Include MAX(1, ...) for standard LCL tariff 1-W/M minimum billing on micro-shipments (< 1 CBM and < 1,000 kg)!
=IF(ISTEXT(E3), E3, ROUND(MAX(1, MAX(B3 / 1000, B2)) * B6, 2))Try the example
Use the original values or change them to test your understanding.