Freight brokerage dashboard: views and checks

A freight brokerage dashboard compares completed-load margin and tender acceptance by desk, shipper, and lane. Start with separate load and tender exports, reconcile their totals, and then arrange the results around the decisions each desk owns. A chart is useful only when its denominator and cutoff are clear.

Start with two exports and a written cutoff

Use a load export with load ID, shipper ID, lane, desk, pickup date, delivery status, net shipper revenue, and carrier cost. Keep accessorial revenue and cost on the same basis. Specify whether the margin is provisional or closed and whether claims or other adjustments are included.

Use a separate tender export with offer ID, shipper ID, desk, offered date, final decision, and accepted load ID where available. Keep open offers visible but outside a final-decision acceptance rate until they receive a decision. Document any exclusions, such as offers canceled before a response was required.

For definitions beyond this dashboard layout, use the existing freight broker and 3PL sales KPIs. Agree on those definitions before comparing people or desks.

The example below is an illustrative US brokerage reviewing a fully resolved weekly cohort. All accepted offers in this small cohort have completed loads. In a live export, accepted freight may still be waiting for pickup or delivery; retain those statuses rather than forcing the counts to match.

Build the completed-load view

Put this compact export on a worksheet named Loads, with the header in row 1. Amounts are USD. Column G is computed from E less F.

In G2, fill down through G5:

=E2-F2

The first row returns 2,400 - 2,000 = $400. The total check is 9,000 - 7,700 = $1,300, equal to the sum of G2:G5. Tie revenue and carrier cost separately to the corresponding source report totals before interpreting the difference.

For each desk, sum matching revenue and cost rows. With a desk name in summary cell A2, revenue is:

=SUMIFS(Loads!$E$2:$E$5,Loads!$D$2:$D$5,$A2)

Replace E with F for carrier cost and G for margin. Microsoft's SUMIFS documentation explains summing rows that meet criteria; additional criteria can select a shipper or lane without changing the measure.

The detailed margin per load by shipper and lane page owns the profitability interpretation. Here, the task is to make its numerator and load population reconcile across views.

Put tender decisions beside the load results

On Tenders, enter these columns in A:E. A dash means there is no accepted load ID; leave that cell blank in the workbook.

These are unique eligible offers with final decisions. With the desk in summary A2, offered and accepted counts are:

=COUNTIF(Tenders!$C$2:$C$8,$A2)
=COUNTIFS(Tenders!$C$2:$C$8,$A2,Tenders!$D$2:$D$8,"Accepted")

Microsoft's counting guide documents using COUNTIFS when multiple conditions must hold. These formulas assume duplicate events and out-of-scope offers have already been removed from the analysis population.

If offered is B2 and accepted is C2, acceptance is:

=IF(B2=0,"",C2/B2)

Format that result as a percentage. A blank denominator result means no eligible offers, rather than a measured acceptance rate of zero. Primary tender share answers a separate question about routing position; acceptance alone does not reveal whether the broker was the first call.

Arrange the desk review around comparable totals

The source rows produce this summary. Percentages are rounded to one decimal place; all calculations use unrounded amounts.

Total acceptance is 4 / 7 = 57.1%. Margin per load is 1,300 / 4 = $325; margin percentage is 1,300 / 9,000 = 14.4%. Recompute each total from its numerator and denominator instead of averaging the displayed desk percentages.

Keep the overview small: desk comparisons first, then the shipper and lane rows behind them, then unresolved reconciliation items. Central earns more margin per load, while South retains a larger share of revenue. The table establishes that difference; the individual loads explain what to investigate next.

Where it goes wrong

• Joining tender events onto every charge line. Repeated events or accessorial lines multiply revenue. Aggregate each export to its intended row level before comparing totals.
• Mixing cohorts. Offers received this week and loads delivered this week answer different questions. Label both dates; use linked cohorts when examining conversion from offer to delivery.
• Changing desk ownership retrospectively. A current account assignment can rewrite historical performance. Use the agreed owner as of the transaction date.
• Reading missing costs as zero. Unbilled carrier charges make margin look better. Keep provisional loads separate until the expected costs are complete.
• Dropping exceptions to make a chart tidy. Unmapped shippers, lanes, and desks belong in an exception total that still contributes to the overall reconciliation.

Covirage is not affiliated with Microsoft.

Compare your own freight exports in Covirage

Upload a load export and tender log with their cutoff dates and identifiers; Covirage maps the columns, and its tools compute and check the supplied margin and tender comparisons. The external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own export.

See Covirage for freight brokers and 3PLs for the supporting industry context.