¿Cómo hacer un inventario en Excel? - Aprende a usar Excel y lleva el inventario de tu negocio

Microsoft Excel es la herramienta virtual predilecta para ordenar datos y hacerlos comprensibles. Muchas veces ha sido considerada como una herramienta con métodos muy complejos y, si te resulta complicada, en esta ocasión te mostraremos un paso a paso muy breve para aprender a ordenar un inventario con facilidad.

El inventario es un proceso administrativo fundamental y, por ello, antes de realizar uno, es importante conocer cada detalle. De esta forma, sabremos cómo hacerlo y estará estructurado de manera correcta. En esta guía aprenderemos sobre las nociones y conceptos necesarios.

Pasos para crear un inventario en Excel

La planificación es esencial para llevar a cabo cualquier objetivo que te hayas propuesto. Para ello, debes tener en cuenta muchos factores que pueden repercutir a la hora de hacer el inventario que estarás desarrollando.

En este proyecto, la clave está en tener en cuenta las tres ramas financieras: las entradas, salidas y el stock. Estas nos ayudarán a diferenciar las distintas etapas de un inventario, y esto, a su vez, nos permitirá tener un mejor control de la situación económica del negocio. Representan aspectos importantes para poder llevar un control minucioso del movimiento y comportamiento de nuestro inventario.

Para comenzar a crear este archivo, el cual llevará el control y el conteo de tu inventario, debes tener a la mano tus datos base, es decir, lo que es popularmente conocido como 'stock inicial', con el fin de llevar el control desde un punto de partida específico. Ahora, para planificar es necesario comprender que es imprescindible atender varios puntos antes de realizar cualquier proyecto:

• Planeamiento: Antes de comenzar con el inventario, se deben planificar y establecer los objetivos, cualquier necesidad específica del mismo y los modelos de valoración a utilizar. Dado que establecemos criterios de clasificación, es importante determinar cuáles son los objetivos y necesidades específicas a cubrir con este inventario.
• Orden: Antes de empezar, nuevamente, debemos tener una lista completa y actualizada de todos los artículos y los detalles a incluir, como los costos, por ejemplo. También es destacable pensar en la estructura en la que deseas tener tu stock, o buscar una plantilla que se amolde a tus requerimientos.
• Formatos y fórmulas: Cuando estés creando el inventario, es necesario tener en cuenta el procedimiento que se aplicará con los resultados que buscas. Teniendo esto en cuenta, pensemos en los detalles que se pueden incluir en el inventario, los cuales tendrán relevancia en las fórmulas que se emplearán para optimizar el tiempo y, de tal manera, que los resultados vayan acorde a lo que se busca.

• Actualización: Para tener un inventario que nos sirva como una herramienta práctica, debemos tener en cuenta que es imprescindible mantener toda la información actualizada y organizada. De esta forma, podremos consultar y analizar los resultados en cualquier momento, y podremos tomar decisiones con mayor facilidad y con rapidez, tomando en cuenta, costos, ingresos y egresos. Además, es prudente llevar un registro durante el trayecto de la empresa.

Resumiendo, es importante planificar, organizar y tener en cuenta todos los aspectos necesarios de un inventario antes de iniciar su creación en Excel. Esto incluye la cantidad y el orden de las fórmulas y formatos adecuados. De esta forma, podremos tener una administración eficaz de nuestro emprendimiento.

Crea un documento de Excel con los productos

• Abrimos Excel y creamos un nuevo documento.

• Nos vamos a asegurar de que este documento tenga 3 hoja de cálculo distintas. Le colocamos un nombre distinto a cada una, para no confundir las propiedades que cumple cada hoja.
  • “Stock” se llamará nuestra primera página. Aquí vamos a ver todos los artículos que tenemos y manejamos en nuestro Stock actualmente y en tiempo real).
  • “Ingresos” tendrá por nombre la segunda página. En esta se van a registrar las cantidades de artículos ingresados.
  • “Egresos” llevará por nombre la tercera página. En esta, vamos a registrar el número de artículos que se venden.

