viernes, 30 de noviembre de 2012

Stock y compras

Ante este comentario en el grupo Excel Práctico para Empresas de LinkedIn, me surgió la siguiente solución :
Comentario :
"...En este debate necesito vuestra colaboración. 
Excel es una herramienta muy útil en las empresas, pero quizás a veces el desconocimiento de todas sus herramientas nos dificultan el trabajo.
Alguien me puede explicar como a través de Excel puedo calcular los niveles máximos de compra de materias primas en función del stock que se tenga en almacén y en función del nivel de tesorería que se tenga?"

1) En primer medida, lo que debemos tener es el Stock Actual, en nuestro ejemplo está en la columna B.


2) En otras columnas cargamos nuestros valores constantes de Stock Mínimo y Stock Máximo por producto. Entiéndase el Stock Mínimo como aquel necesario para operar tranquilos, esto puede variar por producto y también por estacionalidad. El Stock Máximo es aquel que utilizaremos para solicitar nuestro pedido y debería estar relacionado con nuestra capacidad de ventas, la rotación del producto y nuestra capacidad de almacenamiento. El precio de compra es el precio por unidad ofrecido por nuestros proveedores.

3) El Pedido Máximo surge de la diferencia de (Stock Máximo-Stock Actual). Luego el Máximo Valorizado nos dará el precio total a pagar por la totalidad del pedido. En nuestro ejemplo, la sumatoria de G18 nos muestra ese total.

4) Como la necesidad original era realizar la compra teniendo en cuenta la disponibilidad de tesorería, cargamos dicha disponibilidad, en nuestro modelo será en G19. Luego, en G20 calculamos la proporción de dicha disponibilidad con respecto a nuestro Total Max Valor.  

5) Utilizando el valor de proporcionalidad obtenido en G20, calculamos el Pedido a Realizar en la columna H, tomando la misma proporcionalidad con la columna F de Pedido Máximo. Si nuestro pedido Máximo fuese de 60 y nuestra proporcionalidad de 50% el pedido a realizar sería de 30. Aquí hacemos una salvedad y es que si el cáculo del pedido a realizar es mayor al Stock Mínimo entonces aplicamos la fórmula proporcional. Si no es así, el valor del pedido será el del Stock Mínimo. La fórmula para la primer celda de Pedido a Realizar es :
=SI(ENTERO(F2*$G$20)>C2;ENTERO(F2*$G$20);C2)

Notas: Este es sólo un ejemplo de resolver la situación planteada. También puede realizarse con Solver o con Macros.
El ejemplo no dará un buen resultado cuando la disponibilidad sea pequeña con respecto al Total Max Valor (el valorizado de nuestro pedido máximo) con lo que tal vez muchos productos nos queden por debajo del pedido mínimo y luego la sumatoria de Total Pedido nos dé por encima de nuestra disponibilidad. Para hacerlo más burdo : si nuestra disponibilidad es menor a Nuestra disponibilidad Mínima (sumatoria de stocks mínimos por valor de compra de cada producto), debemos de usar otro criterio, o salir a buscar más dinero.

<<Bajar ejemplo>>





1 comentario:

Anónimo dijo...

hola, queria consultarte si a esta planilla le agrego una columna que me indica si el producto es "valido" o "caducado", como seria la formula para determinar el pedido a realizar, teniendo en cuenta las dos condiciones anteriores(stock minimo, presupuesto disponible) mas esta ultima del estado del producto. GRACIAS!