Anuncios

sábado, 28 de noviembre de 2020

📊📈 Como HACER un DASHBOARD en Excel AVANZADO desde CERO y PROFESIONAL- [ PASO a PASO ]

Sabes como hacer un dashboard en excel avanzado desde cero, sencillo y profesional mostrando el paso a paso para hacer un informe en excel con gráficos, KPI e Indicadores dinámicos e interactivos. El ejercicio es crear un informe dashboard financiero donde se contempla la medición de ingresos, costos y gastos con tablas dinámicas y gráficos avanzados.


¿Que es un DASHBOARD en Excel y PARA QUE SIRVE?

Los dashboard los podemos conocer como informes dinámicos, informes interactivos informes gerenciales donde a través de unos gráficos avanzados traer una información y representarlos en los gráficos interactivos para poder analizar la información, para poder revisar y medir todas esas metas que queremos y podemos también traer esos datos que tenemos en nuestro negocio, de su trabajo.


Un dashboard se puede dominar también como un informe de cuadro de mandos, es un informe dinámico entre otros y es una representación gráfica de los principales indicadores que se desean medir y gestar, viene en la consecución de los objetivos que se ha planteado y adicionalmente está orientada a la forma de decisiones para optimizar la estrategia empresarial o personas.


¿COMO HACER PASO A PASO UN DASHBOARD EN EXCEL?


Para construir un dashboard en excel es muy importante primero que todo entender la base de datos que tenemos y que queremos modelar y adicionalmente debemos saber cuales son los indicadores mas importantes que queremos monitorear y que nos arrojará resultados de valor porque son indicadores muy importantes de la base de datos que nos permitirá con el informe tomar decisiones para el mejoramiento continuo.


para realizar este ejercicio nos vamos apoyar de las tablas dinámica, también de diferentes tipos de gráficos en excel, a medida que vamos avanzando les voy dejando un enlace para que puedas profundizar para realizar el dashboard. Aunque se considera que ya se cuenta con estos previos conocimientos.


la base de datos que tenemos tiene la siguiente información.


Tenemos un auxiliar contable de enero a diciembre, tenemos la cuenta,  el nombre de tipo cuenta la clasificación es Ingresos, Costos y Gastos, en la columna negocio, tenemos 4 sucursales, el valor neto tenemos el valor de cada movimiento.

En la siguiente imagen se ve una muestra de lo anterior.



El siguiente paso es seleccionar toda la base de datos y convertirla en tabla presionando ctrl + t y le asigna el nombre a la tabla.



Es importante convertir la información en tabla porque cuando la base de datos se va ir alimentando de mas información entonces cuando se actualiza el informe va tomar la información de una tabla y no de un rango.


El siguiente paso vamos a crear una nueva hoja donde vamos a parametrizar nuestro Dashboard



Ahora vamos a realizar una TABLA DINAMICA en la celda A1 donde vamos a resumir el total de ingresos. Te dejo el siguiente ENLACE para que profundices sobre esta herramienta.

En nombre tipo de cuenta filtramos por ingresos



Este sería el resultado.


Ahora replicamos lo mismo, insertamos dos tablas dinámicas para obtener el total de costos y de los gastos, recuerde que el nombre tipo de cuenta se filtra por el criterio, ingreso, costos o gasto dependiendo de la necesidad


Ahora vamos a crear una relacion de ingresos, costos y gastos donde el valor de ingreso vamos a decir que es igual a la celda A4, y así con cada variable y para la utilidad operativa va ser igual a los ingresos - costos - gastos



Ahora despues de asociar la columna B valores a cada uno de los datos de cada tabla dinamica en la columna C debemos de calcular el porcentaje de participación con respecto al ingreso. Entonces en la celda C9  sería la celda B9/$B$8 y ahora arrastramos hacia abajo.


En la columna D tenemos otra variable de 1 menos el % de participación donde sería 1 - 72% el resultado sería 28% y arrastramos hacia abajo


Ahora creamos una nueva hoja donde vamos a crear el informe



