En la entrada de hoy te voy a mostrar cómo utilizo Power Query a la hora de elaborar el presupuesto de ventas.
Es habitual que, desde el área comercial, nos envíen los presupuestos de ventas mensualizados para que desde el departamento financiero metamos los datos en la coctelera con la que elaboramos el presupuesto global de la compañía.
A nivel de cierres financieros, con tener las ventas con esa granularidad mensual es suficiente, ya que los cierres contables los realizamos, a lo sumo, con periodicidad mensual.
El problema viene cuando queremos utilizar el presupuesto de ventas para nuestras reuniones periódicas de seguimiento de la evolución de las ventas.
Lo habitual en estas reuniones es hacer una comparativa de nuestras ventas tanto contra periodos anteriores como contra el presupuesto. Y ese seguimiento, en la práctica, se hace ya no solo a nivel semanal, sino que la parte comercial y la gerencia suelen revisar estos datos a nivel diario (incluso varias veces al día si me apuras 😜).
Por ello, para crear esos reportes de seguimiento de ventas, necesitamos trabajar el presupuesto mensualizado que nos llega desde el área comercial y repartir ese presupuesto a granularidad diaria.
Vamos a ver a continuación cómo Power Query nos puede facilitar la vida para hacer ese trabajo de reparto:
Partimos de una tabla en la que nos envían el presupuesto de ventas de 2024 para cada una de los departamentos de la empresa (Directas, postventa y distribución) y para cada uno de los meses (la fecha indicada es el último día de cada uno de los 12 meses). Por tanto, tenemos una tabla de presupuesto con 36 registros (12 meses * 3 departamentos):
El objetivo es repartir esos importes mensuales entre cada uno de los días para que cuando hagamos los seguimientos de ventas a cada fecha, podamos comparar contra presupuesto sin tener que esperar a final de mes.
Para ello, vamos a utilizar nuestra tabla de calendario, a la que habremos incorporado una columna con el número de horas laborables de cada uno de los días. Yo utilizo este sistema de indicar el número de horas porque hay negocios en los que no todos los días se trabajan el mismo número de horas. En este ejemplo, los días de Nochebuena y Nochevieja se trabaja únicamente media jornada, por lo que esos días en lugar de indicar 8 horas indico 4 horas. Los días de cierre (festivos y fines de semana en este ejemplo) indicamos 0 horas:
En esta tabla de calendario de 2024 tendríamos 366 filas (es año bisiesto), indicado las horas laborables de cada uno de los días.
Lo siguiente que vamos a hacer es duplicar nuestra consulta de Calendario y llamamos a la nueva consulta “Calendario mensual”.
A cada una de las 3 consultas que tenemos (Presupuesto, Calendario y Calendario mensual) las agregamos una nueva columna con el número de mes, el cual nos servirá posteriormente para combinar las consultas. Para ello, seleccionamos la columna de Fecha en una consulta, vamos al menú de Agregar columna => Fecha => Mes => Mes (como vemos en la siguiente imagen). Esta operación la repetimos en las demás consultas.
A continuación, en la consulta de Calendario mensual, agrupamos los datos por la columna Mes para sumar las horas laborables de cada uno de los meses.
Tenemos que pulsar sobre “Agrupar por” y configurar el menú de la siguiente forma:
Obtendremos una tabla con 12 registros que refleja el total de horas laborables de cada uno de los meses:
Tras esto, combinamos la consulta del presupuesto con la del calendario mensual por el campo del mes y nos traemos las horas laborables mensuales a cada fila del presupuesto:
El resultado sería el siguiente:
Ahora combinamos la consulta del presupuesto con la del calendario (el calendario normal, no el mensual) por el campo del número de mes y expandimos los campos de la fecha y las horas laborables diarias. (nuestra tabla de presupuesto pasará de 36 filas a 1098 => 366 días * 3 departamentos).
El resultado sería el siguiente:
Ahora ya tenemos en nuestra tabla de presupuesto, para cada combinación de fecha y departamento, el presupuesto mensual del departamento, las horas laborables mensuales y las horas laborables diarias.
Por ejemplo, en la fila 2 de la anterior imagen observamos que el día 02/01/2024 (columna FECHA.1) tiene 8 horas laborables de un total de 176 horas laborables del mes de enero; y que el presupuesto mensual del departamento DIRECTAS era de 11.591.186 €.
Con todos estos datos, ya podemos calcular la parte proporcional de presupuesto que le toca a cada combinación de departamento-fecha.
Para ello, vamos a crear una columna personalizada que llamaremos “Presupuesto diario”. Será el resultado de multiplicar el valor del presupuesto mensual por las horas laborables diarias y dividirlo entre las horas laborables mensuales:
Ponemos a la nueva columna formato número decimal fijo y el resultado sería el siguiente:
Por último, dentro de la consulta de presupuesto nos quedaríamos únicamente con las columnas “Departamento”, “FECHA.1” (a la que renombraremos posteriormente como “Fecha”) y “Presupuesto diario”.
El resultado sería el siguiente:
Por tanto, gracias a estas transformaciones realizadas en Power Query, ya habríamos conseguido nuestro objetivo de distribuir un presupuesto que nos habían entregado a granularidad mensual y dejarlo distribuido a granularidad diaria.
Espero que te haya resultado útil y puedas utilizarlo cuando te toque elaborar el presupuesto de 2025 😜
Seguimos!!! 👊
PD: Te dejo preparado un pbix con los datos de este ejercicio para que puedas practicar el paso a paso.
El enlace de descarga es el siguiente: