Control de flotillas en Excel

Plantilla, campos y fórmulas para empezar

El control de flotillas en Excel puede funcionar cuando la empresa necesita ordenar sus unidades, recorridos, cargas de combustible, mantenimientos y llantas sin implementar todavía una plataforma especializada.

El problema no suele ser Excel. El problema aparece cuando el archivo se construye como una lista improvisada: placas escritas de distintas maneras, fórmulas que alguien modifica, kilometrajes sin actualizar y una pestaña diferente para cada vehículo.

Una hoja de cálculo bien organizada debe permitir responder preguntas operativas concretas:

• ¿Cuánto cuesta recorrer un kilómetro con cada unidad?
• ¿Qué vehículos tienen mantenimientos próximos o vencidos?
• ¿Cuál es el rendimiento de combustible de cada modelo?
• ¿Cuánto se ha gastado en reparaciones durante el mes?
• ¿Qué llanta está instalada en cada posición?
• ¿Qué unidad permanece detenida y cuánto tiempo lleva fuera de operación?

Para obtener esas respuestas no basta con crear una tabla de vehículos. Se necesita un archivo que conserve el historial de cada movimiento.

Tres formas de llevar el control de flotillas en Excel

Antes de definir columnas y fórmulas, conviene elegir cómo se almacenará la información. Estas son las tres estructuras más frecuentes.

Una sola hoja con una fila por unidad

Es el formato más sencillo. Cada renglón representa un vehículo y las columnas muestran datos como placa, modelo, kilometraje, último mantenimiento y gasto acumulado.

Por ejemplo:

Este modelo permite revisar rápidamente el estado actual de la flotilla. Su limitación es que cada actualización reemplaza el dato anterior.

Si la Nissan NP300 pasa de 86,430 a 87,200 kilómetros, Excel mostrará el kilometraje nuevo, pero no conservará quién hizo el recorrido, qué ruta atendió ni cuánto combustible utilizó. Sirve como inventario, pero no como bitácora operativa.

Una pestaña independiente para cada vehículo

Algunas empresas crean una hoja para la unidad U-001, otra para la U-002 y así sucesivamente. Cada pestaña contiene mantenimientos, cargas y kilometrajes del vehículo correspondiente.

El historial queda más ordenado que en el modelo anterior, pero consolidar información se vuelve complicado. Para conocer el gasto mensual de 35 camionetas habría que consultar 35 pestañas o mantener fórmulas vinculadas que pueden romperse cuando alguien cambia el nombre de una hoja.

También se corre el riesgo de que cada unidad termine con columnas diferentes. Este formato puede ser manejable con tres o cuatro vehículos. Cuando aumenta la cantidad de unidades, proveedores o movimientos, deja de ofrecer una visión completa de la operación.

Un libro estructurado por procesos

La alternativa más confiable es separar la información por tipo de registro:

• Una hoja maestra con el catálogo de unidades.
• Una hoja para recorridos y kilometrajes.
• Una para cargas de combustible.
• Una para mantenimientos.
• Una para movimientos de llantas.
• Una hoja de alertas e indicadores.

Todas las hojas se relacionan mediante un identificador único. En vez de depender exclusivamente de la placa, cada vehículo recibe una clave como U-001, U-002 o TR-018. Así, el control de flotillas en Excel conserva el historial sin duplicar la información general del vehículo en cada registro.

Cómo organizar un archivo para el control de flotillas en Excel

La estructura recomendada funciona como una base de datos sencilla. Cada renglón representa un evento: un recorrido, una carga, una orden de mantenimiento o un movimiento de llanta.

La regla principal es no mezclar distintos tipos de eventos en la misma fila. Una carga de diésel no debería ocupar el mismo registro que un cambio de aceite. Ambos movimientos utilizan fechas, costos y kilometrajes, pero tienen campos y responsables diferentes.

Hoja de catálogo de unidades

Esta hoja contiene un registro por vehículo y funciona como fuente para las listas desplegables del resto del archivo.

Los campos recomendados son:

