AI for Freight Business Analytics: Margin by Lane From a Spreadsheet

A small freight business can upload a cleaned shipment export to ChatGPT or Claude, ask it to calculate revenue minus carrier, fuel and accessorial costs for each shipment, then group the result by lane and customer. Reconcile the 200 source rows and every total to accounts before changing prices or dropping a lane.

The analysis is only as good as the cost boundary. A lane may look attractive if the export includes carrier base charges but leaves fuel, detention and redelivery in separate invoices. A chatbot can help join and explain the numbers; it cannot know which unrecorded charge belongs to a shipment. Make missing costs visible rather than treating blanks as zero.

Follow me on Instagram@sagnikteaches

Decide what margin means in this export

For this review, shipment gross margin is invoiced customer revenue less direct carrier cost, fuel charge borne by the business, and accessorial costs borne by the business. An accessorial is an additional transport charge such as waiting, liftgate or redelivery. If you recharge an accessorial to the customer, put that recovery in revenue. Keep the cost in cost. The result is a direct contribution figure, not full business profit after salaries and overhead.

Connect on LinkedInSagnik Bhattacharya

Write that definition on the sheet. Do not change it midway because one lane looks poor. Decide whether figures use issued invoices, cash received or accrued work, and use the same basis for all 200 shipments. A cancelled load with a customer charge and carrier cancellation fee needs both sides represented. A shipment with no customer invoice yet should be marked unbilled, not counted as zero-revenue finished work. The related carrier invoice audit tutorial helps establish whether the carrier amount is final.

Subscribe on YouTube@sagnikteaches

Choose a period that has largely settled. For a first pass, the last completed month may be easier to reconcile than the current month, when bills and customer credits are still arriving. Record a data cut-off date and a report version. When a late accessorial arrives, update the affected shipment and re-run the report; keep the previous version so the change can be explained. A spreadsheet that silently changes yesterday's margin is hard to trust.

Build one row per shipment and cost lines beside it

Export a stable shipment ID, load date, origin zone, destination zone, customer ID, carrier ID, service type, equipment, delivered status, customer invoice amount and carrier base amount. Add fuel, accessorial cost, accessorial recovery, credits and adjustments as separate numeric columns. If the transport system produces one row per charge, first pivot or aggregate those charge rows by shipment ID. Do not let a join multiply an $80 fuel line across three invoice lines.

Use a canonical lane key such as origin zone + destination zone + service type + equipment. The two directions can have different economics, so do not merge A-to-B with B-to-A by sorting the place names alphabetically. Distinguish full truckload from shared capacity where the cost structure differs. If a customer uses two spellings or account IDs after a merger, map both to a verified customer key without overwriting the original ID.

Keep a second tab for exceptions: unmatched carrier bills, duplicate shipment IDs, negative values, missing dates, unbilled shipments, manual credits and rows whose status is not delivered. A three-step data check is useful: count unique shipment IDs, sum revenue and costs by source system, then compare those totals to the prepared analysis tab. For a more general data-cleaning method, see the messy-data cleaning tutorial. The focus here remains the freight cost allocation and lane decision.

For sensitive exports, remove contact names, addresses and free-text notes that the analysis does not need. An account key and zone are often enough. Use an AI account approved for business data, check its current terms, and keep the raw export in your controlled storage. The analysis should reference shipment IDs so staff can trace a suspicious number without putting the entire customer file into a prompt.

A 200-shipment example from export to answer

Imagine a broker with exactly 200 completed shipments in a month: 80 on lane A, 60 on lane B and 60 on lane C. Every value here is illustrative. Lane A has an average customer invoice of $1,200, carrier base of $900, fuel cost of $80 and accessorial cost of $40. Lane B has $1,500 revenue, $1,200 carrier base, $100 fuel and $50 accessorial. Lane C has $1,800 revenue, $1,550 carrier base, $120 fuel and $80 accessorial. Assume no accessorial recovery or credits for this first calculation.

Check one row before trusting the group: an A shipment yields $1,200 - $900 - $80 - $40 = $180 gross margin. Multiply $180 by 80 to obtain $14,400. Lane B yields $150 per load, or $9,000. Lane C yields only $50 per load, or $3,000. The full period produces $294,000 - $237,000 - $19,600 - $11,000 = $26,400. Divide $26,400 by $294,000 for about 9.0% gross margin on the stated definition. Lane C's $3,000 divided by $108,000 is about 2.8%.

