Cómo hacer un control de inventario en Excel con entradas y salidas

Saber cuánto tienes de cada producto sin ir a contarlo es la base de cualquier negocio que compra y vende mercadería. Un control de inventario en Excel lo consigue con dos tablas: una con tus productos y otra donde apuntas cada entrada y cada salida. El stock se calcula solo, y Excel te avisa cuando toca reponer.

Esta guía forma parte de Excel para empresas. Las fórmulas usan el punto y coma para separar argumentos; si tu Excel usa la coma, cámbialo al copiarlas.

La idea: el stock no se escribe, se calcula

El error más común es tener una columna «Stock» y cambiar el número a mano cada vez que entra o sale algo. Al cabo de un mes nadie sabe por qué el número es el que es. La forma correcta es registrar movimientos y dejar que Excel calcule el saldo: stock actual = stock inicial + entradas − salidas. Así cada unidad tiene un rastro.

Paso 1: la tabla de productos

En una hoja «Productos», crea una tabla con Ctrl + T llamada Productos, con estas columnas: Código, Descripción, Unidad, Stock inicial, Stock mínimo, Costo unitario, Stock actual, Alerta y Valor. Las tres últimas serán fórmulas. El código debe ser único y corto, por ejemplo P-101: es lo que escribirás en cada movimiento.

Paso 2: el registro de movimientos

En otra hoja, una tabla llamada Movimientos con: Fecha, Código, Descripción, Tipo, Cantidad y Documento. En Tipo, una lista desplegable con Entrada y Salida (Datos → Validación de datos → Lista). En Código, otra lista con los códigos de la tabla Productos, para que no se cuelen códigos mal escritos.

La descripción se completa sola a partir del código:

=BUSCARX([@Código];Productos[Código];Productos[Descripción];"Código no existe")

Si ves «Código no existe», el producto no está dado de alta. Lo explicamos con detalle en BUSCARV y BUSCARX.

Paso 3: el stock actual

En la columna Stock actual de la tabla Productos:

=[@[Stock inicial]]+SUMAR.SI.CONJUNTO(Movimientos[Cantidad];Movimientos[Código];[@Código];Movimientos[Tipo];"Entrada")-SUMAR.SI.CONJUNTO(Movimientos[Cantidad];Movimientos[Código];[@Código];Movimientos[Tipo];"Salida")

Es larga, pero se lee fácil: el stock inicial, más todas las entradas de ese código, menos todas sus salidas. Los dobles corchetes de [@[Stock inicial]] son necesarios porque el nombre de la columna tiene un espacio.

Paso 4: alerta de reposición y valor del inventario

• Alerta: =SI([@[Stock actual]]<=[@[Stock mínimo]];"Reponer";""). Añade formato condicional para que «Reponer» salga en rojo.
• Valor: =[@[Stock actual]]*[@[Costo unitario]]. La suma de esta columna es lo que tienes inmovilizado en mercadería.

El stock mínimo no es un número al azar: debería cubrir lo que vendes mientras llega el pedido. Si vendes 5 unidades al día y el proveedor tarda 4 días, por debajo de 20 ya vas tarde.

Ejemplo resuelto

Tres productos de una librería con estos movimientos en la primera quincena de marzo: salen 12 millares de papel, salen 4 tóneres, entran 20 millares de papel, salen 45 archivadores y salen 30 millares de papel.

Código	Producto	Inicial	Entradas	Salidas	Stock actual	Mínimo	Alerta
P-101	Papel bond A4, millar	40	20	42	18	15
P-102	Archivador de palanca	60	0	45	15	20	Reponer
P-103	Tóner negro	8	0	4	4	5	Reponer

El papel ha tenido más movimiento, pero gracias a la entrada del día 6 sigue por encima del mínimo. Los archivadores y el tóner, con menos movimientos, son los que hay que pedir. Sin el cálculo automático, lo más probable es que se repusiera primero lo que más se vende y no lo que de verdad falta.

Paso 5: el kardex de un producto

El kardex es el historial de un producto con su saldo después de cada movimiento. Con los movimientos registrados en orden de fecha y el código en la columna B, añade dos columnas a la tabla Movimientos. En G, la cantidad con signo: =SI([@Tipo]="Entrada";[@Cantidad];-[@Cantidad]). En H, el saldo:

