ESTADÍSTICA EN EXCEL: PRONÓSTICO DE VENTAS CONSIDERANDO LA TENDENCIA, ESTACIONALIDAD E INTERVALOS HD
Summary
TLDREn este video, se enseña a realizar un pronóstico de ventas utilizando modelos de series cronológicas. Se analiza gráficamente los datos de ventas mensuales de 2017 a 2019, se identifican componentes como tendencia y estacionalidad, y se calculan índices estacionales y un modelo de regresión para ajustar la tendencia. Se pronostica para 2020 y se evalúa la significancia del modelo con análisis de regresión en Excel.
Takeaways
- 📈 El video enseña a realizar un pronóstico de ventas utilizando modelos de series cronológicas.
- 📊 Se analiza gráficamente los datos de ventas mensuales desde 2017 hasta 2019 para identificar tendencias y estacionalidad.
- 📝 Se sugiere agregar una variable de tendencia para ajustar un modelo de regresión a los datos.
- 🔍 Se identifica una tendencia creciente en las ventas a lo largo del tiempo y una estacionalidad en los meses de octubre y abril o mayo.
- 📉 En octubre se presentan las ventas más bajas y en abril y mayo las más altas, lo que indica un patrón estacional.
- 🧮 Se calculan índices estacionales para cada mes dividiendo las ventas mensuales por el promedio anual correspondiente.
- 📚 Los índices estacionales se utilizan para ajustar el componente estacional en el pronóstico de ventas.
- ⚖️ Se estiman los componentes de tendencia y estacionalidad para construir un modelo de regresión que predice las ventas.
- 📉 El modelo de regresión incluye un intercepto y una pendiente que representan la tendencia de las ventas a través del tiempo.
- 🔮 Se hace un pronóstico intra-muestral y extra-muestral para las ventas del año 2020 utilizando el modelo de regresión y los índices estacionales.
- 📊 Se utiliza la herramienta de análisis de regresión en Excel para validar el modelo y calcular indicadores como el coeficiente de correlación y el error típico.
- 📉 Se construyen intervalos de confianza para los valores pronosticados, considerando un nivel de confianza del 95%.
Q & A
¿Qué es lo que se enseña en el video?
-El video enseña cómo hacer un pronóstico utilizando modelos de series cronológicas con datos de ventas mensuales de 2017 a 2019.
¿Cuáles son los componentes de una serie cronológica que se pueden analizar en el video?
-Los componentes que se pueden analizar son tendencia, estacionalidad, variaciones cíclicas e irregularidades.
¿Cómo se identifican las variaciones estacionales en los datos de ventas?
-Se identifican observando los meses en los que las ventas presentan comportamientos similares a lo largo de los años, como en octubre, donde las ventas son bajas, y en abril y mayo, donde son altas.
¿Cómo se calculan los índices estacionales en el video?
-Se calculan dividiendo cada observación de un mes en un año entre el promedio de ventas de ese año y luego se promedian los índices mensuales para obtener los índices estacionales.
¿Qué modelo de regresión se utiliza para ajustar la tendencia a los datos de ventas?
-Se utiliza el modelo de regresión por mínimos cuadrados para encontrar la pendiente y el intercepto que ajustan la tendencia a los datos.
¿Cómo se hace el pronóstico intra-muestral en el video?
-Se hace utilizando la fórmula del modelo de regresión (intercepto + pendiente * variable de tendencia) para los datos dentro del rango de observaciones disponibles.
¿Cómo se realiza el pronóstico para el año 2020 en el video?
-Se extiende la variable de tendencia para el año 2020 y se multiplica por los índices estacionales y el modelo de tendencia para obtener los valores pronosticados.
¿Qué herramienta de Excel se usa para analizar la significancia del modelo de regresión?
-Se utiliza la herramienta de análisis de regresión en Excel para calcular indicadores como el coeficiente de correlación, el error típico y la bondad de ajuste del modelo.
¿Qué porcentaje de la variabilidad en las ventas se explica el modelo de tendencia por sí solo?
-El modelo de tendencia por sí solo explica el 41,16% de la variabilidad en las ventas.
¿Cómo se calculan los intervalos de confianza para las ventas pronosticadas?
-Se calculan multiplicando los límites inferiores y superiores de los intervalos de confianza del modelo de regresión por los índices estacionales correspondientes.
¿Cómo se interpreta el resultado del análisis de regresión en el video?
-El resultado se interpreta observando la significancia de los coeficientes, la bondad de ajuste del modelo, y los intervalos de confianza para evaluar la precisión del pronóstico.
Outlines
📊 Introducción al pronóstico con series cronológicas
El video comienza explicando cómo realizar un pronóstico utilizando modelos de series cronológicas. Se presenta una serie de datos de ventas mensuales desde 2017 hasta 2019. Inicialmente, se grafica esta información para analizar componentes de tendencia, estacionalidad, variaciones cíclicas y movimientos irregulares. Se observa que las ventas muestran una tendencia creciente y un patrón estacional evidente en ciertos meses.
📈 Cálculo de índices estacionales
El segundo párrafo se enfoca en el cálculo de los índices estacionales. Se describen los pasos para sumar las ventas anuales y calcular los promedios mensuales para cada año. Luego, se determina el porcentaje de ventas mensuales respecto al promedio anual, obteniendo así los índices estacionales. Finalmente, se verifica que la suma de estos índices sea del 1200%, asegurando su corrección.
📉 Estimación del componente de tendencia
En este párrafo, se detalla cómo estimar el componente de tendencia mediante un modelo de regresión lineal. Se explica el uso de fórmulas en Excel para calcular el intercepto y la pendiente de la tendencia, y se construye el modelo de regresión con estos valores. Posteriormente, se realiza un pronóstico intra muestral y se extiende la tendencia para prever las ventas en 2020.
🔢 Pronóstico y ajuste estacional
El cuarto párrafo trata sobre la combinación del modelo de tendencia y los índices estacionales para obtener el pronóstico final. Se multiplica cada valor estimado de la tendencia por el índice estacional correspondiente, generando así el pronóstico mensual para 2020. Además, se ilustra cómo graficar estos resultados y se menciona la validación del modelo mediante el análisis de regresión en Excel.
🧮 Validación y análisis de resultados
Aquí se profundiza en la validación del modelo de pronóstico. Se calcula el coeficiente de correlación y el coeficiente de determinación, y se analiza la significancia de los estimadores. Se describe cómo obtener intervalos de confianza para los pronósticos y se explica la importancia de estos en la interpretación de los resultados. También se compara el modelo con y sin el componente estacional.
🎬 Conclusión y despedida
El video concluye resumiendo el proceso de creación del pronóstico considerando los componentes de tendencia y estacionalidad. Se invita a los espectadores a compartir el video y suscribirse para más contenido similar. Se enfatiza la utilidad del gráfico de índices estacionales para el análisis referencial.
Mindmap
Keywords
💡Pronóstico
💡Series Cronológicas
💡Tendencia
💡Estacionalidad
💡Regresión
💡Índices Estacionales
💡Mínimos Cuadrados
💡Intercepto
💡Pendiente
💡Intervalos de Confianza
💡Residuales
💡Coeficiente de Correlación
💡Coeficiente de Determinación (R²)
💡Error Típico
Highlights
Se enseña cómo hacer un pronóstico utilizando modelos de series cronológicas.
Se analizan ventas mensuales desde el año 2017 hasta 2019.
Se agrega una variable de tendencia para ajustar un modelo de regresión.
Se analiza si la serie cronológica tiene tendencia, estacionalidad, variaciones cíclicas y movimientos irregulares.
Se identifica un comportamiento estacional en las ventas, especialmente en octubre.
Se asume que las ventas tienen una tendencia creciente a lo largo del tiempo.
Se calculan los índices estacionales para cada mes a través de la suma y promedio de las ventas anuales.
Se utiliza el método del porcentaje promedio para calcular los índices estacionales.
Se calcula la media o mediana de los índices estacionales para obtener los valores mensuales.
Se estima el componente de tendencia utilizando un modelo de regresión.
Se calcula el interceptó y la pendiente del modelo de regresión para ajustar la tendencia a los datos.
Se realiza un pronóstico intra-muestral y extra-muestral para el año 2020.
Se ajusta el pronóstico considerando los índices estacionales y la tendencia.
Se utiliza la herramienta de análisis de regresión en Excel para calcular indicadores del modelo de regresión.
Se calcula el coeficiente de correlación para evaluar la relación entre el tiempo y las ventas.
Se determina la bondad de ajuste del modelo con el R cuadrado.
Se calcula el error típico para construir intervalos de confianza.
Se analizan los residuales y la curva de regresión ajustada para evaluar la calidad del modelo.
Se calculan los intervalos de confianza para los valores estimados y pronosticados.
Se realiza un gráfico de los índices estacionales para analizar los valores referenciales.
Transcripts
hola como estan amigos en este vídeo voy
a enseñarles a hacer un pronóstico
utilizando modelos de series
cronológicas para tal efecto tenemos una
serie de datos que corresponden a las
ventas mensuales desde el año 2017 hasta
el año 2019 lo que podemos hacer en
primer lugar es analizar gráficamente
estos datos para ello volví a copiar los
datos en estas columnas que corresponden
las ventas que el es representado por la
variable i y el periodo que corresponde
a cada una de estas observaciones por
ejemplo para enero del 2017 las ventas
fueron de 300 para febrero del 2017 las
ventas fueron de 180 y así sucesivamente
hasta la última observación que es en
diciembre del 2019 que las ventas son de
460 en todo caso en la primera columna
le agregue lo que es la variable de
tendencia que nos va a servir
posteriormente para la estimación de
precisamente un modelo de regresión que
que ajuste la tendencia a los datos que
tenemos que simplemente es enumerando en
orden cronológico
del 1 hasta la última observación en
este caso son 36 observaciones como
podemos ver lo primero que podemos
analizar es ver si este modelo cuenta
con los componentes que tiene una serie
cronológica es decir si tiene tendencia
estacionalidad variaciones cíclicas y
movimientos irregulares de porcía
podríamos analizar que no podría
distinguirse así tal cual una variación
cíclica ya que las variaciones cíclicas
son comportamientos que se tienen a
largo plazo en cambio la estacionalidad
es un componente de fluctuaciones a
corto plazo por lo que es muy probable
que pueda darse en el comportamiento de
esta variable entonces para analizar
esos componentes vamos a hacer un
gráfico insertamos el gráfico de
dispersión con líneas rectas y
marcadores donde ponemos el valor
observado de las ventas entonces por
como podemos apreciar aquí en el gráfico
en los meses de octubre de 2017 2018 y
2019 podemos apreciar que las ventas
tienen un comportamiento estacional ya
que en este mes es donde se presencia
ventas más bajas en todas las gestiones
por el mes de abril y mayo de 2017 abril
y mayo de 2018 y abril y mayo de 2019 es
cuando se dan las ventas más altas por
lo tanto podríamos asumir que este
modelo evidentemente puede contar con el
componente estacional y así como el
componente de tendencia que en todo caso
podemos apreciar intuitivamente que las
ventas van de forma creciente a lo largo
del tiempo
entonces para estimar primeramente las
variaciones estacionales debemos
calcular los correspondientes índices
estacionales para cada mes de todas las
observaciones del periodo estudiado en
todo caso podemos calcular primeramente
los totales que simplemente es la suma
de las ventas de los años
correspondientes en este caso para el
año 2017 aplicamos la fórmula suma
seleccionamos todos los datos y ponemos
enter para el año 2018 y 19 simplemente
podemos estirar esta fórmula extenderla
hasta dicho año entonces ya tenemos la
suma
el total de las ventas para cada año y
para calcular el promedio simplemente
ponemos la fórmula promedio
y seleccionamos las ventas
correspondientes de todos los meses del
año 2017 ponemos enter y ya tenemos el
promedio de las ventas desde enero hasta
diciembre en todo caso el promedio para
2017 es de 223 y si estiramos esta
fórmula para los demás años ya tenemos
el promedio de las ventas para el año
2018 y 2019 para ajustar el componente
estacional en todo caso podemos emplear
uno de los métodos más sencillos del
cálculo de índices estacionales que es
por el porcentaje promedio en todo caso
lo que debemos hacer es dividir cada
observación de un determinado mes en un
determinado año entre el promedio de ese
año en este caso del 2017 y para la
primera observación dividimos 300 entre
223 punto 33
para truncar este valor del promedio
vamos a poner el signo de dólar al
centro para que cuando extendemos la
fórmula hacia la derecha pueda recorrer
conjuntamente con los años que
requerimos
ahí tenemos un porcentaje mensual de 130
y 4.3 por ciento en enero del 2017 y así
sucesivamente podemos extender la
fórmula hacia abajo y tenemos para todo
el año 2017 y para los años 2018 y 19
hacia la derecha y ya tendríamos todos
los porcentajes mensuales para el índice
estacional lo que podemos hacer es
calcular la media o la mediana de los
porcentajes mensuales correspondientes a
los meses de enero febrero hasta el mes
de diciembre en todo caso puedo utilizar
para este ejemplo el promedio empleando
la fórmula promedio seleccionamos los
porcentajes mensuales que corresponden
al mes de enero y apretamos enter y ya
tenemos el índice estacional que
corresponde al mes de enero y así
podemos estirar la fórmula hasta el mes
de diciembre y ya tenemos todos los
índices estacionales para todos los
meses
sumando estos índices estacionales debe
darnos un total de 1200 % en este caso
no requiere de ninguna corrección ya que
la suma de índices estacionales es igual
a mil doscientos por ciento exactamente
una vez que ya tenemos los índices
estacionales podemos pasar al siguiente
paso que es estimar el componente de
tendencia para tal efecto
podemos agarrar los datos que tenemos
correspondientes a la variable de
tendencia que es la codificación de cada
periodo y la variable ya que
corresponden a las ventas vamos a
estimar un modelo de regresión que
ajuste los datos de las ventas a la
tendencia que existe es decir las ventas
en función de la tendencia por lo tanto
para eso excel tiene fórmulas exclusivas
para aplicar la estimación por el método
de mínimos cuadrados para hallar el
interceptó a la intersección debemos
adoptar la fórmula interceptar o
intersección eje en todo caso aquí
tenemos seleccionamos
los datos que corresponden a las ventas
y los datos que corresponden a la
variable de tendencia es decir la
variable independiente ponemos enter y
tenemos el valor del interceptó de 196
punto 33
podemos omitir el stop entonces para
hallar el valor de la pendiente o el
estimador de la pendiente debemos
adoptar la fórmula estimación línea
seleccionamos las observaciones que
corresponden a las ventas ponemos punto
y coma seleccionamos las observaciones
que corresponden a la codificación de la
variable de tendencia ponemos punto y
coma ahí nos dice que se establece b
igual a cero bueno en todo caso esta vez
hace referencia a lo que nosotros hemos
puesto como a que es el interceptó pero
en todo caso como estamos calculando el
valor del interceptó entonces tenemos
que poner verdadero que cuando el
interceptó se calcula normalmente y esto
lo podemos dejar en blanco porque no
vamos a requerirlo
y ya tenemos ahí el valor de la
pendiente del modelo es de 5.83 entonces
armamos nuestro modelo de regresión que
nos va a servir para la estimación para
el siguiente año que es 196 punto 33 más
5.83 x la variable de tendencia aquí ya
tenemos una columna que nos dice y
estimado y podemos hacer primero el
pronóstico intra muestral que en todo
caso es 196 punto 33 más 5.83
multiplicado por el valor de la variable
de tendencia en todo caso podemos poner
aquí podemos truncar las el valor del
interceptor y de la pendiente ponemos
enter y ya tenemos el valor estimado en
la variable ventas entonces si nosotros
extendemos la fórmula hacia la última
observación ya tendríamos el valor
estimado intra muestral es decir dentro
de las observaciones en consideración y
para realizar el pronóstico
correspondiente para el año 2020 lo
único que debemos hacer es agregar
los elementos que se ven aquí que es
extendiendo la variable de tendencia a
partir de la última observación que
nosotros tenemos si sabemos que para
diciembre de 2019 es igual a 36 para
enero de 2020 va a ser 37 febrero de
2020 va a ser 38 y así sucesivamente
hasta la última observación en la que
nosotros vamos a hacer el pronóstico es
decir en diciembre del 2020 que
corresponde al valor de 48 simplemente
lo que debemos hacer es extender la
fórmula hasta este valor y en todo caso
tendríamos la estimación que corresponde
para el año 2020 una vez que ya hemos
estimado la tendencia lo que prosigue es
que nosotros copiamos los sin el valor
de los índices estacionales a los
valores que queremos pronosticar pegamos
simplemente los valores y ya tenemos
aquí los índices estacionales que
corresponden para cada mes para enero
2020 febrero 2020 y así sucesivamente
posteriormente lo que debemos hacer es
multiplicar cada valor estimado por la
tendencia por el modelo de tendencia
multiplicado por el valor del índice
estacional correspondiente a dicho mes
en este caso al mes de enero que es de
120 puntos 5 por ciento presionamos
enter y ya tenemos el valor pronosticado
para enero de 2020 extendiendo la
fórmula tendríamos todos los valores
pronosticados o estimados para el año
2020 y si nosotros gustamos hacerle un
gráfico en todo caso lo que deberíamos
hacer es graficar primeramente el
periodo de tiempo
la variable ventas y el valor estimado
de las ventas para el año 2020 entonces
insertamos gráfico y ya tendríamos aquí
el pronóstico que está representado por
el color naranja y el valor observado
que está representado por el color azul
en todo caso podemos ver de mejor manera
aquí lo vamos a trasladar que tenemos un
pronóstico para enero del 2020 de 496
ventas las ventas en febrero serán de
354 355 en marzo de 2020 serán de 433 en
abril del 2020 serán de 506 y así
sucesivamente hasta la última
observación que en todo caso es
diciembre de 2020 que serán las ventas
esperadas de 596 y así es como se
realizaría un pronóstico tomando en
cuenta el componente de tendencia y las
variaciones estacionales para ver si
efectivamente el modelo de tendencia es
significativo lo que podemos hacer es
aplicar la herramienta de análisis de
regresión en excel lo que debemos hacer
es irnos a datos análisis de datos y nos
da la opción de regresión donde nos dice
rango y de entrada seleccionamos el
valor observado de las ventas incluyendo
los rótulos para donde nos dice zhang o
x de entrada seleccionamos los valores
de nuestra variable de tendencia podemos
incluir rótulos
no seleccionamos ninguno
podemos poner en una hoja nueva que nos
diga regresión
podemos marcar los residuales el gráfico
de residuales y la curva de regresión
ajustada podemos aceptar y ya calcular
los indicadores correspondientes al
modelo de regresión estimada lo que
podemos rescatar de estos indicadores es
el coeficiente de correlación que en
todo caso nos indica una relación
directa entre el tiempo y las ventas que
nos dice que si tiene un signo positivo
y un valor que se acerca bastante a uno
existe con relación lineal directa es
decir que a medida que pasa el tiempo
las ventas se van incrementando podemos
seleccionarlo como en amarillo los
indicadores que estamos analizando el
ere cuadrado es la bondad de ajustes del
modelo que efectivamente la tendencia
influye en las ventas en un 45 punto 61
por ciento tomando en cuenta que es un
porcentaje bajo no es realmente
importante ya que nosotros le hemos
agregado el componente estacional a
nuestro pronóstico así que no habría
mucho de que preocuparse en este sentido
el error típico es aquella
habilidad que existe alrededor del
modelo de regresión en todo caso nos
puede servir para la construcción de los
intervalos de confianza tanto para los
estimadores como los valores de
pronóstico en todo caso tendríamos para
la estimación los coeficientes de 196
punto 33 y 5.83 que corresponden a el
interceptor y la pendiente
respectivamente podemos apreciar que
ambos indicadores son significativos
debido a que la probabilidad de su
estadístico t es menor a un nivel de
significación que nosotros podríamos
asignarle por ejemplo el 5% en estos
valores son menores en todo caso al 5%
como podemos apreciar de esta forma
también tenemos el valor de los
intervalos de confianza a un 95% del
nivel de confianza que el cero no se
encuentra entre los límites inferior y
superior de ambos estimadores por lo que
podríamos consolidar la significancia de
ambos estimadores y decir que aquel
modelo
efectivamente es es válido de manera
global considerando que el valor crítico
de f es decir la probabilidad del valor
crítico calculado de la distribución f
de fisher también es menor al 5%
tenemos la gráfica de residuales tenemos
aquí el valor de los residuales o
también tenemos el gráfico de la curva
de regresión ajustada solamente a la
tendencia podemos aquí hacerle algunas
modificaciones
aquí también podríamos calcular
manualmente el coeficiente de
correlación que simplemente es anotando
la fórmula de coeficiente de correlación
seleccionando los valores de la variable
de tendencia y los valores observados de
la variable ventas para el coeficiente
de terminación podríamos elevar el
coeficiente de correlación al cuadrado o
simplemente anotar la fórmula
coeficiente r2 seleccionamos los valores
observados de la variable ventas
posteriormente los valores de la
variable de tendencia presionamos enter
y ya tenemos ahí un coeficiente de
determinación del 41
punto 16 % que es el mismo que nos ha
generado automáticamente mediante la
herramienta análisis de datos el error
típico nada más es anotando la fórmula
error típico xy seleccionando los
valores de y posteriormente los valores
de x que en todo caso es la variable de
tendencia para la suma total de
cuadrados de las variables de tendencia
simplemente debemos anotar igual a sumar
cuadrados de la variable de tendencia
restando a el cuadrado de la suma
de los valores de x es decir de la
variable de tendencia elevando al
cuadrado y dividiendo entre el número de
observaciones que en este caso es 36 y
tenemos una suma total de cuadrados de x
de 3.885 y la media de los valores de la
variable de tendencia en todo caso es
promedio de nuevamente 2
valor este que es de 18.5
ahora el valor crítico de tablas que
corresponde a la distribución de the
student aquí podemos
especificar que es a un 0.05 anotar la
fórmula inversión de a dos colas la
probabilidad como dijimos es el 0.05 que
es nuestro nivel de significación y
nuestros grados de libertad es n menos 2
en todo caso 36 menos 2 es decir 34
grados de libertad tenemos un valor
crítico de tablas de 2.03 una vez que ya
hemos definido todos estos indicadores
procedemos a calcular la desviación
estándar que en todo caso es el error
típico de la regresión o es decir el
error estándar de estimación
multiplicado por la raíz de uno más uno
sobre el número de observaciones que es
36 sumado el valor de la variable de
tendencia en el período correspondiente
menos el promedio de la variable de
tendencia que en todo caso es 18.5
elevado al cuadrado y todo esto dividido
entre la suma total de cuadrados de la
variable independiente que es la
variable de tendencia estirando las
fórmulas tenemos las desviaciones
estándar
del valor estimado de la variable ventas
para definir el límite inferior
simplemente restamos el valor estimado
de las ventas menos el estadístico t de
tablas correspondiente a la distribución
td student con 5 % de nivel de
significación y a 34 grados de libertad
x la desviación estándar que corresponde
a cada observación en todo caso para la
primera tenemos un límite inferior de 61
puntos 17 y para el límite superior es
el mismo procedimiento simplemente con
signo positivo
estirando las fórmulas ya tener ya
tendríamos los intervalos de confianza
para los valores estimados los que nos
importan más que todo es los valores
pronosticados
estos valores pronosticados podemos
multiplicar cada límite y ya sea
inferior y superior por los índices
estacionales correspondientes así
teniendo un pronóstico más certero
tomando en cuenta intervalos de
confianza en este caso de un 95% que es
simplemente el restante del nivel de
significación que es del 5% entonces a
un 95% podríamos asumir que las ventas
en enero podrían estar entre los 326 y
los 667 unidades monetarias y así
sucesivamente hasta la última que puede
en la que podríamos interpretar de la
misma forma que en diciembre de 2020 a
un 95% de nivel de confianza
las ventas estarían entre los 409 y 784
unidades monetarias aquí tenemos el
y correspondiente a los intervalos de
confianza donde la línea en amarillo es
el límite superior y la línea en gris es
el límite inferior y como vimos la línea
naranja es el valor estimado central que
es el valor promedio estimado y así es
como se realiza un el pronóstico o una
estimación para las ventas considerando
los componentes de tendencia y de
estacionalidad también podríamos hacer
un gráfico de los índices estacionales
para analizar los valores referenciales
correspondientes a estos índices
entonces eso sería todo por el vídeo de
hoy y no olvides darle like y
compartirlo para más vídeos así nos
vemos en un próximo vídeo
Browse More Related Video
5.0 / 5 (0 votes)