Power BI · Other
Power BI job life cycle, exception and debtors reports for a freight forwarder
A set of Power BI reports for a freight forwarding and customs clearing company with many branches. They follow every job from creation to closure and flag exceptions, pending billing, debtors and profit.
- Client
- A freight forwarding and customs clearing company with many branches and a finance team at head office
- Industry
- Other
- Platform
- Power BI
- Started from
- Power BI Dashboards product
01The challenge
Job data sat in the operating system and in exports. Branch managers could not easily see which jobs were signed off but not billed, billed but not closed, or open for weeks. Finance chased missing salesmen and coordinators by hand, jobs closed with a leftover neutral balance went unseen, and debtor ageing came from separate sheets.
02The solution
We built Power BI reports on the job and finance extracts. One job table is the base and is joined to revenue and cost data and to salesman and coordinator lookups. The reports cover the job life cycle, exceptions, debtors, profitability and branch visibility, with separate report pages for customs clearing import and export. Every counter opens the list of jobs behind it, and row level security shows each branch only its own jobs while finance sees all of them.
How a job is tracked from creation to closure
- Job openedEach job comes from the operating system with its branch, customer, salesman, client coordinator and job date. Total jobs and open jobs are counted by branch.
- Signed offJobs are split into signed off and not signed off. Jobs still unsigned after 60 days are flagged, and a pending sign off page lists them by age band.
- BilledBilled and unbilled jobs are counted, with draft invoice counts by branch. A pending billing page lists the top jobs with cost but no revenue, and the top ten for each branch.
- ClosedClosure percentage is shown by branch. Jobs billed but not closed, and jobs closed but not complete, are listed.
- Net neutral checkedJobs closed with a leftover neutral balance below minus 100 or above 100 are listed so finance can clear them.
- Exceptions clearedJobs with no salesman or no client coordinator are counted by branch so the data is fixed at the source.
- Collected and reviewedDebtor ageing and a debtor tracker show what is outstanding. Profitability pages show gross profit by branch, job, customer and month.
Screens from the reports
Sample data only. These screens show the layout and flow, not real records.
Screen 1 of 7Built for this client
Job summary
Counters for total, closed, signed off and billed jobs, closure by branch and net neutral bands.
What we built
- Job life cycle reportSummary counters and lists from open to closed, signed off and billed.
- Exception reportMissing salesman or coordinator, net neutral outside range, slow sign off and pending billing.
- AgeingAverage days between each stage, by branch and by customer.
- Debtors reportAgeing buckets, a debtor tracker and a summary.
- ProfitabilityGross profit by branch and job, by customer, by difference and by month.
- Customs clearing pagesSeparate import and export reports on the same job model.
- Branch securityRow level security by branch, with finance in every role.
- Technical documentEach report has a document of its measures, tables and column logic.
Built with Power BI · DAX measures · Row level security
The result
- 60 daysJobs not signed off after this time are flagged
- Every counter opens the job list behind it
- Each branch sees only its own jobs, finance sees all
- Ageing, debtors and profit in the same reports
More case studies
Power BI asset performance dashboards for a renewable energy portfolio
A Power BI report that follows generation, losses, breakdowns and module cleaning across every solar plant in a portfolio, from an executive summary down to the inverter.
Read the case study →Power BI · Retail and consumer goodsPower BI sales dashboards for digital channels and prescription sales
Two Power BI reports that bring sales data from every channel into one view. Digital sales covers partners, marketplaces, website and products, and Rx sales covers prescribers, zones, brands and field teams.
Read the case study →Power BI · Food and beverageA Power BI supervisor scorecard for a multi-branch cafe chain
A Power BI report that gives branch supervisors one scorecard for sales, costs, delivery partner times, loss prevention, customer feedback and stock, each shown against target, with the standards documents one click away.
Read the case study →Planning something similar?
Tell us what you need. The first call is free.