Fuel Record Keeping for Business Fleet Vehicles

03. Adding QuickBooks Accounts for Types of Fuel

2012-01-07T10:29:00.003-05:00

This is part of a tutorial on how to track fuel purchases and gallons using QuickBooks® by Intuit.

These steps create a report that separates and subtotals fuel gallons by fuel type, truck, and even state. While you are bookkeeping, you can also record gallons, killing two birds with one stone.

The tutorial starts here.

Review

Previous posts:

Explained that you can use QuickBooks®, Quicken and other bookkeeping applications to record fuel gallons
Listed the benefits of keeping fuel records in your electronic ledger
Presented a short video of the fuel record keeping process
Explained how to create a main fuel account to enable fuel gallon reports

IFTA Reportable Fleets

If you do not know what IFTA stands for, then you hopefully do not fall under IFTA regulations. IFTA reportable fleet owners must separate fuel by type. For example, they must separate fuel used by IFTA reportable vehicles from that used by non-IFTA reportable vehicles. Creating a sub account for IFTA Reportable Fuel accomplishes this.

Fleets containing vehicles that use both Gas and Diesel must also separate fuel gallons by type as well as IFTA and non-IFTA use. Sub accounts make this possible as well.

Other Fleets & Theft

Other reasons exist for keeping track of fuel purchases. You may become as detailed as desired to the point of separating gallons by vehicle.

Separating gallons by vehicle requires diligence from your drivers. You must require them to write the vehicle number on each fuel receipt. However, you can then reconcile gallon reports against mileage to expose any employee theft. For example, with accurate records, you may find a pickup that gets three miles per gallon highly suspect. The information gained from good record keeping may surprise you.

This Post

This post explains how to track more than one fuel type using sub accounts of the parent Fuel Account. These instructions use QuickBooks®. For other bookkeeping applications, consult the application's documentation for how to create accounts and sub accounts.

You should have already set up a main account for fuel as instructed previously. If not, return to the beginning of this tutorial.

Using sub accounts for each fuel type is helpful if you collect receipts from employees who purchase fuel.

You may download credit card transactions before employees submit receipts. In that case, you may not know the type of fuel for each fuel purchase when adding the downloaded transactions to your ledger.

If you select the main Fuel Account at that time, you can at least record the purchases as fuel. Later, when you have the receipts, you can select the proper sub account for the fuel type.

Creating the Sub Account

The following example illustrates how to create an IFTA Diesel account. Repeat the steps for other sub accounts (fuel types) if desired. A short video at the end of this posts illustrates the entire process.

Select Chart of Accounts from the Lists menu.

Select New from the Account menu:

Select the radio button next to Expense as the Type of Account.

Follow the same steps you performed previously when setting up the main fuel account. Refer to the video in Part 2 if you do not remember how to do this.

Type a name for Account in the Account Name text box.
Check the box next to Sub Account of.
Select the main Fuel Account from the drop-down box next to Subaccount of
Click the Save & Close button to save the account and close the Add New Account window.
Repeat for additional sub accounts (fuel types).

Now the QuickBooks® Chart of Accounts lists the fuel sub account below the main fuel account like this:

If your subaccounts are not indented see: http://www.fuelrecordkeeping.com/2011/10/indent-quickbooks-fuel-sub-accounts.html for how to fix this.

This short video illustrates the entire process.

Continue to the next post… or return in a day or two for the next installment. Check http://www.twitter.com/vbquick for news and updates.

Tell me what you think. Offer suggestions. Fill out my Feedback Form or sign up for email updates here.

Organize Truck Fleet Records Using Quickbooks® - Do-It-Yourself

2012-01-06T13:26:00.000-05:00

Organizing a fleet of trucks can be challenging for small business owners. Large corporations have the funds to purchase or produce fleet tracking software. Small business owners often lack the funds for expensive software or programmers but must still comply with the same laws.

Even if you are a small contractor with only a few trucks and do not report to DOT or IFTA, organizing your fleet will help organize like registrations, inspections, repairs, etc.

QuickBooks® has organized a fleet averaging over 60 vehicles and pieces of equipment for our business for over a decade. It remains the first and most important record of each vehicle and piece of equipment. That means all equipment records are accessible through QuickBooks® in addition to the Microsoft® Access database we maintain.

Database Design