Cuando ya tengamos lista cada página, vamos a organizar en la primera fila, en todas las columnas, los siguientes nombres para dar un orden a nuestro stock:

En la página “Stock”

1. Ítem (este es el nombre del producto, artículo que manejamos).
2. Serial (esta es la clave para identificar al producto, un código).
3. Descripción (esto lo hacemos con propiedades del producto, como color, peso, cantidad, marca entre otras características).
4. Cantidad o stock inicial (esta cantidad nos dice el número de iguales de un producto al comenzar nuestro inventario, o punto de partida para el conteo del Ítem).
5. Ingresos.
6. Egresos.
7. Total (esta cantidad nos indica directamente lo que hay al final del proceso de ingreso, de egreso o de venta al final del día, o al momento de realizar el conteo).

Debería verse como te indicaos en la imagen.

Crear tabla para los movimientos de almacén

• Primero, nos aseguramos de que las tablas que creamos tengan el formato adecuado.

• Para ello, seleccionamos nuestra tabla con todos sus cuadrantes, filas y columnas. Estando en la pestaña “Insertar” de la interfaz en Excel, hacemos clic en la pestaña de “Tabla”.
• Aparecerá un pequeño cuadro emergente en el centro titulado “Crear tabla”. Aquí, vamos a asegurarnos de seleccionar la opción que dice “La tabla tiene encabezados”, y pinchamos en “Aceptar”. Nuestra tabla va a adquirir otro aspecto de inmediato. A continuación, te mostramos un poco cómo se verían estos cambios.

• No podemos dejar pasar por alto el cambio de nombre a nuestras tablas, para su correcta identificación. Estos pasos serán los mismos para cada tabla que tengamos en las tres páginas:

• La tabla en la página de stock tiene que llevar por nombre «Stock».
• La tabla en la página de ingresos tiene que llevar por nombre «Ingresos».
• La tabla en la página de egresos tiene que llevar por nombre «Egresos».

Introducir los datos a nuestro control de inventario

Una vez que tengamos nuestras tres tablas en regla, procedemos a verificar que estén en orden.

• Nos dirigimos a nuestra primera página (Stock), y llenaremos la columna “Serial” con sus respectivos seriales, o códigos de cada ítem.
• Luego, nos dirigimos a la hoja de 'Ingresos'. Ahí, vamos a seleccionar toda la columna de “Serial” (sin contar el encabezado que ya se titula “Serial”), Una vez seleccionadas, pinchamos en la pestaña de “Datos”.
• En el  recuadro emergente, vamos a seleccionar la opción de “Validación de datos”.
• Una ventana aparecerá y, en el menú de “Permitir”, escogemos la opción de “Lista”. Esto hará que se abra una sección llamada “Origen” y, aquí, vamos a hacer clic en el icono de selección de celdas.

• Después de que la ventana cambie de aspecto y permita buscar nuestra selección de origen, vamos directo a la primera página (Stock) y seleccionamos la columna entera que contiene los seriales de nuestros productos. Finalmente, hacemos clic en "Enter". Eso nos llevará de vuelta a la ventana anterior y, en el apartado de “Origen”, aparecerá la selección de celdas que hicimos. Hacemos clic en “Aceptar”.

Este proceso nos permite usar un menú desplegable para seleccionar el serial del producto al pasar el cursor sobre cada celda de la columna. De esta manera, podremos ingresar los nombres de los productos, o sus seriales, sin cometer equivocaciones.

• Luego vamos a llenar nuestra tabla con los seriales de nuestros productos. Será un proceso sencillo, ya que solo desplegaremos un menú y seleccionaremos seriales.  Hacemos lo mismo en la hoja de “Egresos”, y vamos a terminar con los datos faltantes.
• Nos vamos a regresar a la pestaña de “Stock”.  Ya con todos nuestros seriales en orden, vamos a rellenar las columnas de “Ítem”, “Descripción” e “inventario inicial” con los datos correspondientes.
• Tenemos que tomar en consideración que, al no tener un producto inicial en nuestro stock inicial, lo podemos considerar en esta tabla con un valor cero.
• Después, nos vamos a la pagina de “Ingresos”, donde ya tenemos nuestra columna de “Serial” llena. En la columna “Ítem”, vamos a escribir la siguiente Fórmula:

