Expert guide

Freight Load Tracking Spreadsheet

Where every load is, what it earned per mile, and which ones still need billing

Free Cost Audit

Call us at +1 (385) 386-3860

Short answer: keep one Excel Table of loads with customer, lanes, appointments, carrier or driver, customer rate, cost, accessorials, loaded and deadhead miles, status, POD and invoice fields. Calculate margin or trip profit, revenue and margin per total mile, and days from delivery to invoice. Build a live load board filtered by status, a daily POD and billing exceptions list, and lane and customer profitability reports. For accounting sync, see QuickBooks integration.

Small freight brokers and owner-operator fleets often run dispatch from a whiteboard, a load board website, texts and a spreadsheet. The spreadsheet usually records the rate and not much else. That leaves the questions that decide profitability unanswered: which lanes really pay once deadhead is counted, which customers generate detention, and how many delivered loads are sitting unbilled because the POD never came back.

The Load Table

ColumnNotes
LoadNo, Customer, CustomerRefReference numbers shippers will quote
Origin, Destination, LaneLane = origin state/city → destination, for analysis
PickupAppt, DeliveryApptDate and time windows
Carrier / Driver, Truck, TrailerWho is moving it
CustomerRate, BilledAccessorialsRevenue
CarrierCost or DriverPay, PaidAccessorials, Fuel, TollsCost
LoadedMiles, DeadheadMilesFrom routing or ELD
StatusBooked, Dispatched, Picked up, In transit, Delivered, POD received, Invoiced, Paid
DeliveredOn, PODReceivedOn, InvoiceNo, InvoicedOn, PaidOnBilling control

Profit Formulas

Revenue:          =[@CustomerRate] + N([@BilledAccessorials])
Cost (broker):    =[@CarrierCost] + N([@PaidAccessorials])
Cost (carrier):   =[@DriverPay] + [@Fuel] + [@Tolls] + N([@PaidAccessorials])
Margin:           =[@Revenue] - [@Cost]
Margin %:         =IF([@Revenue] = 0, "", [@Margin] / [@Revenue])

Total miles:      =[@LoadedMiles] + N([@DeadheadMiles])
Revenue / mile:   =IF([@[Total miles]] = 0, "", [@Revenue] / [@[Total miles]])
Margin / mile:    =IF([@[Total miles]] = 0, "", [@Margin] / [@[Total miles]])

Rate per loaded mile flatters loads with long empty repositioning. Revenue per total mile is the honest comparison.

The Load Board

Active loads, by pickup:
=SORT(FILTER(tblLoads[[LoadNo]:[Status]],
       ISNA(MATCH(tblLoads[Status], {"Invoiced","Paid"}, 0)),
       "No active loads"), 5, 1)

  • Conditional formatting: pickups today in bold, late pickups or deliveries in red.

  • A Check call column with last update time; anything in transit without an update in 4 hours is highlighted.

  • Slicers by dispatcher and customer for a shared screen in the office.

Billing Control: POD to Cash

Days delivered without POD:
=IF(AND([@Status] = "Delivered", [@PODReceivedOn] = ""), TODAY() - [@DeliveredOn], "")

Days POD without invoice:
=IF(AND([@PODReceivedOn] <> "", [@InvoicedOn] = ""), TODAY() - [@PODReceivedOn], "")

Days to invoice (closed loads):
=IF([@InvoicedOn] = "", "", [@InvoicedOn] - [@DeliveredOn])

Days to pay:
=IF([@PaidOn] = "", "", [@PaidOn] - [@InvoicedOn])

A daily exceptions list of loads delivered without POD and PODs without invoices is the single most effective cash-flow tool for a small broker. Every day a load sits unbilled adds a day to when you get paid.

Lane and Customer Analysis

ReportWhat it reveals
Margin per mile by laneLanes worth pursuing and lanes to reprice
Customer profitabilityMargin, detention frequency, days to pay
Accessorials by customerWho causes detention and lumper fees, and whether you bill them
Carrier performanceOn-time pickup and delivery, claims, cost vs market
Dispatcher volume and marginWorkload and pricing discipline

Where AI Helps a Freight Office

  • Reading rate confirmations and BOLs from email into the load table.

  • Matching signed PODs to loads and updating status automatically.

  • Summarising shipper emails about appointment changes into the load notes.

  • Drafting the weekly customer performance summary from the load data.

Document capture is covered in extracting PDF data with AI; email intake in classifying emails with AI. Carriers should also keep fleet maintenance and IFTA records linked to the same trucks.

Growing Beyond the Spreadsheet

Several dispatchers editing one workbook leads to conflicts. Moving the load table into a shared database, with forms for booking and dispatch and invoice export to accounting, is a natural next step; our warehouse and logistics Access database work covers similar ground, and a custom spreadsheet is the right start for one or two dispatchers.

Weekly Review Routine for the Owner

  • Loads delivered last week, total revenue, total margin and margin per mile against the previous four weeks.

  • The five lowest-margin loads, with lane and customer, to decide whether to reprice or decline similar loads.

  • Accessorials billed versus incurred: detention and lumper fees that were paid but not billed are lost money.

  • Delivered loads without POD and PODs without invoices, with days outstanding.

  • Customers whose average days to pay has increased.

A PivotTable on the load table with slicers for week, customer and dispatcher gives this view in a few clicks. Fifteen minutes on it every Monday usually pays back many times over in recovered accessorials and faster billing.

FAQ

Freight load tracking FAQ

Dispatch, margin, POD and billing

Load number, customer, origin and destination, pickup and delivery appointments, carrier or driver and truck, rate to customer, carrier cost or driver pay, accessorials (detention, lumper, layover), miles, status, proof-of-delivery received, invoice number and payment status.

For brokers: margin = customer rate + billed accessorials − carrier cost − paid accessorials. For carriers: profit = revenue − (driver pay + fuel + tolls + other trip costs). Revenue per mile and margin per mile, using loaded plus deadhead miles, make loads comparable.

Record the empty miles from the previous delivery to the next pickup on each load. Total miles = loaded + deadhead. Rate per total mile shows whether a well-paying load was actually worth it once the empty miles are included.

Track proof of delivery (POD) as a status. As soon as the signed POD is received and attached, the load moves to Ready to invoice. A daily list of delivered loads without POD, and PODs without invoices, removes most billing delays.

When load volume, number of dispatchers, carrier compliance checks, EDI with shippers, or integrated accounting become too much for a shared workbook. A well-structured spreadsheet makes that migration easier because the data is already clean.