Inventario en Excel: cómo llevar el control de stock paso a paso
Cómo montar un inventario en Excel para controlar el stock: hoja de artículos, registro de entradas y salidas, stock actual con SUMAR.SI.CONJUNTO, coste medio, punto de pedido y recuentos físicos.
Respuesta rápida: para llevar el inventario en Excel no guardes el stock como un número que vas sobrescribiendo. Registra cada movimiento —entradas, salidas y ajustes— en una hoja, y deja que una segunda hoja calcule el stock de cada artículo con SUMAR.SI.CONJUNTO. Con tres hojas (Artículos, Movimientos y Stock) obtienes el stock actual, su valor a coste medio y un aviso de cuándo hay que hacer pedido al proveedor.
Este método sirve para una tienda online, un pequeño comercio o un negocio que fabrica sus propios productos. Funciona igual en Excel y en Google Sheets, y deja un historial completo: si el recuento físico no cuadra, puedes ver qué movimiento lo explica. A continuación tienes la estructura, las fórmulas y un ejemplo con cifras ilustrativas.
Por qué registrar movimientos y no solo el stock
Muchas hojas de inventario tienen una columna “Stock” que alguien corrige a mano cada vez que entra o sale mercancía. Funciona hasta el primer error: un día el número no cuadra con la estantería y no hay forma de saber por qué. Con un registro de movimientos:
- cada cambio de stock tiene fecha, cantidad y documento (albarán, pedido, devolución);
- puedes calcular el stock en cualquier fecha pasada;
- sabes cuánto vende cada artículo, lo que permite calcular cuándo pedir;
- el coste medio se obtiene de las compras reales.
Las tres hojas del inventario
| Hoja | Una fila por | Columnas |
|---|---|---|
| Artículos | referencia | Referencia, descripción, categoría, unidad, proveedor, plazo de entrega (días), stock de seguridad |
| Movimientos | entrada, salida o ajuste | Fecha, referencia, tipo, cantidad, coste unitario, documento, notas |
| Stock | referencia | Entradas, salidas, ajustes, stock actual, coste medio, consumo diario, punto de pedido, estado, valor |
Convierte cada rango en tabla (Insertar → Tabla o Ctrl + T) para que las fórmulas se extiendan solas, y usa Datos → Validación de datos para que las columnas Referencia y Tipo solo admitan valores de una lista. Un espacio de más en “Entrada ” basta para que una fórmula deje de contar ese movimiento.
Paso 1: la hoja Artículos
Cada producto, y cada variante con su propio stock (talla, color, tamaño), necesita una referencia única: corta, sin espacios y que no cambie nunca, por ejemplo FUN-IP15-NEG. Es la clave que une todas las hojas.
Añade dos datos de planificación: el plazo de entrega del proveedor en días y el stock de seguridad, la cantidad mínima que quieres tener siempre por si el proveedor se retrasa o las ventas suben. Al principio son estimaciones; ajústalas cuando tengas historial.
Paso 2: la hoja Movimientos
Columnas: A Fecha, B Referencia, C Tipo, D Cantidad, E Coste unitario sin IVA, F Documento, G Notas. Reglas sencillas que evitan la mayoría de errores:
- Tipo solo puede ser Entrada, Salida o Ajuste.
- En entradas y salidas la cantidad se escribe siempre en positivo; el tipo indica la dirección.
- En ajustes la cantidad lleva signo: negativa si falta mercancía o se ha roto, positiva si el recuento encuentra más.
- Las devoluciones de clientes que vuelven a la venta son una Entrada con la nota “Devolución”.
- El coste unitario se rellena en las compras e incluye el transporte del proveedor repartido por unidad.
Si vendes en una tienda online, puedes exportar los pedidos cada semana y pegarlos como salidas en lugar de escribirlos uno a uno.
Paso 3: calcular el stock actual
En la hoja Stock, con la referencia en la columna A, las fórmulas son (Excel en español separa los argumentos con punto y coma):
- Entradas (B):
=SUMAR.SI.CONJUNTO(Movimientos!D:D;Movimientos!B:B;A2;Movimientos!C:C;"Entrada") - Salidas (C):
=SUMAR.SI.CONJUNTO(Movimientos!D:D;Movimientos!B:B;A2;Movimientos!C:C;"Salida") - Ajustes (D):
=SUMAR.SI.CONJUNTO(Movimientos!D:D;Movimientos!B:B;A2;Movimientos!C:C;"Ajuste") - Stock actual (E):
=B2-C2+D2
Si tu Excel está en inglés, la función es SUMIFS y el separador es la coma.
Paso 4: coste medio y valor del inventario
El coste medio ponderado de cada referencia es el importe total de las compras dividido entre las unidades compradas:
=SI.ERROR(SUMAPRODUCTO((Movimientos!B2:B5000=A2)*(Movimientos!C2:C5000="Entrada")*Movimientos!D2:D5000*Movimientos!E2:E5000)/B2;0)
El valor del stock (J) es el stock actual por el coste medio: =E2*F2. Sumando esa columna sabes cuánto dinero tienes inmovilizado en mercancía, un dato útil para la tesorería y para el cierre del ejercicio.
El Plan General de Contabilidad establece con carácter general el método del precio medio ponderado para bienes intercambiables entre sí y admite también FIFO. Si la hoja va a servir de base para tus cuentas anuales, confirma el criterio con tu asesor.
Paso 5: punto de pedido y cantidad a pedir
El punto de pedido es el nivel de stock en el que hay que hacer el pedido para no quedarse sin existencias mientras llega la mercancía:
Punto de pedido = consumo medio diario × plazo de entrega + stock de seguridad
- Consumo diario (G), con las salidas de los últimos 30 días:
=SUMAR.SI.CONJUNTO(Movimientos!D:D;Movimientos!B:B;A2;Movimientos!C:C;"Salida";Movimientos!A:A;">="&HOY()-30)/30 - Punto de pedido (H):
=G2*BUSCARX(A2;Artículos!A:A;Artículos!F:F)+BUSCARX(A2;Artículos!A:A;Artículos!G:G) - Estado (I):
=SI(E2<=H2;"Pedir";"OK")
Ejemplo ilustrativo: una funda de móvil vendió 240 unidades en los últimos 30 días, es decir, 8 al día. El proveedor tarda 10 días y quieres 40 unidades de seguridad. El punto de pedido es 8 × 10 + 40 = 120 unidades. Con 105 en el almacén, la hoja muestra “Pedir”.
¿Cuánto pedir? Una regla práctica es pedir lo necesario para cubrir un periodo de ventas después de la llegada: stock objetivo = consumo diario × (plazo + días de cobertura) + stock de seguridad. Para cubrir 30 días: 8 × (10 + 30) + 40 = 360 unidades, de modo que el pedido es 360 − 105 = 255. Mientras llega se venderán unas 80, así que al recibirlo tendrás 25 + 255 = 280 unidades: 30 días de venta más el stock de seguridad.
Paso 6: recuentos físicos y ajustes
La hoja solo es tan buena como su último recuento. En lugar de un gran inventario al año, cuenta cada mes un grupo de referencias y rota hasta cubrir todas. Prioriza los artículos que más valor o más movimiento tienen: suelen ser pocos y concentran la mayor parte de las diferencias.
Cuando el recuento no coincida con el stock calculado, registra la diferencia como Ajuste con la causa en Notas (rotura, error de envío, muestra, robo). Si una causa se repite, corrige el proceso que la provoca.
Errores frecuentes
- Corregir el stock a mano en lugar de registrar un movimiento: se pierde el historial.
- Una misma referencia para variantes distintas: si la talla M se agota y la L sobra, el total no te lo dirá.
- No anotar devoluciones ni roturas: son la causa más habitual de descuadres.
- Coste sin transporte: el coste medio sale más bajo que el real y el margen parece mejor de lo que es. La guía para calcular el margen de beneficio muestra cómo afecta al precio.
- Borrar filas antiguas para aligerar el archivo: mejor cerrar el año y empezar otro libro con el stock final como entrada inicial.
¿Excel o un programa de gestión de stock?
| Excel suele bastar si… | Plantéate un programa si… |
|---|---|
| Gestionas decenas o pocos cientos de referencias | Necesitas sincronizar el stock automáticamente entre varias tiendas o marketplaces |
| Una o dos personas registran los movimientos | Varias personas preparan pedidos a la vez |
| Tienes un solo almacén | Trabajas con varios almacenes, lotes o números de serie |
| Quieres controlar tú las fórmulas y los informes | Pasas más tiempo registrando datos que vendiendo |
Preguntas frecuentes
¿Puedo hacer esto en Google Sheets?
Sí. La estructura es la misma y las funciones existen también en Google Sheets. Es práctico si varias personas registran movimientos desde el móvil o el almacén.
¿Cómo gestiono productos con tallas o colores?
Crea una referencia por variante (CAM-AZU-M, CAM-AZU-L…) y añade una columna “Producto” para agrupar las variantes en una tabla dinámica cuando quieras ver el total.
¿Puedo usar un lector de códigos de barras?
Sí. Un lector USB o Bluetooth normal escribe el código como si fuera un teclado, así que basta con usar el código de barras como referencia o añadir una columna con él y buscarlo con BUSCARX.
¿Cada cuánto debo hacer inventario físico?
Un recuento rotativo mensual de algunas referencias y un recuento completo al cierre del ejercicio es una buena base para la mayoría de pequeños negocios.
¿Cómo registro un pedido que el cliente cancela antes del envío?
Si ya lo habías registrado como salida, añade una entrada con la misma cantidad y la nota “Cancelación”. Así el historial sigue completo y el stock vuelve a cuadrar.
Más guías para vender en línea y gestionar el stock en la sección Venta online e inventario.