Cómo hacer un Dashboard económico-financiero con Excel (4)

Seguimos en este artículo con la planificación de un dashboard o cuadro de mando económico-financiero. Vamos a ver la forma en la que se organizan los datos en las distintas hojas de cálculo. Es decir, los modelos de datos.

Como resumen y adelanto a este artículo y los siguientes, podemos decir que tenemos que aprender a llegar hasta aquí (presentación)...

...pasando por aquí (análisis)...

...y empezando desde aquí (datos)....

Lo que acabamos de ver son 3 ejemplos de las 3 capas o fases del flujo de la información:

datos > análisis> presentación

o bien...

datos > información > conocimiento

Cuando necesitamos desarrollar una aplicación cuya utilidad última va a ser el análisis de datos, no podemos pretender hacerlo de una vez y en una sóla hoja de Excel. Debemos trabajar en distintas hojas "especializadas" porque ello dará orden y escalabilidad (posibilidad de ampliación y sostenibilidad a lo largo del tiempo). Además, utilizar una hoja más, es gratis en Excel. Remitimos al lector al artículo "Buenas prácticas: Estructura y tipos de hojas en Excel" especialmente al gráfico de pirámide y al punto nº 7.

En este artículo vamos a desarrollar la primera de las fases anteriormente mencionadas:

DATOS

La primera fase o estado en el que se encuentra la información se llama "fase o capa de datos" y puede tomar varias formas. Comenzamos por descubrir los modelos que pueden adoptar los datos en esta primera fase:, pero antes recordemos el concepto "Dimensión" a la hora de hablar de tablas y modelos de datos:

"Dimensión" es un criterio por el que podemos ordenar, clasificar o agrupar unos datos numéricos. Por ejemplo, si tenemos una tabla con los campos "Fecha", "Tipo de gasto" y "Euros", las dimensiones serán los campos "Fecha" y "Tipo de gasto", puesto que podremos ordenar por fechas (ya sea por días, meses, años, etc.) o clasificar/ordenar por tipo de gasto (Suministros, Reparaciones, Impuestos, etc.). 

Ahora si....vamos allá con los modelos de datos...

1) TABLAS UNIDIMENSIONALES

Son las resultantes cuando introducimos nuestros datos en una colección de tablas de una dimensión, donde cada tabla representa normalmente a un período de tiempo (no siempre). En las siguientes tablas, la única dimensión es la "Cuenta" (Ingresos, Compras, Servicios, etc.). Esta primera modalidad que comentamos es UN GRAVE ERROR... y es el error más habitual en el usuario. 

... también es muy habitual poner cada tabla en una hoja distinta a la cual se pone un nombre descriptivo ...

Si no acertamos en la forma en la que disponemos nuestros datos en esta fase, el resto de desarrollo de nuestro dashboard será un proceso arduo, muy poco eficiente y tremendamente limitado en sus posibilidades. La mayoría de usuarios de Excel no superan esta fase de forma óptima porque no conocen otras posibilidades y aplican la única que saben y que cumple con estas 3 condiciones:

• La disposición de los datos se hace de forma intuitiva y responde a corto plazo a algunas necesidades de información.

• El usuario sólo necesita utilizar los operadores básicos (+-*/) y poco más.

• Como consecuencia de los anteriores puntos, es rápida.

El motivo de considerar este tipo de organización de datos como un error, estriba en su limitación a la hora de ofrecer más información (y de más valor) de la que se ve en cada tabla:

• Cada mes hay que copiar y pegar una nueva tabla (o copiar en una nueva hoja y nombrarla) y modificar como mínimo la fecha.

• Para sumar los datos de todos los meses se debe hacer una nueva tabla e introducir en cada celda correspondiente, una fórmula que vaya sumando cada dato en cada uno de todos los meses. Cuando transcurre un mes, se deben modificar todas las fórmulas para añadir el nuevo período. Todo ello es un consumo de recursos enorme y además, con una alta probabilidad de errores.

• Si quisiéramos sumar los datos de un número determinado de meses distinto al anteriormente realizado, deberíamos construir otra tabla más y de forma exclusiva para esos meses, debiendo modificar todas las fórmulas de la tabla cada vez que quisiéramos variar los meses consultados.

• Si quisiéramos realizar un gráfico para ver la evolución de alguna cuenta, nos encontraríamos con los mismos problemas de los anteriores puntos.

• Si transcurren, por ejemplo, 7 años desde que se crea la aplicación, tendríamos 84 hojas de calculo para manejar con 84 tablas, o bien 84 hojas ubicadas en una misma tabla... (según modelo elegido) y todo ello, necesitando manejarlas como hemos comentado en los anteriores puntos.

Es decir, para cualquier otra información que no sea la que se ve directamente en esas tablas, hay que volver a copiar y pegar la estructura, crear nuevas fórmulas y además modificarlas a lo largo del tiempo. Todo esto es insostenible y nada productivo.

En resumen, podemos decir que es un modelo de datos que:

NUNCA HAY QUE UTILIZARLO

Podemos ver un ejemplo si descargamos el siguiente archivo:

2) TABLAS BIDIMENSIONALES ("flat tables" o tablas planas): 

Son las que tienen una dimensión con sus items extendidos a lo largo de los encabezados de columna y otra dimensión con sus items extendidos a lo largo de las filas.

No tiene porqué ser así siempre, pero suelen presentar en las columnas los períodos de tiempo (la dimensión "fecha", que está extendida a lo largo de las columnas, pero es sólo una dimensión. ¡Cada mes NO es una dimensión!) y en las filas los items de otra dimensión a estudiar, por ejemplo, las cuentas:

Se utilizan para modelos de datos que están limitados en el tiempo y cuyo análisis se circunscribe a un período definido de antemano (sería insostenible una tabla durante muchos años con decenas de columnas). Ejemplos de ello, podrían ser un plan de viabilidad económico-financiero a X años, previsiones de tesorería o una gestión presupuestaria.