Ahora vamos a darle un poco de personalización a la hoja de informe  donde insertamos imágenes de representación al informe, puede ser un logo empresarial.


También seleccionamos las filas 5 hasta las 10 y le pintamos relleno de color verde, aquí puedes pintar del color que deseas o que haga representación a la empresa o al gusto que desees


Ahora creamos una segmentación de datos en tablas dinámicas del criterio MES y le cambiamos la opción de diseño donde serán 2 columnas. Te dejo el siguiente ENLACE donde esta todo el curso completo de TABLAS DINÁMICAS.


El siguiente paso vamos a personalizar nuestra segmentación de datos y como pueden ver excel nos brinda diferentes tipos de formatos y diseño pero tambien lo podemos personalizar presionando clic nuevo estilo de segmentación de datos.


Nos abre la ventana de modificar estilo de segmentación de datos y en esta ventana podemos personalizar diferente elementos de segmentación de datos, podemos personalizar encabezado, elementos seleccionados y no seleccionados con datos, sin datos, ahí podemos darle clic al formato y colocar el formato que deseamos.


El diseño va ser acorde a la tematica de color verde

Ahora creamos nuevamente otra segmentación de datos con el criterio de negocio donde vamos a tener las sucursales


Personalizamos nuevamente la segmentación de datos.



Ahora ubicamos cada segmentación en la hoja del informe.


En el siguiente video se explica lo anterior


Ahora vamos a realizar una TARJETA DINAMICA, te relaciono el siguiente ENLACE para que refuerces este concepto.


Ahora vamos a relacionar la tarjeta con el ingreso, la celda es en la hoja dashboard B8. =Dashboard!B8


Copiamos la tarjeta dinamica y la replicamos tres veces mas para poder asociarlo al costos, gastos y utiliza


Asociamos los valores para cada tarjeta, para el costo =Dashboard!B9, para el gasto =Dashboard!B10, para la utilidad total =Dashboard!B11


El siguiente paso vamos a crear tres tablas dinamicas donde vamos a obtener el ingreso, costo y gastos pero por mes.


Se recomienda cada tabla dinámica asignar un nombre como se muestra en la imagen que la primera tabla dinámica la llamaremos Ingreso_SS donde nos permitirá identificarla para que las segmentaciones que realizamos anteriores nos nos filtre estas gráficas porque como se muestra en la imagen la tenemos aplicando los filtros donde solo nos muestra enero.


Teniendo en cuenta lo anterior se nombra también la tabla dinámica para gastos y costos.


Ahora nos ubicamos a la primera segmentación de datos donde son los meses y nos vamos para la pestaña de opciones presionamos clic en conexión de informes


y se nos abre la ventana de conexión de informe y nos muestra todas la tablas dinámicas que están asociadas a la segmentación de datos y des habilitamos las tablas dinámicas que creamos con los meses y le asignamos un nombre terminado _SS sin segmentación y creamos anteriormente como se muestra en la imagen



Al quitar la segmentación ahora las tablas dinámicas nos muestra todos los meses


Ahora vamos a insertar un gráfico de LINEAS, te comparto el siguiente ENLACE para que profundices sobre todos los gráficos predeterminados y como PERSONALIZARLOS en excel.


el gráfico lo creamos con los datos de los ingresos de cada mes de la tabla dinamica.


Personalizamos el gráfico, donde le quitamos el fondo por transparente, el tamaño y lo ubicamos dentro de la tarjeta correspondiente al ingreso como se muestra en la imagen.



Ahora debemos de hacer lo mismo para los costos y gastos y lo ubicamos dentro de cada tarjeta correspondiente y teniendo en cuenta los datos de las tablas dinámicas correspondiente.



Ahora debemos de crear una columna para la utilidad donde va ser la diferencia entre el ingreso - costos y - gastos por cada mes.


Ahora creamos el gráfico de lineas y lo personalizamos para la tarjeta de utilidad total como se muestra en la imagen.


