Cuadro de mando en Excel: semáforos, tendencias y una página que se lee en un minuto

Ya tienes todos los KPIs calculados. Ahora toca lo que convierte una hoja de números en una herramienta de gestión: que se entienda en un minuto y que avise sola cuando algo se sale de rango.

Descarga el archivo de esta lección: es el cuadro de mando completo con todas las fórmulas, para que lo uses como solución o como plantilla para tu almacén.

Paso 1: la hoja de KPIs

Una fila por KPI y una columna por mes. El truco que hace que todo funcione: una fila oculta con el número de mes (fila 5: 1, 2, 3…) a la que apuntan todas las fórmulas.

=CONTAR.SI.CONJUNTO(Pedidos[Mes];D$5;Pedidos[OTIF];1)/CONTAR.SI.CONJUNTO(Pedidos[Mes];D$5)

Con D$5 (fila fija, columna libre) escribes la fórmula una vez en enero y la arrastras hasta junio. Cuando llegue julio, añades una columna y listo.

Añade tres columnas más:

Columna	Contenido
Objetivo	El valor que quieres alcanzar
Mejor si	↑ si más es mejor (OTIF), ↓ si menos es mejor (coste, roturas)
Tendencia	Un minigráfico de línea: Insertar → Minigráficos → Línea

Paso 2: semáforos automáticos

Selecciona los valores (D6:I25) y crea tres reglas de formato condicional con fórmula (Inicio → Formato condicional → Nueva regla → Utilice una fórmula), en este orden:

Verde:  =SI($K6="↑";D6>=$J6;D6<=$J6)
Ámbar:  =SI($K6="↑";D6>=$J6*0,97;D6<=$J6*1,05)
Rojo:   =VERDADERO

• Marca “Detener si es verdad” en las dos primeras, para que una celda verde no se pinte también de ámbar.
• La columna $K (Mejor si) hace que la misma regla sirva para KPIs que deben subir y que deben bajar.
• El ámbar avisa de que estás cerca del objetivo (a un 3 % por debajo o un 5 % por encima). Ajusta esos márgenes a tu gusto.

Cómo fijar objetivos con sentido: parte de tu histórico (por ejemplo, la media de los tres mejores meses), compáralo con lo que pide el cliente o el contrato, y pon un objetivo alcanzable pero exigente. Un objetivo que se cumple siempre no motiva; uno que no se cumple nunca, tampoco.

Paso 3: el cuadro de mando de una página

Una hoja limpia, sin cuadrícula, con:

1. Un selector de mes: celda con Datos → Validación de datos → Lista con Ene,Feb,Mar,Abr,May,Jun.
2. El número de mes elegido, en una celda auxiliar:

=COINCIDIR(C4;{"Ene";"Feb";"Mar";"Abr";"May";"Jun"};0)

3. Tarjetas con los 8 KPIs principales, que leen la hoja de KPIs según el mes elegido:

=INDICE(KPIs!$D$6:$I$6;$E$4)

4. Los mismos semáforos aplicados a cada tarjeta.

Cambia el selector a marzo y el cuadro se tiñe de rojo en servicio; cámbialo a abril y el rojo se mueve a productividad y exactitud. Sin leer un solo número ya sabes dónde mirar.

Paso 4: solo los gráficos que cuentan algo

• Tendencia del KPI principal frente al objetivo: línea del OTIF y una línea discontinua con el 95 %.
• Dos KPIs relacionados en el mismo gráfico: productividad (columnas) y errores (línea en el eje secundario). Así se ve que en abril caen juntos.
• Desglose de la causa: roturas por proveedor, en columnas apiladas.

Reglas de diseño que marcan la diferencia

1. Máximo una página. Si no cabe en una pantalla, sobran cosas.
2. Lo importante, arriba a la izquierda. Es donde empieza a leer todo el mundo.
3. Color solo para avisar. Si todo tiene color, nada destaca.
4. Sin gráficos 3D ni tartas con 10 porciones.
5. Siempre con el objetivo al lado. Un 91,7 % no dice nada; un 91,7 % con un objetivo del 95 %, sí.

Tarea: abre el cuadro de mando, cambia el objetivo del OTIF al 93 % en la hoja KPIs y mira cómo cambian los colores de todo el semestre. Después añade una tarjeta nueva con el KPI que más te importe en tu trabajo.