Supplementing QuickBooks® with Microsoft® Access extends record keeping functionality. However, the database design must guarantee that if the database and all backups become corrupted, QuickBooks® and electronic files contain all the necessary data for records reporting.

In other words, the database should only serve as a reporting and organizing tool for QuickBooks® transactions and electronic files. The database should:

only store a minimum of data.
avoid duplicate or manual data entry by importing data from QuickBooks® or other bookkeeping applications
only store data available through other means for convenience. Original data should be maintained in:

electronic receipts
paper files
online records

Example of Data Stored in the Database

For example, the database may contain a field for VIN numbers that users enter manually. Registration cards already contain VIN numbers. Therefore, a reason must exist for entering them into the database. Reasons for storing VIN numbers in a database might include:

Reports that reconcile vehicles against insurance summaries.
Copy and pasting the VIN from the database for online registration renewal.
Confirming vehicle identities for recording mileage.

You may think of other reasons.

The Do-It-Yourself Approach

You need not be a computer genius to use QuickBooks® with Microsoft® Access. You do not need QODBC or knowledge of VBA. Microsoft® Access organized our fleet records long before incorporating QODBC or VBA. This is possible through QuickBooks® export feature.

QuickBooks® export feature lets you export reports like Asset Account to spreadsheets. A Microsoft® Access database can link to these spreadsheets so the data remains in QuickBooks® but can be accessed through the database.

Once in the database, the options are many and varied for how to set up and implement a fleet record keeping system. The system may be as simple or complex as your ability and desire dictate.

Summary

The Link Principle scratches the surface of fleet records but does give readers a running start on how to code computer files for organizing and reporting registrations, inspections, etc. The system uses file names instead of data entry to record and report fleet records. We use this record keeping system with Windows® but it works with any operating system.

A registration or insurance card viewed on the computer screen is the best proof you have aside from paper in your hand. The Link Principle includes illustrations on how to report upcoming registration renewals and inspections with a few keystrokes.

Small fleet owners with limited computer knowledge may still organize fleet records when using QuickBooks® and electronic files to their full potential.

Tell me what you think. Offer suggestions. Fill out my Feedback Form or sign up for email updates here.

02. Creating the Fuel Account

2012-01-04T19:33:00.001-05:00

This is part of a tutorial on how to track fuel purchases and gallons using QuickBooks® by Intuit.

These steps create a report that separates and subtotals gallons by fuel type, truck, and even state. While you are bookkeeping, you can also record gallons, killing two birds with one stone.

The tutorial starts here.

This post explains how to create a QuickBooks® account to track gallons. If you use another bookkeepiong application, see that application's documentation for how to creae a new account.

Fuel Accounts

To record gallons, you need to work with the QuickBooks® Chart of Accounts. A separate account for fuel makes reporting and separating gallons easier. Discuss changes to your books with your accountant. I am not an accountant. However, my accountant is fine with my Chart of Accounts and with my method of recording gallons.

You can create the Fuel Account as a Parent Account of the type Expense. Or you can create the Fuel Account as a sub-account of another Expense account. For example, you can create a Fuel Account as a sub-account of Vehicle Expenses. The fuel tracking method explained here works either way.

A later post explains how to use the Fuel Account to track gallons. First we have to set up the account.

Create a New Account

These instructions are for the inexperienced. If you are an experienced QuickBooks® user, you may be tempted to skip ahead. Reading everything, however, insures you don't miss a detail that could mean the difference between success and failure so do read everything.

This method has recorded and reported gallons since 2003 for a fleet averaging fourteen IFTA reportable trucks.

The illustrations use QuickBooks® but the method works with any bookkeeping application that:

provides a memo field for expense lines
provides transaction detail reports

To set up the Chart of Accounts for tracking gallons, follow the steps in the video below:

left-click on Lists on the QuickBooks menu
left-click on Chart of Accounts from the drop down list
The Chart of Accounts window opens.
left-click the Account button
left-click New from the drop down list
The Add New Account window opens.
left-click the radio button next to Expense
left-click the Continue button.
Name the new account something relevant like Fuel Account or Fuel.
left-click the Save & Close button
Now the Chart of Accounts lists the FUEL account.

The next post explains how to set up the Chart of Accounts to track more than one type of fuel.