=BUSCARX(B2;Productos[Código];Productos[Stock inicial];0)+SUMAR.SI($B$2:B2;B2;$G$2:G2)

El rango $B$2:B2 crece fila a fila, así que cada saldo suma solo los movimientos anteriores de ese código. Para ver el kardex de un solo producto, filtra la tabla por su código o, en Microsoft 365, usa =FILTRAR(Movimientos;Movimientos[Código]=K1;"Sin movimientos") con el código en K1.

Un stock mínimo calculado con tus datos

En lugar de fijar el mínimo a ojo, calcúlalo con el consumo real. Añade a Productos una columna Plazo (los días que tarda el proveedor) y otra Seguridad (unas unidades de margen). Las salidas de los últimos 30 días son:

=SUMAR.SI.CONJUNTO(Movimientos[Cantidad];Movimientos[Código];[@Código];Movimientos[Tipo];"Salida";Movimientos[Fecha];">="&HOY()-30)

Divide entre 30 para obtener el consumo diario y multiplica por el plazo. El mínimo queda en =REDONDEAR.MAS(Consumo30/30*[@Plazo];0)+[@Seguridad], donde Consumo30 es la columna con la fórmula anterior. Así, si un producto empieza a venderse más, su mínimo sube solo.

Productos que no se mueven

El inventario parado también cuesta: es dinero inmovilizado que puede caducar o pasar de moda. La fecha del último movimiento de cada producto es:

=MAX.SI.CONJUNTO(Movimientos[Fecha];Movimientos[Código];[@Código])

Y con =SI(HOY()-[@[Último movimiento]]>90;"Sin rotación";"") marcas los que llevan más de tres meses sin salir. Revísalos cada trimestre: liquidarlos a tiempo suele ser mejor que guardarlos un año más. MAX.SI.CONJUNTO está disponible desde Excel 2019.

Buenas prácticas

• Registra el mismo día. Un movimiento apuntado una semana tarde deja el stock equivocado durante una semana.
• Nunca borres un movimiento. Si hubo un error, registra un movimiento contrario con una nota. El historial debe cuadrar.
• Haz un conteo físico periódico. Una vez al mes o al trimestre, cuenta y compara con el stock calculado. La diferencia son mermas, robos o errores de registro, y hay que investigarla.
• Protege las fórmulas. Deja libres solo las celdas de registro y proteger la hoja.

Un apunte importante: este control sirve para la gestión diaria. A partir de ciertos niveles de ingresos, SUNAT exige llevar un registro de inventario permanente con un formato concreto. Si es tu caso, consulta con tu contador qué formato te corresponde.

Cuando el negocio crece

Si además del inventario necesitas controlar ventas, clientes, cobranzas y caja, todo conectado, el sistema de gestión para MYPES en Excel reúne esos módulos en un solo archivo. Para las compras que reponen el stock, conviene formalizar cada pedido con una orden de compra y medir a tus proveedores con una evaluación de proveedores.

Preguntas frecuentes

¿Puedo usar un lector de códigos de barras?

Sí. La mayoría de los lectores funcionan como un teclado: escriben el código en la celda activa y pulsan Enter. Basta con que tus códigos de producto sean los mismos que los del código de barras.

¿Cómo controlo varios almacenes?

Añade una columna Almacén a la tabla Movimientos y un criterio más en SUMAR.SI.CONJUNTO. Con una tabla dinámica sobre Movimientos verás el stock por producto y almacén en una sola tabla.

¿Qué método de costeo uso?

Para la gestión diaria, el costo del último pedido o un costo promedio son suficientes. Para la contabilidad, el método lo define tu contador; los más usados son el promedio ponderado y PEPS, primeras entradas, primeras salidas.

¿Puedo registrar los movimientos desde el celular?

Sí. Guarda el archivo en OneDrive y ábrelo con la aplicación de Excel o con Excel online desde el navegador. Las listas desplegables funcionan, así que registrar una salida en el almacén lleva unos segundos.

¿Cuántos productos aguanta Excel?

Miles sin problema. Con decenas de miles de movimientos, las fórmulas SUMAR.SI.CONJUNTO empiezan a ir lentas; en ese punto conviene resumir con tablas dinámicas o pasar a un sistema de inventario.