Inventario en Excel: cómo hacerlo paso a paso con fórmulas

Si tienes un negocio pequeño y apenas empiezas a ordenar tu mercancía, Excel es una buena primera herramienta: ya la tienes, no cuesta nada y se adapta a tu negocio. Con tres hojas y unas pocas fórmulas puedes saber cuánto hay de cada producto, cuándo volver a comprar y cuánto dinero tienes en bodega.

En esta guía armamos un inventario en Excel paso a paso, con las fórmulas listas para copiar. También te contamos hasta dónde llega una hoja de cálculo y cuándo conviene dar el siguiente paso. Si todavía no tienes claro qué es el control de inventario, empieza por nuestra guía de control de inventario.

Qué necesitas antes de empezar

• Una lista de tus productos, con una clave corta para cada uno (por ejemplo CAF para el café). Evita nombres largos que se escriben distinto cada vez.
• El conteo físico de lo que tienes hoy y lo que te costó. Si aún no lo hiciste, sigue la guía para hacer un inventario.
• Un compromiso: anotar cada compra y cada venta el mismo día. Las fórmulas son sencillas; lo difícil es la constancia.

La estructura: dos hojas que se hablan entre sí

La idea es no escribir nunca la existencia a mano. Tú solo anotas movimientos (lo que entra y lo que sale) y Excel calcula el resto.

• Hoja "Movimientos": un renglón por cada compra, venta o merma. Aquí es donde capturas.
• Hoja "Productos": un renglón por producto, con fórmulas que leen la hoja de movimientos y te dicen cuánto hay y si debes comprar.

Paso 1: la hoja "Movimientos"

En el renglón 1 escribe estos encabezados, uno por columna:

• A: Fecha
• B: Clave
• C: Tipo (solo dos valores: Entrada o Salida)
• D: Cantidad
• E: Costo unitario (solo se llena en las entradas)

Cada vez que compras mercancía, agregas un renglón de tipo Entrada con la cantidad y lo que te costó cada pieza. Cada vez que vendes, o que algo se rompe o caduca, agregas un renglón de tipo Salida.

Ejemplo de captura

Datos inventados para ilustrar:

• 1 de octubre, CAF, Entrada, 20 piezas, $180 cada una.
• 1 de octubre, AZU, Entrada, 30 piezas, $28 cada una.
• 5 de octubre, CAF, Salida, 8 piezas.
• 8 de octubre, AZU, Salida, 12 piezas.
• 12 de octubre, CAF, Entrada, 10 piezas, $200 cada una.
• 15 de octubre, CAF, Salida, 14 piezas.
• 16 de octubre, AZU, Salida, 9 piezas.

Evita errores de captura con una lista desplegable

El error más común es escribir "entrada", "Entrada " o "compra" y que la fórmula no lo reconozca. Para evitarlo, usa la validación de datos: selecciona la columna C, ve a la pestaña Datos, elige Validación de datos, en Permitir selecciona Lista y en Origen escribe Entrada,Salida. Así solo podrás elegir entre esas dos opciones. Microsoft explica las opciones de listas, mensajes y alertas de error en su artículo Más información sobre la validación de datos.

Paso 2: la hoja "Productos"

En la segunda hoja escribe estos encabezados en el renglón 1:

• A: Clave
• B: Producto
• C: Entradas
• D: Salidas
• E: Existencia
• F: Stock mínimo
• G: Estado
• H: Costo promedio
• I: Valor del inventario

Captura a mano solo las columnas A, B y F (clave, nombre y el stock mínimo que decidas). El resto serán fórmulas. En el renglón 2 escribe lo siguiente:

• Entradas (C2): =SUMAR.SI.CONJUNTO(Movimientos!D:D,Movimientos!B:B,A2,Movimientos!C:C,"Entrada")
• Salidas (D2): =SUMAR.SI.CONJUNTO(Movimientos!D:D,Movimientos!B:B,A2,Movimientos!C:C,"Salida")
• Existencia (E2): =C2-D2
• Estado (G2): =SI(E2<=F2,"Comprar","OK")
• Costo promedio (H2): =SI(C2=0,0,SUMAPRODUCTO((Movimientos!B2:B1000=A2)*(Movimientos!C2:C1000="Entrada")*Movimientos!D2:D1000*Movimientos!E2:E1000)/C2)
• Valor del inventario (I2): =E2*H2

