Suavización Exponencial General en Excel (GESMTH)

Escena 1:

Hola y bienvenidos a las series de tutoriales de suavizado exponencial. En este tutorial, les presentaremos nuestra última adición a la familia de funciones de suavizado exponencial, la función de suavizado exponencial general GESMTH. No solamente soporta modelos de suavizado anteriores cubiertos por las funciones de suavizado exponencial triple, doble y simple. Sino que implementa tipos adicionales para componentes de tendencia y de estacionalidad. Además el suavizado exponencial general soporta datos anteriores a la transformación y el ajuste de Chatfield para auto correlación de primer orden en los errores de pronóstico. En resumen el suavizado exponencial general es la principal herramienta de nuestro arsenal para suavizar exponencialmente nuestros datos y proyectar un pronóstico.

En este vídeo cubriremos los modelos de suavizado de un conjunto de datos estacionales y de tendencia usando los distintos tipos de estacionalidad y de tendencia y la optimización automática incorporada para los parámetros de suavizado.

Para los datos usaremos la generación neta mensual de energía en US usando gas natural entre enero de 1989 hasta marzo del 2001. Los datos fueron obtenidos de la Agencia de Información de Energía de US y las unidades se expresan en millones de megawatts por hora.

Examinemos los datos a lo largo del tiempo junto con la media móvil de 24 meses y vamos a extraer algunas observaciones. Que la serie de tiempo definitivamente exhibe estacionalidad anual. Que aunque los altibajos de las señales estén cambiando con el tiempo la serie de tiempo nos sugiere una relación entre niveles. En otras palabras a medida que el nivel se incrementa los picos y puntos mínimos no cambian en concordancia y que el nivel de generación de energía neta está creciendo aproximadamente como una tendencia lineal.

Ahora tenemos una idea de qué es con lo que estamos tratando aquí, así que empecemos. Para la ilustración hacemos un listado de los argumentos del suavizado exponencial general en las celdas D1 a D1. Así que seleccionemos la celda D18. Escribamos el nombre de la función GESMTH en la celda o en la barra de herramientas de la ecuación. No tenemos que la herramienta de autocompletado en Microsoft Excel nos ayudará a encontrar la función correcta. Una vez encontremos la función de suavizado exponencial general, hagamos clic en el botón FX localizado a la izquierda de la barra de herramientas de la ecuación.

Escena 2:

Esto invocará el cuadro de diálogo con los argumentos de la función para el suavizado exponencial general.

Primero, especificamos el ingreso del rango de las celdas. Lo que hemos la referencia de la celda presionando F4. En el campo de orden especifiquemos 1 o verdadero para el orden de tiempo en las series. Esto designa que la primera observación en las series de ingreso corresponde a la fecha más temprana y yo que el tiempo en nuestras series de tiempo progresa en descenso.

Para el nivel de parámetro de suavizado acá alfa, podemos ingresar un valor entre 0 y 1, dejar un espacio en blanco o hacer referencia a una celda existente. En este ejemplo hagamos referencia el valor en D1 y bloqueemos la celda de referencia presionando F4.

Para el parámetro de suavizado de tendencia, beta podemos también ingresar un valor entre 0 y 1, dejar un espacio en blanco o hacer referencia a una celda existente. En este ejemplo hagamos referencia al valor en D2, bloqueemos la celda de referencia presionando F4.

Para gamma o los parámetros de suavizado con índices estacionales debemos ingresar un valor entre 0 y 1, dejar un espacio en blanco o hacer referencia a una celda existente en nuestra hoja de cálculo.

Para consistencia hagamos referencia al valor en D3, bloqueemos la celda de referencia presionando F4.

Escena 3:

Ahora para el coeficiente de amortiguado de tendencia Fi y debemos ingresar un valor entre menos uno y uno, dejar un espacio en blanco o hacer referencia a una celda en nuestra hoja de cálculo. Hagamos referencia al valor en D4, bloqueemos la celda de referencia presionando F4.

Para el parámetro de valor del periodo de corrección de auto correlación desactive, acá lambda, podemos escribir un valor entre 0 y 1, dejar un espacio en blanco o hacer referencia a una celda en nuestra hoja de cálculo. Hagamos referencia al valor en D5, bloqueemos la referencia de la celda presionando F4.

Para el tipo de tendencia, necesitamos escoger un valor entre 0, sin tendencia a 4 amortiguado multiplicativo, dejar un espacio en blanco que no tenga tendencia, o hacer referencia a una celda en nuestra hoja de cálculo.

Escena 4:

De nuevo hagamos referencia a otra celda, D6. Bloqueemos la celda de referencia presionando F4.

Para el argumento de tipo de estacionalidad debemos dejar un espacio en blanco, acá no, ingresar 1 o hacer referencia a la celda en la hoja de cálculo.

Para consistencia con el resto de los argumentos hagamos referencia a D7, bloqueemos la celda de referencia presionando F4.

Para longitud estacional, podemos dejar un espacio en blanco indicando la no estacionalidad, ingresar un valor para los datos mensuales, eje 12 o hacer referencia a una celda. Hagamos referencia al valor en la celda D8. Bloqueemos la celda de referencia presionando F4.

