Encontrar la constante que mejor ajusta una curva de ventas acumuladas puede parecer complejo, pero con Excel Solver el proceso se convierte en una tarea sistemática y precisa. A partir de un portafolio de ocho productos ordenados de mayor a menor en ventas, es posible calcular el parámetro óptimo que define la curvatura de la línea de Pareto, minimizando el error entre los datos reales y los estimados.
¿Cómo se calcula el porcentaje acumulado de artículos y ventas?
El primer paso consiste en obtener las ventas totales del portafolio usando la función =SUMA() sobre las ventas de los ocho productos [0:42]. Dado que el portafolio tiene ocho artículos, cada uno representa un porcentaje equivalente calculado como uno dividido entre ocho, lo que arroja un 12.50% por producto [0:55].
Este valor se va acumulando producto a producto:
- El primer producto representa el 12.50%.
- El segundo acumula el 25.00%.
- El proceso continúa hasta alcanzar el 100% en el octavo producto.
Para facilitar el arrastre de la fórmula en Excel, se fija el denominador (ocho) con la tecla F4 [1:15]. Este acumulado constituye el parámetro X, que será fundamental para el cálculo de la curva estimada.
Posteriormente se calculan las ventas acumuladas en dólares [2:27]. El primer ítem acumula sus propias ventas (5,735 dólares), el segundo suma sus ventas más las del primero, y así sucesivamente. Para obtener el porcentaje de ventas acumulado, cada valor acumulado se divide entre el total de ventas, fijando la celda del total con F4 [3:15]. Al graficar estos porcentajes se obtiene la primera curva real de la gráfica de Pareto.
¿Qué papel juega la constante A en la estimación de ventas?
Para estimar ventas futuras se introduce una constante A [3:47]. Inicialmente se asume un valor arbitrario, por ejemplo A = 2. La fórmula para calcular el Y estimado es:
Y estimado = (1 + A) * X / (A + X)
Donde X es el porcentaje acumulado de artículos [4:05]. Al fijar la constante A y arrastrar la fórmula para cada producto, se genera una nueva curva estimada en la gráfica.
¿Por qué la curva estimada no coincide con la real?
Con A = 2 la línea gris queda muy por debajo de la curva naranja (datos reales) [4:45]. Esto indica que el valor elegido para A no representa adecuadamente el comportamiento histórico de las ventas. Se necesita un método para encontrar el valor óptimo.
¿Cómo se mide la diferencia entre la curva real y la estimada?
Se utiliza el error cuadrático medio (ECM) [5:05]. Este se calcula como la diferencia entre el Y estimado y el Y real, elevada al cuadrado:
Error = (Y estimado - Y real) ^ 2
Elevar al cuadrado cumple dos funciones: eliminar valores negativos y amplificar los errores grandes, lo que facilita la optimización [5:15]. Se calcula este error para cada producto y luego se obtiene su promedio con la función =PROMEDIO() [6:55].
¿Cómo se usa Solver para encontrar la constante A óptima?
Con el error cuadrático medio calculado, se abre Solver desde la pestaña Datos [7:10]. La configuración requiere tres elementos:
- Función objetivo: minimizar la celda del error cuadrático medio.
- Variable a cambiar: la celda que contiene la constante A.
- Restricción: A debe ser mayor o igual a cero, condición necesaria para garantizar una curva cóncava [7:30].
Al ejecutar Solver, la herramienta itera hasta encontrar el valor óptimo. En este ejercicio, la constante A pasó de 2 a 0.22 [7:48]. Con este nuevo valor, la curva estimada (amarilla) queda sobrepuesta a la curva real (naranja), confirmando que el ajuste es correcto.
Este resultado significa que la función Y = (1 + 0.22) * X / (0.22 + X) describe con alta precisión la distribución de ventas del portafolio, permitiendo estimar el comportamiento futuro y tomar decisiones informadas sobre gestión de inventarios basada en la clasificación de Pareto.
¿Has utilizado Solver para resolver problemas de optimización en tus proyectos? Comparte tu experiencia y las dificultades que encontraste en el camino.
