Almost everyone starts tracking shipments in Excel, and that's a normal start: the spreadsheet is at hand, everyone understands it and it needs no implementation. It's time to switch to software when several dispatchers work in the file, it splits into different versions, and finding out what customers owe or how the month went means pulling data together by hand. This is about day-to-day tracking of trips, trucks and payments, not bookkeeping: taxes and financial reports are a question for your accountant.
Below: how to keep records in a spreadsheet while it still copes, the signs that it no longer does, and how to switch to software without stopping work.
How to track shipments in Excel
A spreadsheet usually holds trips, trucks, drivers, customers, rates and payments. To make the file last longer, agree on a few rules:
-
One row per trip. Date, route, customer, truck, driver, rate, status. Everything about a trip goes in one row, not on different sheets.
-
Reference lists on separate sheets. Customers, trucks and drivers are picked from a drop-down list. That way one customer won't turn into three because their name was spelled differently.
-
Payment in separate columns. Amount, invoice date, payment date. A filter will show at once who hasn't paid.
-
Totals on a separate sheet. Build the summary by month, truck and customer from the trips sheet using formulas or a pivot table, rather than filling it in by hand. Then the totals won't drift away from the trips.
-
One shared file. Google Sheets or a file in a shared folder, not copies sent around by email and messenger.
A small company can manage with a spreadsheet like this for a long time: while there are only a few trucks, one dispatcher and customers you can list from memory. The problems start when the number of people and trips grows.
Where the spreadsheet breaks down
-
Several dispatchers. Two people edit the file at the same time, someone accidentally overwrites a colleague's row, and it's no longer clear which rate is right.
-
File versions. One dispatcher has "Trips_summary_final.xlsx", another has "Trips_summary_new.xlsx". Which one is right gets sorted out over the phone.
-
Customer debts. To see who owes what, you have to filter the trips, add up the amounts and check them against payments. While that's being done, the debtor gets another truck.
-
A monthly report done by hand. Trips by truck, earnings by customer, a list of debtors: every month someone puts it together from scratch, and each time a little differently.
-
No history. The rate in a row has been changed, but who changed it, when and why is hard to find out quickly. If a customer disputes the amount, you have nothing to go on.
-
Phones. Editing a big spreadsheet on a phone while on the road is awkward, so statuses get entered in the evening, from memory.
If this sounds like your company, look at the general signs too, in the article on when a business should move from spreadsheets to a CRM.
What freight tracking software gives you
Software stores the same data as the spreadsheet, but links it together and works out by itself what is now pulled together by hand. Data is entered once, and everyone who needs it sees it broken down the way they need.
-
Everything in one place. A customer's record shows all their trips and current debt; a truck's record shows its trips and driver.
-
Trip statuses. Planned, in transit, delivered, paid. The dispatcher changes the status, and management sees it immediately, without a phone call.
-
Reports without manual merging. The month's trips, earnings by truck or customer and the list of debtors are compiled from the data dispatchers already enter.
-
Reminders. The software tells you itself when a customer is late with a payment, so there's no need to go through the whole file once a week.
-
Access from a phone. The dispatcher updates a status from the road, and management checks the summary in the evening without opening a laptop.
-
Access rights. Everyone sees and edits their own data, and nobody accidentally deletes someone else's trips.
Off-the-shelf software or your own
Off-the-shelf software and CRMs cover standard tracking: trips, customers, payments. You can start without any development, but you'll have to work the way the software's creator intended. Features and terms change, so check the current ones on the provider's website.
You need your own software when your tracking doesn't fit a ready-made product: a special rate structure, reports in the form management wants to see them, a connection to your website, Telegram bot or another system. A system like this is built around your process, not the other way round; there's more on our CRM and ERP development page.
Testing an off-the-shelf program is simple. Take a few common situations from your work, such as a trip with two unloading points or payment in instalments, and see how the program handles them. If each one needs a workaround, that's a sign to look at building your own.
It's convenient to handle trip tracking and incoming enquiries in one system. We covered what it should include for carriers and freight forwarders in our article on CRM for freight transport.
How to switch without stopping work
A switch disrupts work when it's done in a single day, for everyone at once and with the entire history. It's less stressful to go step by step:
-
Tidy up the spreadsheet. Remove duplicate customers and bring trucks and drivers together in a single list. These reference lists will be the first to move into the software. While you're at it, agree which trip statuses you have and what they're called: in the software they'll be the same for everyone.
-
Start with one dispatcher. Let them work in the software and point out what's awkward. Fix that before moving everyone else over.
-
Set a switchover date. Move open trips into the software, and from that day on enter new ones only there. The spreadsheet stays for reference, but nobody writes in it any more.
-
Load the history later. Past trips can be moved over from the spreadsheet afterwards, once day-to-day work is already running in the software.
-
Check the first month. Compare the software's report with what you would have got from the spreadsheet. If they match, you can close the old file for good.
Checklist
-
One file. Do all dispatchers work in the same spreadsheet rather than their own copies?
-
No duplicates. Is each customer entered once and under one name?
-
Debts. Can you say right now which customers owe you money and how much?
-
Monthly totals. Can last month's report be put together without merging files by hand?
-
Statuses. Can you tell from the spreadsheet which trips are on the road right now?
-
Cover. If a dispatcher falls ill, can a colleague carry on with their trips using the spreadsheet?
If your spreadsheet can no longer cope, we'll help you move to tracking software: we'll look at what you track now and tell you whether an off-the-shelf system will do or you need your own. See what we do for logistics companies, and an example of our work in this industry: the Renoda Logistics website.