El tutorial muestra cómo realizar pronósticos de series de tiempo en Excel con la función Hoja de pronóstico incorporada y sus propias fórmulas.
En estadística, existen muchas técnicas de pronóstico diferentes y algunas de estas técnicas ya están implementadas en Excel. Este tutorial le enseñará cómo pronosticar en Excel con base en datos históricos y puede ayudarlo a ahorrar dinero en un software de pronóstico especializado 🙂
Pronóstico en Excel
La previsión es una técnica especial para hacer predicciones para el futuro mediante el uso de datos históricos como entradas y el análisis de tendencias.
Este método se usa comúnmente para hacer conjeturas informadas sobre flujos de efectivo, planificar presupuestos, anticipar gastos o ventas futuras, etc. Sin embargo, pronosticar no dice el futuro de manera definitiva, solo muestra probabilidades. Por lo tanto, siempre debe verificar dos veces los resultados antes de tomar una decisión.
Microsoft Excel ofrece algunas herramientas de pronóstico diferentes que incluyen características, funciones y gráficos integrados. Dependiendo de sus necesidades, puede elegir uno de los siguientes métodos:
Cómo pronosticar en Excel usando suavizado exponencial
El pronóstico de suavizado exponencial en Excel se basa en la versión AAA (error aditivo, tendencia aditiva y estacionalidad aditiva) del algoritmo de suavizado triple exponencial (ETS), que suaviza las desviaciones menores en las tendencias de datos pasadas mediante la detección de patrones de estacionalidad e intervalos de confianza.
Este método de pronóstico es más adecuado para no lineal modelos de datos con datos estacionales u otros patrones recurrentes. Está disponible en Excel 2016, Excel 2019 y Excel para Office 365.
Puede hacer un pronóstico de este tipo con sus propias fórmulas o hacer que Excel cree una hoja de pronóstico automáticamente.
Crear una hoja de pronóstico exponencial automáticamente
los Hoja de pronóstico La característica introducida en Excel 2016 hace que la previsión de series temporales sea muy fácil. Básicamente, solo necesita organizar adecuadamente los datos de origen y Excel hará el resto.
Organizar datos
En su hoja de cálculo de Excel, ingrese dos series de datos en columnas adyacentes:
- Serie de tiempo: entradas de fecha u hora que se observan secuencialmente en un intervalo regular como por hora, día, mes, año, etc.
- Serie de valores de datos: valores numéricos correspondientes que se pronosticarán para fechas futuras.
Es importante que su serie de tiempo tenga intervalos iguales entre los puntos de datos. Por ejemplo, puede proporcionar intervalos semanales con valores todos los lunes, intervalos mensuales con valores el primer día de cada mes, etc.
En teoría, su serie de tiempo puede perder hasta el 30% de los puntos de datos o tener varias entradas con la misma fecha u hora, y Excel aún hará el pronóstico correctamente. Sin embargo, se recomienda resumir sus datos para obtener predicciones más precisas.
En este ejemplo, intentaremos pronosticar las ventas para los próximos años en función de los siguientes datos históricos. Preste atención a que la columna A contiene fechas (el 1 de cada mes) en un formato personalizado que muestra solo el mes y el año. Sin embargo, estas son fechas totalmente funcionales, no valores de texto.
Creación de una hoja de previsión
Con las dos series de datos en su lugar, lleve a cabo los siguientes pasos para construir un modelo de pronóstico:
- Seleccione ambas series de datos. En la mayoría de los casos, es suficiente seleccionar solo una celda en cualquiera de sus series y Excel recoge el resto de los datos automáticamente.
- Vaya a la pestaña Datos > grupo Pronóstico y haga clic en el Hoja de pronóstico botón.
- La ventana Crear hoja de trabajo de pronóstico muestra una vista previa del pronóstico y le pide que elija:
- Tipo de gráfico: gráfico de líneas (predeterminado) o de columnas
- Fecha de finalización de la previsión
- Cuando haya terminado, haga clic en el Crear botón.
Excel crea inmediatamente una nueva hoja que contiene una tabla con sus valores originales y pronosticados, así como un gráfico que representa visualmente estos datos.
Así es como se ve nuestro pronóstico de ventas de Excel:
Para averiguar cómo se calculan las predicciones, haga clic en uno de los valores futuros en su hoja de trabajo de pronóstico e investigue la fórmula. O echa un vistazo Fórmulas de pronóstico de suavizado exponencial para una descripción general rápida.
Personalización del pronóstico de Excel
Si desea cambiar cualquiera de las opciones predeterminadas de su pronóstico, haga clic en Opciones en la parte inferior izquierda de la ventana Crear hoja de trabajo de pronóstico y configure los siguientes ajustes de la manera que necesita:
Inicio de previsión – la fecha de inicio de la previsión. Puede seleccionar una fecha del selector de fechas o escribirla directamente en el cuadro.
- Si sus datos son estacionalse recomienda iniciar un pronóstico antes del último punto histórico.
- Para ver qué tan bien las predicciones coinciden con los valores conocidos, elija una fecha antes del final de los datos históricos. En este caso, solo se utilizarán para la previsión los datos anteriores a la fecha de inicio (este método de prueba retrospectiva también se conoce como retrospectiva).
Intervalo de confianza – un rango en el que se espera que caigan las predicciones. En el gráfico de líneas, está representado por las dos líneas más finas a cada lado de la línea de pronóstico; en el gráfico de columnas – por los valores de la barra de error.
El intervalo de confianza puede ayudarlo a comprender la precisión del pronóstico. Un intervalo más pequeño indica más confianza para un punto específico. El nivel predeterminado es 95 %, lo que significa que se espera que el 95 % de los puntos futuros se encuentren dentro del rango.
Puede marcar y desmarcar la casilla Intervalo de confianza para mostrarla u ocultarla. Y puede cambiar el valor predeterminado usando las flechas hacia arriba o hacia abajo.
estacionalidad – la duración del patrón estacional en el que se producen fluctuaciones de datos regulares y predecibles. Por ejemplo, en un patrón anual donde cada punto de datos representa un mes, la estacionalidad es 12.
Excel identifica el ciclo estacional automáticamente pero también le permite configurarlo manualmente. Cuando Excel no puede detectar la estacionalidad (por lo general, con menos de 2 ciclos de datos históricos), las predicciones vuelven a una tendencia lineal.
Incluir estadísticas de previsión – información estadística adicional sobre la previsión. Marque esta casilla si desea que Excel genere una tabla de estadísticas adicionales, como constantes de suavizado (Alpha, Beta, Gamma) y métricas de error (MASE, SMAPE, MAE, RMSE). Todos estos valores se calculan utilizando la función FORECASE.ETS.STAT.
Rango de línea de tiempo – el rango utilizado para su serie de línea de tiempo. De forma predeterminada, incluye todas las fechas en su tabla de origen, pero puede cambiarlo aquí.
Rango de valores – el rango utilizado para su serie de valores. Debe coincidir con el rango de la línea de tiempo.
Rellenar puntos faltantes usando – controla cómo se manejan los puntos que faltan. De forma predeterminada, Excel utiliza el enfoque de interpolación en el que los puntos que faltan se rellenan en función del promedio ponderado de los puntos vecinos. Como alternativa, puede seleccionar Ceros para tratar los puntos que faltan como valores cero.
Duplicar agregados usando – determina cómo se calculan los valores múltiples con la misma marca de tiempo. La opción predeterminada es el promedio, pero puede elegir cualquier otro método de cálculo de la lista, por ejemplo, Mediana, Máx. o Mín.
Fórmulas de pronóstico de suavizado exponencial
Una hoja de pronóstico creada por Excel contiene dos columnas con sus datos originales (serie de línea de tiempo y la serie de datos correspondiente) y tres columnas calculadas (valores de pronóstico y dos límites de confianza).
Naturalmente, nada le impide construir un modelo de pronóstico similar utilizando las siguientes fórmulas.
Valores pronosticados (función FORECAST.ETS)
Los valores futuros se calculan con la función FORECAST.ETS, que tiene la siguiente sintaxis:
PRONÓSTICO.ETS(fecha_objetivo, valores, cronograma, [seasonality], [data_completion], [aggregation])
Para obtener una explicación detallada de cada argumento, consulte Uso de la función FORECAST.ETS en Excel.
Para nuestra hoja de pronóstico de muestra, Excel ha creado esta fórmula:
=FORECAST.ETS(A31, $B$2:$B$30, $A$2:$A$30, 1, 1)
Dónde:
- A31 es la fecha objetivo
- $B$2:$B$30 es el rango de valores de datos
- $A$2:$A$30 es el rango de la serie temporal
- 1 en el cuarto argumento (estacionalidad): le dice a Excel que detecte la estacionalidad automáticamente.
- 1 en el quinto argumento (completado de datos): le dice a Excel que complete los puntos que faltan como el promedio de los puntos vecinos.
- Se omite el sexto argumento (agregación), lo que significa que se deben agregar múltiples valores con la misma marca de tiempo usando PROMEDIO.
Intervalo de confianza (función FORECAST.ETS.CONFINT)
Para devolver un intervalo de confianza para el valor de pronóstico en una fecha específica, se usa la función FORECAST.ETS.CONFINT.
Para nuestro conjunto de datos de muestra, el intervalo de confianza se puede calcular con esta fórmula:
=FORECAST.ETS.CONFINT(A31, $B$2:$B$30, $A$2:$A$30, 0.95, 1, 1)
Dónde:
- A31 es la fecha objetivo
- $B$2:$B$30 es el rango de valores de datos
- $A$2:$A$30 es el rango de la serie temporal
- 0,95 – el nivel de confianza es igual al 95%.
- 1 en el 5to argumento (estacionalidad) – detección automática de estacionalidad.
- 1 en el sexto argumento (completar datos): los puntos que faltan se completan en función del promedio de los puntos vecinos.
- Séptimo argumento omitido (agregación): agregue múltiples valores de datos con la misma marca de tiempo mediante la función PROMEDIO.
En la hoja de pronóstico creada automáticamente, Excel no genera el valor del intervalo de confianza. En su lugar, utiliza la función FORECAST.ETS.CONFINT en combinación con el valor de pronóstico para calcular los límites de confianza, siempre que la casilla Intervalo de confianza esté marcada en la sección Opciones.
Para obtener el límite inferiorresta el intervalo de confianza del valor pronosticado:
=C31 - FORECAST.ETS.CONFINT(A31, $B$2:$B$30, $A$2:$A$30, 0.95, 1, 1)
Para obtener el límite superioragrega el intervalo de confianza al valor pronosticado:
=C31 + FORECAST.ETS.CONFINT(A31, $B$2:$B$30, $A$2:$A$30, 0.95, 1, 1)
Donde C31 es el valor pronosticado devuelto por FORECAST.ETS.
Cómo pronosticar usando regresión lineal en Excel
Para datos sin estacionalidad u otros ciclos, puede predecir valores futuros mediante la regresión lineal. Este método también es adecuado para conjuntos de datos pequeños y simples que no tienen suficientes datos históricos para detectar la estacionalidad.
Microsoft Excel no proporciona una función integrada para realizar pronósticos lineales automáticamente, pero tiene una función especial para esto, más precisamente, dos funciones: PRONÓSTICO y PRONÓSTICO.LINEAL.
Ambas funciones tienen el mismo propósito, sintaxis y devuelven los mismos resultados. La diferencia es solo en las versiones de Excel:
- En Excel 2016 y Excel 2019, ambas funciones están disponibles, pero se recomienda utilizar la versión más nueva de FORECAST.LINEAR.
- En Excel 2013, 2010 y 2007, solo está disponible la función PRONÓSTICO.
La explicación detallada de la sintaxis de las funciones se puede encontrar en este tutorial: Cómo usar la función PRONÓSTICO en Excel. Por ahora, concentrémonos en un ejemplo de pronóstico de línea.
Fórmulas de pronóstico lineal
Suponga que tiene los datos de ventas del año anterior y desea predecir las ventas de este año. Con solo un ciclo de datos históricos, Excel no puede identificar un patrón de estacionalidad, por lo que el suavizado exponencial no es una opción. Bueno, hagamos un pronóstico lineal con una de estas fórmulas:
En Excel 2016 y 2019:
=FORECAST.LINEAR(A14, $B$2:$B$13, $A$2:$A$13)
En Excel 2013 y versiones anteriores:
=FORECAST(A14, $B$2:$B$13, $A$2:$A$13)
Dónde:
- A14 es la fecha objetivo
- $B$2:$B$13 es el rango de valores de datos
- $A$2:$A$13 es el rango de la serie temporal
Preste atención a que bloqueamos ambos rangos con referencias de celdas absolutas para evitar que cambien cuando copiamos la fórmula en la columna.
Entonces, ingrese una de las fórmulas anteriores en cualquier celda vacía en la fila 14, arrástrela hacia abajo a tantas celdas como sea necesario y obtenga este resultado:
Gráfico de pronóstico de regresión lineal
Para comprender mejor las estrategias futuras, puede representar visualmente los valores pronosticados en un gráfico de líneas.
Para dibujar un gráfico de pronóstico lineal como el que se muestra en la siguiente captura de pantalla, esto es lo que debe hacer:
- Copie el último valor de datos históricos al Pronóstico En este ejemplo, copiamos el valor de B13 a C13. Esto nos ayudará a lograr el efecto de una línea continua e ininterrumpida.
- Seleccione 3 columnas de datos: serie temporal, valores de datos históricos y valores pronosticados.
- En la pestaña Insertar, en el grupo Gráficos, haga clic en el icono Insertar gráfico de líneas o de áreas y elija el primer tipo de gráfico (Líneas 2D).
¡Hecho!
Así es como se hacen pronósticos en Excel. Para ver más de cerca los ejemplos discutidos en este tutorial, puede descargar nuestro Libro de muestra de pronóstico de Excel.
¡Gracias por leer y espero verte en nuestro blog la próxima semana!