Hay una realidad que no podemos negar, los cuadros de mando están de moda.
Llámense Dashboard, cuadros de mando, paneles de control, como quieras, pero esos paneles en los que puedas visualizar y analizar los principales datos de tu negocio, de tu proyecto, de tu informe, de forma totalmente gráfica y que te ayude a tomar decisiones.
Y si, están de moda, porque cada día estamos más saturados de información, por todos lados, por ello debemos de tratar de sintetizarla, de mostrarla lo más visual y atractiva posible, que nos permita analizarla fácilmente y por lo tanto podamos tomar acción y decisión sobre ellas.
Y podrás decirme, que “esto está muy bien Miguel”, pero ¿cómo diseño un dashboard en Excel desde cero? Y además que este sea lo más automático y analítico posible…
Bueno pues vamos a por ello, lo vamos a hacer sobre una cuenta de resultados, para verlo muy gráficamente, pero la base seríaa la misma para cualquier tipo de información origen que tengas en Excel.
Vídeo: cómo crear un cuadro de mando financiero en Excel
Tienes a continuación un videotutorial sobre cómo crear un cuadro de mando en Excel con la cuenta de resultados explicado con toto detalle, pero igualmente te lo dejo a más abajo en texto con capturas de pantalla.
Paso a paso para aprender a crear un cuadro de mando con la cuenta de explotación de una empresa
Muy brevemente decirte que una cuenta de resultados no es más que una suma y una resta de los ingresos y los gastos de un negocio o actividad económica, así descrito muy sencillo, algo así…
No tienes por qué entender este cuadro como tal, simplemente saber que están informados como ves en verde los datos de ingresos, de gastos y una fila naranja al final con lo que ha ganado la empresa por mes.
Bien esta cuenta de resultados, que es relativamente sencilla, podríamos intentar analizarla simplemente con esta tabla, que además ya está formulada de unas hojas de gastos e ingresos, que ahora no vienen al caso.
Pero la realidad es que, aunque podríamos analizarla y tomar decisiones, sería un trabajo más denso, no tendríamos la información visual en ningún momento y por lo tanto nos llevaría un tiempo importante sacar conclusiones claras y sencillas.
Para ello vamos a diseñar un dashboard, un cuadro de mando que nos de la información de los principales indicadores a tener en cuenta.
Esto dependerá del tipo de informe que quieras plasmar lógicamente, pero los pasos que puedes seguir, la base, es la misma.
Paso 1: Selector de mes del cuadro de mando en Excel
Bueno primero vamos a ver qué es lo que ha pasado en el mes que estamos, supongamos que es junio, lo primero es crear en una hoja nueva una lista de validación para poder seleccionar el mes y que, en base a este, nos muestra la información y cambie según nuestra selección.
Por lo que de momento da igual donde lo podamos colocar, dentro de la nueva hoja, lo colocamos por el medio de la misma, a través de Datos > Crear listas de validación de datos, como ves en la imagen.
Ya nos preocuparemos después de mover la lista si nos es necesario.
Simplemente deberemos cambiar el check de “Permitir” > “Cualquier valor” por “lista.
Ahora haremos referencia a la hoja de nuestra cuenta de resultados y seleccionaremos la fila de cabecera para poder seleccionar los meses, fácil y sencillo.
Ahora como comprobaras en la celda que tenías activa tienes un desplegable para poder seleccionar el mes.
Un tema muy fácil, pero super importante para hacer que nuestro dashboard cobre vida.
Paso 2: cambiar el valor de una celda en Excel en base a otra celda
Bien ahora vamos a importar nuestros principales datos, que serán los ingresos, el total de gastos y el resultado.
Por ello los situamos cerca de la celda donde hemos metido la selección del mes, para hacer un cuadro con los principales indicadores en función del mes seleccionado.
Como ves los he puesto uno debajo de otro con un espacio y dado formato, cuestión de gustos lógicamente, esta parte del formato te la dejo a tu elección.
Y como ves he metido dos celdas de cabecera, una con el nombre del mes y con el acumulado del año.
El dato del mes simplemente es una referencia a la celda de selección superior.
Pues bien, manos a la obra, vamos a traernos los datos de la cuenta de resultado, para ello vamos a usar dos fórmulas juntas que para mí son un coctel super potente a la hora de búsqueda de datos, BuscarV y Coincidir.
Lo que vamos a decir para los ingresos del mes, es que nos busque la celda de ingresos, dentro del rango donde tengamos los ingresos en la cuenta de resultados, y como indicador de columna, al ser variable el mes, meteremos el coincidir para que nos busque el mes en la cabecera de la cuenta de resultados.
La fórmula quedaría de esta forma:
Y como ves el resultado es que ya tenemos los ingresos para junio, y si pruebas a cambiar el mes los ingresos cambian, así de sencillo, así de útil y así casi casi de mágico.
Venga pues replicamos lo mismo para el total de gastos y resultado. Aquí como son dos indicadores más, sinceramente es igual hacer la fórmula de nuevo que es uy sencilla o estar jugando con las referencias absolutas y relativas para poder copiar la misma… no ganaras mucho tiempo, pero si ten claro que este trabajo solo lo hacemos ahora, después cuando tengas que actualizar o analizar los datos ya tendrás el dashboard montado y solo tendrás que analizar.
Con nuestro cuadro de indicadores principales en este estado vamos a atacar los datos del acumulado de año.
Bueno pues para obtener los datos del acumulado del año, y que por lo tanto para cualquier mes que seleccionemos nos dé el dato del valor acumulado del año hasta ese mes lo vamos a realizar con una función super útil como es SUMAPRODUCTO.
Para ello simplemente insertaremos en la fila del acumulado para los ingresos la función de la siguiente forma:
Donde como ves lo que hacemos es multiplicar la fila de los valores de los ingresos mensuales, por la superior de la hoja, donde tengo simplemente 1, 0.
Estos unos y ceros, lo que indican es si los meses son anteriores a la selección con un 1 o posterior con un cero.
Por ello al multiplicar cada celda correspondiente del mes con la superior y acumular el resultado cuando multiplique por cero esta será cero… una formula brutal para hacer acumulados dinámicos.
Venga ya tenemos nuestro cuadro completo
Ya podemos revisar los principales indicadores según el mes que seleccionemos y el acumulado correspondiente.
Paso 3: crear gráficos financieros en excel
Ahora podemos pasar a la parte gráfica, vamos a dar un poco de vida a los datos.
Vamos a colocar dos gráficos en primer lugar, el primero a la izquierda de esta tabla con los valores del mes en cuestión y el segundo a la derecha con los valores del acumulado del año.
Crearemos el primero, un gráfico de barras normal, y lo editaremos desde cero.
Para ello agregaremos tres series, una por cada dato de ingresos, gastos y resultado, donde indicaremos el valor correspondiente, esto muy fácil, quedando el grafico así, con nuestros datos al lado:
Como ves de momento es simple, ya le terminaremos de dar formato.
Ahora vamos a por el otro gráfico, de la misma forma que el anterior y lo colocaremos a la derecha de los datos, quedando así, misterio ninguno ehhh:
Venga vamos a dar un poco de formato ahora a los gráficos.
Esta parte es sensible, no por complejidad sino porque podemos pasarnos un tiempo muy valioso, siendo detallista con ellos y excedernos en el diseño hasta el punto de pasarnos con ello.
Yo soy muy partidario de los diseños predefinidos que vienen, que 365 son brutales, y darles un toque de diseño que los hagan más impactantes o sintamos que son nuestros gráficos.
Para ello vamos a seleccionar el que más nos guste de los diseños que vienen predefinidos, yo me quedo con este
A este grafico le doy algo de formato, le aumento las etiquetas de tamaño, le pongo un borde sombreado y le redondeo las esquinas, por darle un toque y que sea más resultón, quedando así
Por cierto, ya nos hemos quitado la cuadricula del fondo de nuestra hoja para que este más limpia y de esa sensación de dashboard.
Sobre los colores, ya sabes decide la gama, o si quieres que sea monocromática, colores corporativos… tú decides.
El grafico del acumulado lo haremos de la misma forma, si cambiaremos la gama de colores y le pondremos un par de títulos.
Eh esto ya va cogiendo tono, mira cómo vamos
Ahora prueba a cambiar el mes, para mí esto es pura magia, sobre todo de cara a actualizar los datos en periodos posteriores.
Bien ahora vamos a crear dos gráficos más por debajo, para analizar los ingresos y resultado, y en otro los tipos de gasto.
Simplemente crearemos unos gráficos, de barras o líneas, muy simples, vamos a ir a por lo simple siempre que se entienda y nos facilite el trabajo ya habrá tiempo de complicarlo.
En los que meteremos los meses de todo el año de ingresos y resultados, por un lado, y de los principales gastos por otro lado.
Quedando de la siguiente forma:
Como ves hemos usado un gráfico de barras, por seguir la misma línea, no te aconsejo utilizar gráficos muy dispares porque tengan la misma sintonía, pero hay que valorar muchos aspectos para ello.
A estos le hemos dado un diseño un poco más de impacto, sin usar el típico gráfico de fondo blanco, que ojo sigue siendo de los más efectivos, pero estos no dejaran indiferente a nadie.
Como ves estos gráficos no son dinámicos, simplemente irán mostrando datos que vayas alimentando en las hojas de datos, pero será muy bueno poder tener la evolución mensual de forma gráfica para analizar los resultados.
Ya solo con todo esto tendríamos un dashboard sencillo pero efectivo para analizar nuestros principales indicadores.
De regalo y como deberes te muestro que otros dos gráficos podríamos hacer para completar el dashboard, uno con los principales clientes y otro con la deuda pendiente que tenemos, a través de una tabla dinámica con segmentación de datos, ¿te atreves a desarrollarlo?
Te recomiendo que eches un ojo al video completo, este tipo de contenido se ve mucho más practico sin duda.
Detrás de este post…
Detrás e este este post está Miguel Antúnez. Un profesional del Excel como la copa de un pino. Experto en finanzas ya que ha dedicado más de 15 años de su vida a esta práctica. En su página web puedes encontrar valiosísimos trucos de Excel para todos los niveles y no sólo del área de finanzas.
Un última recomendación de miguel
Y te recomiendo, o te reto a que visites mi academia online, donde tengo cursos que no solo te van a ayudar a desatar tu potencial con Excel sino a ser un maestro en el diseño de Dashboard.
Más de 12 cursos disponibles (entre ellos un curso de diseño de dashboards completo), cursos nuevos todos los meses y mi soporte para todas tus dudas.
En septiembre salen 3 cursos completos muy potentes, un curso de Google Sheets, curso de Google Data Studio y Curso de Power BI.
Además, tenemos un grupo de Telegram privado solo para suscriptores, webinars en directo todos los meses, plantillas y recursos premium y te desarrollo un plan formativo a medida ajustado a tus objetivos.
Si no te lo quieres perder, suscríbete hasta el 31 de julio por 9€ al mes u 80€ por un año completo, ya que después los precios van a subir a 18€/mes y 180/año, por la cantidad de contenido y recursos que tenemos tendreo de la academia esperándote…
Aprovecha la oportunidad y date de alta a los precios vigentes para mantenerlos antes del 31 de julio, sin compromisos ni permanencia, pero te aseguro que voy a desatar todo tu potencial con Excel.
No te lo pierdas, aprende Excel como un experto desde ya