¡Acceso ilimitado 24/7 a todos nuestros libros y vídeos! Descubra la Biblioteca Online ENI. Pulse aquí
¡Acceso ilimitado 24/7 a todos nuestros libros y vídeos! Descubra la Biblioteca Online ENI. Pulse aquí
  1. Libros
  2. Cuadros resumen y cuadros de mando
  3. Cuadro de mando e indicadores
Extrait - Cuadros resumen y cuadros de mando Tratamiento y análisis de grandes volúmenes de datos con Excel 2016
Extractos del libro
Cuadros resumen y cuadros de mando Tratamiento y análisis de grandes volúmenes de datos con Excel 2016
1 opinión
Volver a la página de compra del libro

Cuadro de mando e indicadores

Objetivo

Elemento clave en sus tomas de decisiones, un cuadro de mando le permitirá destacar los puntos clave del funcionamiento de la empresa. Este debe sintetizar los datos importantes y presentarlos en forma de indicadores numéricos o gráficos.

Si los indicadores escogidos son relevantes, un cuadro de mando le ayudará a dirigir su empresa. Los elementos mostrados deben:

  • escogerse por grado de importancia en la gestión de la empresa,

  • poder compararse con valores óptimos relacionados con su actividad,

  • obtenerse en tiempo real a partir de datos actualizados,

  • ser el reflejo de la realidad económica, logística, comercial, medioambiental o de calidad de su empresa.

Estos indicadores pueden pertenecer a múltiples categorías como:

  • Gestión financiera

  • Seguimiento comercial

  • Gestión de producción

  • Posicionamiento respecto a la competencia

  • Producción y logística

  • Seguimiento de calidad

  • Satisfacción de los clientes, etc.

Los ámbitos que hay que tener en cuenta son múltiples y variados, es importante que no se disperse y que se centre en los datos relevantes que quiere seguir.

Presentación

A modo de ejemplo, vamos a crear un conjunto de tres pequeños cuadros de mando.

  • El primero nos permitirá visualizar los indicadores de volúmenes de negocios y de calidad.

  • El segundo mostrará el volumen de ventas mensual y las ventas por familia de productos.

  • Por último, el tercero se centrará en los indicadores de calidad y de seguimiento de los plazos de pago de los clientes.

La navegación entre estos tres cuadros de mando deberá facilitarse mediante hipervínculos.

La empresa que nos servirá de ejemplo fabrica carpinterías de aluminio y PVC. El libro Carpinterías.xlsx contiene:

  • Dos hojas de cálculo (Fact2015 y Fact2016) con las ventas, cada una para un año.

  • Una hoja de cálculo que incluye las tablas de cálculo de estadísticas necesarias para nuestros indicadores (Estad).

  • Tres hojas (TB1, TB2 y TB3) cada una relativa a uno de los tres cuadros de mando descritos anteriormente.

Las primeras filas de la hoja Fact2015:

images/SOB08_01.png

 Abra el libro Carpintería.xlsx.

Cada uno de los cuadros de mando hace referencia a fórmulas incluidas en las tablas de cálculos intermedios. Para no sobrecargar la visualización de sus indicadores, por lo general debe crear tablas que le garanticen la relación entre sus datos de origen y los cuadros de mando.

En nuestro ejemplo, estas tablas ya están hechas en la hoja Estad. Las distintas...

Creación del primer cuadro de mando

Este primer cuadro de mando (que se hará en la hoja TB1) debe informarnos rápidamente sobre los puntos clave:

  • grado de consecución de los objetivos,

  • seguimiento rápido de calidad, de entrega y de plazos de pago de los clientes,

  • seguimiento de los volúmenes de ventas acumulados y medida de las diferencias con respecto al año anterior.

images/SOB08_05.png

Se ha preparado la hoja TB1 e incluye una pequeña tabla (a partir de la columna O) que nos permitirá configurar nuestros primeros indicadores.

images/SOB08_06.png

Vamos a retomar el principio de un semáforo tricolor, la visualización es la siguiente:

  • Si la tasa de rechazo medio es superior al 1% aparecerá un círculo rojo; si no el círculo será verde.

  • Para el plazo de entrega medio y el plazo de pago medio, si el valor es superior a 30 días, el círculo será rojo; en caso contrario, será verde.

  • Si hemos alcanzado o sobrepasado el objetivo, el "semáforo" correspondiente será verde.

  • Si el volumen de ventas realizado se sitúa entre el 70% y 99,99% del objetivo, el "semáforo" será ámbar.

  • Si el volumen de ventas alcanzado es inferior al 70% del objetivo, el "semáforo" será rojo.

