Resaltar datos en gráficos

Siempre me ha sorprendido la enorme variedad de gráficos que tiene Excel (la mayoría de poco valor o incluso contraproducentes)  y las poca ayuda que da para pintar gráficos que ayuden a una comprensión clara y rápida de lo que queremos transmitir. Estoy seguro de que lo ingenieros de Microsoft tienen alguna poderosa razón para ello (y que sólo ellos conocen) pero la realidad es que en muchos casos nos quedará más remedio que tirar de ingenio para mejorar las posibilidades de visualización que de serie nos ofrece esta, por otra parte, estupenda herramienta

Veremos algunos consejos para que nuestros gráficos sean más legibles

Gráficos de Barras

La representación que Excel nos da por defecto de un gráfico de barras es un tanto pobre:

En una entrada anterior ya hablábamos de como usar este tipo de gráficos, ahora nos centraremos sólo en un punto: como mejorar su legibilidad. Para ello bastará con formatear, en distinto color aquellas series que queramos destacar; por ejemplo, la mayor y la menor

De este modo percibiremos de forma mucho más rápida e mensaje que nos quiere transmitir. También podríamos no formatear la serie sino la etiqueta de valores (en mi caso, prefiero esta opción) quedando como sigue:

Deberemos hacer dinámico nuestro formato, para asegurarnos que cambia cuando varíen los datos:

Ejes confusos

En ocasiones, nuestros datos alternan valores positivos y negativos lo que hace que los ejes no se lean:

Interpretar un gráfico así es complicado. Lo primero que debemos hacer es situar el rótulo del eje bien por encima, bien por debajo de las series, para que no se "apelotonen":

En segundo lugar, deberemos ordenar las series, para que su interpretación sea más rápida y sencilla, borrar los ejes e incluir los valores dentro de las barras:

Por último, si así lo queremos, podríamos pintar de diferente color los positivos y negativos

Líneas con multitud de valores

¿Quién no se ha encontrado alguna vez con un gráfico tan poco útil como este?

La disparidad de series entrecruzadas asegura que no podamos extraer ninguna conclusión sino es con algún esfuerzo. Con datos como estos debemos preguntarnos qué es lo que queremos analizar en realidad. Normalmente lo que buscaremos será verificar como evoluciona cada una de las series en relación a las demás (dentro del máximo y mínimo que configuran) y comparando con algún criterio en concreto: la media, otra serie.... Para nuestro ejemplo usaremos la media y la mediana

La representación quedará:

Marcamos el máximo el mínimo y destacamos la serie que queremos analizar (en este caso, la empresa K). Además, pintamos la media y la mediana (podría haber sido otra empresa). El hacerlo dinámico nos permitirá ir seleccionando cada una de las empresas que queramos ver

Cómo construir mejores libros Excel (1)

Excel es una herramienta maravillosa, que permite grandes cosas.... y grandes fiascos. Una de sus mayores ventajas (la flexibilidad) puede terminar convirtiéndose en una de sus mayores problemas si no trabajamos ordenadamente. Así nos podemos encontrar con problemas como

• errores en fórmulas,
  
• apariencia confusa,
  
• dificultad para incluir cambios (¡ay de aquel que deba cambiar un Excel desarrollado por otra persona!),
  
• falta de integridad
  

Sin embargo, un Excel correctamente planeado y desarrollado nos va a permitir minimizar los errores, hacerlo crecer sin complejidad, fácil de entender por el usuario, fácil de auditar....


 
En esta primera entrada veremos algunos consejos para que nuestros Excel estén mejor construidos

Tener un propósito claro: es habitual abrir el Excel y empezar a escribir en él sin mayor detenimiento pero conviene hacer una reflexión inicial ¿qué es lo que queremos conseguir? O, dicho de otra forma, ¿cuál es la visualización que debe obtener el usuario del mismo? Debemos tener esa imagen en la cabeza y no perderla en ningún momento, ya que todo lo que construyamos debe estar orientado a ella. También es imprescindible que controlemos en todo momento las necesidades y capacidades de los receptores

Antes que el ordenador, el papel. Nuestro Excel tendrá (probablemente) datos de entrada, fórmulas que consumen dichos datos, nuevos datos generados a partir de los cálculos realizados, un lay out final (ya sea tabla, gráfico, o ambos)... Dibujar el concepto o "arquitectura base" que ha de tener y las interrelaciones entre todos los componentes nos va a permitir asegurar desde el inicio que no pasamos nada por alto, así como ser el "guión" en todo el proceso

Crear tres tipos de hojas y distinguirlas por nombre y colores. Casi con toda seguridad que en nuestro Excel tendremos tres tipos de datos

• Datos de origen y asunciones
• Cálculos
• Output (por ejemplo, el Dashboard)

