Microsoft Excel is a popular choice for logging mileage, whether for tax purposes or to reimburse employees for vehicle costs. But using a spreadsheet to track mileage is a flawed procedure that needs rethinking.
Why spreadsheet mileage logs break down
Are your employees padding their mileage? If your organization uses Microsoft Excel for as a mileage log, they might be. Although the Excel mileage log is not the best tool, it serves adequately as a mileage log. It's easy to understand, you enter all pertinent information from a trip, and formulas calculate the total mileage.
But there’s a cost. Using a spreadsheet mileage log often leads to overestimated mileage. Here are two reasons why:
Using Excel to track mileage leads to shortcuts by busy employees.
Mobile employees don’t have a lot of time. And this leads to shortcuts, such as estimating their mileage. They are tasked with their core job functions: sales, marketing, account management, merchandising. On top of that they find their schedules filled with internal meetings, committees, and training.
Then there are administrative tasks like managing email, maintaining their CRM, running marketing and sales campaigns, keeping track of receipts, and logging expenses. When you add a mileage log into the mix, it’s no surprise that many employees guesstimate their mileage – and not in the company’s favor.
Spreadsheet mileage logs amount to one more time-consuming task for employees.
Keeping an Excel spreadsheet is a time consuming and inconvenient process, especially for mobile employees. To maintain an Excel spreadsheet mileage log, your employees must take three steps each month:
- Keep a separate trip log of their travel, which includes addresses—because they are not in their office.
- Calculate mileage either by tracking odometer readings or by entering addresses into Google maps or MapQuest.
- Input data into the spreadsheet on a daily, weekly, and/or monthly basis.
You can see why many employees find ways to reduce the amount of time spent calculating mileage. Shortcuts such as rounding up or estimating mileage can cost your company thousands of dollars a year per employee in over-reimbursements, especially if you use the IRS mileage rate.
It can be a challenge for managers to verify accuracy of spreadsheet mileage tracking. They often don't know enough about the drivers' territories or the distances to know whether the mileage is padded. But there are three indicators to watch out for.
3 signs your Excel spreadsheet contains overestimated mileage
Here are three ways you can tell if employees are estimating rather than calculating mileage, along with three fixes:
The mileage amount is always a whole number.
If you see that mileage consistently ends with a 0 or .5, there is a good chance employees are rounding mileage. The distance between two locations is not always 20 miles; it could be 19.1 miles or some fraction of a mile.
How to correct this: Require employees to add the physical address as well as company name. Adding the additional information will take a little more time, but will allow administrators to easily audit the mileage calculation.
Productivity does not match employee mileage.
Lots of activity does not equal lots of productivity. Unfortunately, because a spreadsheet only holds data, it makes it difficult to measure how productive employees really are, or if they are just very active.
How to correct this: Make employees accountable. Create a column in the spreadsheet into which they must add a short business purpose for each trip.
When gas prices increase, so does employees’ mileage.
Because the mileage rate does not rise with gas prices, employees may add a little extra to their mileage totals in an attempt to keep up with gas prices.
How to correct this: During times of higher mileage, audit mileage calculations and activity vs. productivity.
You can follow this guide to improve your Excel spreadsheet. But you may be wondering whether there’s a better way to log mileage. And there is.
Mileage tracking alternatives to Excel spreadsheet logs
mBurse offers several 21st-century alternatives to mileage logs that will provide visibility, increase accuracy, and decrease time spent calculating and reporting mileage. The key is to incorporate mileage logs into mobile phones and integrate them with CRMs.
We offer three different approaches that increase automation, simplify the process, and ensure accurate results:
- GPS mileage tracking - an app that resides in mobile devices and records, calculates, and submits mileage
- CRM mileage log - integrate automated mileage with your existing CRM software to increase employee adoption of CRM
- Smart spreadsheet - continue using a mileage spreadsheet, but in an enhanced format that removes self-reporting
Click here for a mileage log demo or click below to take an in-depth look at current trends in business mileage tracking so you can make an informed choice about the best mileage tracker for your business.