Serves the Commercial Small Fleet Market of 10 – 50 Vehicles

Use Spreadsheets To Help Manage Your Fleet

September 2004, by Terry Flesia

There are a multitude of software programs on the market for tracking all manner of fleet activity. But, you, the small business owner who operates from 10 to 50 vehicles, may look at the cost of that software and decide, “It’s just not worth the expense.”

You’re right—spending hundreds of dollars on specialized software to track just a few vehicles does not make economical sense. However, the no-cost solution may already be at your fingertips. Just about every business today uses spreadsheet software such as Microsoft Excel or Lotus 1-2-3 to help manage its business. So why not use your existing spreadsheet capabilities to help you manage your vehicle fleet as well?

Using a Spreadsheet as a Database

Using Excel or Lotus 1-2-3 spreadsheets as a database is one very simple way to create and maintain an ongoing vehicle inventory. Chart 1 is an example of how to set it up. 

First identify the vehicle by your company vehicle number. Then list the model year of the vehicle, followed by the make and model. Some state agencies may make enquiries such as unpaid parking tickets or red light camera citations by the vehicle’s license plate number. You’ll want a column identifying the plate number and the vehicle’s state of registration. You may also want to list the VIN (Vehicle Identification Number).

Another column should list the original or capitalized cost of the vehicle, and the date that the vehicle was placed in service. Those numbers and dates will also serve as a reference for other spreadsheet reports you may want to create. The assigned driver should also be noted.

Feel free to include information unique to your business on the Vehicle Inventory Report, another advantage to creating your own database. Keeping the Fleet Vehicle Inventory Report current is very simple. As you acquire new vehicles, simply input their information into the report. Delete vehicles as you retire and sell them.

Mileage Forecast Report

Most businesses have a policy governing when they are going to replace their old vehicles with new ones. Most policies have a mileage parameter. Some may simply wait until a vehicle reaches the mileage limit, and then replace it by buying the new vehicle out of a dealer’s existing stock. That’s the more expensive way of doing it. A more prudent way—and less costly—is through factory ordering. This means you will need to order new vehicles anywhere from six to eight weeks in advance of your actual need

In this instance a Fleet Vehicle Mileage Forecast Report will help you forecast replacing your vehicles in advance. Chart 2 is an example of this type of report as a spreadsheet. Column one identifies the vehicle by your company vehicle number. The next column determines the months in service from the date the vehicle entered into service, in this case May 1, 2004. (Note: See Fleet Vehicle Inventory Report for the date in service.) Then determine the vehicle odometer reading as of that date. Divide the current odometer reading by the number of months in service on the spreadsheet to determine the average monthly mileage, the next column on the Mileage Forecast Report.

The average monthly mileage is added to the current mileage month-by-month to reach the total forecasted mileage at the report’s ending month, November 1, 2005. Let’s say it’s May 1, 2005 and you’ve forecasted mileage on all of your vehicles through November 1, 2005. Let’s assume your policy is to replace vehicles at 80,000 miles. You need to know which vehicles will near, reach or exceed the 80,000-mile replacement cap by October 1, 2005 so you can order new vehicles during the June/July period to replace those vehicles around October 1.

In this example you have five vehicles, 1004, 1005, 1007, 1008 and 1009 that will either be very near or exceed 80,000 miles by October 1, 2005. The chart shows that you’ll need to order five 2006-model vehicles during the June/July 2005 period to replace those five vehicles by October or November at the latest. The second line shows the number of months those vehicles are in service as a reference.

Sold Vehicle Report

Another easily created tool to help manage the fleet is the Sold Vehicle Report. This report allows you to track and evaluate the sales performance of the vehicles that you retire and sell. Chart 3 is an example of a Sold Vehicle Report.

The first column identifies the vehicle by the company vehicle number. The second, third and fourth columns state the vehicle’s year, make and model. The following column shows the original capitalized cost of the vehicle, derived from the Fleet Vehicle Inventory Report. The next column shows the total months in service, calculated from the original date in service from the Fleet Inventory Report and the Sale Date column on this report.