Ya que tenemos tres tipos de datos, que deben ser tratados de forma separada, ¿por qué no incluirlos en pestañas distintas, e identificar cada pestaña con un color que identifique su tipo? Es una forma de navegar mucho más rápido por el Excel, y siempre tendremos controlado que estamos haciendo y que datos estamos manejando en cada momento. Es importante destinar cada pestaña a una cosa y sólo a una cosa

Tres tipos de pestañas y cada una de ellas identificada con un color

Bloquear celdas: La parte más sensible de un Excel son las fórmulas. Accidentalmente se puede editar una y…. podemos pasar una bonita tarde entera rastreando el error entre todos los cálculos del libro. La mejor manera de evitar esto es bloquear las celdas de manera que no puedan ser borradas o alteradas de forma accidental. Para hacer eso debemos asegurarnos que las celdas que contienen las fórmulas están marcadas como "bloqueadas" (en "Formato de Celdas")

Posteriormente vamos al menú "Revisar" y seleccionamos "Proteger Hoja"

Por último, nos aseguramos que esté seleccionada la opción "Proteger hoja y contenido de celdas bloqueadas". Si queremos, podremos incluir una contraseña para desprotegerla (ojo que Excel no proporciona ningún método para recuperarla si se olvida…)

 

En próximas entradas incluiremos más consejos como (entre otros) utilizar diferentes formatos de celda según sus valores (de manera que visualmente se pueda detectar si algo "no encaja"), normalizar la entrada de datos con la ayuda de "validación de datos" y mensajes de ayuda y usar nombres de rangos, al objeto de hacer más inteligible la lectura de las fórmulas.

Cómo y cuándo usar los gráficos "bridge" o "waterfall". Contando historias con los gráficos

Uno de los principios básicos de cualquier reporting es presentar información fácilmente comprensible y que ayude a saber qué ha pasado y por qué. En este sentido, existen varias técnicas que nos ayudan en nuestro propósito. Una de ellas es el "análisis causal" y su representación gráfica que suele ser el gráfico tipo puente (normalmente llamado "bridge" o "waterfall").

Veámoslo con un ejemplo. Supongamos que nos dan la siguiente cuenta de resultados, correspondiente al primer trimestre de 2014 comparado con el primer trimestre del 2013

Lo primero que veremos es que el resultado en ambos periodos ha sido el mismo (240) pero, ¿por qué ha sido así?. La tabla no da mucha explicación: vemos que han caído las ventas y los costes directos, los indirectos suben, caen las depreciaciones y amortizaciones y suben los intereses... Se puede ver, pero no resulta demasiado intuitivo saber que ha ocurrido. 

Podemos mejorar algo la legibilidad de la tabla añadiendo margenes intermedios y variaciones:

Ahora resulta algo más sencillo entender qué ha ocurrido: el Margen Bruto ha crecido en 50 u.m. ya que, aunque tenemos menos ventas, el porcentaje de margen (el beneficio directo obtenido con cada venta) es mayor: mientras en el 1T2013 sólo obteníamos un rendimiento de 37 u.m. de cada 100 u.m. de venta (37%), en el 1T2014 este rendimiento es de 50 u.m. (50%). Por otro lado, los costes indirectos han crecido, neutralizando todo el mayor margen obtenido en las ventas. Y aunque ahorramos 15 u.m. en intereses, se gastan en D&P por lo que el resultado final es el mismo. Como vemos, hemos podido obtener algo más de información que respecto a la tabla inicial, con unas sencillas técnicas de análisis. 

Pero podemos ir un poco más allá, y entrar en el análisis causal:

Lo que tenemos ahora es una explicación de por qué varia el Margen Bruto

- Por Ventas: -110 u.m.

- Por porcentaje de margen: 160 um.

- Total: 50 u.m.

¿Qué quiere esto decir? Que si hubiésemos mantenido el mismo porcentaje de margen bruto que en el 2013 (es es, un 37%), la caída de las ventas hubiese supuesto una pérdida de 110 u.m. de Margen. Por otro lado, si hubiésemos repetido ventas (1.500 u.m.) pero al porcentaje de margen superior de este año (50%), el incremento hubiese sido de 160. Es el efecto combinado de "ventas" y "porcentaje de margen" lo que hace que crezca en 50 u.m. entre el 2013 y 2014.

Dicho de otro modo, la caída de ventas habría hecho que la empresa perdiera 110 u.m. de Margen Bruto pero como se ha logrado incrementar la rentabilidad de las mismas en 160 u.m., el efecto final es de +50.

El mismo análisis podría realizarse con el resto de las partidas de la Cuenta de Resultados

Y esto, ¿como lo llevamos a un gráfico? Podemos usar los gráficos tipo "puente" que nos ayudan a contar una historia. Partimos de 50 u.m. de BAI en el primer trimestre de 2013 y llegamos a 50 u.m. en el 2014. Este tipo de gráfico construye un "puente" entre ambas cifras, explicando o contando la historia de como llegamos de una a otra: 

