Calcular presupuesto con las ventas reales.

Vamos a ver cómo podemos comparar el presupuesto con lo real, donde lo haremos, primero, con la función SUMAPRODUCTO, y, FIN.MES, y, después con las funciones SUMAR.SI.CONJUNTO, y, FIN.MES.

Tenemos un presupuesto para un producto en particular, para el final de mes de cada mes.

Después, tenemos otra tabla, donde tenemos el producto, la fecha de transacción, las unidades, y, ventas.

En el primer modelo, tenemos el presupuesto por mes, y, en el segundo modelo, por días, por ejemplo, en el primer modelo, tenemos el presupuesto para el día 31 de diciembre de 2020.

Y, en el segundo modelo, tenemos varias líneas para el mes de diciembre de 2020, por lo que necesitamos el principio y final de mes, es decir, dos condiciones.

En la celda E3, vamos a calcular las ventas, para después, compararla con el presupuesto real, primero lo haremos con la función SUMAPRODUCTO.

Antes de usar la función, hemos dicho que en el segundo modelo tenemos diferentes fechas que pertenecen al mismo mes, y, en el primer modelo, solo el final de mes, y, tenemos que comparar desde el principio de mes hasta final de mes, bien, si en una celda, escribo la función FIN.MES, donde como argumento fecha inicial, selecciono la celda B3, y, como meses pongo -1.

=FIN.MES(B3;-1)

Me dará el fin de mes del mes anterior a la celda B3.

Si ahora sumo 1, tendré el comienzo de mes de la celda B3.

=FIN.MES(B3;-1) + 1

Habiéndolo aclarado, empezamos, en la celda C3, escribimos el signo igual, seguido de SUMAPRODUCTO, y, abrimos un paréntesis.

=SUMAPRODUCTO(

Abrimos un paréntesis para poner la primera condición, donde seleccionamos el rango K3:K57375, y, lo igualamos al valor de la celda C3, es decir, vamos a comparar los productos del segundo modelo que sean iguales al valor de C3.

=SUMAPRODUCTO((K3:K57375=C3)

Ponemos el símbolo de asterisco, que es igual que el operador lógico Y, es decir, devuelve VERDADERO si todas las condiciones se cumplen, abrimos otro paréntesis, la siguiente condición, es que el rango J3:J57375, debe de ser mayor o igual que el comienzo de mes, como lo hemos hecho anteriormente.

=SUMAPRODUCTO((K3:K57375=C3)*(J3:J57375>=FIN.MES(B3;-1)+1)

Ponemos de nuevo el símbolo de asterisco, abrimos otro paréntesis, y, la siguiente condición es que, de nuevo el rango J3:J57375, tiene que ser menor al último día del mes, en este caso, usamos FIN.MES, y, como argumento meses, ponemos cero, quiere decir que calculara el final de mes de la celda que seleccionemos.

=SUMAPRODUCTO((K3:K57375=C3)*(J3:J57375>=FIN.MES(B3;-1)+1)*(J3:J57375<=FIN.MES(B3;0))

Ponemos el símbolo asterisco, ahora, debemos de indicar que columna queremos que nos devuelva, que es la columna ventas del segundo modelo.

=SUMAPRODUCTO((K3:K57375=C3)*(J3:J57375>=FIN.MES(B3;-1)+1)*(J3:J57375<=FIN.MES(B3;0))*(M3:M57375))

Cerramos paréntesis, y, aceptamos.

Tenemos las ventas desde el día 1 de enero de 2019 hasta el día 31 de enero de 2019.

Lo ponemos en formato de moneda.

Ahora, tenemos que arrastrar en vertical, pera antes, debemos de fijar algunas celdas.

=SUMAPRODUCTO(($K$3:$K$57375=C3)*($J$3:$J$57375>=FIN.MES(B3;-1)+1)*($J$3:$J$57375<=FIN.MES(B3;0))*($M$3:$M$57375))

Ya tenemos las ventas para cada final de mes para cada producto.

Ahora, lo haremos con la función SUMAR.SI,CONJUNTO, en la celda F3, escribimos el signo igual, seguido de la función, y, abrimos un paréntesis.

=SUMAR.SI.CONJUNTO(

Como primer argumento rango de suma, seleccionamos la columna de ventas del segundo modelo.

=SUMAR.SI.CONJUNTO(M3:M57375

Como argumento rango criterios 1, seleccionamos el rango K3:K57375, es decir, los productos del segundo modelo.

=SUMAR.SI.CONJUNTO(M3:M57375;K3:K57375

Como argumento criterio 1, seleccionamos la celda C3.

=SUMAR.SI.CONJUNTO(M3:M57375;K3:K57375;C3

Como argumento rango de criterios 2, seleccionamos el rango J3:J57375, es decir, las fechas del primer modelo.

=SUMAR.SI.CONJUNTO(M3:M57375;K3:K57375;C3;J3:J57375

Como criterio 2, ponemos entre comillas dobles, mayor o igual.

=SUMAR.SI.CONJUNTO(M3:M57375;K3:K57375;C3;J3:J57375;">="

Lo concatenamos con el primer día del mes.

=SUMAR.SI.CONJUNTO(M3:M57375;K3:K57375;C3;J3:J57375;">=" & FIN.MES(B3;-1)+1

Como argumento rango de criterios 3, seleccionamos de nuevo las fechas del segundo modelo.

=SUMAR.SI.CONJUNTO(M3:M57375;K3:K57375;C3;J3:J57375;">=" & FIN.MES(B3;-1)+1;J3:J57375

Como argumento criterio 3, entre comillas dobles, ponemos menor o igual, y, lo concatenamos con la función FIN.MES para indicar el último día del mes.

Cerramos paréntesis y aceptamos.

=SUMAR.SI.CONJUNTO(M3:M57375;K3:K57375;C3;J3:J57375;">=" & FIN.MES(B3;-1)+1;J3:J57375;"<=" & FIN.MES(B3;0))

Lo ponemos en formato de moneda, y, vemos que tenemos el mismo resultado.

Ahora, debemos de arrastrar, pero antes, debemos de fijar algunas celdas.

=SUMAR.SI.CONJUNTO($M$3:$M$57375;$K$3:$K$57375;C3;$J$3:$J$57375;">=" & FIN.MES(B3;-1)+1;$J$3:$J$57375;"<=" & FIN.MES(B3;0))

Vemos que tenemos los mismos resultados.

Ahora, vamos a calcular la diferencia, para ello, en la celda G3, restamos ventas reales menos el presupuesto de ventas.

=E3-D3

En este caso tenemos un valor positivo porque las ventas han sido mayores que las presupuestadas.

Arrastramos.

Vemos que a veces estamos por debajo de lo presupuestado.

Lo siguiente es calcular la diferencia, pero en porcentaje, para ello, en la celda H3, restamos E3 menos D3, y, el resultado lo dividimos entre E3.

=(E3-D3)/E3

Aunque también podríamos dividir el valor de la celda G3, donde ya esta hecha la resta por el valor de la celda D3.

=G3/D3

Arrastramos, y, ya tenemos la diferencia porcentual.

Lo ponemos en formato de porcentaje con dos decimales.

Hemos resumido las fechas del segundo modelo en una única fecha del primer modelo.