¿Tienes almacén? Calcula los costes de almacenamiento con Excel

Si quieres sacarle el máximo rendimiento a tu empresa, debes hacer una correcta gestión de almacén para evitar los costes de inventario y aumentar su rendimiento. Por suerte, tenemos el cálculo del EOQ en Excel como herramienta ideal para determinar los costes de almacenamiento de nuestros productos. Lo único que tenemos que saber es la demanda anual constante y conocida de productos, que no sufrirá grandes cambios a lo largo del periodo, unos datos que deben ser lo más precisos posible.

Cómo calcular el coste de almacenamiento en Excel

Lo primero de todo, será crear una pequeña tabla de Excel en la que indicaremos el nombre del producto, la demanda anual del ejercicio anterior, el coste de encargar un lote y el coste de mantenimiento. Al final añadimos una quinta columna, Q o cantidad óptima a pedir, donde aplicaremos la función =EOQ de Excel. Cuando lo hagas, aparecerá un cuadro de diálogo donde tendrás que introducir los argumentos K, D y H que corresponden al coste de encargar el lote (K), la demanda del año anterior (D) y el coste de mantenimiento (H).

Tras aplicarla, tenemos la cantidad óptima a pedir para sacar el máximo rendimiento económico al almacén. De ahí pasamos a una nueva tabla en la que indicaremos las unidades, el coste de ordenar, coste de mantenimiento y coste total, que es lo que nos indicará el coste de almacenamiento real que estamos pagando por ello. Además, luego puedes exportar estos datos a un gráfico de Excel para conocer de forma más visual los gastos de almacenamiento de la empresa a partir del punto óptimo.

Empezaremos definiendo las unidades. No hace falta ponerlas de una en una, sino que lo haremos siguiendo una progresión aritmética según el tamaño del almacén: de 3 en 3, 5 en 5, 10 en 10, 20 en 20… Eso sí, no olvides añadir la cantidad óptima que hemos calculado en el punto anterior para saber el coste de almacenamiento óptimo. A continuación pasamos a calcular el coste de orden, que se obtiene aplicando la fórmula =Coste de ordenar lote*(demanda anual/unidades). Basta con cambiar el concepto por la celda donde tengamos los valores, por ejemplo =C2*(B2/A6).

Utilizando autocompletar rellenaremos la columna del costo de ordenar. Lo mismo haremos con el costo de mantenimiento, que calcularemos utilizando la fórmula =Costo de mantenimiento*(unidades/2), tomando como referencia los costes de la tabla inicial. Calculamos la primera celda y rellenamos utilizando autocompletar. Por último, nos falta calcular los valores para la columna coste total, que se obtienen sumando el coste de ordenar y el coste de mantenimiento. De este modo, sabemos cuál es el coste de almacenamiento según las unidades pedidas.

Para que este cálculo sea exacto, hay que tener en cuenta diferentes parámetros empezando porque el coste unitario no depende de la cantidad pedida, de modo que no hay descuentos por comprar una mayor cantidad. Además, los productos se compran en lotes, y el coste de emitir una orden es constante. Por último, hay que tener en cuenta que no se admiten roturas de stock, ya que cualquier alteración supondría importantes cambios en los costes de almacenamiento de nuestro almacén.

Plantilla de Excel para evitar la rotura de stock

Ya solo nos quedaría trasladar los gastos de almacenamiento a un gráfico de Excel, para lo que vamos a utilizar los datos de la última tabla. Lo ideal es trazar un gráfico lineal en el que aparezcan los costes de orden, mantenimiento y total. Los dos primeros deberían cruzarse sobre el punto Q, esa cantidad óptima que nos permite sacarle el máximo rendimiento a nuestro almacén. El eje vertical corresponde al coste, mientras que en el horizontal deberíamos situar el número de unidades.