¿Cómo llegamos del BAI del 1T2013 al BAI 1T2014?

Los primeros tramos del "puente" que tenemos que construir son los referentes al Margen Bruto, en sus dos vertientes: variación por más/menos ventas y variación por más/menos margen.

La historia que nos cuenta es que partimos de un BAI de 240. Tenemos una caída en ventas (en rojo) que tiene un efecto de -110 u.m. por lo que, de no haber existido nada más, el BAI había caído hasta 130 u.m.. Sin embargo, existe un efecto positivo (en verde) de +160 u.m. derivado del mayor margen, llevando el BAI hasta 290 u.m.

Como el BAI final ha sido de 240 u.m., tenemos que continuar con nuestra historia y añadir el resto de los tramos (costes indirectos, depreciaciones y amortizaciones e intereses):

El efecto negativo de los costes indirectos desciende el BAI hasta 240 (290um - 50 um), el positivo de D&A lo sube a 255 (u.m.) y el negativo de los intereses lo vuelve a dejar en 240 u.m.

De esta manera, hemos logrado explicar, de una forma visual y rápida, qué ha ocurrido para que partiendo del resultado del año anterior, hayamos llegado al de este año.

En este enlace podéis descargaros el Excel que sirve de soporte al ejemplo.

El Cuadro de Mando en el área Comercial (y 2)

Algunas de las métricas clave, en el área Comercial, con las que debe contar la empresa y monitorizar permanentemente son:

• El coste de adquisición de un cliente

• El margen que proporciona un cliente

• La vida media que tiene un cliente

Las explicamos a continuación:

1. Coste de adquisición de clientes: debemos incluir todos los costes en los que incurre la empresa para traer clientes. Es decir, que aquí se sumarán no sólo los salarios y bonus  de lo empleados de ventas (comerciales y administrativos), televenta y marketing, sino también todas las actividades promocionales que se lleven a cabo (publicidad, propaganda, relaciones públicas, SEM, SEO, etc.), presencia en medios sociales, comunity managers, etc. El coste unitario de adquisición de clientes será la división de esta suma, en un periodo dado, entre el número de clientes captados en dicho periodo
2. Margen del cliente: Se refiere al margen bruto, es decir, lo que nos queda de los ingresos después de deducir todos los costes directamente imputables al mantenimiento del cliente. or ejemplo, el coste del departamento de atención a clientes, etc
3. Vida media del ciente: si se trata de de un negocio de suscripción, el cliente permanecerá activo un determinado tiempo, más o menos largo, antes de que se de de baja definitivamente. Si nuestra tasa de bajas (churn rate) es, por ejemplo, del 8%, la vida media del cliente será de 13 años (100%/8%) y si aumenta al 10%, habremos perdido 3 años al pasar la vida media a 10 años (100%/10%).

Por sorprendente que parezca, muchas empresas no saben cual es el valor de un cliente (seguramente la métrica más importante en la mayoría de los negocios), que se puede  deducir fácilmente de los datos anteriores. Resulta peligroso que se acometan acciones comerciales que tienen un coste superior al beneficio que darán los clientes captados, lo que ocurre por no tener claras las tres variables anteriores. 

Uno de los aspectos clave de cualquier negocio es la relación existente entre el coste adquirir nuevos clientes y la rentabilidad de los mismos. Un modelo de negocio sólo es sostenible a largo plazo si los clientes aportan valor de forma sostenida, es decir, si el beneficio que nos dejan es superior al coste de captarlos. Esto, que parece obvio, no siempre se cumple y una larga serie de empresas (sobre todo en el ámbito de internet) parecen inmersas en una huida hacia adelante, en una estrategia de alcanzar el elevado numero de clientes sin tener muy claro cómo van a llegar a rentabilizarlos.

Afortunadamente, con las tres variables anteriores, más conociendo la compra media de los clientes, ya estaremos en condiciones de saber si nuestra política de captación y retención  es rentable o no (es decir, si incorpora valor a la compañía o lo destruye). En negocios de suscripción es usual que los clientes sean deficitarios al principio para comenzar a ser rentables a partir de un momento dato. Mediante un excel, podemos calcular fácilmente estos datos y ver visualmente la gráfica de su evolución:

Aquí podemos descargar un excel que nos permitirá hace simulaciones de rentabilidad. Para ello sólo tenemos que ir cambiando los datos de las casillas:

• Compra media (el importe medio de cada compra que realiza el cliente de nuestro producto)

• # Compras por año (¿cuantas compras realiza un cliente, en media, al año?

• Coste de adquisición -en negativo- (definido como en el punto 1 anterior)

• % MG Bruto (definido como en el punto 2 anterior)

