Driver control spreadsheet: the 5 tabs

Managing drivers in a fleet requires records of trips, vehicles, maintenance and other information. That is why a driver control spreadsheet is a solution for this part of company management.

It lets you manage hours worked, see how many trips each driver made in the month and even create a driver ranking to reward the period's positive standouts.

To keep the tabs connected, the most practical sequence starts with registering drivers and vehicles. Day-to-day work then feeds trip and event control; these records form the basis for the ranking and monthly report.

See below:

• What is recorded in a driver control spreadsheet;
• How to build a driver control spreadsheet;
• Why use a driver control spreadsheet.

What is recorded in a driver control spreadsheet

Driver and vehicle registration

To start using your spreadsheet, you need to register the drivers and the vehicles available for use. This makes control more practical and enables some automation between the document's tabs.

To register a driver, include their name and the category, number and expiration date of their driver's license (CNH in Brazil). Other information can also be added if you need to manage it, such as hiring date, contract type (self-employed, PJ — a legal entity, CLT — employment under Brazilian labor legislation, etc.) and examinations completed.

Vehicles should be identified by vehicle type, make and model, license plate and mileage.

This tab serves as the basis for the others. To make lookups and formulas easier, use clear column labels, keep similar items in the same column and avoid blank rows or columns within the data area.

Trip control

In the trip control tab, you can see the trip date, driver, plate used, route taken, refueling amount on the route and total mileage.

This information helps show how a vehicle performs per liter of fuel. This makes it possible to set realistic savings and consumption goals.

This stage also includes routine control, identifying whether the driver completed assigned activities, such as the inspection checklist.

Since drivers and plates have already been registered, these fields can be selected from the existing database. Data validation can also restrict the type of information or values accepted in a cell, including through a drop-down list, to keep entries consistent.

Vehicle inspection and event control

Completing the inspection checklist is the main way to control fleet maintenance, identify issues in advance and avoid excessive spending or vehicle losses.

Just as this activity generates positive scores for those who perform it correctly, negative events can harm an employee's ranking. For example, if they receive a fine or are frequently late on routes.

As well as helping with rewards by acting as a driver incentive program, it provides a complete, concise view of driver behavior and what measures to take as a result.

The purpose of this tab is to record and keep track of events. To organize the history, keep each type of data in its own column and follow the same entry standard throughout the month.

Driver ranking

Driver ranking can be automated in the spreadsheet, as it is in the one we developed at Prolog.

As you record the activities you want to score and the amounts to add or deduct for each, rankings change when you enter data from the fleet's daily routine.

At the end of each month, you know which drivers performed best and should receive bonuses.

Remember also to assess performance against the goals you set for each individual driver. After all, if they reached number 1 in the ranking but did not meet the most important goal, you should adjust some scores in the spreadsheet.

Because it depends on records in the previous tabs, the ranking should be checked alongside trips and events before closing the period. Scored activities and their respective additions or deductions must follow the criteria defined for the operation.

Monthly reports

Reports are now essential for more organized, higher-quality management, because you make decisions that match operational reality and bring even more positive results.

In other words, they could not be left out of the driver control spreadsheet.

This is another tab that fills in automatically as you enter information in the other sections. Here, you can see the main information you need to understand whether the fleet is performing well.

This includes total trips completed, refueling expenditure and total distance traveled.

As the consolidation stage, the report should be consulted after registration and operational records have been filled in. It brings together totals without replacing checks of the source information.

How to build a driver control spreadsheet

Define what types of control you want

You need to prioritize what makes the most sense for management. A transport company spreadsheet can serve several purposes, from inventory control to route management.

That is why you need a very clear objective when planning and building your tool.

For driver control, you need to think about drivers' daily routines and their role in fleet management. The question to answer is: “What do they do that contributes most to positive results in our operations?”

The answer generally involves making deliveries on time, requesting repair services before a vehicle breaks down or suffers irreparable damage, avoiding fines and so on.

Given these options, you know the spreadsheet needs to include control of these activities.

Also define who enters each piece of information and at what point in the routine. This helps prevent the same event from being described in different ways or left unrecorded.

Create the necessary tabs

When building a driver control spreadsheet, you need to include calculations and conditional rules so that all tabs work properly. A trip control tab needs to pull data from registered drivers and plates, for example.

Whoever structures the spreadsheet should therefore have some basic understanding of how to create these automations. Or you can download our ready-made template.

The main tabs should be:

• Driver and plate registration;
• Trip control;
• Positive events and incidents;
• Driver ranking;
• Report.

Create the registration tab first, because trips, events, rankings and reports depend on data recorded during daily work. When structuring each area, use column labels to identify the data and avoid gaps within the filled range.

Apply calculations to help generate reports and rankings

All Excel spreadsheets, whether for transport or other management areas, have supporting calculations to perform automations.

As discussed earlier, you need to know how to do this or have someone who does, to avoid errors in the spreadsheet and even generating incorrect information at the end of the month.

Organizing the range as a table can make sorting, filtering, totaling and calculating data easier. Before using the ranking or report, check that formulas cover new records and that required fields have been completed.

Why use a driver control spreadsheet

Because you need a tool that records information and keeps some history of your control process. This way, you do not lose information throughout the month and always have it on hand when you need to look something up.

The spreadsheet can contribute, for example, to controlling working hours and the hours a driver can drive per day.

It also provides views of the fleet's best drivers and the most recurring issues, both by driver and overall. In the manager's hands, this information is crucial for making effective fleet decisions.

To keep the history useful, maintain the same labels and entry standards, record information in the corresponding tab and review the database before closing the monthly report.

Enjoy the benefits of a driver control spreadsheet by downloading Prolog's template for free (in Portuguese).