The report says which lane to investigate, not which one to abandon. Lane C has the most revenue but a thin direct margin. It may anchor a valuable customer relationship, fill an otherwise empty vehicle, or include a recoverable surcharge missing from the export. Alternatively, a repeated waiting charge may be underquoted. Ask for the distribution of shipment margins, not only an average: are all 60 C loads around $50, or are ten losing $300 while others are healthy?

Now add a customer cut. Suppose, illustratively, lane C's 60 loads consist of 40 for Customer X and 20 for Customer Y. X's loads each earn $80 direct margin, or $3,200; Y's loads each lose $10, or negative $200. The lane total remains $3,000. If an AI summary says “lane C is weak”, staff should ask whether the issue is a customer contract, service requirement, accessorial recovery or cost allocation. A lane-level answer alone hides the actual pricing decision.

Give the assistant a verifiable calculation request

Ask ChatGPT or Claude to inspect the schema before it calculates. It should identify currency columns, sign conventions, duplicate keys, blank costs and status values. Then request a shipment-level calculation, a lane table, a customer table and a reconciliation table. A narrative summary without these intermediate outputs is hard to audit. If a tool generates a file, open it and inspect formulas and rows; do not rely on its chat response.

Both assistants can do the sums in code rather than in prose, which is what you want for 200 rows. OpenAI's help centre says ChatGPT accepts .xls, .xlsx and .csv files and, for some data-analysis tasks, writes and runs Python; it also warns that a file inside the upload limit can still be too large or complex for complete analysis. Claude's help centre says an XLSX upload needs code execution and file creation switched on in your settings, while a CSV does not, and allows up to 20 files in one chat. In practice, export the shipment file and the accounts totals as two CSVs, ask the assistant to show the code it ran, and rerun one lane by hand. If the reply contains totals but no code or output table, treat those totals as unverified.

This is a shipment export for a direct gross-margin review.
First list every column and ask about ambiguous units or signs.
Use only delivered shipments with a unique shipment ID.
Gross margin = customer invoice + accessorial recovery
  - carrier base - fuel cost - accessorial cost - credits.
Show one row per shipment, then group by directional lane and customer.
Report row counts, excluded IDs, missing cost fields, negative margins,
revenue and cost totals, and the exact formula used. Do not turn blanks
into zero. Do not infer a missing bill. Explain any mismatch between
the grouped totals and the shipment-level totals.

An illustrative output might report “200 delivered rows; 17 shipments with blank fuel cost; lane C margin $3,000”. That is not ready for a decision. Ask whether the 17 blanks mean no fuel charge, a missing join or a charge included in carrier base. If those 17 are all on lane C, the $3,000 may be overstated. Correct the source data or label an estimate separately, then rerun. A polished chart cannot resolve the bookkeeping meaning of a blank cell.

Do the arithmetic in the spreadsheet as well. A formula for a row might be =Revenue+Recovery-CarrierBase-FuelCost-AccessorialCost-Credits if credits are stored as positive deductions. If credits are already negative, the formula differs. Test one credit row by hand. A pivot table can then aggregate margin by lane and customer. The AI is most useful for spotting missing joins, proposing views and explaining unexpected patterns; the reproducible sheet remains the audit trail. For the broader tool workflow, the ChatGPT spreadsheet guide has upload and checking advice.

Cost quirks that reorder the lane ranking

Fuel is already in carrier base: an illustrative carrier bill states “all-in $1,300”, while the export also carries an $80 fuel field copied from the original quote. Subtracting both double-counts cost. Mark a charge-basis column and reconcile it to the bill. The assistant should flag that an all-in amount and a fuel line coexist, not automatically choose one. The final cost is whatever the approved bill and accounting entry establish.

A waiting fee arrives late: a B load initially shows $150 margin. Two weeks later a $220 detention bill is approved, making the shipment margin negative $70 unless a customer recovery is invoiced. The lane's earlier margin report should be restated or carry a late-cost adjustment. Record the date and reason, so the broker can see whether late fees are a repeatable quoting issue rather than a one-off clerical delay.

A credit note reverses an invoice: a shipment has $1,500 revenue, then a $150 customer credit for a service failure. If the credit appears as a separate accounts row, joining only the original invoice overstates revenue. Join credits by shipment ID and confirm sign conventions. A negative revenue amount in one system may already subtract the credit; another may export “credit amount” as positive. Sample both before applying one formula to 200 rows.