Continue to the next post… or return in a day or two for the next installment. Check http://www.twitter.com/vbquick for news and updates.

Tell me what you think. Offer suggestions. Fill out my Feedback Form or sign up for email updates here.

01. Introduction to Fuel Tracking using QuickBooks®

2012-01-04T14:10:00.001-05:00

If you lack the funds for expensive DOT and IFTA software and hardware, you can record and track gallons for IFTA and DOT from QuickBooks®, Quicken or any bookkeeping application that provides transaction memos.

Solutions and ROI - The Bad News

Large fleets require large, expensive solutions, especially when complying with hours-of-service regulations for hundreds of drivers. Additionally, the ROI justifies the expense for large fleets simply by dividing the number of drivers into the cost of compliance. The ROI of a $12,000 hardware/software compliance system divided by a hundred drivers is significantly lower than when divided by three or four drivers.

The bad news for small fleet or single truck owners is that no matter the size of the fleet, all must comply with the same regulations. Small fleet owners typically use written logs to comply with hours-of-service regulations. Many also rely on time consuming manual methods of data entry for gallons.

The Good News

The good news is that you can track gallons in QuickBooks®, Quicken or any bookkeeping application that provides transaction memos. You can do this during regular bookkeeping. This method adds a few seconds to each fuel transaction on the books.

Even an assistant, bookkeeper or accountant can use this method if they use your receipts to do your books.

You enter fuel purchases anyway so why not enter them in a way that lets you report gallons by truck, state and fuel type? This method has worked for a fleet of IFTA reportable trucks since 2003.

Even if you download credit card transactions, you reconcile receipts against charges. With only a few additional seconds, you can enter the gallons for each charge in your bookkeeping application.

Keeping gallon records in your bookkeeping application means the data is always available to you. Using a time-honored and popular bookkeeping application like QuickBooks® or Quicken practically guarantees future availability. The chances are excellent that this software will remain available for a very long time. Additionally, the popularity of QuickBooks® and Quicken practically guarantees compliance with major operating systems like Windows®. These companies have a stake in each other due to huge demand.

You probably already have what you need to use this quick and easy method for keeping records of the fuel gallons you use.

Requirements

We use Microsoft® Access but not everyone knows VBA or database design.

If you prefer Microsoft® Excel, this tutorial explains how to use it with recorded gallons from QuickBooks®, Quicken or similar accounting software. The only requirement is that your bookkeeping application provides:

a place for memos for each transaction
a way to add subaccounts (for fuel types, ie.: gas, diesel, IFTa reportable, etc.)
classes for transaction (for separating gallons by state)
a spreadsheet export feature

The video below shows the end process, the result of the added bookkeeping step and the Microsoft® Excel WorkBook included at the end of this tutorial.

Alternative to Downloading a Workbook

If you are squeemish about downloading from the Internet (and who could blame you!), this tutorial includes the code behind the workbook. This lets you check the code against any VBA source or someone you trust. With confidence, you may then copy and paste the code into your own workbook.

Additionally, perhaps you use Lotus® or another version of Microsoft® Excel. You may alter the code or find a student or knowledgable person to alter the code for you.

Finally, you can skip the code and total the gallons using whatever method your spreadsheet application provides. The gallons will have been separated by truck, state, fuel type, etc. in QuickBooks® or Quicken. The only thing left is to total the gallons.

The code does highlight transactions with missing gallons so that is a plus.

Continue to the next post… Check http://www.twitter.com/vbquick for news and updates.

Tell me what you think. Offer suggestions. Fill out my Feedback Form or sign up for email updates here.

06. How to Create a Fuel Gallons Report in QuickBooks

2011-10-21T13:59:00.001-04:00

This is part of a tutorial on how to track fuel purchases and gallons using QuickBooks® by Intuit.

When correctly completed, these steps culminate in a report that separates gallons by fuel type, truck, and even state. The tutorial starts here.

This post covers how to produce a report that includes gallon entries. Later posts will cover vehicle and state tracking but for now let us concentrate on fuel type.

You should have entered gallons for at least a few fuel purchases before creating a report.

Tell me what you think. Offer suggestions. Fill out my Feedback Form.

05. How to Enter Gallons for Downloaded QuickBooks Transactions

2011-10-14T12:25:00.000-04:00

This is part of a tutorial on how to track fuel purchases and gallons using QuickBooks® by Intuit.

