How do you build a maintenance control spreadsheet?

Corrective maintenance costs, on average, 3 to 5 times more than preventive maintenance. That is why maintenance control is essential for the fleet to have effective, high-level management. One of the alternatives: the maintenance control spreadsheet.

Besides a complete view of which types of maintenance were carried out and which budgets were used, you can control the fleet's parts inventory and keep parts from becoming unavailable.

If you just want the ready-made template: Prolog offers a fleet maintenance control spreadsheet for free (in Portuguese). But you can also keep reading to learn:

• Why use a spreadsheet for fleet maintenance control
• What to include in a fleet maintenance control spreadsheet
• How to build the maintenance control spreadsheet
• Preventive vs. corrective maintenance: how to control each one in the spreadsheet
• How to prepare a maintenance report from your spreadsheet
• When the maintenance spreadsheet is no longer enough
• Prolog: from the spreadsheet to the maintenance management system

Why use a fleet maintenance control spreadsheet?

A maintenance control spreadsheet is a tool for recording, organizing and keeping track of all the maintenance activities performed on a fleet's vehicles, whether preventive, corrective or predictive.

This way, it centralizes the information in a single place: which services were done, on which vehicle, on which date, for what amount, with which parts, and when the next service is scheduled.

This turns a set of notes, receipts and individual memories into a maintenance history you can look up, useful both for day-to-day operations and for the medium- and long-term decisions of fleet management.

In smaller operations, the spreadsheet is often the first formal maintenance control tool. That is why implementing it correctly already represents a leap compared to operating without any systematic record.

What to include in a fleet maintenance control spreadsheet

A good fleet maintenance spreadsheet is not a list of services performed. In fact, it is a database structured by vehicle, which lets you look up the history, anticipate needs and make data-driven decisions.

So the minimum information it should contain is:

About the vehicle

• License plate
• Model and manufacturer
• Year of manufacture
• Current mileage (updated with each record)
• Person in charge / main driver

Service information

• Type of maintenance: preventive, corrective or predictive
• Description of the service performed
• Check-in and check-out dates for maintenance
• Mileage at the time of the service
• Who performed it (in-house or outside repair shop)

Financial data

• Labor cost
• Parts used (code, quantity, unit cost)
• Total cost of the service
• Supplier / service provider

Control of upcoming services

• Date or mileage scheduled for the next service
• Scheduled service type
• Status: up to date / upcoming / expired

Parts inventory control (separate tab)

• Parts in inventory by code
• Current quantity
• Reorder point (minimum quantity that triggers a new order)
• Preferred supplier

How do you build the maintenance control spreadsheet?

A fleet maintenance control spreadsheet works best when it is organized into tabs with distinct functions. The structure below is the one that usually works best for operations with 20 to 80 vehicles:

• Tab 1: Vehicle register. Include the fixed information for each license plate. It is each vehicle's “profile.” It serves as a reference for all the other tabs.
• Tab 2: Maintenance log. This is the operation's diary. Each row is a service. Each service has: date, license plate, type (preventive/corrective), description, cost and person in charge. This is where the history is built.
• Tab 3: Preventive control. This will be the list of services scheduled by vehicle, with the date or km of the next execution. It can be filtered by due date. This tab is what keeps a service from being forgotten.
• Tab 4: Parts inventory. It works as the inventory of the tire shop or in-house repair shop. Updated with every part that comes in and goes out.
• Tab 5: Financial report. In this tab, you will see the cost summaries by vehicle, by type of maintenance and by period. It is what lets you see, at a glance, where the money is going.

Preventive vs. corrective maintenance: how to control each one in the spreadsheet

Both need to be in the same spreadsheet, but with different controls:

Corrective maintenance goes into the record when it happens, and the focus of control is cost, time to resolution and identified cause. Over time, analyzing corrective maintenance by vehicle reveals patterns, since the same components failing repeatedly point to a problem in the preventive plan or in the quality of the part used.

Preventive maintenance, on the other hand, needs to be planned before it happens. So control is done through the scheduling tab, with dates or mileages scheduled for each service type, such as oil changes, filters, brake inspections, alignment, among others.

The discipline here is to review this tab regularly and trigger the services before the due date, not after.

However, the most common mistake in fleets that use a spreadsheet is updating only the records tab (what happened) and forgetting to update the preventive tab (what needs to happen). The result is a rich history of corrective maintenance and zero preventive control.

How do you prepare a maintenance report from the spreadsheet?

The control spreadsheet will generate a body of information over time, and it is exactly this history that you will use to calculate maintenance KPIs and build reports for management. In this sense, the main indicators it lets you measure are:

• Ratio of corrective vs. preventive maintenance
• Maintenance cost per vehicle and per km (CMK)
• Mean downtime per maintenance (MTTR)
• Fleet availability in the period
• Backlog of preventive services that are overdue or close to their due date
• Frequency of occurrences by issue type

A good maintenance report is not a compilation of data, but the answer to a question. Before putting together any report, define which question you want to answer. That determines which data needs to go in and how to organize it so the analysis makes sense to whoever will read it.

For example: the manager notices a high frequency of flat tires in the fleet and wants to understand what is causing it. The report needs to answer exactly that.

So show how many punctures happened in the period analyzed, on which vehicles, which drivers were behind the wheel in each occurrence and on which routes the incidents were concentrated.

With these four pieces of data cross-referenced, it is already possible to identify patterns and whether the problem lies in a specific route (bad road or load), in a group of drivers (driving behavior) or in vehicles of a certain model or age.

In short, for a report that really supports decision-making, gather only the data most relevant to that question, build the analysis from the cross-references that reveal the cause and always close with a recommended action.

After all, a report that presents the problem without suggesting the next step is information with no use.

When the maintenance spreadsheet is no longer enough

The spreadsheet works well within certain limits. Therefore, identifying when those limits have been exceeded is what separates management that evolves from management that stays stuck in operational work.

Signs that the fleet has grown beyond the spreadsheet:

• More than one person in charge needing to update and look up the data at the same time, which can create a fragmented local file version, or the version shared in the cloud creates editing conflicts.
• Difficulty tracing a vehicle's individual history without filtering row after row of records.
• Preventive services coming due without anyone noticing, and the volume has made it unfeasible to review the scheduling tab regularly.
• Generating reports for the board requires hours of manual work.
• No integration between maintenance control, the pre-trip checklist and tire control. As a result, the information sits in separate files, with no connection.

From 50 vehicles on, most operations reach this point. The spreadsheet becomes a bottleneck, because the volume of information that needs to be managed exceeds what a manual tool can handle efficiently.

Prolog: from the spreadsheet to the maintenance management system

When the operation grows beyond what the spreadsheet can handle, the next step is a system that automates what the spreadsheet requires manually.

That is why Prolog's Maintenance Management solution was developed for road freight transport operations.

Currently, it digitizes the process from start to finish: opening work orders, recording services performed, parts inventory control, individual history by vehicle, automatic preventive alerts and ready-made KPI reports. And without depending on anyone building formulas in Excel.

What is more, integrated with the Electronic Checklist and Tire Management, the platform connects the three areas of the operation in a single view: what was inspected, what needs maintenance and what condition each vehicle's tires are in.

If you want to understand whether your operation has already reached this point, Prolog's free diagnosis identifies exactly where the gaps are and what it would take to close each of them. Request your free fleet diagnosis (in Portuguese).