23 sept 20224 Min. de lectura

Cálculo de presupuesto contra lo real

Si estamos haciendo el curso de Excel básico hasta avanzado, en el módulo 97, comparamos el presupuesto con las ventas reales.

Si estamos cursando el curso de Excel intermedio, es el módulo 41.

Pues, vamos a realizar lo mismo, pero lo haremos en Power Pivot, en los módulos anteriores, lo hicimos con fórmulas, ahora, lo haremos con un modelo de datos.

Tenemos las dos tablas de los videos anteriores, una primera a nivel de mes, y, una segunda a nivel de producto.

Necesitamos comparar el producto mensual del modelo 1, con cada línea de fecha individual para un mismo producto del modelo1.

Tenemos otra tabla con el producto, y, su precio.

Y otra tabla con le fecha, el numero de mes, nombre del mes, año, y, final de mes.

Vamos a usar un esquema estrella, tenemos que trabajar con fecha, y, producto, cuando seleccione un producto, o, un mes, quiero que filtre tanto la primera tabla, como la segunda.

Voy a traer todas las tablas a Power Pivot, para ello, con una celda dentro del primer modelo, vamos a la pestaña de Power Pivot, dentro del grupo tabla, hacemos clic en agregar a modelo de datos.

Ya tenemos el primer modelo en Power Pivot.

Seguimos los pasos anteriores, y, nos traemos los demás modelos.

Desde la pestaña de inicio, vamos al grupo ver, y, hacemos clic en vista de diagrama, para poder crear las relaciones.

Podemos ver las tablas.

La primera relación que vamos a crear es desde fecha de la tabla segundo modelo con fecha de la tabla fechas, donde se creara una relación de uno a varios, siendo el lado uno, la columna fecha de la tabla fechas, donde las fechas son únicas, y, el lado varios, es la columna fecha de la tabla segundo modelo, donde si se pueden repetir las fechas.

Vemos que tenemos la tabla producto, donde los productos son únicos, pues, vamos a relacionar la columna de producto de la tabla segundo modelo, con la columna producto de la tabla producto.

Ahora, cuando seleccionemos un mes, y, un producto, se filtrará la tabla segundo modelo.

La siguiente relación es entre producto de la tabla primer modelo, y, producto de la tabla producto.

Hay que recordar que en la tabla primer modelo, hay muchas repeticiones, si en la tabla producto, hay aspiradora, en la tabla primer modelo, hay muchas aspiradoras, una para cada mes, ahora, cuando seleccione un producto, filtrara correctamente tanta el primer como el segundo modelo.

En la tabla primer modelo, tenemos la columna fin de mes.

Pues, vamos a relacionar esta columna, con la columna fecha de la tabla fechas.

Hay que recordar que tenemos tres productos para cada fin de mes.

Tenemos la relación entre la columna fin de mes de la tabla primer modelo con la columna fecha de la tabla fechas.

Ahora, si selecciono enero de 2019 de la tabla fechas, me devolverá desde el día 1 al día 31, para comprobarlo vamos a crear una tabla dinámica, para ello, vamos a la pestaña de inicio, desplegamos tabla dinámica, y, seleccionamos tabla dinámica.

Se abre la ventana crear tabla dinámica, donde dejamos marcado nueva hoja de cálculo, y, aceptamos.

Llevo a filas fecha de la tabla fechas, a columnas, producto de la tabla producto, y, a valores para probar, ventas de la tabla segundo modelo.

Creamos una segmentación de datos, y, marcamos las casillas de año y mes de la tabla fechas.

Seleccionamos un año, y, un mes, y, podemos ver las ventas para dicho mes, desde el día 1 hasta el último día del mes.

Si nos fijamos en las relaciones entre fecha-fin de mes del primer modelo, y, fecha-fecha del segundo modelo, vemos que el lado varios es la columna fin de mes, y, la columna fecha del segundo modelo, entonces, si selecciono un mes, y, un año, ira desde la columna fecha de la tabla fechas, hasta la columna fecha del segundo modelo, y, las que sean idénticas, nos devolverá las ventas.

Pero si solo hay un día de final de mes, por ejemplo, para enero del año 2019, fluirá hasta la columna de fin de mes de la tabla primer modelo, y, solo extraerá un valor para cada producto.

Vamos al modelo primer modelo en Power Pivot, donde podemos crear las medidas que necesitamos.

Voy a la zona de cálculo, y, voy a crear una medida para calcular el total, es decir, la suma de la columna presupuesto de ventas, donde usare la función SUM.

Lo pongo en formato de moneda, para ello, desde la pestaña de inicio, despliego el siguiente icono, y, seleccionamos nuestra moneda.

Si vamos a la hoja de cálculo, a la tabla dinámica, con una celda dentro del modelo, hacemos clic con botón alternativo de ratón, y, seleccionamos actualizar, veremos la medida total.

La cual llevo a valores.

Podemos ver las ventas para cada último día de mes.

Seleccionamos el segundo modelo, y, creamos otra medida, para calcular el total de la columna ventas de la tabla segundo modelo.

Lo ponemos en formato de moneda.

Lo siguiente es calcular la diferencia, la haremos en la tabla segundo modelo, donde vamos a restar la medida total2 menos la medida total.

Lo ponemos en formato de moneda.

Ahora, calculamos la diferencia porcentual, para ello, dividimos la medida diferencia entre la medida tota, y, lo ponemos en porcentaje.

Volvemos a la vista de diagrama, vamos a la tabla primer modelo, y, vamos a dejar visible solo la medida, para ello, seleccionamos las tres primeras columnas, hacemos clic con botón alternativo de ratón, y, seleccionamos ocultar en herramientas de cliente.