top of page

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.

ree




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

ree




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.

ree








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.

ree





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

ree








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.

ree






Ya tenemos el primer modelo en Power Pivot.

ree








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.

ree





Podemos ver las tablas.

ree

















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.

ree

















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.

ree


















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.

ree



















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.

ree






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

ree






















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

ree






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

ree









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.

ree









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

ree







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.

ree




















ree












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

ree













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.

ree

























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.

ree















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.

ree




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

ree











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.

ree









La cual llevo a valores.


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

ree










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

ree



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.

ree



Lo ponemos en formato de moneda.

ree





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

ree



ree






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.

ree








En la tabla fechas, voy a dejar visible solo nombre mes, y, año

ree












En la tabla producto, voy a dejar visible solo el producto.

ree








En la tabla segundo modelo, voy a dejar visible solo las medidas.

ree













Insertamos una tabla dinámica en una hoja nueva.


Si nos fijamos en la zona de campos, vemos solo los campos que hemos dejado visible.

ree










Llevo los siguientes campos:

ree













Y ya tenemos nuestro modelo, donde tenemos el presupuesto real, las ventas, la diferencia, y, la diferencia porcentual.

ree














ree

Comentarios


© 2019 Miguel Ángel Franco García

bottom of page