IFTA Reporting

I use my German Dezl to keep track of it. Each quarter I print it out. It keeps track of all miles driven in each state and I enter my fuel purchases.

 

You can use links to hook that directly into a yearly report sheet in the same workbook.

 

Yop, you could. The issue is...

The IFTA spreadsheet is based on a template. So each quarter there is a new spreadsheet saved as a file. That new file is not linked to the annual spreadsheet.

I keep each quarter as it's own file specifically to segregate them out. The annual sheet is nothing but a running average for the year for me and it can be done without if I don't feel like doing anything in it. Each year of operation will have it's own worksheet in this file though allowing me to have a life average as well if I care to develop it that far.

 

MN, most spreadsheet software can link to other files, not just other pages inside the file.

My fuel card keeps track of fuel purchased by state and I can generate monthly reports with subtotals for each state. Add the three months together and it's easy.

For mileage, I use the pen and paper method. I print out a 'trip report' sheet, and write the mileages (usually just the last four) on the back of it. When done with the trip, figure the mileages for each, and then transpose them to the front of the report with the routing, pricing, figure the price-per-mile, additional pay or expenses (lumpers, detention, etc), and after scanning the BOLs to send, staple it all together and file it away. At the end of the quarter, get my big stack of paperwork out and add up the state-by-state totals. Takes me an hour or two once a quarter, but I prefer it over doing it separately for each trip.

 

sly,

You are correct, they can. All of it with OLE. I used to teach advanced MS Office and how to program with VBA at a 300 level college course. I could make all of these a LOT more complicated than what they are.


Personally, I don't like linking across files. Move one file and all those links are screwed up until your fix them

 

Its whatever works for you. If you're looking in... the "XYZ for Dummies" book on spreadsheets is probably a good buy. It's not that they're dumbed down, but they concentrate a lot of info in a small space without the boring crap you go through in standard computer user manuals. The thing is you have to pump enough data through them, and check the calculations/results enough to have confidence that the darn things are working properly. I do cross check sums throughout the data entry pages just so I can look and see that things are adding up properly...

Once you get to that point, they save a bunch of work. But you have to do the hard work, learn how this software product works, and then dredge up those ol' arithmetic classes from the dusty ol' storage room in the basement of your mind! LOL!!

Or spend the money on someone's canned product to do it for you.

 

It's funny you should mention it.

When I taught it, the bookstore wanted to know WHAT I wanted for a textbook.

I choose several "XYZ for Dummies" books for my text material. It was used in the college for over 11 years just getting new editions as the software changed.

It's written for non-programmers and much easier to understand.

The course I taught.
https://webproc.mnscu.edu/registrat...campuses/072/terms/20135/curriculums/00137214

 

Y'all probably didn't know this....but I, Otter, was the Poster Boy for all the "Dummies" books!!!!

And people think being on the cover of Sports Illustrated is something to brag about.....

 

Those dummies books have saved a bunch of people headache. I would consider them to be one of the best layperson books if you want to use a spreadsheet.

 

You need to audit those reports carefully. I use these in two trucks for IFTA miles. I don't enter fuel purchases as it's redundant to what I already do (QuickBooks) and tedious to do on the gps. I do use the trip reports off the gps. Not the jurisdiction recap though, because it is calculated from the trip reports that always contain errors.

The most common is duplicate entries. The two gps that I have were bought a year apart and they both do it. Usually at the end of a quarter in the last 3-4 trips. The bad thing about that particular error is it will inflate your mpg basis and result in underpayment. Once in a while the gps will drop a trip. Usually that's what happens if the unit locks up and has to be restarted. I probably see one or two a year on each. If that weren't enough, there's odometer calibration. The odometer will never be as precise as a gps, and it will disagree with what's on the reports. Easily checked against repair invoices by an IFTA auditor.

So I do a couple of things to achieve accuracy when I file. On the gps we true up the odometer number in the truck profile when it gets more than about 10 miles different, usually every 2-3 weeks. When I pull the trip reports, I sort them by starting odometer and run a new column that shows the difference from each starting number to it's corresponding finish number on the previous trip. Normal is a value less than 1. Every 2-3 weeks a jump up/down of 10-15 miles is the adjustment we do. Large numbers will appear on duplicates and dropped trips, making them easy to spot and fix manually.

Once the table is cleaned up, the formulas I use to produce the state mile chart apply the odometer error proportionally to all states. The numbers that go in the IFTA return will match the truck odometers if audited against repair invoices or other recorded events (repair invoices most common) where the location, date/time, and odometer reading are known.

Now that sounds really complicated, but it isn't. For the driver, it's just a matter of turning the gps on when moving and recording fuel expenses at the end of a trip. Bare minimum to take the burden off while driving. When I sit down to do IFTA it takes me about 15 minutes to prepare the mileage workbooks from plugging in the gps units to a finished report, and less than 5 minutes to do the fuel by state. Then another 10 minutes or so to type all the values into the state's web-based tax form, submit and pay all online. Print out the return and payment confirmation for my files and I'm done.