Este es un modelo de organización de datos que tiene las siguientes VENTAJAS:

• Es ciertamente intuitivo y fácil de desarrollar en un principio (los datos con este formato, suelen ser introducidos por el usuario, no importados de otros programas).

• En el paso de la fase de DATOS a INFORMACIÓN hay posibilidad y flexibilidad para introducir ratios y fórmulas complejas y otras combinaciones de datos. Por ejemplo, las fórmulas del punto de equilibrio o las de los períodos medios de cobro y pago.

• Es un modelo de datos que aporta muchas posibilidades para la última fase de PRESENTACIÓN en el propio dashboard: es fácil extraer rangos de celdas para construir gráficos y es fácil extraer valores para formar KPI's comparativos y de alto valor (gráficos de velocímetro, de termómetro, etc.).

Pero también presenta algunas DESVENTAJAS:

• La posibilidad de agrupar la información por distintos períodos de tiempo no es ágil y se hace compleja.

• Debido al anterior punto y a otros similares, el usuario se ve obligado a utilizar de forma intensiva fórmulas y funciones como SI, Y, O, BUSCARV, BUSCARH, INDICE, COINCIDIR, DESREF, etc. En definitiva, el paso de la fase DATOS  a INFORMACIÓN es más difícil y ardua.

• 2 dimensiones es un origen de datos muy escaso. Estamos privados de conocer otros puntos de vista (otras dimensiones) de forma ágil, aunque sí es posible con ciertas rigideces y apelando a algunos "trucos".

En resumen y como factores más destacados, podemos decir que es un modelo de datos que:

• No tiene limitaciones para ofrecer KPI's y gráficos de alto valor
• Requiere más conocimientos del usuario y utilización intensiva de funciones
• El manejo de los períodos de tiempo

Las tablas bidimensionales suponen un avance espectacular respecto de las tablas unidimensionales, como se puede ver en el archivo que a continuación puede descargar el lector:

TABLAS DE DATOS MULTIDIMENSIONALES (Cubos [3D] o hipercubos [+3D] OLAP)

Son tablas en las que hay una o varias columnas con un "Valor en euros" y el resto de columnas son dimensiones o criterios por las que se podría consultar la información. Entre las dimensiones, en el ámbito económico-financiero casi siempre está el campo o columna "Fecha". Vemos a continuación un ejemplo de "Tabla Multidimensional":

Es la forma habitual que nos encontramos en las tablas que importamos desde otras aplicaciones (por ejemplo, el diario de una contabilidad o el listado de facturas emitidas), pero también es posible que sea una tabla que el usuario administre introduciendo datos registro a registro (por ejemplo una aplicación de gestión de costes por proyectos o de gestión de nóminas). Podemos ver en la tabla anterior las dimensiones "Fecha", "Tipo de movimiento", "Código Cuenta" y "Descripción Cuenta". Es decir, un hipercubo OLAP.

Con este modelo de datos, se tienen las siguientes VENTAJAS: 

• Desaparecen los problemas de sostenibilidad en el tiempo, puesto que  la tabla puede aumentar hasta 1.048.576 registros.

• Se pueden añadir, mediante columnas, tantas dimensiones como sea necesario.

• Una organización de datos así, podrá servir como origen de datos de Tablas Dinámicas y ahí... se abre un mundo de posibilidades que podemos descubrir (si el lector no lo ha hecho ya) en la serie de artículos dedicado a las Tablas Dinámicas que comienza por este: "Tablas Dinámicas (I) ¿Qué son y para qué sirven?". La principal ventaja que se obtiene de esto es la velocidad de proceso y la agilidad. Es decir:
• Es una forma eficiente de ordenación de datos y se procesan las consultas y filtros a una velocidad inigualable y muy superior a las bases de datos relacionales.
• Podemos cambiar la presentación de la información por un criterio u otro de forma ágil y en tiempo real según necesidades.

Estas serían las DESVENTAJAS:

• La estructura multidimensional u OLAP, es poco intuitiva y genera rechazo a usuarios poco experimentados.