A shared vehicle serves two accounts: the carrier bill is $2,000 for a multi-drop run, while two customer invoices are $1,400 and $1,100. Duplicating $2,000 against each customer would show a loss, although total revenue $2,500 less $2,000 before other costs is $500. Allocate the cost once according to a documented rule, such as occupied pallet spaces or agreed commercial shares. Show the allocated cost in two customer rows and verify it sums back to $2,000.

A route label hides direction: outbound A-to-B earns $200 per load, but inbound B-to-A loses $90. A report that groups both as “A/B” can produce a flattering average. Preserve direction and service in the lane key. If a backhaul is sold partly to reduce empty running, analyse its incremental contribution separately, with the cost allocation stated. The backhaul freight tutorial takes that operational question further.

An unusual equipment request distorts a customer average: Customer X has 25 ordinary loads at $120 margin and one special load at negative $600. The average is roughly $92, but that masks a specific service price issue. Give the owner both average and distribution, flag the one special-equipment row, and check whether its cost should have been approved as a separately quoted exception. Do not raise every Customer X price based on one atypical move.

Handle the rows that refuse a clean join

The most consequential work in a 200-row export may concern fewer than ten rows. Suppose five carrier bills have no shipment ID because the supplier used its own reference. Do not exclude them quietly to make the pivot work. Build a matching queue that shows carrier reference, billed date, route, amount and possible shipment IDs. A person can compare those details against the original bill and load record, then record a verified match. If no match is possible, leave the cost unallocated and state that the lane report is incomplete by that amount.

Another illustrative complication is a single shipment ID used on a round trip. The outward and return legs have separate customer invoices but one carrier bill. If you group by shipment ID alone, you may collapse two revenue records into one row or duplicate the full cost. Create a leg identifier, allocate the carrier bill according to the contract or a documented operational rule, and make the two leg costs add back to the bill. The report can then show direction-specific margins without inventing a separate carrier charge.

Some systems export a “fuel surcharge” on the customer invoice and a separate “fuel surcharge” on the carrier bill. The names look the same but sit on opposite sides of margin. Add a source-side column: customer recovery or carrier expense. Suppose a shipment has $1,200 base customer revenue, $90 customer fuel recovery, $900 carrier base and $80 carrier fuel. Its direct margin before other costs is $310, not $220 and not $390. Check the account's invoice against the ledger to ensure the $90 was actually billed.

Watch for mixed currencies and units even if your normal business records use one reporting currency. A customer invoice may be in a different currency from the carrier bill, and a quote may be per pallet while the final bill is per truck. Record the original amount, currency, unit and conversion used by accounts. Use the posted accounting conversion for a historical reconciliation, rather than asking the model to fetch a current exchange rate for a past shipment. If conversions are unavailable, put those loads in a separate exception group instead of mixing their raw amounts into a lane total.

Dates can be just as ambiguous. A load collected at the end of one month may be delivered and invoiced in the next; the carrier's bill may arrive later still. Decide whether the report groups by delivery date, invoice date or accounting period, and label it. If management wants an operational view by delivery date, reconcile the timing difference to accounts explicitly. For example, three delivered loads may have $4,500 of customer invoices posted in the next period. Those are not missing sales; they are a known bridge between the operational and accounting views.

Finally, test the negative numbers on the actual export, not on a made-up formula alone. Select one customer credit, one carrier credit and one cancelled load. Write the expected margin by hand from the underlying documents, then see whether the formula agrees. If it does not, fix the sign mapping and recalculate every row. A model that successfully explains a wrong formula is still giving the owner a wrong lane ranking. Keep the three test IDs in a control tab for the next monthly run.

Reconcile the report before a pricing meeting

Compare the 200 unique shipment IDs with the transport system's completed-load count. Then tie customer revenue to the accounts sales total for the same period and basis. Tie carrier base and accessorial costs to approved supplier bills, and account for unbilled estimates separately. An amount can match by coincidence even when two rows are duplicated and two are missing, so check both counts and totals. Investigate any difference by shipment ID rather than accepting a percentage tolerance without explanation.

Make a small control table with source total, analysis total, difference and reason. If accounts revenue is $294,000 but the analysis shows $293,850, locate the $150 credit or missing invoice. If carrier bills total $237,500 while the export has $237,000, find the $500 bill and decide whether it belongs to the period. A reconciled report may still have provisional costs, but it will show how many and where. Share those limitations with anyone using the margin table.