Para habilitar o inhabilitar el optimizador incorporado, podemos ingresar verdadero o falso, dejar un espacio en blanco o hacer referencia al valor en la hoja de cálculo. Hagamos referencia al valor en D9, bloqueemos la celda de referencia presionando F4.

Escena 5:

Para habilitar o deshabilitar la corrección de auto correlación de Chatfield, ingresemos verdadero o falso, dejemos un espacio en blanco o hagamos referencia al valor en D10. Bloqueemos la celda de referencia presionando F4.

Para transformación de datos ingresados previamente, hagamos referencia al valor en la celda D11, bloqueemos la celda de referencia presionando F4.

Para el tiempo de pronóstico, pronosticar hemos el valor al final de los datos de ingreso. Así que programemos te como 0 o mejor hagamos referencia a la celda en la columna A de manera que podamos cambiarla luego del final de los datos de ingreso. Bloqueemos la celda para el movimiento de la columna presionando F4 hasta que el signo de dólar aparezca a la izquierda.

Para el tipo de retorno, dejemos en blanco o ingresemos 0 para el pronóstico, y luego hagamos clic en aceptar.

Escena 6:

La función devuelve no aplica ya que no tenemos datos suficientes. Copiamos las celdas de abajo incluso hasta el final de la muestra de datos. No se preocupen por las celdas vacías que fueron seleccionadas luego del final de los datos de ingreso. La función las ignorará.

Escena 7:

Antes de que podamos comparar otros modelos de suavizado, cuantificamos la exactitud del pronóstico de las nuevas series.

Para esta ilustración usaremos tres medidas de comportamiento de pronóstico. Para la medida absoluta usaremos el error cuadrática medio (MSE por sus siglas en inglés), para el error de porcentaje escogeremos error porcentual absoluto medio simétrico (MAPE por sus siglas en inglés), y para el error de escala relativa relativo al modelo primitivo estacional y el período de 12 meses usaremos el error escalado absoluto (MASE por sus siglas en inglés). Estas funciones hacen parte en NumXL y están disponibles como funciones de la hoja de cálculo.

Primero examinemos un modelo sin tendencia y sin estacionalidad aka suavizado exponencial simple de Brown. Programamos el tipo de tendencia como 0 para indicar que no hay tendencia en D6 y el tipo de estacionalidad como 0 en D7. Dejemos el valor en D8 impactos ya que el suavizado exponerse al general simplemente lo ignorará. La hoja de cálculo auto calcula todos los valores y la gráfica refleja las nuevas series. No se ve muy bien. Mirando con más detenimiento la medida de exactitud de pronóstico. Vemos que MASE muestra que se comporta peor que el pronóstico primitivo simple, un 29% peor. MAPE nos muestra que el error de porcentaje promedio está alrededor de 14%. Antes de que podamos examinar modelos estacionales, que construyamos la señal de suavizado y el pronóstico entre nivel tendencia en índices estacionales de manera que podamos entender lo que se ajusta para cada modelo. Copiamos la fórmula D18 a E18, seleccionemos E18 y presionamos el botón FX. Cambiemos el rango de las celdas de ingreso para cubrir todos los ingresos desde C18 hasta C356.

Escena 8:

Presionemos F4 para bloquear todo el rango de celdas. Cambiemos el tiempo del pronóstico a un valor que equivalga el número de pasos para nuestro pronóstico más allá del final del rango de celdas de ingreso. Para nuestra ilustración estamos pronosticando 41 meses posteriores al mes de marzo de 2017.

Escena 9:

Presionemos F4 para bloquear la celda. Finalmente, cambiamos el tipo de retorno de 0 o espacio en blanco a 6 para el componente de nivel. Hagamos clic en aceptar. 

Escena 10:

La función devuelve no aplica, esto está bien ya que es el primer valor en la matriz. Ahora empezando desde la celda E18 seleccionemos todas las celdas hasta la a 397. Presionemos F2 para editar la fórmula. Presionemos control más hits más enter, esto poblará los valores de nivel.

Escena 11:

Repitamos los mismos pasos para los índices de tendencia y estacionalidad en las columnas F y G. Notemos que ambas series devuelven no aplica puesto que todavía no hemos especificado tendencia o estacionalidad en el modelo.

Escena 12:

Graficemos los índices de nivel, tendencia y estacionalidad en nuestra hoja de cálculo.

Escena 13:

OK adicionamos un componente de estacionalidad aditiva, programemos el tipo de estacionalidad como uno en D7. De nuevo la hoja de cálculo se calcula todos los valores de pronóstico y la gráfica está actualizada, se ve mejor ahora que antes examinando la medida de exactitud. MASE se muestra que se comporta mejor que el modelo primitivo, 31% mejor. MAPE muestra el mejor error absoluto promedio 8% de 14%. Examinemos visualmente el pronóstico. Pronóstico parece encajar bien en los datos y el pronóstico de muestra arrojado se ve de alguna manera bajo.

Escena 14:

Examinemos ambos componentes para estabilidad el de nivel y el de estacionalidad. El componente estacional se ve estable sobre la duración de la muestra y el pronóstico de tiempo. El componente de nivel es un poco ruidoso. Este es un buen comienzo.