=BUSCAR([@Serial];Stock;Stock[Item])

Esta columna se va a llenar de inmediato con sus respectivos nombres de forma automática.  Ahora rellenamos con los datos respectivos en las columnas de 'Factura', 'Fecha' y 'Cantidad' y, por supuesto, repetimos este proceso con la página de “Egresos”.

Aplicar formatos y fórmulas a la tabla de movimientos

Los pasos serán relativamente sencillos y se centrarán en la introducción de datos. Esto nos ayudará a obtener resultados, como el total de productos que tenemos al final de nuestro inventario.

Vamos a integrar una fórmula en nuestra primera hoja 'Stock' para que ésta se complete a medida que vayamos ingresando información en las columnas correspondientes. Primero, copiamos la siguiente fórmula en la columna de "Ingresos" en la página de “Stock”:

=SUMAR.SI(Ingresos[Serial];[@Serial];Ingresos[Cantidad])

Inmediatamente, la columna se rellenará con los ingresos de nuestra segunda página. En la columna “Egresos” de la página “Stock” vamos a utilizar una fórmula distinta, la siguiente:

=SUMAR.SI(Egresos[Serial];[@Serial];Egresos[Cantidad])

Esto estaría llenando nuestras columnas de todos los movimientos y, de tal manera, tendremos mayor control visual de todo lo que estamos integrando.

Aplicar formatos y fórmulas a la tabla de productos

Como ya hemos creado nuestra tabla de Stock, vamos a usarla como base para crear una tabla de movimientos. Así, podremos usar una fórmula que nos da el resultado que necesitamos.

Para tener un resultado final, debemos agregar una columna llamada "Total", con la fórmula:

=[@[Inventario inicial]]+[@Ingresos]-[@Egresos]

¡Y listo! Tenemos nuestro inventario listo y cada vez que ingreses nueva información, la tabla se actualizará por sí sola. Ahora podemos continuar con otra parte de nuestro proyecto.

Este será el aspecto que tendrá la tabla que hemos creado.

¿Qué métodos de valoración de inventarios existen?

Considerando la naturaleza de diversos métodos, los categorizamos y nombramos de la siguiente manera:

Método PEPS

La técnica PEPS (Primeras Entradas, Primeras Salidas) consiste en entregar las mercancías que se ingresaron al stock primero, antes que las más recientes. Esto busca vender el stock lo más rápido posible. Así, las mercancías más antiguas se van retirando del stock mientras las más nuevas llegan.

La técnica del PEPS es útil para las empresas que tienen productos con un tiempo de caducidad, ya que esta técnica permite eliminar primero el stock que ya tiene un tiempo almacenado, evitando así el deterioro de la mercancía. Con ello, los primeros ingresos siempre tendrán que ser las primeras ventas.

Para aplicar el método PEPS, es importante llevar un registro adecuado de las entradas y salidas de productos en algún sistema de control de inventario. Esto permitirá identificar las unidades más antiguas y determinar el costo de venta de manera precisa.

Método UEPS

La técnica UEPS  (Últimas Entradas, Primeras Salidas) es un método de valoración de inventarios que organiza la salida de mercancías en función de su antigüedad en el inventario. De este modo, los productos más recientes son los primeros en salir de los almacenes. Es decir, las unidades de mercancías que menos tiempo llevan en el almacén van a ser las primeras en salir vendidas.

Esta práctica también se conoce por sus siglas en inglés como LIFO (Last Inputs, First Outputs). La regla 'UEPS' se utiliza comúnmente para los artículos que no manejan fechas de caducidad, por ejemplo materias primas como madera y piedra.

No refleja el costo real de los artículos en el stock, ya que asume que los productos más recientes son los primeros en salir. Esto puede ser problemático si los costos de los productos han aumentado desde su adquisición, ya que el costo de los productos vendidos será mayor que el costo real.

