The monthly spreadsheet that takes three days
Every month, someone exports jobs from the forwarding system and spends days turning them into a management report. Port codes are inconsistent: the same Chinese port appears three ways. Some jobs are marked as sea when they were sea-air. Costs for last month are still arriving. The directors get a report that says turnover is up and profit is flat, but cannot tell which lanes or customers are behind it.
Salespeople argue about which accounts are theirs. The report is out of date as soon as it lands.
Why the data does not add up
Forwarding systems hold all the information, but in the form operations need, not the form management needs. Turning one into the other is manual.
- Ports, lanes and modes are coded inconsistently across jobs and branches.
- Costs arrive after jobs are invoiced, so recent months look better than they are.
- Customer groups and salesperson allocations are held in spreadsheets.
- Each month's report is rebuilt from a fresh export.
- Nobody can drill from a total to the jobs behind it.
What it costs
Decisions about pricing, sales focus and which lanes to grow are made without good information. Salespeople are managed on turnover because profit cannot be trusted, which quietly rewards volume on thin margins. Someone skilled spends days a month on data cleaning. And when a lane's margin falls, nobody notices until months later, by which time the rates behind it have been repeated on many more jobs.
The gross profit reporting we build
- Job revenue and costs are read from your forwarding system every day.
- Ports, lanes and modes are cleaned using mapping rules we set up with you, so the same lane is always grouped together.
- Customers are grouped by parent company, and salesperson and branch allocations are held in one place rather than spreadsheets.
- Jobs still awaiting costs are marked, with estimated costs where you use accruals, so recent profit is not overstated.
- Dashboards show gross profit by lane, mode, customer, salesperson and branch, with trends, and every figure drills down to the jobs behind it.
- Alerts can flag lanes or customers whose margin falls below a level you set.
| Question | Monthly spreadsheet | Profit reporting |
|---|---|---|
| Which lanes make money? | Rebuilt each month | Always current |
| Is recent profit real? | Unclear, costs still arriving | Estimated and final marked |
| Which salesperson? | Arguments | Allocations held centrally |
| Why did margin fall? | Unknown | Drill down to jobs |
After
Management sees profit by lane and customer whenever they want it. Sales conversations are about margin, not only turnover. Falling margins are noticed quickly. And the person who used to build the monthly spreadsheet can spend their time on analysis instead.
Branch managers see their own figures daily rather than waiting for head office. A lane that looked healthy on turnover but has been losing margin for three months shows up in the trend, and the drill-down shows whether it was buy rates rising, sell rates falling, or a single large customer on a special rate. That is the kind of question the monthly spreadsheet could never answer in time to act.
Checklist
- The management report is rebuilt from exports every month.
- Port and lane codes are inconsistent.
- Recent months look better than they turn out.
- Salesperson allocations are disputed.
- You cannot drill from a total to the jobs.