Después, selecciona el renglón 2 y arrastra hacia abajo para copiar las fórmulas a los demás productos. Si tu Excel está configurado con punto y coma como separador, cambia las comas de las fórmulas por punto y coma.

SUMAR.SI.CONJUNTO suma los valores de un rango que cumplen varios criterios a la vez; aquí, que la clave coincida y que el tipo sea Entrada o Salida. Microsoft lo explica en su página de CONTAR.SI.CONJUNTO y SUMAR.SI.CONJUNTO.

Qué debes ver con los datos del ejemplo

Con los datos de arriba, estas fórmulas dan el siguiente resultado:

• Café (CAF): 30 entradas, 22 salidas, existencia de 8. Con un stock mínimo de 10, el estado es Comprar. El costo promedio de tus compras es ($3,600 + $2,000) ÷ 30 = $186.67, y el valor en bodega es 8 × $186.67 = $1,493.33.
• Azúcar (AZU): 30 entradas, 21 salidas, existencia de 9. Con un stock mínimo de 8, el estado es OK. Costo $28 y valor en bodega de $252.
• Total invertido en inventario: $1,493.33 + $252 = $1,745.33. Para sumarlo, en una celda aparte escribe =SUMA(I2:I3).

Una aclaración sobre el costo promedio

La fórmula calcula el promedio de todas las compras que has registrado, ponderado por cantidad. Es una buena aproximación para empezar, pero no recalcula el costo después de cada venta como lo haría un método de valuación completo. Para entender las diferencias entre PEPS y costo promedio, revisa la sección de métodos en nuestra guía de control de inventario.

Paso 3: que Excel te avise cuándo comprar

Para no revisar renglón por renglón, pinta de rojo los productos por comprar. Selecciona la columna G, ve a Inicio, Formato condicional, Resaltar reglas de celdas, Es igual a, y escribe Comprar. Elige un relleno rojo claro. Microsoft detalla todas las reglas disponibles en Utilice el formato condicional para resaltar la información en Excel.

Para decidir qué número poner como stock mínimo, usa la fórmula de punto de reorden de nuestra guía de stock mínimo.

Buenas prácticas para que no se descomponga

• Nunca escribas la existencia a mano. Si lo haces, rompes la fórmula y el inventario deja de ser confiable.
• Usa siempre la misma clave para el mismo producto. "CAF" y "caf " cuentan como cosas distintas si hay espacios de más.
• No borres movimientos. Si te equivocaste, agrega un movimiento que lo corrija y anota la causa.
• Guarda una copia cada semana, con la fecha en el nombre del archivo.
• Haz un conteo físico periódico y compáralo contra la columna de existencia. Si no coinciden, investiga la diferencia.

Hasta dónde llega Excel y cuándo conviene cambiar

Un inventario en Excel funciona bien cuando tienes pocos productos, una sola persona captura y vendes sin mucha complejidad. Empieza a quedarse corto cuando:

• Tienes cientos de productos o varias bodegas. La hoja se vuelve lenta y difícil de revisar.
• Varias personas capturan. Es fácil que alguien sobrescriba una fórmula o que haya dos versiones del archivo.
• Quieres saber tu utilidad por producto. Requiere cruzar ventas, costos y precios, y las fórmulas se complican.
• Necesitas avisos automáticos o consultar el inventario desde el celular.

Cuando llegues a ese punto, un sistema de inventarios hace ese trabajo por ti. Te explicamos cómo elegir uno en nuestra guía de sistemas de inventarios, o puedes probar el Sistema de Inventarios de Plan Perfecto, pensado para micronegocios.

Siguiente paso

Crea hoy la hoja "Movimientos", captura tus diez productos más vendidos con su existencia actual como primera entrada, y arma la hoja "Productos" con las fórmulas de arriba. En una semana de uso sabrás qué se vende y qué se queda parado.