La UEPS es menos popular que PEPS debido a que tiene un impacto fiscal más bajo, ya que se asignan costos mayores a los productos más recientes, lo que reduce la base imponible sobre la cual se calcula el impuesto. Además, algunos países prohíben su uso debido a que puede ser manipulado para declarar menos beneficios y pagar menos impuestos.

Método ABC

El procedimiento ABC (Análisis ABC) es una herramienta de gestión empresarial que se utiliza para clasificar y organizar los productos o inventarios en función de su importancia o valor. El objetivo principal del método ABC es mejorar la distribución y asignación de recursos, centrándose en los productos más relevantes para la empresa.

Aquí están los aspectos clave del método ABC:

• Clasificación: El método ABC caracteriza los artículos en categorías A, B y C, según su importancia o valor. Esta categorización se realiza con base en criterios como el volumen de ventas, el margen de beneficio, la demanda u otros factores relevantes para la empresa.

• Asignación de recursos: El método ABC permite asignar recursos de manera más eficiente, centrándose en los productos de mayor importancia. Los artículos de la clasificación A, que suelen ser los más relevantes, obtienen una mayor atención y recursos, mientras que los de las categorías B y C reciben menos recursos en proporción a su importancia.

• Manejo de inventarios: El método ABC ayuda a gestionar los inventarios de manera más efectiva. Al clasificar los productos según su importancia, se pueden tomar decisiones más informadas sobre aspectos como el almacenamiento, la reposición de stock y la planificación de la demanda.

• Optimización de recursos: Al asignar recursos de manera más eficiente, el método ABC ayuda a mejorar un proceso para los costos y maximizar los beneficios. Permite identificar los productos clave que generan la mayor parte de los ingresos y enfocar los esfuerzos en ellos, mientras se reducen los recursos destinados a productos menos relevantes.

• Mejora de la eficiencia: Al tener una visión clara de la importancia de cada producto, el método ABC ayuda a mejorar la eficiencia en la gestión de inventarios y en las operaciones logísticas. Permite priorizar los esfuerzos en los productos más importantes y optimizar los procesos relacionados con su almacenamiento, distribución y venta.

En resumen, el método ABC es una herramienta útil para clasificar y gestionar los inventarios de manera más eficiente, centrándose en los productos más importantes. Ayuda a asignar recursos de manera adecuada y a tomar decisiones informadas para optimizar los costos y maximizar los beneficios.

Método EOQ

La técnica EOQ (Economic Order Quantity) o Cantidad Económica de Pedidos se emplea para determinar la cantidad ideal de artículos que se deberían solicitar en cada pedido, con el fin de obtener un mínimo de costos totales. Esto con el propósito de minimizar gastos teniendo en cuenta factores como el almacenamiento y costos relacionados.

La fórmula básica del EOQ es la siguiente:

EOQ = √((2 * D * S) / H)

Donde:

• EOQ: Cantidad Económica de Pedido.

• D: Demanda anual de unidades.

• S: Costo de realizar un pedido.

• H: Costo de mantener una unidad en inventario durante un año (costo de almacenamiento).

Para esta técnica hay una variedad de condiciones que tienen que ser cumplidas, como el almacenamiento, la demanda y la disponibilidad de los artículos. A pesar de estas condiciones, en la práctica se suelen cambiar las condiciones según venga la necesidad.

Desventajas del método EOQ

• Supuestos simplificados: Esta técnica consta de supuestos simplificados, como una demanda constante y conocida, costos de pedido y mantenimiento constantes, y disponibilidad inmediata de productos. Estos supuestos pueden no ser realistas en todas las situaciones y pueden repercutir en lo preciso de nuestros resultados.
• No considera variaciones en la demanda: No se tiene en consideración las variaciones en la demanda en cualquier plazo de tiempo. Si la demanda es constante o fluctúa significativamente, la técnica puede no ser adecuada para determinar la cantidad óptima de pedido.
• Requiere datos precisos: Para usar esta técnica y que sea lo más efectiva posible, se requiere tener datos precisos, destacando los costos de todo y la demanda anual. Si estos datos no están disponibles o son inexactos, los resultados del método EOQ pueden no ser confiables.