• ID de unidad.
• Placa.
• Número de serie o VIN.
• Tipo de vehículo.
• Marca.
• Modelo.
• Año.
• Tipo de combustible.
• Capacidad del tanque.
• Base operativa.
• Centro de costos.
• Operador asignado.
• Kilometraje actual.
• Estado de la unidad.
• Fecha de alta.
• Fecha de baja, cuando corresponda.

El ID no debe cambiar aunque se reemplacen las placas o se reasigne el vehículo a otra base. Para una flotilla mixta se pueden emplear prefijos diferentes: C- para camiones, V- para vehículos ligeros y R- para remolques. Lo importante es mantener un criterio único.

Hoja de recorridos y kilometraje

Esta sección permite conocer cuánto trabajó cada vehículo y sirve como referencia para calcular rendimientos y costos. Cada recorrido debería registrar:

• Fecha.
• ID de unidad.
• Operador.
• Ruta o servicio.
• Origen.
• Destino.
• Odómetro inicial.
• Odómetro final.
• Kilómetros recorridos.
• Carga transportada, si aplica.
• Folio del viaje.
• Observaciones.

La fórmula básica es: Kilómetros recorridos = odómetro final − odómetro inicial

Si una camioneta inicia su ruta en 86,430 kilómetros y regresa con 86,685, recorrió 255 kilómetros. Excel también debe validar que el odómetro final sea mayor que el inicial. Un resultado negativo puede indicar un error de captura o que se registraron datos de unidades diferentes.

Hoja de control de combustible

La hoja de combustible necesita relacionar cada carga con una unidad, un operador y una lectura de odómetro.

Los campos mínimos son:

• Fecha y hora.
• ID de unidad.
• Operador.
• Estación de servicio.
• Número de ticket o factura.
• Tipo de combustible.
• Litros cargados.
• Precio por litro.
• Importe total.
• Odómetro al cargar.
• Kilómetros desde la carga anterior.
• Rendimiento.
• Costo de combustible por kilómetro.
• Medio de pago.
• Enlace o referencia de la evidencia.
• Estado de revisión.

La Conuee utiliza variables como consumo de combustible, distancia recorrida y facturación de combustible y mantenimiento para el seguimiento de flotas vehiculares de la Administración Pública Federal. Aunque este esquema no representa una obligación para una flotilla privada, confirma la utilidad de relacionar consumo, kilometraje y costo en un mismo periodo.

Supongamos que la unidad U-014 recorrió 498 kilómetros entre dos cargas y recibió 47.2 litros:

Rendimiento = 498 km ÷ 47.2 L = 10.55 km/L

Si la carga costó $1,153.60:

Costo de combustible por kilómetro = $1,153.60 ÷ 498 km = $2.32 por km

El rendimiento debe compararse contra vehículos similares. No tendría sentido medir una Nissan NP300 de reparto urbano contra un tractocamión con carga completa.

Hoja de mantenimiento

La hoja de mantenimiento debe registrar tanto servicios preventivos como reparaciones correctivas.

Conviene incluir:

• Número de orden de trabajo.
• ID de unidad.
• Tipo de mantenimiento.
• Fecha de ingreso.
• Kilometraje de ingreso.
• Fecha de salida.
• Kilometraje de salida.
• Trabajo realizado.
• Taller o proveedor.
• Costo de refacciones.
• Costo de mano de obra.
• Costo total.
• Próximo servicio por fecha.
• Próximo servicio por kilometraje.
• Días fuera de operación.
• Responsable que autorizó.
• Referencia de factura u orden de servicio.

Para calcular los kilómetros pendientes para el siguiente servicio:

Kilómetros restantes = kilometraje del próximo servicio − kilometraje actual

Una Nissan NP300 con servicio programado a los 90,150 kilómetros y un odómetro actual de 88,900 tiene 1,250 kilómetros disponibles antes de la intervención.

El archivo puede marcar la unidad en amarillo cuando falten menos de 1,500 kilómetros y en rojo cuando el resultado sea igual o menor que cero. Los rangos deben ajustarse al tipo de vehículo, su recorrido diario y el tiempo necesario para programar el taller.