The Mileage column shows the final vehicle odometer reading at sale. Following the Sale Date and Sale Proceeds is the average per-month net depreciation column. This figure is determined by subtracting the sales proceeds from the capitalized cost and then dividing the result by the number of months the vehicle was in service. Reducing each vehicle to an average net depreciation per month creates a common denominator that allows you to compare the financial outcomes of one vehicle against another.

The wholesale guidebook column shows the value of that particular make and model vehicle on the date it was sold. Black Book Clean values are used here, though Automotive Market Report (AMR) wholesale values or any other wholesale reporting guide values can be used. The next column shows the high mileage deduction. Many fleet vehicles run over the expected mileage at the end of their term and thus suffer a mileage deduction penalty at sale.

In this example Black Book mileage deductions are shown appropriate to the year, model and ending mileage on the vehicles in this report. The reconditioning cost amount is inputted using the reconditioning reports received on vehicles just prior to sale. Benchmark Value is derived by subtracting the mileage deduction and estimated reconditioning costs from the wholesale guidebook value. This creates a benchmark value to weigh against the actual sale proceeds of the vehicle. The plus or minus figure represents the difference between the benchmark value and the actual sale price.

The last column contains a code representing to whom each vehicle was sold: Buyer A might represent vehicles sold to employees while B might be a certain wholesaler. Evaluating sales performance in this case indicates that sales to employees (A) exceed benchmarks, though sales to Wholesaler B fall below benchmarks. Here we can see that sales performance to A (whether A is a wholesaler, employee, dealer or whoever) is consistently above our benchmark, while sales to B fall consistently below the benchmark. These figures should spur you to take a harder look at any future vehicles to B.

Other Spread Sheets

Another useful spreadsheet is the Vehicle Expense Detail Report. This type of spreadsheet report lists each vehicle’s expenses. Categories include variable expenses such as fuel, oil and lube, tires, mechanical repair, washing/detailing and parking/tolls. Fixed expenses include depreciation and interest, or lease and insurance. From this report you can establish average expenses by category and create another report. That report, the Vehicle Expense Exception report, allows you to monitor costs that are out of line with the average.

A Beautiful Thing

The beauty of creating these spreadsheets is that you most likely already own a spreadsheet program such as those offered by Lotus or Excel. It’s merely a matter of applying existing software technology to the management of your fleet.

Twitter Facebook Google+

Comments

Please note that comments may be moderated. 
Leave this field empty:
 
 

  1. 1. Helder Dias [ April 03, 2017 @ 02:48PM ]

    The following link is no good: Click here to view the three charts that accompany this article. There is no chart or pdf file, it only links to another site that doesn't relate to the original

  2. 2. Amy [ April 03, 2017 @ 05:11PM ]

    Hi Helder,
    Chris Brown emailed you a PDF of the story.
    Thanks!

  3. 3. Kim [ June 09, 2017 @ 03:44PM ]

    I was also wondering about the chart referenced. There doesn't appear to be one.

  4. 4. Carson Ifill [ July 21, 2017 @ 04:50PM ]

    Where can you find the three charts

  5. 5. Debra [ July 31, 2017 @ 04:44PM ]

    I would be interested in the charts as well.

  6. 6. Susan Warner [ August 06, 2017 @ 09:35PM ]

    Hi there, I would love the charts too please. Thank you.

  7. 7. Susan Warner [ August 06, 2017 @ 09:35PM ]

    Hi there, I would love the charts too please. Thank you.

 

Fleet Incentives

Determine the actual cost of owning and running a vehicle in your fleet. Compare vehicles by class and model.

FleetFAQ

Fleet Tracking And Telematics

Todd Ewing from Fleetmatics will answer your questions and challenges

View All

 

Fleet Management And Leasing

Merchants Experts will answer your questions and challenges

View All

 

Sponsored by

Chevrolet's all-electric vehicle, introduced in 2011.

Read more

Up Next

More From The World's Largest Fleet Publisher