KHIZHNYAKOVCuadro de mando de indicadores por departamento en Excel: plan, real y semáforo
Monitoreo de indicadores de departamentos en Excel: plan/real, ranking, semáforo — Ver la plantilla →
Cuando cada departamento lleva sus indicadores en su propio archivo, comparar resultados es un trabajo artesanal: copiar cifras, unificar formatos y discutir si «un 92 %» en Ventas significa lo mismo que «un 92 %» en Calidad. Un cuadro de mando mensual en Excel, con reglas comunes, resuelve gran parte del problema.
Aquí verá cómo estructurarlo, qué fórmulas usar para el cumplimiento acumulado y el semáforo, y cómo construir un ranking justo. Al final, una plantilla que lo trae hecho.
Estructura: plan, real y cálculo
Mantenga tres bloques separados:
- Catálogos: departamentos e indicadores, con su unidad, su dirección (más es mejor o menos es mejor), su tipo de acumulación (suma o promedio) y su peso.
- Plan: una fila por pareja departamento-indicador, con el valor planificado de cada mes.
- Real: la misma estructura, con los valores logrados.
Separar plan y real evita sobrescribir sin querer los objetivos.
| Departamento | Indicador | Ene | Feb | Mar |
|---|---|---|---|---|
| Comercial | Ventas (miles €) plan | 100 | 110 | 120 |
| Comercial | Ventas (miles €) real | 95 | 112 |
Cumplimiento acumulado
Los indicadores de tipo «suma» (ventas, producción) se acumulan desde enero hasta el mes del informe. Si estamos en febrero, el plan acumulado es 100 + 110 = 210 y el real acumulado 95 + 112 = 207:
=SUMA(Real!C2:D2)/SUMA(Plan!C2:D2) = 207/210 = 98,6 %
Los indicadores de tipo «promedio» (satisfacción, por ejemplo) no se suman: se toma el promedio de los meses con real ingresado, con PROMEDIO, que ignora las celdas vacías.
Más es mejor y menos es mejor
No todos los indicadores mejoran al crecer. Si el indicador es «tiempo de respuesta», el objetivo es bajar. La regla:
- Más es mejor: cumplimiento = real / plan
- Menos es mejor: cumplimiento = plan / real
=SI(Dirección="Más";R/P;P/R)
Si el plan de respuesta es 24 horas y el real 30: 24/30 = 80 %. Limite además el máximo computable (por ejemplo 150 %) para que un resultado excepcional en un indicador no compense los fallos de otros.
Semáforo
Defina dos umbrales, por ejemplo verde desde el 95 % y amarillo desde el 80 %:
=SI(C2>=0,95;"Logrado";SI(C2>=0,8;"Riesgo";"Rezago"))
Ponga los umbrales en celdas de parámetros, no dentro de la fórmula; así se ajustan sin tocar nada más. Con el 98,6 % del ejemplo, Comercial queda en «Logrado».
Variación respecto al mes anterior
Calcule el cumplimiento acumulado del mes actual y del anterior, y reste. Un departamento que está en «Riesgo» pero mejora 5 puntos cuenta una historia distinta a otro que está en «Riesgo» y empeora.
Ranking con pesos
No todos los indicadores pesan lo mismo. Asigne a cada uno un peso (por ejemplo, 3 para ventas, 1 para puntualidad) y calcule el cumplimiento ponderado del departamento:
=SUMAPRODUCTO(Cumplimiento;Peso)/SUMA(Peso)
Después ordene con K.ESIMO.MAYOR o con JERARQUIA para obtener la posición. Los pesos son una decisión de la organización: documéntela.
Errores frecuentes
- Escribir un cero cuando aún no hay dato del mes: distorsiona el promedio. Deje la celda vacía.
- Mezclar unidades (miles y unidades) en un mismo indicador.
- Cambiar los umbrales a mitad de año sin avisar.
- Dar demasiados indicadores a un departamento: cinco o seis bien elegidos informan más que veinte.
Plantilla lista para usar: monitoreo de indicadores de departamentos
La plantilla de pago «Monitoreo de indicadores de departamentos» ofrece cinco hojas:
- Catálogos: año, mes del informe, umbrales del semáforo, departamentos e indicadores (unidad, dirección, acumulación, peso).
- Plan: hasta 200 filas «departamento — indicador» con valores planificados por mes.
- Real: valores reales por mes; el departamento y el indicador se completan solos.
- Cálculo: cumplimiento acumulado, semáforo y variación respecto al mes anterior.
- Resumen: ranking, número de indicadores por zona, evolución por mes y dos gráficos.
Es útil para jefes de departamento y de áreas de planificación, control y calidad, y para analistas de organizaciones públicas y privadas que necesitan un panel mensual sencillo. El máximo computable es de 150 % por defecto y un mes sin dato real no se tiene en cuenta. Funciona en Excel 2007 o posterior y en LibreOffice, sin macros, con el archivo en español. Los valores planificados, umbrales y pesos son referencias internas de su organización; el cálculo es orientativo.
Preguntas frecuentes
¿Cuántos indicadores por departamento son razonables?
Entre cinco y ocho. Más de eso diluye la atención y encarece la recogida de datos.
¿Qué hago si un mes no hay dato?
Déjelo vacío. Un mes sin real no se incluye en el cálculo; un cero sí lo haría.
¿Puedo cambiar los umbrales del semáforo?
Sí, se definen en los catálogos. Mantenga los mismos para todos los departamentos para que la comparación sea justa.
¿Cómo se acumulan los indicadores de promedio?
Se toma el promedio de los meses con valor real, no la suma.