• Churn Rate (tasa de bajas que tenemos del cliente)

El modelo sirve tanto para negocios que ya están en funcionamiento y que así pueden comprobar su su modelo es sano o, por el contrario, está destruyendo valor, como en la fase de evaluación de nuevos modelos y así determinar su nuestras hipótesis de partida nos llevan a la rentabilidad o no.

Por ejemplo, si tenemos un negocio en el que la compra media es de 30€ 12 veces al año (una suscripción, por ejemplo), los costes de captación de un cliente son de 600€ proporcionando un margen bruto del 50% y estimamos una churn rate del 10%, alcanzaríamos el break even en el año 4 con una rentabilidad del 65% (390€). Sin embargo, si la tasa de bajas se sitúa en el 20% (muy habitual) el negocio sería deficitario, nunca llegaríamos al break even y cada cliente nos aportaría -60€, es decir una pérdida del 10% de la inversión en él.

Haced pruebas cambiando el resto de las variables y adaptarlo a vuestro modelo de negocio para poder tomar las decisiones oportunas que permitan maximizar la rentabilidad de vuestras empresas.

¿Y si empezamos con un Excel?

Para una empresa que no tenga ninguna herramienta de análisis de datos, comenzar a usar una aplicación de Business Inteligence puede ser una experiencia muy dura, sobre todo si pensamos en pequeñas compañías. La soluciónes de SAP, COGNOS, QlikView (mi favorita) o incluso las más sencillas basadas en Excel como la de Apesoft representan un salto cuantitativo importante que no todas las empresas están preparadas para dar. No olvidemos que muchas de las implantaciones de BI fallan porque el receptor (y en ocasiones el emisor) de la información no tienen los conocimientos necesarios para entender la herramienta adoptada, y no porque el negocio se haya definido mal o los informes están mal diseñados. 

Ante esto, surge una pregunta, ¿que herramienta podemos escoger que sea ampliamente conocida por toda la organización, el usuario se sienta confortable con ella y tenga capacidad suficiente para analizar datos? La respuesta es obvia.... Excel

El Excel es tan común en nuestros ordenadores que nos hemos acostumbrado a él y no somos del todo conscientes de las capacidades que tiene. Cierto es que cuenta con algunos problemas (que trataremos más adelante) pero las ventajas para una pequeña/mediana empresa que comience con BI sus superiores a sus inconvenientes

• Ventajas de usar Excel
• Es muy potente. Aun cuando la mayoría de los usuarios hagan un uso elemental de la herramienta, no es difícil encontrar personas que trabajan cómodamente con fórmulas avanzadas, macros, solver... permitiendo una explotación de datos muy completa
• El receptor de la información sabe usar Excel y entiende perfectamente un informe en él cargado
• Está instalado en prácticamente todos los ordenadores de todas las empresas
• En contraposición a otras herramientas, permite incluir datos de forma manual, haciendo más sencillo el completar información que no está en la BBDD de la empresa
• Facilidad para compartir los informes con otros usuarios, internos o externos a la organización
• Potencia de las tablas dinámicas
• Amplias funcionalidades de diseño y gráficas (sobre todo las versiones 2007/2010 con minigráficos, etc.)
• Desventajas de usar Excel
• Es necesario estructurar muy bien la información inicial, ya que de lo contrario se puede convertir en un caos
• Existencia de diferentes versiones que entran en conflicto (Informe, Informe-1, Informe-Definitivo...)
• Dificultad (pero no imposibilidad) de cargar la información de las bases de datos de la empresa en Excel
• Proliferación de Excel por toda la compañía, cada departamento se hace el suyo, con sus datos parciales y es difícil obtener una visión de conjunto. Además, en numerosas veces, los datos suministrados por diferentes departamentos son contradictorios

Como vemos, las desventajas son varias e importantes, pero creemos que, en determinados casos como los expuestos al inicio de este post, las ventajas compensan. Eso sí, es necesario hacer las cosas bien desde el principio para minimizar estos inconvenientes siguiendo unos consejos sencillos

1. Los informes deben partir siempre de un mismo departamento (Control de Gestión si existe o IT, Sistemas...)
2. Al frente del proyecto debe estar una persona que maneje con solvencia el Excel y sea analítico y ordenado, para estructurar la información inicial de forma correcta
3. Los Excel deben estar bloqueados, de forma que el usuario pueda consultar los datos pero no incluir nueva información en ellos (de ser preciso, se solicitará al departamento emisor) evitando así tener versiones distintas por cada departamento de la empresa, cada una con su propia información

Cumpliendo con estos requisitos, en empresas pequeñas y medianas, Excel puede ser una solución ideal para iniciarse en el mundo de BI. Una vez que la empresa esté madura y requiera de más información y capacidad de análisis, será el momento de saltar a herramientas específicas.

Captura de un cuadro de mando diseñado en Excel