Cómo incorporar el control de llantas en Excel

Registrar solamente la compra de las llantas no permite conocer su rendimiento. Cada llanta necesita un identificador propio y un historial de movimientos.

La hoja debería incluir:

• ID de llanta.
• Marca.
• Modelo o diseño.
• Medida.
• Número DOT.
• Fecha de compra.
• Costo de adquisición.
• Estado.
• ID de la unidad donde está instalada.
• Posición.
• Fecha de montaje.
• Kilometraje de montaje.
• Fecha de desmontaje.
• Kilometraje de desmontaje.
• Profundidad de piso.
• Presión registrada.
• Motivo del desmontaje.
• Kilómetros acumulados.
• Número de renovaciones.
• Costo acumulado.
• Costo por kilómetro.

Por ejemplo, la llanta L-0082 se monta en la posición delantera derecha del camión C-018 a los 210,400 kilómetros y se desmonta a los 278,900:

Kilómetros del periodo = 278,900 − 210,400 = 68,500 km

Si posteriormente se instala en otra unidad, debe añadirse un movimiento nuevo. No se debe reemplazar el montaje anterior, porque se perdería una parte de su recorrido. Cuando una llanta acumula 92,000 kilómetros y su costo total —incluyendo reparaciones y renovaciones— es de $13,800:

Costo por kilómetro de la llanta = $13,800 ÷ 92,000 km = $0.15 por km

Este indicador permite comparar llantas por aplicación y posición, en lugar de elegir únicamente por precio de compra.

Fórmulas útiles para administrar la flotilla

Una plantilla de control de flotillas en Excel no necesita fórmulas demasiado complejas. Los cálculos deben ser comprensibles para que el responsable pueda auditarlos.

Costo total de mantenimiento

Costo total = refacciones + mano de obra + servicios adicionales

Si una reparación incluye $8,900 de refacciones, $3,200 de mano de obra y $850 de servicios externos, el costo total es de $12,950.

Costo de mantenimiento por kilómetro

Costo de mantenimiento por kilómetro = gasto de mantenimiento del periodo ÷ kilómetros recorridos

Si un tractocamión recorrió 12,600 kilómetros durante el mes y acumuló $18,270 de mantenimiento:

$18,270 ÷ 12,600 km = $1.45 por km

Costo operativo controlado por kilómetro

En una hoja básica se pueden sumar los costos que realmente están registrados:

Costo controlado por kilómetro = combustible por km + mantenimiento por km + llantas por km

No debe presentarse como costo operativo total si todavía faltan conceptos como salarios, seguros, casetas, depreciación, permisos o financiamiento.

Disponibilidad de la flotilla

Disponibilidad = unidades disponibles ÷ unidades totales × 100

Si 27 de 30 unidades están disponibles:

27 ÷ 30 × 100 = 90% de disponibilidad

La empresa debe definir qué significa “disponible”. Una unidad sin operador, con documentación vencida o esperando una llanta puede estar físicamente en el patio, pero no necesariamente lista para trabajar.

Reglas para conservar la confiabilidad del archivo

Un tablero atractivo no compensa datos incompletos. Para que el control de flotillas en Excel sea útil, el archivo necesita reglas de captura y revisión.

Utilizar una fila por movimiento

No deben combinarse varias cargas o reparaciones dentro de una sola celda. Cada evento necesita su propia fecha, importe, kilometraje y evidencia.

Convertir los registros en tablas de Excel

Las tablas permiten extender fórmulas, aplicar filtros y mantener rangos actualizados cuando se agregan filas. También facilitan la construcción de tablas dinámicas.

Separar captura y cálculo

Las celdas donde el usuario introduce información pueden tener un color claro, mientras que las columnas con fórmulas deben protegerse.

Así se evita que alguien escriba manualmente encima del rendimiento o del costo por kilómetro.

Crear listas desplegables

Los IDs de unidades, operadores, proveedores, estados y tipos de mantenimiento deben seleccionarse de un catálogo.

