Continuamos con el curso de excel, esta vez veremos la formula para calcular existencias de un almacén, el calculo de inventarios es algo muy solicitado y buscado, así que pon mucha atención.
Aquí la idea es básicamente que al registrar un movimiento de entrada o salida, automáticamente se actualice el inventario
Se que dependiendo del nivel que tengas en este momento del uso de las formulas y funciones de excel, es muy probable que veas muy difícil el poder llevar un control automatizado de las existencias de un almacén, pero se por experiencia, que esa creencia esta muy relacionada con malas practicas a la hora de realizar los registros de entradas y salidas, básicamente me refiero a que en la mayoría de los casos se registran los movimientos de la manera incorrecta, finalmente ese error provoca muchos dolores de cabeza.
Bien, para una mayor comprensión, te dejo este vídeo donde explico todo el proceso de las formulas para calcular la existencia.
Link de descarga al final del post
Ejemplos:
Agregar una hoja para los movimientos de cada día, imagina, si el almacén trabaja los 365 días del año, al final de 1 solo año tendrás 365 hojas en tu libro, ahora imagina que te piden un reporte de todos los movimientos en el año de un proveedor o cliente!!!.
Se que de inicio la idea es buena, quien querría deliberadamente complicarse la vida y trabajar horas extras para tener un reporte que cumple ciertos criterios, pero finalmente es un error total el llevar registros de esa manera, igual es un error manejarlo con una hoja para cada semana, cada mes, para cada cliente, proveedor, etc.
Bueno, después de este rollo te estarás preguntando, y cual es la forma correcta de registrar…?
La forma correcta de diseñar una base de datos.
En realidad es mas simple de lo que te puedes imaginar, simplemente tienes que usar la primera fila para indicar los nombres de campo, cada campo sera usado para plasmar un dato especifico de cada registro, se debe usar una columna para cada campo.
No es buena idea inventarse formatos complicados, combinando celdas, poner campos encimados, etc, simplemente poner los campos en linea usando de preferencia la primera fila.
Tal como se ve en la imagen, es la forma correcta de estructurar una base de datos, así de simple.
En ocasiones me dicen, Nolberto, yo lo tengo que separar en hojas porque mis productos estas separados por categorías, así que necesito llevar una hoja para cada categoría, pues no, no hace falta una hoja para cada categoría o cada tipo, ni por días, semana, cliente, etc.
La solución es muy sencilla, si tus productos son identificados por categorías, agrega una columna en la base de datos para indicar la categoría de cada producto que se registra en las entradas y salidas, si necesitas obtener reportes diarios, semanales, mensuales, trimestrales, anuales o cualquier rango de fechas, solo hace falta una columna para poner la fecha, tal como se ve en la imagen, en otras clases ya vimos como hacer consolidados tomando como base la fecha, también vimos como generar reportes en otras hojas usando filtro avanzado.
Así que no hay pretextos para llevar bien una base de datos que finalmente te ayude a generar reportes de una manera fácil y sin mayores complicaciones.
Parece que ya me extendí un poco, seguro estas pensando… y la formula para calcular la existencia!!.. vamos.
1.- Identificar datos clave para poder calcular existencia.
Esto es sumamente importante, como podrías hacer un calculo si no conoces los componentes que intervienen en ese proceso, en este caso es muy simple, tenemos primero el nombre o código del producto, la cantidad de cada movimiento o registro y el tipo de movimiento ( Entrada, Salida ).
Ahora, es muy importante que los campos que identifican a esos datos estén bien definidos en la primera fila de la base de datos, uno por cada columna, esto tiene una razón muy simple que de hecho aplica para cualquier lenguaje de programación, en este caso particular seria para las formulas, que prácticamente todas trabajan con una orientación basada en la cuadricula de la tabla u hoja de calculo, principalmente las formulas de búsqueda y referencia, así como las funciones matriciales como SUMAPRODUCTO, esto es, hacen búsquedas por filas y columnas, devuelven datos que pueden estar en esa misma fila o columna, pero por lo regular suelen devolver resultados de la intersección de una fila y columna.
Se que esto ultimo puede parecer algo complicado, pero no te preocupes, sigue leyendo y veras que realmente no es tan difícil, una vez que lo asimiles te sorprenderás de las cosas que podrás hacer.
2.- La formula para sumar las entradas al almacén.
Bien, llego la hora, veremos esa formula que en un instante te dará la suma de las entradas, tomando los datos de una base de datos donde se registran todos los movimientos, entradas y salidas, de todos los productos, de todos los días , de todos los clientes y proveedores, de todos los meses, de todos los años… todo en una sola hoja.
=SUMAPRODUCTO(($B5=almarticulo)*(almtipomov="Entrada")*(almcantidad))
Si observas la formula, estamos usando SUMAPRODUCTO, esa función suma los productos de rangos o matrices, en este caso el rango que realmente suma es el rango de la columna de cantidad, pero a su vez usamos condiciones para que esa suma se pueda dar, aqui son dos condiciones, la primera es que debe contemplar solo los registros que en su columna de Articulo tengan en nombre del Articulo1, la segunda condición es que en la columna de Tipo ( Entrada, Salida ), diga Entrada.
Esa comparación se puede observar en la formula ( $B5=almarticulo ) y ( almtipomov=”Entrada” ), los nombres almarticulo y almtipomov son nombres de rango que hemos dado a las columnas de articulo y tipo de movimiento.
En este caso la lógica es muy simple, cuando la formula encuentra u registro que tiene el nombre del articulo buscado y que ademas es una entrada, suma el valor que hay en la columna de cantidad en esa misma fila, de ese modo sumara todos los registros que cumplan esas condiciones y así de simple se obtiene la suma de las entradas.
3.- La formula para sumar las salidas.
Realmente aquí es el mismo cuento, simplemente se cambia el criterio del tipo de movimiento por “Salida”.
=SUMAPRODUCTO(($B5=almarticulo)*(almtipomov="Salida")*(almcantidad))
Como se ve en la imagen, no hay ningún misterio, es prácticamente la misma formula excepto por el criterio en la comparación de la columna del tipo de movimiento.
4.- Formula para calcular la existencia.
Teniendo ya las entradas y salidas, resulta mas que sencillo calcular la existencia, solo debemos restar el resultado de la suma de entradas menos el resultado de la suma de salidas.
Pero también podridos hacer todo el calculo en una sola celda.
=SUMAPRODUCTO(($B5=almarticulo)*(almtipomov="Entrada")*(almcantidad))-SUMAPRODUCTO(($B5=almarticulo)*(almtipomov="Salida")*(almcantidad))
Si ven esa gran formula, en realidad son las dos formulas anteriores, solo que en la misma celda se resta una de la otra ( Entradas menos Salidas ).
Eso es Todo…?
Si, como has visto, finalmente no es tan difícil calcular la existencia de un almacén, esto gracias al poder de la función SUMAPRODUCTO, esta es una función matricial, esto es porque internamente procesa los datos de matrices completas, en este caso procesa las columnas que intervienen en el calculo de la existencia, haciendo comparaciones y sumando cuando esas comparaciones son positivas, todo en una misma celda.
Descargar Excel para Calcular Existencia
Si el articulo te ha gustado, no lo pienses, comenta y comparte con tus amigos en las redes sociales.