Escena 15:

Ahora incluyamos un tipo de tendencia aditivo dentro del modelo.

Escena 16:

Programamos el valor en D6 como 1, una vez que excel recalcular la hoja de cálculo examinemos los resultados. MASE ha mejorado 8% y MAPE permanece igual en 8%. Examinemos ambos componentes para esta habilidad el de nivel y el de estacionalidad.

Escena 17:

El componente estacional se ve estable sobre la duración de la muestra y el pronóstico de tiempo. El componente de nivel es un poco ruidoso. Este es un buen comienzo. Qué tal una tendencia aditiva amortiguada? Cambiemos el tipo de tendencia en D6 a 2.

Escena 18:

Una vez que Excel termina de recalcular examinemos los resultados. MASE se puso peor, 3% peor que anteriormente.

Escena 19:

Examinemos ambos componentes para esta habilidad, el de nivel y el de estacionalidad. El componente de índices estacionales exhibe variaciones con respecto a la duración de la muestra y el pronóstico de tiempo. Esto no es bueno. La tendencia con virgin 0 después de lo cual permanece como 0. El nivel es un poco ruidoso. Esto no es una mejora.

Escena 20:

Cambiemos el tipo de tendencia a una adictiva, programamos el valor en D6 como 1. Qué tal una estacionalidad multiplicativo? Programemos el valor del tipo de estacionalidad igual a dos en la celda D7. Luego de que la hoja de cálculo complete su calcule acción automática, notemos que la medida de exactitud, MASE empeoró.

Escena 21:

Además examinemos los componentes y notemos la variación de los índices estacionales a lo largo de la muestra. Esto no es una mejor.

Escena 22:

Programamos el tipo de estacionalidad como aditiva programando el valor en D7 como 1.

Escena 23:

Ahora intentemos usar el ingreso transformado para los datos. Programamos el valor en D11 como verdadero o 1.

Escena 24:

Luego de que el cálculo de la hoja de cálculo se complete, notemos que las medidas de exactitud MASE mejoraron 71% mientras que MAPE retrocede a 8%.

Escena 25:

Examinemos los componentes de las series. El nivel se ve un poco ruidoso pero los índices estacionales se ven estables a lo largo de la muestra y el horizonte de pronóstico.

Escena 26:

Ahora tratemos una tendencia aditiva amortiguada. Programemos el valor en D6 como 2.

Escena 27:

La medida de exactitud no cambió mucho, pero la muestra de pronóstico arrojada no marca tendencia. Hay otro parámetro más que no hemos usado hasta el momento, la corrección de Chatfield. La corrección de Chatfield para auto correlación de primer orden en los modelos de valores de pronóstico. Tratemos de usarla.

Programamos el ingreso de transformación como falso o cero para deshabilitar lo. Programamos el tipo de tendencia como aditiva, ingresemos 1 en las celdas 6.

Programamos el periodo de corrección ajuste de Chatfield como verdadero o 1 en la celda D10.

Escena 28:

Una vez la re calcule acción de la hoja de cálculo se complete, notemos como MASE y MAPE mejorado significativamente. Esto es una mejoría.

Escena 29:

Examinemos los componentes. Nivel y tendencia se ven ruidosos y los sims es de estacionalidad están estables.

Escena 30:

Examinemos el patrón de pronóstico fuera de la muestra. El modelo proyecta un cambio en niveles generales avanzando hacia adelante. Que tan razonable es esto? Una intuición de la materia es clave aquí.

Escena 31:

Asumamos que estamos en desacuerdo con el modelo anterior así que escojamos uno diferente. Programamos el tipo de tendencia como multiplicativo programando el valor en D6 como 3. Esperemos a que el recalculado de la hoja de cálculo se complete. Luego examinemos los resultados.

Escena 32:

La exactitud de pronóstico permanece en los mismos niveles, MAPE 7% y MASE 62%. Lo cual es bueno. Los componentes de nivel y de tendencia son relativamente suaves y estables. Los índices de estacionalidad varían a lo largo de la muestra, pero son estables. El pronóstico fuera de la muestra proyecta un crecimiento en niveles generales.

Podemos combinar el ingreso transformado con la corrección de Chatfield? Seguro que sí pero esto se lo dejaremos a ustedes para que experimenten por su cuenta.

NumXL toma todos los números pesados de manera que el experto en la materia tenga el tiempo para ejercer su intuición filtrando a través de las diferentes alternativas hasta llegar a la que tenga más sentido. Que sigue desde aquí en este tutorial solamente hemos hablado del suavizado exponencial general de una serie de tiempo con un patrón estacional.

Muchas series de tiempo que encontramos en la práctica exhiben distintas variaciones estacionales, por ejemplo para demanda de energía, muestra patrones diarios semanales y mensuales. Además lo hemos incluido ningún factor exógeno como el nivel del precio de combustible, capacidad disponible generación de energía para gas natural y otras fuentes como energía hídrica, el viento, el carbón, etcétera.

Escena 33:

Eso es todo por ahora, gracias por vernos!