An interactive Power BI analysis using synthetic shipment data in the Truckload mode to analyze profitability, compare carrier costs, model margin targets, and prioritize lanes for follow-up. The goal is to give the Carrier Sales and Pricing teams a clear path from identifying a problem to deciding what to review.
Establishes the financial picture first. The page shows weighted margin performance by customer, lane, and month, along with revenue, carrier cost, shipment volume, and low-margin lanes so the user can quickly see where performance is strongest and weakest in the Truckload mode.
Gives the user a what-if modeling board instead of forcing one fixed target. After selecting a lane and target margin, the report calculates the carrier cost required to reach that target margin while holding revenue constant. This allows Carrier Sales and Pricing teams to test different margin targets by lane and see what carrier cost would be required to make those targets possible. The resulting modeled opportunity can help frame carrier negotiations and identify where cost reductions may be worth pursuing. Modeled opportunity is not guaranteed savings, but provides a practical estimate of what carrier cost would need to be to achieve the selected financial target.
The Priority Lane Action Plan turns the profitability analysis into a lane-level review process, with priority rankings. Each lane is evaluated using its current margin, Median-Benchmark Opportunity, and the number of loads that moved with carriers priced above the lane benchmark. The Median-Benchmark Opportunity is more conservative than the what-if model on Page 2 because it uses the lane's own historical carrier-cost performance as the benchmark instead of a user-selected margin target. That makes it more realistic for prioritization because the median reflects a cost level that has already been achieved on the lane, without assuming every carrier can be pushed to the lowest observed rate. The page uses these factors to rank lanes by priority with a recommended action, and also lets Carrier Sales and Pricing teams review the individual carriers contributing to the opportunity.
Each carrier's average cost per load is compared with the median carrier average cost on the same lane for the selected period. When a carrier is above that benchmark, the excess cost per load is multiplied by that carrier's load count. Those amounts are summed to estimate the lane's modeled opportunity. It is an analytical benchmark, not guaranteed savings.
Priority is calculated using defined margin and opportunity thresholds.
Margin below 11%, or margin below 15.5% with Median-Benchmark Opportunity of at least $900. These lanes should be reviewed first, with the recommended action to Prioritize Carrier Mix Review and focus on whether carrier selection or rate structure should change.
Margin below 18%, Median-Benchmark Opportunity of at least $350, and at least five loads moved above the benchmark, or margin below 19% with opportunity of at least $750. The recommended action is to Validate Carrier Cost Improvement and confirm whether the modeled opportunity is realistic before taking action.
Any remaining lane with benchmark opportunity is Low Priority. The recommended action is to Monitor Carrier Spread because some cost opportunity exists but does not create the same immediate financial pressure. Lanes with no benchmark opportunity receive No Action.
The project was built in Power BI using DAX and Power Query.
This project uses synthetic freight data created for portfolio demonstration purposes, with realistic transportation patterns modeled to support the analysis. No proprietary company, customer, or carrier data is included.
