Los pronósticos son la herramienta principal para los departamentos lideres de las empresas tanto comercializadoras como manufactureras, no sería adecuado adjudicar un uso exclusivo a un departamento, ya que son utilizados desde producción para realizar una planeación oportuna de sus insumos, mano de obra y materia prima en función a su capacidad productiva o de almacenamiento y hasta marketing para que se ha vuelto tendencia la predicción de conversiones en productos y servicios de publicidad.
Vemos pues que los pronósticos son esenciales para los ejercicios diarios de la industria, en ingeniería clasificamos los pronósticos por el método utilizado, es decir, pueden ser cuantitativos o cualitativos, en este caso nos enfocaremos en un método cuantitativo bajo el supuesto que contamos con la información histórica necesaria.
En esta contribución utilizaremos métodos de suavización, y es importante considerar que dependiendo del tipo de serie de datos con la que nos encontremos deberemos determinar el método especifico, me explico:
Se usan métodos cuantitativos cuando se tienen datos históricos, podríamos clasificar estos métodos de la siguiente manera:
- Multivariados (Causales)
- Regresión Lineal Simple
- Regresión Lineal Múltiple
- Univariados (Series de tiempo)
- Métodos de Suavización
- Suavización exponencial simple
- Suavización exponencial doble
- Promedios móvil simple
- Promedio móvil doble
- Descomposición de Series
- Métodos de Suavización
La determinación del método a utilizar es crucial y hay que entender que es lo que estamos pronosticando, una analogía que sirve mucho para determinar si se debe utilizar métodos causales o de series de tiempo es imaginar a dos personas con una planta, una tiene el recurso de agua constante y la otra tiene sequias prolongadas, la variable será si muere la planta por falta de agua, en el caso de la persona que tiene recurso constante de agua entramos en un modelo de series de tiempo ya que la salud de la planta dependerá en la cantidad de agua que utiliza para regarla cada cierto tiempo por otra parte la persona limitada de este recurso entra en un modelo causal ya que dependerá de otra variable la cual es la disponibilidad del agua.
Finalmente determinamos utilizar un método simple o doble de acuerdo a la estacionalidad y tendencia de los datos, en el caso de los los métodos en donde utilizamos un solo componente (simple) encontramos datos estacionales, en el caso de no tener estacionalidad se incorpora un segundo componente (doble).
El método que utilizaremos para la optimización de sus dos componentes (α, β) mediante Solver es el método de Holt, la aplicación sería exactamente igual si se desea utilizar el método de Brown.p
El método Holt utiliza dos constantes α (alfa) como componente de suavizamiento y β (beta) como tendencia. La formula para calcular el valor de la variable en un periodo futuro esta dada por:
\hat{Y_t}=L_t+pT_t
donde:
\hat{Y_t} = Valor pronosticado para el periodo t
L_t = Valor estimado para el periodo t
T_t = Valor de la tendencia en el periodo t
Para calcular el valor estimado utilizamos la siguiente ecuación:
L_t=\alpha{}Y_{t-1}+\left(1-\alpha{}\right)\left(L_{t-1}+T_{t-1}\right)
Podemos observar que ya aparece el elemento α (alfa) el cual corresponde al componente de suavizamiento.
Por ultimo para calcular el valor de la tendencia utilizamos:
T_t=\beta{}\left(L_t-L_{t-1}\right)+(1-\beta{})T_{t-1}
La constante β (beta) nos servirá para incorporar la tendencia en el suavizamiento.
Estas ecuaciones las pueden consultar en el libro Estadística para administración y economía (10a. edición Anderson, David R., Dennis J. Sweeney y Thomas A. William)
Para ambas constantes los valores asignados pueden ser entre 0 y 1.
Una vez definidas todas las ecuaciones necesarias pongámosla a prueba, en la siguiente tabla tenemos los datos de venta de enero a octubre del producto x
| PRODUCTO | ENE | FEB | MAR | ABR | MAY | JUN | JUL | AGO | SEP | OCT | NOV |
| PRODUCTO X | 8413.85 | 10679.89 | 12659.72 | 11814.28 | 2947.56 | 3338.11 | 1344.75 | 1781.73 | 2570.85 | 5360.06 | Y |
Se desea conocer el pronostico para el mes de noviembre, para ello agregaremos 3 columnas a la izquierda del inicio de los periodos para poder agregar las constantes del método holt y en la fila de la constante beta agregaremos 5 filas, 3 correspondientes a las variables del método y 2 para el error. El acomodo puede ser cualquiera sin embargo optamos por este para mantener los datos con la estructura más sencilla y pueda ser replicable como una tabla.
| PRODUCTO | ALFA | BETA | ERROR | ENE | FEB | MAR | ABR | MAY | JUN | JUL | AGO | SEP | OCT | NOV |
| PRODUCTO X | 0.3 | 0.3 | 0 | 8413.85 | 10679.89 | 12659.72 | 11814.28 | 2947.56 | 3338.11 | 1344.75 | 1781.73 | 2570.85 | 5360.06 | Y |
| Lt | ||||||||||||||
| Tt | ||||||||||||||
| Y | ||||||||||||||
| E | ||||||||||||||
| ABS |
En Excel veríamos algo así:
Una vez estructurada nuestra plantilla iniciaremos calculando el valor L_t. Para evitar incrementar el error definiremos un valor estimado en el periodo L_{t=1}, en este caso colocaremos el valor real de la demanda de ese mismo periodo, o sea Y_{t=1}, y para el valor de la tendencia del mismo periodo (T_{t=1}) colocaremos el valor 500. Con esto facilitamos el calculo de ambos valores y aprovechamos al máximo este método cuando no tenemos muchos datos históricos como es el caso.
Recordemos la formula para calcular el valor estimado:
L_t=\alpha{}Y_{t-1}+\left(1-\alpha{}\right)\left(L_{t-1}+T_{t-1}\right)
Aplicando la formula en nuestra plantilla vamos a anclar (F4) el valor de α (alfa) localizado en la celda C3 ya que la replicaremos hacia la derecha. La función quedaría expresada de la siguiente manera ‘=(F3*$C$3)+(1-$C$3)*(F4+F5)’.
Una vez aplicada la función en la celda F4 replicamos para el resto de periodos. Procedemos a calcular el valor de la tendencia, la formula es:
T_t=\beta{}\left(L_t-L_{t-1}\right)+(1-\beta{})T_{t-1}
De igual forma debemos anclar la celda en donde se encuentra el valor para nuestra β (beta) el cual se encuentra en la celda D3, aplicando la formula la función en Excel debe ser ‘=$D$3*(G4–F4)+(1-$D$3)*F5‘
Finalmente calculamos el valor pronosticado \hat{Y_t} sumando ambos valores de las celdas G4 y G5.
\hat{Y_t}=L_t+T_t
Ahora, para poder cuantificar la asertividad del pronostico calculamos un error, el cual será la diferencia entre el valor pronosticado y el valor real de la demanda ambos en el mismo periodo, es decir restamos la celda G3 – G6 y este mismo valor lo elevamos al cuadrado o se puede generar un valor absoluto del mismo. Por ultimo calculamos en la celda E3 el valor promedio de los errores, También podemos hacer una suma de cuadrados si queremos simplificar la plantilla.
Hecho lo anterior tenemos lista nuestra plantilla para calcular pronósticos.
El valor pronosticado para el mes de noviembre es de 1299.502 con un error promedio de 17,908,412. Es un pronostico muy alejado al comportamiento que se ha tenido en los meses anteriores, veamos que sucede cuando cambiamos los valores de alfa y beta.
Para:
α = 0.7
β= 0.4
Obtenemos los siguientes resultados:
Con el cambio de los valores de los parámetros pudimos minimizar el error a 170,012,372 calculando un pronostico para noviembre de 4669.07.
Graficando ambas series de datos podemos observar que tan apegados están los pronósticos de los datos reales, la idea es que ambas líneas reflejen el mismo comportamiento lo máximo posible.
[visualizer id=»286″]
Podríamos estar jugando con dichos valores todo el tiempo que queramos hasta minimizar el error al máximo, sin embargo tenemos herramientas como Solver que nos ayudan a minimizar o maximizar variables en fracciones de minutos.
Hagamos un ejemplo con la herramienta de Solver. Si aún no tienes habilitada puedes ir a mi post: Cómo habilitar Solver en Excel
Nos iremos a la pestaña de Datos y nos localizaremos en la sección de Análisis
Se nos abrirá una ventana en la cual vamos a definir los parámetros que necesita Solver para ejecutarse.
El objetivo será la celda en donde se encuentre nuestro error, utilizaremos el método de minimización ya que lo que deseamos es reducir el error generado del pronostico respecto a los datos reales, para ello cambiaremos las celdas en donde se encuentren nuestros parámetros alfa y beta.
Recordemos que este método tiene 2 restricciones, la primera es que ambos parámetros α y β deben ser menores o iguales a 1 (<=1) y la segunda que las mismas deberán ser mayores o iguales a 0 (>= 0). Dichas restricciones las deberemos agregar por separado en el campo sujeto a restricciones.
Por ultimo el método de resolución que utilizaremos será el GRG Nonlinear (Generalized Reduced Gradient), este método es rápido pero depende mucho de las condiciones iniciales, sin embargo es adaptable a funciones no lineales a diferencia del método Simplex LP
Una vez configurados los parámetros bastará con dar click en el botón de resolver.
Una vez que termine de ejecutar las posibles soluciones para la celda objetivo (en la parte inferior izquierda podemos ver dichos valores en tiempo real) nos abrirá la ventana de resultados.
Para este ejercicio los valores de α y β que asigno fue de 0.91 y 0 respectivamente para el valor mínimo del error (celda objetivo). Podemos guardar este escenario si es que lo vamos a ejecutar en repetidas ocasiones manualmente. Aceptaremos la solución indicando que conserve dicho valor.
Con los valores calculados por Solver nuevamente graficamos y vemos que el comportamiento de la serie de datos pronosticados se ajusta más al de los datos reales.
[visualizer id=»301″]
Como se puede observar los datos pronosticados se asemejan mucho en comportamiento a los datos reales, con esto podríamos decir que el ejercicio ha terminado, pero…. ¿Qué pasa cuándo tengo más de un producto a pronosticar? Tal vez 20, o 100 ¿Qué pasaría si tengo más de 500 productos? Está pregunta inevitablemente surgirá en algún momento, pues en realidad sería sumamente tardado abrir el formulario de Solver para una lista extensa de productos (serie de datos), pues bien, ahí es donde llega la aplicación de Macros con Visual Basic para Aplicaciones (VBA)
Para ello deberemos habilitar el Editor de Visual Basic que aparece en la pestaña de Programador. La activación de este editor es bastante sencillo, bastará con ir a Archivo -> Opciones -> Personalizar cinta de opciones -> Programador
Una vez habilitado abriremos el editor de VBA.
Una vez en la interfaz de desarrollo procedemos a crear un modulo el cual nombraremos cómo pronostico. Para crearlo solo debemos dar click derecho en el navegador de la interfaz y dar click en insertar modulo.
Iniciaremos definiendo un macro para realizar pronósticos individuales de manera rápida, ya que no siempre vamos a querer pronosticar todos los productos, si no simplemente verificar ciertos productos.
Decidimos no crear una función ya que una subrutina es más practica para lo que se desea realizar.
A este macro le llamaremos individual.
Sub individual() 'code...' End Sub
Solver en VBA es muy fácil de usar, toda la información de las diferentes funciones que tiene y sus parámetros se pueden encontrar en el sitio oficial de Microsoft: Using the Solver VBA Functions
Realizamos una optimización sencilla.
Sub individual()
SolverOk SetCell:="$E$" & ActiveCell.Row, MaxMinVal:=2, ValueOf:=0, ByChange:="$C$" & ":$D$" & ActiveCell.Row, _ Engine:=1, EngineDesc:="GRG Nonlinear"
SolverAdd CellRef:="$C$" & ":$D$" & ActiveCell.Row, Relation:=3, FormulaText:="0"
SolverAdd CellRef:="$C$" & ":$D$" & ActiveCell.Row, Relation:=1, FormulaText:="1"
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1
End Sub
Revisemos los parámetros que estamos definiendo:
SetCell: Es igual a la celda objetivo , en nuestro ejercicio se encuentra en la columna G, como la fila será variable nos apoyamos de la propiedad ActiveCell (Celda Activa) y Row (Fila) para definir que la celda objetivo será la localizada en la columna G y en la fila de la celda que tengamos seleccionada.
MaxMinVal: Corresponde al tipo de optimización que haremos, si será para Maximizar, Minimizar o para un valor constante definido, este parámetro tiene que ser expresado por un valor entero en función a los siguientes casos:
| MaxMinVal | Optimización |
|---|---|
| 1 | Maximizar |
| 2 | Minimizar |
| 3 | Valor especifico |
ValueOf: Este siempre será 0 a excepción que el tipo de optimización indicado sea del tipo 3, en ese caso se deberá colocar el valor numérico constante como objetivo para coincidir.
ByChange: Será el rango de celdas variables a cambiar para optimizar la celda objetivo. En este caso serán las celdas en donde se encuentren nuestros parámetros α y β
Engine: Al igual que el MaxMinVal se tiene que definir un valor constante, dependiendo del método:
| Engine | Método |
|---|---|
| 1 | GRG Nonlinear |
| 2 | Simplex LP |
| 3 | Evolutionary |
EngineDesc: Es otra forma de definir el método de resolución a utilizar solo que en lugar de utilizar un valor numérico utiliza una cadena de texto (String).
Recordemos que nuestros parámetros α y β están sujetos a dos restricciones, que no sean menores a 0 y que no sean mayores a 1, para indicar dichas restricciones utilizamos la función SolverOk.
Esta función tiene tres parámetros a definir:
CellRef: Será la celda o rango que estará sujeto a la restricción, en nuestro caso son las celdas que contienen nuestros parámetros α y β.
Relation: Será la relación aritmética de la restricción, está deberá ser un valor numérico entero en función a la siguiente tabla:
| Relation | Relación aritmética |
|---|---|
| 1 | <= |
| 2 | = |
| 3 | >= |
| 4 | Solo números enteros |
| 5 | Solo valores 0 o 1 |
| 6 | Números enteros y diferentes |
FormulaText: Valor a comparar la relación aritmética.
Finalmente vamos a resolver el modelo, para ello utilizaremos dos funciones: SolverSolve y SolverFinish.
El parámetro UserFinish de la función SolverSolve lo hemos definido como verdadero para que nos arroje el resultado sin desplegar los detalles del resultado en un cuadro de dialogo, y el valor KeepFinal de la función SolverFinish lo establecemos en el valor 1 para que mantenga los valores de α y β determinados por la solución final remplazando los valores que tenían dichas celdas antes de la ejecución de Solver. Lo hacemos de esta forma para optimizar el script y no requerir intervención por parte del usuario.
Ya está lista nuestra función para realizar pronósticos, ahora hay que programar un accionador de esa función. Dado que todo lo estamos haciendo en función a la fila, realicemos un script que al seleccionar el id del producto nos pregunte si deseamos ejecutar un pronostico.
Para ello nos apoyaremos del método Intersec del objeto Applications el cual resultará muy practico de utilizar en combinación con el evento SelectionChange. de esta forma podremos ejecutar la función individual() cuándo el usuario cambie la selección a una celda dentro de un rango de celdas especifico.
Iniciaremos creando una Subrutina Privada dentro de la Hoja de Datos (Hoja1), llamaremos al evento Worksheet_SelectionChange para que nuestro código únicamente se ejecute cuando haya un cambio en la selección de una celda por parte del usuario o por una macro.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim q As String
If Intersect(Target, Range("B:B")) Is Nothing Then
Exit Sub
Else
q = MsgBox("¿Realizar Pronostico?", vbYesNo + vbQuestion, "Pronosticos Solver")
If q = vbYes Then
Call pronostico.individual
End If
End If
End Sub
Vemos que el evento lo llamamos en la primer linea y tiene un argumento llamado Target el cual corresponde a un objeto Range, precisamente este argumento es la nueva celda seleccionada.
El método Intersect básicamente nos retorna la celda resultante de la intersección de dos rangos, dichos rangos los especificamos en sus parámetros, veamos un ejemplo ¿Cuál es la intersección del rango B2:B13 y A3:F3?
La respuesta es sencilla de localizar visualmente, evidentemente es la celda B3, y precisamente el método Intersect nos arrojará el mismo resultado para cualquier conjunto de rangos.
Entonces, ya rescatamos el rango de la celda que hemos seleccionado con el parámetro Target del evento, y también sabemos que si dicha selección esta dentro de la columna B (nombre de nuestro producto) debe ejecutar la subrutina para realizar el pronostico, de tal forma que no nos ejecute la subrutina en todo momento que cambiemos de celda.
Así que bastará con colocar el condicional que se muestra en la linea 5, el cual lo podemos interpretar como: Si no existe una intersección entre la celda seleccionada y el rango fijo de la columna termine la subrutina, si si existe ejecute un cuadro de dialogo preguntando si se desea ejecutar un pronostico.
El resultado de la respuesta del cuadro de dialogo se guardará en la variable q del tipo cadena de texto (string) y en la linea 9 volvemos a utilizar un condicional para llamar a la subrutina individual() en caso que la respuesta del usuario haya sido Si (Yes).
Con esto ya podemos hacer pronosticos individuales para cada producto que seleccionemos. Como se comento anteriormente esto resulta muy util cuando solo queremos optimizar los parámetros α y β de un producto en especifico.
Este post se ha extendido bastante para una sola entrada, vamos a realizar una función para realizar el pronostico de una gran cantidad de productos de manera continua sin la necesidad de que el usuario interactue: