Module 03 · Predictive Budgeting & Risk
Why did freight cost move, what does next year cost, and did the savings we promised actually land?
Linehaul is modelled as volume times miles-per-pound times rate-per-mile, and decomposed by sequential substitution into Volume, Mix, Network and Rate, with Fuel and Accessorial separated out. The six factors sum exactly to the actual change — the residual is zero by construction, so there is nothing to argue about. That matters because the headline number almost never tells the truth: freight up twelve percent can mean the network team delivered a large structural saving that was swamped by growth, or that they delivered nothing. Only the decomposition distinguishes those two.
Who uses it
Logistics finance, cost control, anyone presenting a freight budget
When
Monthly for variance, annually for the budget build
Time to first answer
Bridge computes in about 1 ms; the Monte Carlo in about 9 ms
Quick start
From actuals to a defensible budget
Load actuals on tab 1
Either upload monthly actuals or use the generator. The capability strip immediately shows which of the four decomposable factors your columns support. miles is the important one — without it, Network cannot be separated from Rate.
Pick two periods on tab 2
Base and compare. The bridge appears immediately, with the residual shown at the bottom of the factor table. It should read zero.
Read the narrative block, not just the waterfall
Underneath the headline it splits the change into what was outside your control, what procurement moved, and what operations and structure moved. That third figure is the one you defend.
Check the structural effect line
Mix + Network combined is highlighted in the factor table. Widening a consolidation radius shifts distance-band and mode mix as well as density, so the gain lands in both factors. Verify against target on the combined figure, never on Network alone.
Build the budget on tab 3
Set growth, rate increase, network target, diesel and accessorial. Then set the sigmas and read P10 / P50 / P90. Report the band, not the point.
Screen map
What each tab is for
Tab 2 answers what happened. Tab 3 answers what happens next. Tab 4 is what stops the tools drifting apart.
| Tab | What it controls | Leave it alone unless |
|---|---|---|
| 1. Data | Upload or generate. The capability strip tells you which factors your data can support. | Never — and read the capability strip every time. |
| 2. Bridge | Period selection, decomposition method, the six-factor table, unit cost metrics, fuel split, segment contribution. | Never. |
| 3. Budget | Drivers, network target import from the optimizer, realization rate, Monte Carlo settings, budget bridge. | You are only doing variance analysis this cycle. |
| 4. Assumptions | Fuel surcharge formula, the shared assumption registry, and the method documentation. | Never — export the registry every cycle. |
Reading the output
The six factors and what to do with each
Group them by who can act. That grouping is what turns a variance report into a management conversation.
| Metric | What it means | What to do about it |
|---|---|---|
| Volume | Change in total shipped weight at last year’s mix, density and rates. | Not controllable. State it plainly and move on — this is usually the largest single factor. |
| Mix + Network | Segment composition change plus within-segment miles-per-pound change. | This is the structural result. Compare it against what the routing and network studies promised. |
| Rate | Contracted rate movement, excluding fuel. | Procurement owns it. Compare against the market and against your own renewal calendar. |
| Fuel | Fuel surcharge movement, further split into a miles effect and a diesel price effect. | A large price effect is a hedging or FSC-renegotiation conversation, not an operations one. |
| Accessorial | Detention, overages, expedites, reconsignment. | Controllable and usually neglected. Take it into the Cost-to-Serve tool to find the root causes. |
Use cases
Three questions this actually settles
Use case 1
Freight is up twelve percent. Explain it to the executive committee.
| Set up | Tab 2 → select last year as base, this year as compare, sequential substitution. |
|---|---|
| Watch | The narrative block splits the change three ways. A typical result: volume growth and fuel add far more than the headline, procurement adds a few points, and structure gives a large amount back. |
| Decide | Present the three groups, not the six factors. “Volume grew fifteen percent and we absorbed it into a twelve percent cost increase because structure returned five” is a completely different meeting from “freight is up twelve percent”. |
Use case 2
Build next year’s budget with a defensible risk band.
| Set up | Tab 3 → set growth, rate increase, diesel and accessorial. Import the network result from the Consolidation Optimizer and set a realization rate. Then set sigmas on the three uncertain drivers. |
|---|---|
| Watch | P10, P50, P90 and the variance contribution chart. In testing, volume growth accounted for over 80% of budget uncertainty while diesel accounted for under 5%. |
| Decide | Commit to P90, not P50 — committing to the median means being wrong half the time by construction. And if volume dominates the variance, improving demand planning beats fuel hedging by a wide margin. |
Use case 3
Did the network project deliver what it promised?
| Set up | Take last year’s network target from the budget. Run the bridge on the completed year. |
|---|---|
| Watch | The Mix + Network combined figure against that target. |
| Decide | Within about ±20% of target, the model is working and next year’s promises can be trusted. Far outside it, correct the underlying assumptions — usually fill rate or circuity — before promising again. This comparison is the entire reason the tool set exists. |
Pitfalls
Where people misread this tool
Every one of these has actually happened during testing.
Network alone is not the structural result
In testing, an aggregate miles-per-pound improvement of −9.3% appeared as only −1.1% in the Network factor, with the rest landing in Mix. Both are correct — widening consolidation changes segment composition too. Reading Network alone would have made a successful project look like a failure.
The substitution order must never change
Sequential substitution is order dependent. Volume → Mix → Network → Rate is documented on tab 4 and should be treated as fixed. Changing it produces different factor values and destroys comparability with prior years. Consistency matters more here than absolute precision.
Without the miles column, Network disappears into Rate
Miles per pound is the only way to separate structural change from rate change. If your actuals do not carry mileage, the tool will still run but the one factor you most want to measure will be invisible. Sorting this out at the source is usually the highest-value data project in the whole programme.
A theoretical optimum is not a budget target
The optimizer reports what is achievable in principle. Delivering it in full inside one budget year does not happen. The realization rate exists for this — default 60% — and a warning appears when the imported figure is aggressive. In testing, feeding the raw figure straight through produced a budget nearly thirty percent below base.
An import can be silently wrong
The tool refuses a network result that was measured against an unconsolidated baseline rather than against current actuals, because using it collapses the budget. If the import is rejected, go back to the optimizer, import the assumption registry first, then export again.
Data in and out
Files this tool reads and writes
| Direction | File | Purpose |
|---|---|---|
| In | actuals_24m.csv | Monthly actuals. Required month, volume_lb, linehaul_usd; strongly recommended miles, fsc_usd, accessorial_usd, mode, dist_band. |
| In | network_performance_for_S2.csv | Structural improvement from the Consolidation Optimizer, applied to the budget target after the realization rate. |
| Out | assumption_registry.csv | Actual-based rates, miles per pound, accessorial ratio and volume — the single source of truth for the other three tools. |
| Out | freight_budget_variance_analysis.csv | Full bridge with both decomposition methods, unit metrics, budget and Monte Carlo results. |
Where it sits in the loop
This is where the loop closes. The other tools make predictions; this one measures whether they came true, and exports the assumption registry that keeps all four working from the same numbers. Without this stage the set is open-loop — predictions are made every year and never checked.