El método EOQ ayuda a determinar la cantidad óptima de productos a pedir en cada pedido, lo que permite minimizar los costos totales de inventario al equilibrar los costos de almacenamiento y los precios del pedido. Al utilizar esta fórmula, las empresas pueden tomar decisiones más informadas sobre la gestión de sus inventarios y optimizar sus operaciones logísticas.

Técnica de promedio ponderado

El método Promedio Ponderado es una técnica de valuación de stocks que se utiliza para concretar el costo en promedio de los artículos en nuestro stock, considerando todos los costos en fueron adquiridos y la cantidad de unidades. Esta técnica se usa la mayor parte del tiempo en los sistemas de inventario definitivos y es útil para corporaciones que manejan artículos homogéneos, o que no poseen una estructura de seguimiento de lotes o números de serie.

Aquí vamos a presentar también algunos aspectos y ejemplos de esta técnica de promedio ponderado:

• Características: El método Promedio Ponderado toma en consideración únicamente el costo total de las unidades en stock, y lo divide por la cantidad total de unidades en stock para conseguir el costo promedio por cada unidad. Este costo promedio se usa para valorar las unidades vendidas o sacadas de nuestro stock.
• Un ejemplo: Si alguna corporación posee 100 unidades de un producto en stock, con un costo total $1000, el costo promedio por unidad sería de $10. Si la empresa vende 50 unidades de este producto, el costo de venta sería de $500 (50 unidades x $10 costo promedio por unidad).
• Beneficios: La técnica del Promedio Ponderado es fácil de calcular y no requiere un seguimiento detallado y arduo de los costos de adquisición de cada unidad. Además, suaviza las fluctuaciones de precios y costos en el inventario.
• Desventajas: La técnica Promedio Ponderado puede que no refleje el costo en tiempo real de las unidades más antiguas o las más recientes en el stock. Además, puede ser menos útil para corporaciones que manejan productos con características únicas o que requieren un seguimiento constante y preciso de los costos en que fueron adquiridos.

Esta técnica es un método de valoración de stocks que se usa para determinar el costo promedio de los productos en el inventario. Es fácil de calcular y útil para empresas que manejan productos homogéneos o que no tienen un sistema de seguimiento de lotes o números de serie. Sin embargo, puede no reflejar el costo real de las unidades más antiguas o más recientes en el stock.

Importancia de manejar nuestros inventarios en Excel

Vamos a considerar varios motivos por los que deberías tener un inventario en Excel para tu negocio o emprendimiento.

• Valor de lo existente: Si empleamos uno de los sistemas de valoración de inventarios, podemos considerar la totalidad de nuestros bienes en productos, lo que nos ayudaría a tener conocimiento de los costos, ganancias o pérdidas.
• Genera un orden constante: Un inventario ordenado y preciso es una ventaja, ya que nos permite saber con detalle lo que tenemos en stock, y también nos permite establecer una prioridad de valoración en caso de productos con fecha de caducidad. También se puede mantener un seguimiento exacto de cada producto.
• Optimizar el tiempo: Llevar un inventario detallado de los productos permite mejorar la eficiencia al reducir el tiempo empleado en verificaciones y ubicación de los artículos. Además, la aplicación de las fórmulas en promedios, sumas o restas nos ayuda a sacar cuentas que sean un tanto extensas en cuestión de lo que tardamos ingresando algunos datos para obtener nuestros totales.
• Aumentar el funcionamiento de la compañía: Ya que nuestro inventario nos indica el contenido de nuestro stock, podemos estar al tanto de la frecuencia con la que se puede ingresar cierta cantidad de productos, o cuáles artículos deben de tener salida primero. Esto mejora de forma considerable la potestad para tomar cualquier decisión dentro de la empresa.

Un inventario bien elaborado es fundamental para mantener el control de los productos, ahorrar tiempo en su manejo y conocer el valor de cada artículo. Por lo tanto, los beneficios de un inventario son múltiples, ya que además ayuda a mantener la eficiencia de la empresa. Incluso, existe una variedad de plantillas que pueden ser de utilidad para cada compañía según la necesidad de ésta, así como también existen distintos modelos de valoración de inventarios que optimizan la toma de decisiones.