A freight brokerage is, in part, a working-capital business. Carriers want to be paid in days — many take a quick-pay discount to get there — while shippers stretch terms to 30, 45, or 60. The gap between when you pay out and when you collect is days of cash the brokerage has to float, and on thin margins that gap can be the difference between growing and stalling. This report puts days-to-collect against days-to-pay and quantifies the cash gap.
What this report answers
How much is outstanding in receivables and payables right now? On a dollar-weighted basis, how long do customers take to pay us, and how long do we take to pay carriers? What's the resulting cash gap — the net days we float? And how much of our carrier spend is going out on discounted quick-pay? The Working Capital Position page carries the outstanding balances, the weighted day counts, AR and AP aging, and the gap; the Collections & Quick-Pay page ranks the slowest-paying customers and shows quick-pay penetration.
The metrics that matter
Both day counts are dollar-weighted — SUM(amount × days) ÷ SUM(amount) — not simple averages, so a pile of tiny or blank invoices can't distort the number; a large slow-paying invoice rightly counts more. Cash Gap (DSO minus DPO) is the headline: a positive gap means you're financing your carriers ahead of your customers paying you. Quick-Pay Penetration shows how much margin you're trading away for speed — a lever you control when the gap gets tight.
Why the data is trapped
Receivables and payables straddle two systems. Customer AR and carrier AP often live in QuickBooks, while the load and settlement detail that explains them lives in the TMS (McLeod, Tai, Aljex). Computing a dollar-weighted DSO and DPO, aging both sides, and netting them into a cash gap is not a native report in either system — it's a controller's spreadsheet, rebuilt each month. This template models AR and AP together and computes the weighted metrics for you.
How to read it
Read the cash gap first: a widening gap means you're floating more days, which on thin margins strains the line of credit. Use the AR aging and the slowest-payer ranking to target collections where the dollars are, and read quick-pay penetration as a deliberate trade — paying carriers faster costs margin, so it's worth knowing how much of the book is on it and whether the cash-gap relief is worth the discount. The sample on this page uses entirely synthetic data — generic labels and made-up dollar figures, no real loads, carriers, or customers.
Metrics it tracks
| Metric | What it means |
|---|---|
| Total AR Outstanding | Open customer receivables — invoice amount on AR rows still open. |
| Total AP Outstanding | Open carrier payables — invoice amount on AP rows still open. |
| Weighted Days-to-Collect | Dollar-weighted DSO on receivables (SUM of amount×days ÷ SUM of amount) — avoids skew from small or blank rows. |
| Weighted Days-to-Pay | Dollar-weighted DPO on payables (SUM of amount×days ÷ SUM of amount). |
| Quick-Pay Penetration | Carrier spend paid via quick-pay ÷ total carrier payables (SUM/SUM) — share on discounted fast pay. |
| Cash Gap (DSO - DPO) | Weighted days-to-collect minus weighted days-to-pay — the net days the brokerage floats; both halves are SUM/SUM. |
Used by: Brokerage owners, controllers
Frequently asked questions
What is the cash gap in a freight brokerage?
It's days-to-collect minus days-to-pay — the net number of days between paying your carriers and getting paid by your shippers. Because carriers want fast pay and shippers stretch terms, the gap is usually positive, meaning the brokerage floats the difference. On thin margins, that float is a real cost and a real constraint on growth.
Why use dollar-weighted days-to-collect instead of a simple average?
A simple average treats a $200 invoice the same as a $20,000 one and gets thrown off by tiny or zero-dollar rows. Dollar-weighting (SUM of amount×days ÷ SUM of amount) makes large invoices count proportionally, which is the honest measure of how long your money is actually tied up.
Can I build this on my own AR/AP data?
Yes. Export your open and paid customer invoices and carrier payables from QuickBooks (with days outstanding and pay method) and use this as a template — we model it into a Power BI report. The sample uses synthetic data, so there are no real invoices in what you see here.
Build this report on your own data
Clone this Freight brokerages template — describe it and we’ll generate sample data so you can try it free, or upload your own export. You get a fully modeled, branded Power BI project that opens in Power BI Desktop.
Use this as a template →