Set a decision threshold tied to your own business, such as “review lanes with less than $100 gross margin per load over at least 20 loads”. That is an illustrative internal triage rule, not an industry standard. For each flagged lane, inspect the five most negative shipments, compare quoted and final costs, check accessorial recovery and speak to the account owner. If the business has only a few loads on a lane, a percentage can swing wildly; use a minimum count and show absolute dollars beside percentages.

Repeat the calculation monthly with the same formula and versioned mapping table. Compare changes in mix, rate and cost separately. If lane C improves from $50 to $90 per load, determine whether customer prices rose, carrier costs fell, or the period merely contained fewer detention charges. A repeatable shipment-level calculation makes those explanations possible. The owner can then choose whether to reprice, renegotiate, change service conditions or leave the lane alone for a documented reason.

Questions raised by a lane margin report

Should margin use the quoted carrier cost or the final bill?

Use the final approved carrier bill for a historical profit report. Keep the quoted amount as a separate column so you can see pricing drift. If a bill is missing, mark that shipment provisional and do not mix it into a supposedly final lane result without a clear count and estimate method.

What if one shipment serves several customers?

Choose an allocation rule before calculating customer margin, such as documented space, weight or agreed commercial share. Show the allocation basis and make allocated costs sum back to the shipment total. Do not let the AI silently duplicate the whole carrier cost across customer rows or assign it all to the largest invoice.

Is a positive gross margin proof that a lane is profitable?

No. Shipment gross margin here subtracts the direct carrier, fuel and accessorial costs you have captured. It excludes broker salaries, systems, claims, financing and other overhead unless you explicitly allocate them. Use it to spot lanes for investigation, then compare contribution with the costs your business actually bears.

Further reads

• Automate a Logistics Dashboard: Shipment Spreadsheets, AI Summaries — Present the checked shipment measures.
• Freight Rate Sheets: AI Extracts Carrier Rates From PDFs — Structure incoming rate data.
• AI for Freight Business Optimisation: Quotes, Loads and Follow-Ups — Use margin findings in future quotes.
• Can AI Analyse My Sales Spreadsheet? What to Upload and Check — Review spreadsheet upload checks.
• How to Use AI in a Trucking Company: Load Paperwork Without Retyping — A step-by-step way to turn rate confirmation PDFs and broker emails into reviewed load records with ChatGPT or Claude and a two-tab sheet.
• Trucking Price Quote Template: Build One and Check Rates With AI — A filled-in trucking quote template with a cost-per-mile build-up, deadhead and fuel lines, one worked lane and an AI rate check with sample output.
• Cost per Mile for Trucking: Work It Out From Your Own Statements — Build a true cost per mile from your own statements, split into fixed and variable, with a worked one-truck example and AI statement-reading prompts.
• Fuel Surcharge Calculator: Set a Weekly Rate for Haulage Quotes — A weekly fuel surcharge policy for haulage and courier quotes: base price, fuel share, step table, spreadsheet formulas and customer clause wording.
• Pallet Delivery Rates: Build a Per-Pallet Price Grid by Zone — A carrier-side method for a zone-by-pallet price grid, with surcharges for fuel, tail-lift and waiting, and an AI check that finds gaps and odd prices.
• AI Freight Quoting: Turn Customer Emails Into Draft Quotes — A practical email-to-draft-quote workflow with rate validity, surcharges, margin rules and approval controls.
• AI for Freight Business Sustainability: Emissions Figures for Tenders — A haulier's tender sheet separates measured fuel, allocation, factor source and the emissions figure a customer requested.
• Commercial Invoice vs Packing List: Cross-Check Both With AI — A pre-shipment check where AI compares the commercial invoice and packing list line by line and returns a mismatch report you verify before documents go out.
• Demurrage and Detention: AI Tracks Free Days and Drafts Disputes — A free-day tracker, an evidence checklist and AI-drafted dispute letters for forwarders who are tired of paying demurrage and detention bills unchallenged.
• Detention Pay: AI Tracks Wait Times and Invoices Brokers — A carrier-side workflow: capture timestamps at the dock, test them against your contract terms, then invoice the broker with AI-drafted emails.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: OpenAI help centre article on data analysis with ChatGPT; Claude help centre article on supported upload file types (both read 5 November 2026). All shipment figures, lanes and customers in the calculation are illustrative.