Los círculos se obtendrán por la letra L minúscula. Se aplicará un formato condicional en cada celda.

 Escriba una L minúscula en las celdas E3, E4, E5, E9, E10 y E11.

Dado que el relleno de las celdas es negro, al igual que la fuente, no podrá ver lo que escribe. Estas solo podrán verse cuando el formato condicional se haya aplicado.

 Seleccione las tres celdas E3, E4 y E5 luego seleccione la fuente Wingdings y el tamaño 26 pt.

 Proceda igual para E9, E10 y E11.

De momento, no debería distinguir nada en las celdas.

images/SOB08_07.png

1. Indicador de índice de rechazo medio

 Colóquese en E3.

 Pestaña Inicio - grupo Estilos, haga clic en el botón Formato condicional.

 Haga clic en Nueva regla.

 En el cuadro de diálogo Nueva regla de formato, escoja la opción Utilice una fórmula que determine las celdas para aplicar formato.

 Modifique la descripción de la regla según la siguiente imagen.

images/SOB08_08.png

 Haga clic en el botón Formato.

 En la ventana Formato de celdas, seleccione...

Cuadro de mando relativo al volumen de ventas

Este segundo cuadro de mando (que se hará en la hoja TB2) permitirá hacer una comparación rápida entre las cifras de las prestaciones del año actual y las del año anterior.

  • Comparación del volumen de ventas por familia año n, año n-1.

  • Comparación del volumen de ventas mensual año n, año n-1.

images/SOB08_22.png

El gráfico se va a crear a partir de una tabla que se encuentra en la hoja Estad:

images/SOB08_23.png

 Active la hoja Estad.

 Seleccione el rango A42 a D45.

 Pestaña Insertar - grupo Gráficos - haga clic en el botón Insertar gráfico de columnas o de barras; luego, en la categoría Columna en 2D, escoja Columna agrupada.

images/SOB08_24.png

 Pestaña Herramientas de gráfico - Diseño - grupo Estilos de diseño - haga clic en el Estilo 8.

De momento, la línea de los índices de variación no se ve, ya que los valores son desproporcionados con respecto al volumen de ventas. Ahora vamos a configurar un eje secundario para esta serie.

 Pestaña Herramientas de gráfico - Formato - grupo Selección actual, despliegue el menú Elementos de gráfico y seleccione la serie "ÍND. VAR".

 Pestaña Herramientas de gráfico - Formato - grupo Selección actual, haga clic en Aplicar formato a la selección.

 En la ventana Formato de serie de datos, active la opción...

Cuadro de mando relativo a los pagos de los clientes y a la tasa de rechazo de los productos

Este último cuadro de mando (que se hará en la hoja TB3) permitirá ver algunos datos sobre la calidad de nuestras prestaciones y sobre la gestión de los pagos de los clientes.

images/SOB08_30.png

Las fórmulas de este cuadro de mando no conllevan dificultades particulares.

images/SOB08_31.png

 Las fórmulas:

C10: =MAX(Estad!H10:H21)

C11: =MAX(Estad!H26:H37)

D10: =PROMEDIO(Estad!H10:H21)

D11: =PROMEDIO(Estad!H26:H37)

C15: =PROMEDIO(Fact2015!$J$2:$J$2000)

C16: =PROMEDIO(Fact2016!$J$2:$J$2000)

D15: =MAX(Fact2015!$J$2:$J$2000)

D16: =MAX(Fact2016!$J$2:$J$2000)

F15: =PROMEDIO(Fact2015!$K$2:$K$2000)

F16: =PROMEDIO(Fact2016!$K$2:$K$2000)

G15: =MAX(Fact2015!$K$2:$K$2000)

G16: =MAX(Fact2016!$K$2:$K$2000)

Para finalizar este cuadro de mando, vamos a aplicar a las fórmulas los formatos condicionales.

  • Si las tasas de rechazo son superiores al 2%, los valores aparecerán en rojo.

  • Si los plazos son superiores a 30 días, los valores aparecerán en rojo.

1. Tasa de rechazo

 Seleccione el rango C10 a D11.

 Pestaña Inicio - grupo Estilos, haga clic en el botón Formato condicional.

 Haga clic en Nueva regla.

 En el cuadro de diálogo Nueva regla de formato, seleccione la opción Aplicar formato únicamente a las celdas que contengan.

 Configure la regla:

images/SOB08_32.png

 Haga clic en el botón Formato.

 En la ventana...