Hasta el momento tenemos la siguiente forma al informe.



En el siguiente video se explica lo anterior


El siguiente paso vamos a realizar un gráfico de barras con una meta establecida y para eso en la hoja dashboard vamos a crear las metas para la sucursal 1, 2, 3 y 4 con su respectiva meta como se muestra en la imagen. Se debe tener en cuenta que en la celda A39 vamos a colocar el texto de (Todas) y en la celda B39 la sumatoria de todas las metas, esto es muy importante porque cuando insertemos una tabla dinamicas cuando tenemos todas seleccionadas la tabla colocará la palabra de (Todas)


Ahora insertamos una tabla dinámica en la celda E35 donde vamos a desplazar la variable  negocio al campo de filtro


Como se muestra en la imagen tenemos seleccionado todas las sucursales y la tabla dinámica lo muestra como (Todas)




Ahora en la columna G vamos a colocar todos los meses y en la columna H vamos a colocar la meta buscada, entonces en la celda H35 utilizamos la función =BUSCARV($E$35;$A$35:$B$39;2;0)



El siguiente paso es crear un GRÁFICO COMBINADO te relaciono el siguiente ENLACE si deseas profundizar sobre este concepto.


Para crear el gráfico combinado tenemos nuestra primer variable que son los ingresos y la segunda variable son las metas que creamos de enero a diciembre y los gráficos combinados son de columnas y de lineas.


Luego personalizamos el grafico que sea acorde a los colores que deseamos aplicar y lo copiamos y pegamos en nuestra hoja de informe


En el siguiente video se explica lo anterior.

Ahora el siguiente paso vamos a crear los GRAFICOS DE PROGRESO tipo anillo para los costos, gastos y utilidad donde se utilizan los porcentajes de participación y la diferencia con menos 1. Te comparto el siguiente ENLACE para que profundices sobre estos gráficos


El gráfico tipo anillo con progreso lo personalizamos y le colocamos como titulo de margen del costo como se muestra en la imagen. Se debe tener en cuenta que la forma de circulo tiene el 72%  porque estamos amarrando con la participación del costo con respecto al ingreso


Debemos replicar lo mismo para el gasto y la utilidad operativa y lo ubicamos en nuestro informe

En el siguiente video se explica lo anterior


Ahora vamos a realizar un GRÁFICO VELOCÍMETRO y para ello vamos a establecer las metas.


Las metas serán para negativo es del 0 al 15%, aceptable del 15% al 20% y para positivo es del 20% hasta 30%. Recuerda que en un columna tenemos la diferencia y en la otra el acumulado y al final debemos de sumar el % que da 30% 


Ahora vamos a crear en la celda B58 vamos a traer el valor de la utilidad C11


Ahora vamos crear los datos para el puntero y recordemos que para crear el gráfico velocímetro es un gráfico combinado con un gráfico de lineas de regresión y para la variable x la función sería =SI(B58>B54;-COS(PI()*B54/B54);-COS(PI()*B58/B54))



para la variable Y la función sería =SI(B58>B54;SENO(PI()*B54/B54);SENO(PI()*B58/B54))


Ahora después de crear las variables podemos hacer el gráfico de velocímetro y lo personalizamos.


Los rangos es el porcentaje % y también las variables X y Y


Copiamos y pegamos el grafico en nuestro informe y así queda nuestro dashboard listo el cual queda interactivo por mes y por sucursal.


En el siguiente video se explica lo anterior


En el siguiente ENLACE puedes DESCARGAR la base de datos para que puedas practicar y realizar un DASHBOARD.


Seguramente quieres sabes más acerca de Excel, no te olvides darte una vuelta por las siguientes secciones disponibles para ti de manera gratuita


Canal de YouTube
Comunidad Telegram
Instagram: Aprende y Enseña Excel
Facebook: Aprende Excel Fácil
Twitter: Aprende y Enseña Excel
Hasta una próxima y te deseo el mayor de los EXITOS.


Aprende y Enseña Excel