Sin validación, una misma unidad puede aparecer como “U-014”, “U014” y “Unidad 14”, generando tres registros distintos en los reportes.

Definir responsables

La empresa debe establecer quién captura y quién revisa cada información.

Un esquema sencillo podría ser:

• El operador entrega el ticket y el kilometraje.
• El auxiliar registra el movimiento.
• El gerente de flotillas revisa excepciones.
• Administración valida facturas y costos.
• El propietario recibe los indicadores consolidados.

Establecer una fecha de corte

Si los registros se actualizan únicamente al final del mes, una desviación detectada puede haber ocurrido semanas antes. Combustible y kilometraje deberían actualizarse diariamente o después de cada servicio. Los movimientos de llantas y las órdenes de mantenimiento deben registrarse cuando suceden.

Qué decisiones puede tomar el empresario con el archivo

El objetivo del control no es llenar columnas, sino convertir los registros en decisiones.

Con información consistente, el propietario puede identificar:

• Vehículos cuyo rendimiento de combustible cae durante varios periodos.
• Unidades que acumulan reparaciones correctivas.
• Proveedores con costos o tiempos de entrega superiores.
• Mantenimientos que se realizan después del kilometraje programado.
• Llantas con un costo por kilómetro mayor al promedio.
• Camiones con demasiados días fuera de operación.
• Diferencias de costo entre rutas, bases o tipos de vehículo.
• Unidades que deben mantenerse, reasignarse o reemplazarse.

Una lectura aislada rara vez es suficiente. Un rendimiento bajo en un solo viaje puede explicarse por carga, tráfico, ralentí, pendiente o condiciones de la ruta. La decisión debe basarse en tendencias y comparaciones entre vehículos equivalentes.

Cuándo deja de ser confiable el control de flotillas en Excel

Excel puede sostener una operación pequeña o un proceso de transición siempre que exista disciplina de captura. El límite no depende únicamente del número de vehículos.

Una flotilla de 40 unidades con pocos movimientos y un responsable dedicado puede mantener un archivo ordenado. Otra de 12 vehículos puede perder el control si tres personas modifican copias distintas.

Estas señales indican que el archivo está dejando de sostener la operación:

• Existen varias versiones y nadie sabe cuál es la vigente.
• Los kilometrajes se actualizan con varios días de retraso.
• Las fórmulas cambian sin autorización.
• Las cargas no pueden relacionarse con tickets.
• Los movimientos de llantas se registran sin posición ni kilometraje.
• El reporte mensual requiere copiar información entre archivos.
• Los responsables capturan nombres diferentes para la misma unidad.
• Las alertas dependen de que alguien abra y revise la hoja.
• No es posible saber quién modificó un dato.
• El empresario recibe resultados, pero no puede rastrear su origen.

Cuando la captura manual, la consolidación o la falta de trazabilidad comienzan a afectar las decisiones, conviene evaluar una arquitectura centralizada. El hub de software para flotas de Gasmonsoft explica las áreas que pueden integrarse cuando el control deja de depender de archivos separados.

Qué debe contener una plantilla de control de flotillas en Excel

Una plantilla operativa debería incluir, como mínimo:

• Catálogo de unidades.
• Catálogo de operadores y proveedores.
• Registro de recorridos.
• Control de cargas de combustible.
• Historial de mantenimiento.
• Movimientos de llantas.
• Alertas por fecha y kilometraje.
• Indicadores mensuales por unidad.
• Validaciones para reducir errores.
• Datos de ejemplo.
• Instrucciones de captura.
• Protección de las columnas calculadas.

No hace falta comenzar con 40 indicadores. Es preferible mantener seis datos actualizados y verificables que presentar un tablero lleno de resultados calculados a partir de registros incompletos.

El primer objetivo del control de flotillas en Excel debe ser construir un historial confiable. Una vez que combustible, kilometraje, mantenimiento y llantas comparten el mismo identificador de unidad, la empresa puede conocer mejor sus costos y actuar antes de que una desviación termine en una reparación, una unidad detenida o un gasto difícil de explicar.