When correctly completed, these steps culminate in a report that separates gallons by fuel type, truck, and even state.

The tutorial starts here.

This post covers how to enter gallons when you download transactions into QuickBooks® as opposed to entering credit card charges manually.

Downloading

After downloading transactions into your QuickBooks® file, you have two choices of how to enter them into your register.

If you choose Add Multiple, as illustrated below, the Add Multiple Transactions window does not provide memo fields so you cannot enter gallons from that window.

To enter gallons for newly downloaded transactions, do not select Add Multiple right away. First:

select each fuel purchase
click: Show splits, memo, date, number on the right pane

Select the Fuel Type (Sub Account) from the drop-down Accounts menu.

Enter the gallons in the memo field.

Note below that after entered, the transaction stores the gallons in the memo field of the Expense Line. Do not enter gallons in the memo field of the transaction. In other words, do not enter gallons in the top portion of the QuickBooks® transaction.

Gallons must be entered in the Expense or Item lines of transactions to report and total using the method in this tutorial. Expense and Item lines always appear at the bottoms of transactions.

Think of the transaction as the Parent and the Expense and Item lines as the Children. Each transaction (parent) may have multiple children (Expense and Item lines).

To enjoy all the benefits of the Fuel Record Keeping method explained here, enter gallons in the expense line memo, leaving the transaction memo open for other notations. This is extremely important when using The Link Principle to link electronic receipts to QuickBooks® transactions.

A short video:

Continue to learn how to create a fuel gallons report.

04. Entering Fuel Purchases into QuickBooks® for Reporting

2011-10-13T16:17:00.001-04:00

This is part of a tutorial on how to track fuel purchases and gallons using QuickBooks® by Intuit.

When correctly completed, these steps culminate in a report that separates gallons by fuel type, truck, and even state.

The tutorial starts here.

This post covers how and where to enter fuel purchases in a way that also records Fuel Type and gallons. The simplicity of this method may surprise you.

If you followed examples in previous posts, you now have a main Fuel Account and probably one or more Fuel Sub Accounts.

You may select the Fuel Account and enter the gallons for each fuel purchase at different times depending on your particular bookkeeping such as:

manually entering new: cash, check or credit card purchases
editing downloaded check or credit card purchases
reconciling entered check or credit card purchases

Each of these are addressed separately in this and subsequent posts.

1. Manually Entering New: Cash, Check or Credit Card Purchases

Entering gallons for cash, check or credit card purchases use the same method.

Enter the Vendor, Date, Ref No. and Amount as you normally would.
Add a transaction memo if desired.
Choose the Fuel Account from the Account drop-down menu. If you do not have the receipt but know this is a fuel purchase, select the main Fuel Account for now. Later, use the memorized Missing Gallons report to find and fix transactions for which you did not enter gallons or select a fuel type.

Note the difference below between the Transaction Memo and the Transaction Line Memo.

You must enter gallons in the Transaction Line Memo to use this method to separate fuel gallons by type, state and vehicle.

Enter Customer and Billable as you normally would.

You must reserve QuickBooks® Class feature if you plan to track fuel gallons by state and that is covered later. Otherwise, use Class as you normally would.

Recording gallons in QuickBooks® adds only seconds to each transaction but facilitates multi-dimensional reports later as you will see.

A short video of the process:

Continue to learn how to enter gallons while importing downloaded transactions.

Tell me what you think. Offer suggestions. Fill out my Feedback Form.

Appendix A: Indent QuickBooks Fuel Sub Accounts

2011-10-11T09:18:00.001-04:00

If your Fuel Sub Accounts are not indented like these in the QuickBooks® Chart of Accounts, continue below to correct this. A short video at the end of this post shows the process in action.

If the Fuel Parent Account name precedes each Fuel Sub Account as illustrated below, then your Chart of Accounts is in Flat View.

Note how the word Fuel Account precedes each Sub Account below. For example, instead of IFTA Diesel as in the illustration above, the line for the IFTA Diesel sub account reads: Fuel Account: IFTA Diesel.

To Correct this:

Click Account.
Select Hierarchical View from the Account Menu.

The Sub Accounts now appear indented.

The following video shows the entire process in QuickBooks®.

Tell me what you think. Offer suggestions. Fill out my Feedback Form or sign up for email updates here.