• Si la tabla la administramos nosotros introduciendo la información de forma cotidiana, en cada registro hay que introducir de forma repetitiva los datos de cada dimensión (ver en la tabla anterior los nombres de los "Agentes", por ejemplo, repetidos muchas veces en la tabla. Esta desventaja desaparece, evidentemente, si la tabla es importada.

• Las tablas multidimensionales están destinadas casi irremediablemente a ser procesadas mediante Tablas Dinámicas para convertir los "Datos" en "Información". Esto proporciona las ventajas propias de dichas tablas dinámicas, pero también sus servidumbres: las tablas dinámicas ofrecen más rigideces y dificultad para ser incluidas en Dashboards o para combinar datos que generen KPI's de muy alto valor. Es decir, son muy buenas (únicas y las mejores) para pasar de "Datos" a "Información", pero no tanto para pasar de "Información" a "Conocimiento".

En resumen y como factores más destacados, podemos decir que es un modelo de datos que:

• Es fácilmente sostenible en el tiempo y escalable en tamaño.
• Es extremadamente rápido, fácil de utilizar y eficaz para el entorno operativo y táctico en la empresa (a nivel de mandos intermedios).
• En muchas ocasiones, ofrece rigidez y dificultades para construir KPI's para la toma de decisiones estratégica.

Los siguientes archivos muestran ejemplos del modelo de datos multidimensional y de posibles formas de convertir los datos en información mediante Tablas Dinámicas y algunos gráficos:

Tabla Multidimensional importada de otra aplicación:

Tabla Multidimensional administrada por el usuario:

Puede  ver otros artículos de esta serie:

Cómo hacer un Dashboard económico-financiero (1)
Cómo hacer un Dashboard económico-financiero (2)
Cómo hacer un Dashboard económico-financiero (3)
Cómo hacer un Dashboard económico-financiero (5)
Cómo hacer un Dashboard económico-financiero (6)

Si desea llevar a la práctica todo lo que le hemos ofrecido en este artículo, puede asistir a nuestros cursos prácticos de Excel o puede solicitarlos in company:

 

Cómo hacer un Dashboard económico-financiero con Excel (3)

En este artículo hablaremos de 4 principios en el diseño de un dashboard o cuadro de mando económico-financiero.

1. Simplicidad

• Un dashboard debe ocupar una sóla página y preferentemente con orientación horizontal o apaisada. No debe ser necesario hacer scroll en nuestra pantalla de ordenador , ni tampoco pasar de una página a otra en caso de leerlo en papel. Si necesitamos tantos indicadores que no podemos "encajarlos" en una sóla página, tenemos que considerar dividirlos en dos temas distintos y realizar dos dashboards. Por ejemplo, si no hay espacio suficiente en una página para todos los KPI's de costes de una empresa, podemos separarlos por tipo de coste (Coste fijo / Coste variable o bien Costes generales / Costes de la producción, etc.) en varios dashboards.

• No incluir demasiadas tablas y listas. Hablamos de diseñar un dashboard, no un informe ni un scorecard. Un dashboard debe incluir KPI's que comparen de forma visual lo conseguido respecto de las metas a conseguir. Sin embargo, un scorecard ofrece una información principalmente numérica en forma de tablas (y a veces indicadores gráficos que casi siempre muestran la evolución y tendencia), pero orientado a la toma de decisiones más operativa y menos estratégica. Además, los scorecard (a diferencia de los dashboard) se actualizan y son consultados con más frecuencia y están dirigidos a mandos intermedios.

• Formatos austeros. NO a la fantasía. Sobre esto, recomendamos encarecidamente que el lector revise el articulo "20 consejos sobre gráficos y una norma desesperada". En definitiva, se trata de maximizar el "data-ink ratio" o "ratio información / tinta" por centímetro cuadrado. Concepto este introducido por el experto en visualización de datos, Edward Tufte.

2. Uso adecuado del espacio en la página

Si bien en un dashboard sólo debe aparecer información relevante y que aporte conocimiento para la toma de decisiones, dentro de la colección de KPI's que decidimos incluir, no todos tienen la misma importancia y eso es motivo de plantearse la pregunta ¿Qué lugar debe ocupar cada KPI? La respuesta la tenemos en el siguiente esquema:

La numeración de los cuadros anteriores, representa el orden de importancia de cada zona y el orden en el que la mayoría de lectores focalizan su mirada en cada espacio (según el proyecto "Eyetrack III" del "Pointer Institute" , del cual se pueden obtener interesantísimas conclusiones).

3. Intuitivo

Cualquier persona debería poder entender qué tipo de información ofrece el dashboard y su contexto. Sería una buena idea poner esto en práctica con algún conocido que no haya intervenido en el desarrollo. Si esa persona necesita preguntar repetidas veces sobre el significado de las distintas partes del dashboard, es que algo no va bien.

Quizá son necesarios algunos títulos más... o quizá menos. Quizá los gráficos no son adecuados o quizá falta o sobra información numérica de apoyo. También es posible que algunos KPI's o títulos no estén suficientemente resaltados o en el lugar adecuado. Otras causas de confusión pueden ser los formatos aplicados a los números.

Títulos

• Ineludiblemente, el dashboard debe incluir un título haciendo referencia al rango de fechas al que se refiere la información económica.
• Debe quedar muy claro cuál es la fecha última de actualización de los datos que nutren al dashboard
• Los títulos han de ser descriptivos, evitando las siglas o símbolos que produzcan confusión.
• Una técnica que se puede aplicar a los títulos, es aplicarle unos colores muy suaves, precisamente para realzar la importancia de los datos.

Formatos

• Es conveniente usar el punto de los millares, porque facilita la lectura:

NO:  2354890               SI:  2.354.890

• Usar decimales sólo si el contexto de los datos lo requiere. Por ejemplo, si las cifras que se manejan son muy pequeñas. Sin embargo, si representamos cifras "millonarias", no es necesaria la utilización de decimales en un dashboard que va a ser interpretado para la toma de grandes decisiones.
• Usar el símbolo del € o de la moneda correspondiente, sólo cuando sea estrictamente necesario. Si el dashboard está desarrollado en español, va a ser leído por personas de nacionalidad española y además referido a las ventas de una empresa española ¿qué necesidad hay de reflejar el tipo de moneda?
• Si las cifras representan varios millones, se pueden mostrar en "miles".

4. Interactivo y configurable

Interactivo

Nuestro "Tablero de a bordo" debe ofrecer la posibilidad de que el usuario modifique algunos parámetros  o "dimensiones" mediante ciertos controles. Es decir, debemos proveer a nuestro dashborad de listas desplegables, botones de opción, casillas de verificación, etc. que sean capaces de:

• Seleccionar rangos de fechas (podemos ver como ejemplo de vanguardia, los nuevos controles de escala de tiempo de Excel 2013)
• Seleccionar formatos de presentación (euros, porcentajes, unidades, etc.)
• Seleccionar el tipo de presentación (alternar entre gráficos de un tipo u otro)
• Seleccionar lo que se quiere ver (podemos aprovechar un mismo espacio de nuestro dashboard para alternar entre presentar un gráfico de tarta con la distribución de ventas por líneas de negocio, o bien presentar una línea de tendencia de la producción del período)
• etc.

Configurable

En casi cualquier aplicación de Excel, hay ciertos parámetros que no son inamovibles a lo largo del tiempo y tampoco válidos para cualquier empresa. Por ejemplo:

• Podríamos incluir en determinados títulos el nombre comercial de la empresa, pero, ¿qué ocurrirá si algún día cambia dicho nombre?
• Podríamos incluir en las formulas que lo requieran el tipo de IVA vigente, pero, ¿qué ocurrirá si hay un cambio al respecto?
• Podríamos imputar los costes de alquiler según un porcentaje de ocupación determinado, a las distintas áreas funcionales de la empresa, pero, ¿qué pasaría si algún día la empresa hace reformas  y redistribuye el espacio debido a nuevas necesidades?

Por lo tanto y según estos ejemplos, nos damos cuenta de que es necesario que el usuario tenga acceso a estos parámetros mediante una hoja que permita su edición cuando sea necesario.

5. Navegable

Ya hemos dicho en otros artículos que para desarrollar un dashboard en un libro de Excel, es necesaria una estructura con varias hojas de distinto tipo y funcionalidad. Por lo tanto, lo ideal es dotar a la aplicación de un sistema de navegación mediante links y/o botones. Este tema lo podemos ver desarrollado en el artículo "Buenas prácticas: Navegar por un libro de Excel".

En el siguiente artículo de esta serie y después de haber planificado y tomado multiples decisiones sobre los KPI's necesarios y la forma en la que se representarán estos, entramos en la recta final de la planificación. Se trata de establecer la forma de organizar los datos en distintas hojas, desde el principio, hasta que estos están representados en un dashboard. Es decir, conoceremos las 3 capas o fases de la información: desde que el dato se genera o importa de otra aplicación, hasta que está presentado en forma de KPI, pasando por procesos y cálculos intermedios mediante fórmulas, funciones y tablas.

Si desea llevar a la práctica todo lo que le hemos ofrecido en este artículo, puede asistir a nuestros cursos prácticos de Excel o puede solicitarlos in company:

 

Cómo hacer un Dashboard económico-financiero con Excel (2)

En este post, vamos a desarrollar los puntos que conforman las líneas maestras de la planificación de un dashboard económico - financiero para una definitiva comprensión. El objetivo no es empezar el desarrollo con Excel. El lector tan sólo debe reflexionar, tomar notas y hacer un esquema de todo cuanto se aconseja a continuación.

Queremos insistir al lector (para que no pierda de vista el contexto) en algo que ya dijimos en la primera entrega de esta serie de articulos: "...cuando hagamos referencia a "Dashboard", se supone que estamos teniendo en cuenta también la estructura que lo soporta y no sólo la página que contiene los KPI's". Aunque es cierto que en muchos contextos nos referiremos a la página o páginas que contienen precisamente esos indicadores.

DEFINIR EL OBJETIVO GENERAL

Es necesario pedir al que será lector final del dashboard, cuál es el problema que ha considerado suficiente para pensar en necesitar información económico-financiera para la toma de decisiones. Podría ser la necesidad de controlar una nueva unidad de negocio, o quizá la necesidad de controlar la evolución de las ventas en distintas líneas o gamas de productos para guiar la política de ventas, o tal vez cualquier otra razón similar. En definitiva:

El objetivo final debe ser nuestra primera ocupación

DEFINIR LOS DESTINATARIOS DEL DASHBOARD

Es necesario prestar atención a algunos aspectos sobre los destinatarios o "consumidores" finales de un dashboard. El dashboard...

• ...¿Será leido e interpretado por personal interno de la empresa o será presentado a entidades financieras, proveedores u otros agentes? Los contenidos deberán adaptarse a las necesidades y el objetivo, la confidencialidad requerida, etc.
• ...¿Qué lugar ocupan los destinatarios en la organización de la empresa y que formación tienen? No es lo mimso un dashboard dirigido a un responsable de área, que a un gerente con formación en Ciencias Empresariales, que al Director General de la compañía (sin formación específica en materia de empresa y finanzas, pero con conocimientos generales), que a los socios accionistas. Cuanto más alto sea el nivel jerárquico la información debe ser más gráfica e intuitiva y con ausencia de tablas y terminología económica. Tan sólo en el caso de cuadros de mando o informes para responsables de área, deben incluirse tablas en combinación con gráficos y términos técnicos.
• ...¿Será necesario interactuar con el dashboard? Si va a ser interpretado de forma estática (incluido en un Power Point, en un PDF, etc.) no podremos interactuar para acotar fechas, seleccionar líneas de negocio, formato de los datos, etc. Esto nos obligará a afinar mucho con el contenido, puesto que deberá ser exactamente lo que el destinatario necesite. Sin embargo, si es posible interactuar, habrá que utilizar controles especiales como listas desplegables, botones de opción, etc., y deberá ser tenido en cuenta a la hora de definir los KPI's y su distribución en la página.

DEFINIR LOS KPI's

Un KPI es un indicador del nivel de cumplimiento de un objetivo relevante en uno o varios procesos de negocio. Por lo tanto, un KPI nos debe reclamar atención e intervención ante algo que se está desviando de lo establecido como objetivo. Los KPI's que debemos elegir para nuestro dashboard deben dar soporte a nuestro objetivo general. No debemos incluir un dato sólo porque lo tenemos disponible.

Un truco para ayudarnos a configurar nuestros KPI's es formular las preguntas que deben ser contestadas por éstos (basadas en el objetivo general que se comentaba en el punto anterior) y disponerlas en una página como si fuera nuestro futuro dashboard.

..Después, podremos variar este boceto y adecuarlo a ciertos criterios de uso óptimo del espacio en la página (ver el artículo "Cómo construir un Dashboard económico-financiero (3)"). En el ejemplo de arriba, al final del proceso de desarrollo del dashboard, el "boceto" anterior terminó siendo así:

Este es un Dashboard totalmente temático, puesto que analiza sólo la producción de una empresa (las ventas  o facturación, regularizadas con la variación de exsistencias de productos en curso y terminados), pero podría ser un dashboard que en una sóla página aportara la información mas relevante de varios aspectos de una compañía, como pueden ser los costes fijos, variables, de la producción, etc., así como el beneficio, cash flow u otros.

DEFINIR LAS FUENTES DE DATOS

Uno de los sentidos de tener explícito el anterior punto (definición de KPI's) es precisamente, saber de dónde tenemos que extraer los datos.

Imaginemos que vamos a desarrollar un dashboard para un responsable del departamento comercial y, a requerimiento suyo, debemos incluir un KPI con el período medio de cobro a clientes en un período detemrinado. Para ello y, según la fórmula del PMC (Período medio de cobro), necesitaremos saber:

• La facturación neta realizada en el período al cliente
• El saldo medio de deuda que ese cliente ha mantenido con la empresa en el período

El motivo es que el PMC=365 / (Ventas / Saldo medio deuda cliente). O lo que es lo mismo, para un cliente al que se le haya facturado 600.000 € en un año y haya mantenido una deuda de 100.000 € como promedio, el PMC habría sido de 61 días (2 meses aprox).

Conclusión: debe ser posible encontrar en algún sitio de nuestro sistema de información los datos enumerados anteriormente. Por ejemplo, si tuviéramos acceso al "Libro Diario" del programa de contabilidad o al "Libro mayor" de la cuenta 430xxxxxx, podríamos importarlo a formato Excel y a partir de ahí sólo tendríamos que filtrar los movimientos en un período determinado y añadir una columna que genere un acumulado. Posteriormente, para saber la deuda media, calculamos el promedio del acumulado.

De forma similar, la facturación neta sería fácil obtenerla de ese "Libro Diario" filtrando por la cuenta 70xxxxxx y totalizando los registros obtenidos en un período determinado.

Este podría ser un ejemplo, pero debemos reflexionar sobre todos los KPI's que pretendemos incluir y sobre sus orígenes de datos. 

DEFINIR LAS DIMENSIONES Y LOS FILTROS

Dimensiones

Las dimensiones son todos aquellos campos que NO son el valor en euros. Dicho de otra forma, son aquellos campos por los que podríamos consultar cosas sobre el campo protagonista que es el valor en euros. Si nos fijamos en la tabla del punto anterior, vemos que hay 3 campos de dimensión:

• La fecha
• El código de cuenta
• El nombre de la cuenta

El cuarto campo es el propio valor en euros y NO es una dimensión. El quinto campo es añadido después de importar los datos del ERP o del programa de contabilidad y su utilidad es reflejar el acumulado.

Por ejemplo, si en nuestro dashboard  quisiéramos introducir un gráfico de tarta para ver la distribución por líneas de negocio, deberíamos conseguir un campo adicional con la dimensión "Línea de negocio".

Filtros

Antes hemos definido y entendido lo que son las dimensiones. Pero lo más frecuente es que no necesitemos todos los registros que tenemos de nuestra base de datos original. A veces, necesitaremos extraer de ella una tabla ya filtrada mediante fórmulas o funciones de forma continua y como origen de datos de un KPI determinado. Otras veces necesitaremos que los filtros los aplique el usuario de forma interactiva mediante listas desplegables, botones de opción, cuadros de lista, etc. Debemos planificar adecuadamente todo ello.

Ejemplo de filtros en los que el usuario decide interactuando

Si desea llevar a la práctica todo lo que le hemos ofrecido en este artículo, puede asistir a nuestros cursos prácticos de Excel o puede solicitarlos in company:

 

Cómo hacer un Dashboard económico-financiero con Excel (1)

Quizá el lector ha llegado a este artículo ávido de formación e información sobre un tema que ya conocía de su existencia como es el de los Dashboards o Cuadros de Mando económico - financieros. Quizá el lector ha llegado hasta aquí por la curiosidad que le genera un tema que no conocía, pero que cree que puede ayudarle en su desempeño profesional en el área eco-fin de la empresa donde trabaja... o de su propia empresa.

Parte de un Dashboard creado en Excel

En cualquier caso, vamos a intentar construir esta serie como corresponde... desde el principio.

¿Que es un "Dashboard"?

En el ámbito empresarial, un "Dashboard" (traducido de forma literal sería "Tablero de a bordo" por su parecido a un salpicadero con velocímetros y otros indicadores gráficos) es un informe que proporciona indicadores de ciertos procesos de negocio y que cumple las siguientes condiciones:

1. Muestra gráficamente los datos, ayudando al destinatario del informe a enfocar su atención en posibles desviaciones, tendencias, comparaciones y otros comportamientos de algunas métricas de carácter económico y financiero (por ejemplo).
2. Muestra sólo los datos relevantes con respecto a un objetivo general establecido en un Plan de Empresa o en un Plan Estratégico.
3. Muestra conclusiones objetivas para inferir en el lector la construcción de su propio análisis ponderado por matices y factores que van más allá de la frialdad de los números.

Dashboard creado en Excel

Si bien lo que acabamos de ver es la cara visible, tenemos que señalar que detrás de esta página o "tablero de a bordo" debe haber toda una organización o estructura de hojas con distintas funciones. Aprenderemos la forma de desarrollar todo ello más adelante, pero un adelanto de esa estructura se puede ver en el artículo de este mismo Blog: "Buenas prácticas: Estructura y tipos de hojas en Excel"

Así pues, en adelante, cuando hagamos referencia a "Dashboard", se supone que estamos teniendo en cuenta también la estructura que lo soporta y no sólo la página que contiene los KPI's.

¿Cómo empezar?

Se dice que lo ideal cuando nos disponemos a desarrollar una aplicación en Excel es intentar aplicar el principio de Pareto de la siguiente forma:

• 80% del tiempo planificando bolígrafo en mano
• 20% del tiempo desarrollando en Excel

A mi me parece algo exagerado, pero quizá sí sería ajustado considerar una relación de 60% - 40%  o incluso de 70%-30%.

Vamos a ocuparnos de esa planificación que debería ocuparnos entre el 60% y el 80%. Para ello y, de forma contraria a lo que mucho usuarios hacen, DEBEMOS EMPEZAR POR EL FINAL. Es decir, lo primero que debemos decidir al abordar el desarrollo de un cuadro de mando, son todas aquellas cosas que tienen que ver con el final. Estas cosas son:

Planificación

1. Definir el objetivo general que a final queremos conseguir.
2. Definir quienes serán los destinatarios finales de nuestro trabajo, cómo lo utilizarán, el nivel de detalle requerido, etc.
3. Definir los indicadores que al final son los que nos guiarán en la toma de decisiones.
4. Definir las fuentes de datos que "alimentarán" a nuestro Dashboard cuando ya esté terminado.
5. Definir las dimensiones (criterios por los que la información se mostrará) y los filtros (formas de segmentar o acotar los datos y la información.

Es absolutamente necesario tener TODOS estos puntos bien definidos y expresados por escrito, puesto que en el proceso de desarrollo del Dashboard vamos a necesitar una visión de conjunto.

Como puede ver el lector, no hemos decidido nada respecto de nuestro libro de Excel, ni tan siquiera hemos hablado de Excel. Para ello, aun es pronto. En el artículo siguiente desarrollaremos con más detalle cada uno de los 5 puntos enumerados anteriormente, poniendo también algunos ejemplos.

Si desea llevar a la práctica todo lo que le hemos ofrecido en este artículo, puede asistir a nuestros cursos prácticos de Excel o puede solicitarlos in company:

 

Detectar valores duplicados en una columna de Excel

De forma distinta al artículo anterior, en el que se trataba de "Detectar diferencias entre 2 columnas en Excel", vamos en esta ocasión a aprender un "truco" para detectar valores repetidos más de una vez, dentro de una misma columna.

Insistimos en que vamos a "detectar", no a eliminar duplicados. Para esto último, Excel tiene una herramienta que podemos encontrar en la ficha Datos / grupo Herramientas de Datos / Quitar duplicados

Además, y de la misma forma que lo hacíamos en el artículo anterior, lo vamos a hacer con una utilización avanzada de "Formato condicional". Es decir, sin necesidad que crear una columna auxiliar con fórmulas, sino que incluiremos una función "dentro" del formato condicional.

Detectar un valor repetido más de una vez en una columna (forma sencilla)

Si sólo queremos saber los valores que están repetidos (independientemente del nº de veces), se puede conseguir directamente utilizando una de las opciones de Formato Condicional (después de seleccionar el rango donde suponemos que habrá valores repetidos):

Ficha Inicio / Formato condicional / Resaltar reglas de celdas / duplicar valores

Detectar un valor repetido x veces en una columna (forma avanzada)

Vamos a aprender a conseguir lo mismo que antes, pero de una forma algo más avanzada, lo que nos permitirá tener más control en algunos casos. Este mayor control consiste en que, no sólo podremos saber  los valores que están repetidos más de una vez, sino que podemos discriminar aquellos que están repetidos más de x veces sólamente.... o bien aquellos que están repetidos exactamente x veces.

En primer lugar, debemos seleccionar el rango de celdas al cual queremos aplicar la detección de diferencias. Elegimos, por ejemplo, la columna 2 (el rango C3:C9) porque queremos saber si en esa columna ha hay algún valor repetido más de una vez. ¡¡Atención, se debe seleccionar con el ratón de arriba hacia abajo, para que quede activa la celda de arriba y después funcione el formato condicional!!

Vamos a la ficha Inicio / Formato condicional.../Nueva regla... / Utilice una fórmula que determine... y escribimos en el campo "Dar formato a los valores donde esta fórmula sea verdadera" la siguiente fórmula: 

El siguiente paso es dar el formato deseado desde el botón que encontraremos más abajo y presionamos "Aceptar". El resultado será similar al siguiente:

El motivo de utilizar la función =CONTAR.SI ($C$3:$C$9;C3)>1 es que conseguimos que el formato condicional reconozca en cada celda si el valor contenido en ella está repetido más de una vez (por ello se escribe > 1 al final) en todo el rango C3:C9.

Vemos que el rango C3:C9 se escribe con forma de referencia absoluta: $C$3:$C$9;C3 , sin embargo, la referencia C3 NO se debe escribir entre "signos dólar". El motivo de que la celda C3 deba ser una referencia relativa es posibilitar al formato condicional que se aplique a todo el rango (el formato condicional, aunque no lo hayamos puesto, va leyendo celda a celda aumentando un nº de fila: C3, C4, C5, C6...etc.).

Es muy fácil intuir que si queremos detectar sólo los valores que están repetidos más de dos veces, deberíamos cambiar >1  por  >2 ..... y las demás posibilidades son fáciles de imaginar. Por ejemplo, podríamos marcar aquellos valores que estén repetidos exactamente 3 veces, tan sólo cambiando > por =3.

Si desea llevar a la práctica todo lo que le hemos ofrecido en este artículo, puede asistir a nuestros cursos prácticos de Excel o puede solicitarlos in company:

Detectar diferencias entre 2 columnas en Excel

Es muy frecuente que los usuarios de Excel necesiten detectar diferencias entre dos columnas o tablas de Excel. Esto se puede hacer de varias formas, pero en este caso, vamos a hacer que la detección se haga mediante "formato condicional" y que las diferencias encontradas en una columna B respecto de una columna A se marquen con un color de fondo distinto.

Nos podemos encontrar con varios casos o varias necesidades distintas:

Detectar diferencias entre pares de celdas de una misma fila

En primer lugar, debemos seleccionar el rango de celdas al cual queremos aplicar la detección de diferencias. Es decir, seleccionamos el rango C3:C9 porque queremos saber si en esa columna ha cambiado algún valor comparado con su pareja en la columna 1. ¡¡Atención, se debe seleccionar con el ratón de arriba hacia abajo, para que quede activa la celda de arriba y después funcione el formato condicional!!

Vamos a la ficha Inicio / Formato condicional.../Nueva regla... / Utilice una fórmula que determine... y escribimos en el campo "Dar formato a los valores donde esta fórmula sea verdadera" la siguiente fórmula:  

El siguiente paso es dar el formato deseado desde el botón que encontraremos más abajo y presionamos "Aceptar". El resultado será similar al siguiente:

...viendo que han quedado resaltadas las celdas de la columna 2 que suponen una diferencia respecto de sus parejas en la columna 1.

Detectar diferencias en cualquier parte de las columnas

En al caso anterior, sólo se detectaban diferencias entre parejas de celdas. Aunque en la columna 2 hubiesen los mismos valores que en la columna 1, tan sólo con cambiarlos de orden, ya se detectaban diferencias.

Ahora necesitamos detectar si en la columna 2 aparece algún valor que no esta en la columna 1, no importándonos la coincidencia de orden de los demas valores. Dicho sobre un ejemplo, en la siguiente tabla, necesitamos que aparezcan resaltados los valores "S" y "P" de la columna  2, puesto que no existían en la columna 1. Los demás valores de la columna 2, aunque en distinto orden, sí existían en la columna 1:

Para conseguir que los valores "S" y "P" de la columna  2 queden resaltados, debemos seleccionar los datos de la columna 2 de la misma forma que lo hicimos en el otro ejercicio y vamos al mismo cuadro de diálogo. Posteriormente, la fórmula a introducir sería:

...y el resultado...

Explicación para la función BUSCARV:

El primer argumento de la función BUSCARV es la primera celda de la columna 2 donde se van a resaltar las diferencias (¡¡NO debe ser una referencia absoluta. Es decir, no debe tener los sígnos de dólar!!). El segundo argumento es el rango de celdas donde se buscarán las diferencias en la columna 1. El tercer argumento es un 1 porque necesitamos que se busque en la única columna del rango (la B). El cuarto argumento indica que se buscará una coincidencia exacta aunque no esté la columna B ordenada.

Explicación para la función SI.ERROR:

El primer argumento de la función SI:ERROR es la propia función BUSCARV. Esto es, SI:ERROR evaluará lo que se obtenga con BUSCARV. El segundo argumento de la función SI:ERROR es un valor lógico (VERDADERO = 1) que obtendremos si BUSCARV da un error (si NO encuentra lo que busca y por lo tanto hay una diferencia).

Si desea llevar a la práctica todo lo que le hemos ofrecido en este artículo, puede asistir a nuestros cursos prácticos de Excel o puede solicitarlos in company:

Extraer una parte del texto de una celda, con VBA

Hace unos días recibí un e-mail de una lectora del Blog, preguntando cómo podría conseguir mediante código VBA (una macro) extraer una parte del texto de un rango de celdas en una columna. En su mensaje, me proponía un código que ya estaba utilizando, pero quería perfeccionarlo para tener una mayor utilidad.

El código aportado por mi lectora era:

Sub Extraer()

Final = Range("A65536").End(xlUp).Row

For x = 2 To Final

Cells(x, 2) = Mid(Cells(x, 1), 5, 4)

Next

Lo que hacía cada una de las líneas era:

1. Determinar a cuantas celdas se le debe aplicar la "Extracción"  (2ª línea del código)
2. Iniciar un bucle con un número de repeticiones igual al número de celdas hallado en el punto anterior, asignando en la primera vuelta del bucle, el valor x = 2
3. Insertar en la celda B2 un texto que se extraerá del texto de la celda A2 (Cells (x,2), empezando por el 5º caracter (contando desde la izquierda)  y tomando para la extracción los siguientes 4 caracteres.
4. Comienza otra vuelta de bucle en el que inserta en la celda B3 un texto que se extraerá del texto de la celda A3, empezando por el 5º caracter (contando desde la izquierda)  y tomando para la extracción los siguientes 4 caracteres.
5. ...y así sucesivamente...

En definitiva, que del texto  FRANCISCO-01   extraería la cadena de texto   CISC

Pero mi lectora quería algo diferente. Lo que pretendía era tener la posibilidad de "extraer" un cierto número de caracteres contando desde la derecha. Ese "cierto número de caracteres" debería ser elegido previamente por el usuario, mediante un cuadro de diálogo que se obtendría en pantalla al presionar un botón (por ejemplo). 

Pues bien, le ofrecí la siguiente solución:

Sub RecortarDerecha()
NCaracteres = InputBox(Chr(13) & Chr(13) & Chr(13) & Chr(13) & "Introduce el número de caracteres a recortar", "Nº caracteres a recortar", "Nº caracteres a recortar", 1000, 1000)
Final = Range("A65536").End(xlUp).Row
For x = 2 To Final
Cells(x, 2) = Right(Cells(x, 1), NCaracteres)
Next
End Sub

Respetando parte del código que teníamos en la propuesta inicial, una de las soluciones posibles se trata de:

1. Introducir la variable NCaracteres, que representaría a lo introducido por el usuario en un "Inputbox" (2ª línea del código). Podemos recordar cómo se introduce un cuadro de introducción de datos o "Inputbox" en el siguiente artículo: "Introducción de datos en Excel con un cuadro de diálogo"
2. Cambiar la función MID por RIGHT  (recordemos que ahora se pretende extraer desde el primer caracter de la derecha siempre).
3. Cambiar los dos últimos argumentos que tenía la función MID, por un sólo argumento: el número de caracteres que queremos recortar desde el primer caracter de la derecha (NCaracteres).

Podemos comprobar el funcionamiento de todo ello, descargando el siguiente archivo Excel:

Si desea llevar a la práctica todo lo que le hemos ofrecido en este artículo, puede asistir a nuestros cursos prácticos de Excel o puede solicitarlos in company:

Buenas prácticas: Estructura y tipos de hojas en Excel

Nos disponemos a diseñar desde el principio una hoja de cálculo. Hacemos doble click en el icono de Microsoft Excel. Aparecen en pantalla las tres hojas vacías que por defecto proporciona la aplicación. Tenemos todo un mundo por delante. Todo por hacer. Hay que poner los cimientos.

Es imprescindible que no vayamos improvisando la forma de estructurar nuestro libro de Excel (la cantidad, orden y función de cada una de las hojas), sino que debemos ceñirnos en alguna medida, a una estructura óptima que está probada y que nos beneficiará a largo plazo en nuestro trabajo con Excel.

Es decir, con una buena estructura de hojas en nuestro libro, conseguiremos...

• Que nuestro libro de Excel sea escalable sin necesidad de copiar y pegar hojas para añadir datos del próximo mes, año, etc.
• Que no sea necesario editar y modificar fórmulas durante la utilización de nuestro libro de Excel. Una vez terminado el diseño de nuestro libro nunca se debe editar o modificar una fórmula.
• Que otros usuarios puedan utilizar nuestro libro de Excel de forma intuitiva y rápida. Es decir, no perdiéndose en una maraña de hojas sin orden ni objetivo claro.
• Que nosotros mismos, como desarrolladores del libro de Excel, podamos mantenerlo o ampliarlo  en un futuro en caso de ser necesario. Si no hay una estructura clara, no seremos capaces de entender nuestro propio trabajo.
• Que nuestro libro de Excel sea seguro y exento de errores y sorpresas
• Que nuestro libro de Excel aporte el máximo valor con los mínimos recursos

Basándonos en los consejos del apartado BPMS 1-3 Sheet content, del libro Best Practice Spreadsheet Modelling Standards & Conventions (Version 6.1) de la Spreadsheet Standards Review Board (SSRB), pasamos a orientar al lector de cuál sería la estructura y los tipos de hojas que deben estar presentes en cualquier libro de Excel que pretenda gestionar datos, información y conocimiento de forma eficiente.

Tipos de hojas en una aplicación basada en Excel

1. HOJA DE CONFIGURACIONES, TABLAS AUXILIARES Y TABLAS DE BÚSQUEDA

Llamadas "Lookup Sheets" por la SSBR, pueden ser hojas que contienen:

• Datos y supuestos que se asumen de partida y no son modificables por el usuario ("Non-Assumptions"). Por ejemplo, una serie de meses que será utilizada como títulos para la realización de gráficos posteriormente o como origen de datos para otras hojas.
• Datos y supuestos que pueden ser modificados ("Assumptions") . Por ejemplo, un dato que debe ser posible modificar por el usuario sería el beneficio exigido en un Plan de Viabilidad Económico-Financiero.

2. HOJA DE PORTADA

Como su nombre indica, debemos dotar al libro de una hoja al principio en la que se exprese:

• El nombre de la aplicación
• Notas sobre la aplicación:
• Descripción del contenido de la aplicación
• Instrucciones generales de uso: ¿por dónde empezar?
• Advertencias y peligros de uso

3. MENÚ GENERAL, TABLA DE CONTENIDOS O ÍNDICE

Debe haber una hoja que facilite al usuario ir adonde lo necesita. La hoja debe contener botones o bien enlaces mediante hipervínculos y estos deben contener iconos o texto descriptivo en cada opción.

4. PORTADA DE SECCIÓN O SUBMENÚ

Si bien decíamos que es conveniente la existencia de un menú general, éste no debe contener demasiados items, sino que es conveniente estén presentes los más generales. Cada uno de ellos, dirigirá al usuario a otros SUBMENÚS o  PORTADAS DE SECCIÓN.

5. HOJA CON UN ESQUEMA VISUAL DEL MODELO

Siendo fieles a las recomendaciones de la SSBR, es conveniente tener una hoja donde de forma esquemática y visual podamos, desde un principio, ver cuál es el flujo de datos e información a lo largo de las distintas hojas. Esto ayudará al diseñador de la aplicación, pero también se puede poner a disposición del usuario mediante algún botón o enlace.

6. HOJAS EN BLANCO

La SSBR, concibe que los libros de Excel pueden imprimirse de forma íntegra posteriormente (algo que, sinceramente, pienso que es muy dudoso) y que cada hoja tendrá una salida por impresora en determinado orden. Por ello, y al modo de algunos libros que dejan hojas en blanco entre secciones o capítulos, tiene justificación esta recomendación. El lector decidirá si esto es conveniente para su libro de Excel.

7. HOJAS DE INTRODUCCIÓN O IMPORTACIÓN DE DATOS

Llamadas "Time Series Assumptions sheets" por la SSBR, son las hojas donde recibimos los datos de un ERP u otro programa (en el caso de que los datos ya estén generados y sean importados), o bien las hojas donde vamos introduciendo los datos (en al caso de que se generen día a día)

8. HOJAS DE ANÁLISIS E INFORMES

Llamadas "Time Series Output Sheets" por la SSBR, son las hojas que combinan los datos de las "Time Series Assumptions sheets" para ofrecerlos de forma comprensible al usuario como informes para ver o imprimir, pero NO para introducir o importar ningún dato.

9. HOJAS DE GRÁFICOS O CUADROS DE MANDO

Llamadas "Chart Sheets" por la SSBR, son hojas que transmiten conocimiento e información de alto valor mediante gráficos y cuadros de mando intuitivos. Están orientadas a la toma de decisiones y a personal que no necesariamente tiene conocimientos especializados, por lo tanto deben tener la cualidad de transmitir bien lo que se quiere expresar. En este tipo de hojas, el usuario nunca debe tener la posibilidad de introducir datos, aunque SI deberían ser hojas interactivas mediante controles especiales (botones de opción, listas desplegables, cuadros de lista, etc.)

Si ordenamos todo esto en una pirámide que exprese el flujo de información de forma correcta, podría resultar una imagen como la mostrada al principio de este artículo.

Si desea llevar a la práctica todo lo que le hemos ofrecido en este artículo, puede asistir a nuestros cursos prácticos de Excel o puede solicitarlos in company: