Freight Brokerage by the Numbers: Load Margins, Carrier Rates, Quick-Pay & Factoring Fees, Accessorials & Desk Productivity

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Calculate the gross margin and margin % of eight loads, look up each lane's miles with XLOOKUP, compare margin per mile and pool the margin % of the whole set.
• Read the market range of a lane from ten past carrier payments with MIN, MEDIAN and MAX, turn the expected buy rate into a quote at a target margin, protect it with a floor price, round it with CEILING and judge a counter-offer.
• Price accessorial events from a fictional rate table with XLOOKUP, bill only what was invoiced with IF, count unbilled quantities with SUMIFS and add up the leakage: money paid to carriers but never billed to shippers.
• Look up the fuel surcharge step for each week's fuel price with an approximate-match XLOOKUP, compare linehaul plus fuel with the carriers' all-in offers and test what a fuel price rise does to one load.
• Date each carrier payment with IF and WORKDAY, value the quick-pay discount the broker keeps, measure the days the broker is out of pocket and net a fictional factoring fee off one load's margin.
• Turn on-time loads, claims, tender acceptance and price into scores between 0 and 1, weight them into one score per carrier, rank the carriers with RANK.EQ and award the contract.
• Count the month's working days with NETWORKDAYS, measure loads per broker per day and margin per load, pick each broker's commission tier with an approximate-match lookup and total the desk's margin after commission.
• Summarise a month's loads by customer with SUMIFS, compare gross margin and margin % per customer, subtract overhead to get the operating profit, work out the break-even loads per month and chart margin by customer.