PRÁCTICAS DE SERIES TEMPORALES CON EXCEL
En este texto se presentan un conjunto de cuatro prácticas realizadas sobre unos archivos de datos disponibles en formato Excel.
El objetivo de estas prácticas es mostrar, en cada caso, la sistemática de análisis de los datos utilizando la hoja de cálculo. No pretenden ser un manual de Excel, pero sí dar una información detallada de cómo se puede llevar a cabo el estudio en cuestión, a demás de servir de guía para la realización de la práctica que cada alumno tiene encomendada.
PRÁCTICA 1. DESCOMPOSICIÓN CLÁSICA DE UNA SERIE ADITIVA
OBJETIVO: Se dispone del valor diario de la caja resultante de las ventas de un supermercado a lo largo de 12 semanas. Es necesario analizar los datos de esta serie cronológica, estimar el modelo de comportamiento, estudiar su ajuste y hacer las previsiones necesarias. Todo ello se realizará mediante la hoja de cálculo Excel 97 de Microsoft.
1.1 Recuperacion de los datos
Desde Excel se debe recuperar el archivo que contiene los datos objeto de la práctica, y que se encuentran en el directorio habitual de la red. Para ello, se debe seguir la secuencia (figura 1.1):
Archivo Abrir
y, ahora, ir al directorio donde se encuentra el archivo Práctica 1.xls, seleccionarlo y presionar Abrir.
Fig. 1.1
Una vez tenemos el archivo abierto, observamos que consta de una hoja llamada Datos donde figuran 3 columnas de 72 valores cada una, con la estructura mostrada parcialmente en la figura 1.2. En cada columna hay 72 valores, es decir, cada columna comienza en la fila 1 (con el título) y acaba en la 73.
Fig. 1.2
En primer lugar, se debe preparar una nueva hoja donde es situarán los sucesivos gráficos, y que se denominará Gráficos. Para ello, al hacer doble clic en la pestaña Hoja2 (figura 1.3), esta palabra quedará en vídeo inverso y permitirá escribir Gráficos.
Fig. 1.3
De forma similar, a la Hoja3 la denominaremos Tendencia-Modelo.
1.2 Análisis de la evolución de la serie cronológica
Situados en la hoja Datos, es necesario crear una columna con los valores consecutivos del tiempo y, para mayor facilidad al hacer los gráficos, es bueno que esta columna preceda a la de los valores de las ventas (Y). Hacer clic sobre la letra C del encabezado de la columna que quedará toda negra; pulsando el botón derecho, seleccionar Insertar (figura 1.4). En este momento la columna de los datos se habrá desplazado a la D y habrá dejado la C vacía; aquí es donde se introducirán los valores correlativos del tiempo. En C1 escribir tiempo, hacer C2 = 1 y arrastrar (tecleando también Ctrl) desde C2+ hasta C73; aquí aparecerá el valor 72 (página 108 de esta práctica).
Para obtener el gráfico de la evolución de las ventas frente al tiempo, se selecciona desde C1 hasta C73 (tiempo), y desde D1 hasta D73 (ventas =Y) y se pincha el icono de gráficos
o también, en la barra de herramientas, Insertar y después Gráfico.
Entonces surge el Asistente para Gráficos (figura 1.5), donde se debe seleccionar XY (Dispersión)
Fig. 1.4
En el paso 2 del asistente de gráficos se hace directamente Siguiente y en el paso 3 (figura 1.6), se pueden editar los títulos a voluntad.
Fig. 1.6
Por ejemplo, en la pestaña Títulos
Título del gráfico: Evolución cronológica Eje de categorías (X): tiempo
Eje de valores (Y): ventas
En la pestaña Leyenda
eliminar la marca de Mostrar leyenda, pinchando sobre la misma, para dejar sólo . Siguiente
El paso 4 (figura 1.7), permite situar el gráfico donde se desee, para ello se marca Como objeto en
y pinchando la marca aparece el conjunto de hojas disponibles; allí se selecciona Gráficos.
Finalmente
Fig. 1.7
Con el gráfico seleccionado (de forma que se muestre recuadrado externamente con las marcas en el entorno), se puede situar en el lugar adecuado y darle el tamaño que sea necesario.
Si se quiere editar el gráfico y, por ejemplo, eliminar el fondo gris del mismo: Pinchar sobre este fondo, Área de trazado
Presionar el botón derecho Formato del área de trazado Área
Ninguna Aceptar
Para cambiar la escala del eje vertical y aprovechar toda la superficie de la figura:
Situar el cursor sobre el eje de ordenadas, Eje de valores Hacer doble clic o bien presionar el botón derecho
Formato de ejes (figura 1.8)
Fig. 1.8
Si se quiere cambiar la escala del tiempo, por ejemplo para que vaya de 6 en 6 unidades, que son los valores que forman una semana, hay que situar el cursor sobre el eje de abscisas (Eje de valores (X)) y con el botón derecho seguir los mismos pasos que antes, para dejar un mínimo de 1, un máximo de 78, la unidad mayor a 6 y la menor a 1.
El resultado es el gráfico de la página 111 de esta práctica.
Conclusiones: Se detecta una clara estacionalidad, de período p=6, y posiblemente una
tendencia decreciente.
1.3 Estabilización de la serie
Para poder modelizar la serie, en primer lugar se debe estabilizar calculando las medias móviles de período p; en el caso del ejemplo p=6.
Cálculo de las medias móviles
como eso no lo podemos hacer en la hoja de cálculo optamos por empezar en la casilla 5. Situados entonces en E5, hacemos
= Promedio (D2:D7) ↵↵↵↵ (aparece como resultado 6135)
Arrastramos hasta E71, que contendrá la media de los 6 últimos valores de la serie (Promedio(D68:D73)), en este caso 5256,33.
Al ser de período par debemos volver a la media de 2 en 2: la primera media móvil ocupará el cuarto valor (5ª fila), y la última el 69º
(70ª
fila), ya que en total se pierden 3 valores al inicio y 3 al final. Situados en F5 escribiremos
= Promedio (E5:E6) ↵↵↵↵ (aparece como resultado 6103,75)
Arrastramos hasta F70, que contendrá la media de los 2 últimos valores de la columna anterior (Promedio(E70:E71)), en este caso 5262,33.
Titularemos la columna F, Y móvil, y lo escribiremos en F1. En las páginas 107 y 108 se puede ver el conjunto de valores que resultan.
Gráfico de medias móviles
Seleccionar, manteniendo presionada la tecla Control, desde C2 hasta C73, (tiempo), desde D2 hasta D73, (Y) y desde F2 hasta F73, (Y móvil).
Con el icono de gráficos
Paso 1: Asistente para Gráficos (figura 1.5), XY (Dispersión)
(3; 1), Dispersión con puntos conectados por líneas Siguiente
Paso 2: Siguiente
Paso 3: Poner los títulos, por ejemplo medias móviles (p=6), sacar la leyenda y Siguiente
Paso 4: Situar el gráfico como Objeto en la hoja gráficos. Terminar
Es aconsejable editar el gráfico, tal como se ha hecho con el anterior, para que la escala de ordenadas vaya de cero a doce mil; también se puede cambiar la escala de tiempo como antes. El resultado es el gráfico de la página 111.
1.4 Estacionalidad
El estudio de la estacionalidad incluye el cálculo de los índices estacionales, en modelo aditivo que es el caso del ejemplo, y su representación gráfica.
Cálculo de los índices estacionales
Este cálculo es muy cómodo hacerlo con una tabla dinámica. En primer lugar se deben obtener los valores de W, que son las diferencias entre los valores de la serie (Y, columna C) y las medias móviles (Y móvil, columna E). Estos valores se situarán en la columna G. En la casilla G1 escribir W. Situados en G5, hacer
= D5 – F5 (Y – Y móvil) y arrastrar hasta G70.
En la barra de herramientas
Datos Asistente para tablas dinámicas
Paso 1: ¿Dónde están los datos?
Lista o base de datos de Microsoft Excel Siguiente
Paso 2: ¿Dónde están los datos que desea usar? Rango: $AEL:$G$73 (es la opción por defecto,
que incluye todos los datos) Siguiente
Paso 3: arrastrar W a DATOS y Día a FILA (figura 1.9) Doble clic sobre Contar de W
Promedio
Fig. 1.9
Paso 4: ¿Dónde desea situar la tabla dinámica? (figura 1.10) Hoja de cálculo existente
B80 Indicar una casilla tal que ella y las contiguas estén libres para situar la tabla
El resultado es la siguiente tabla, copiada de la página 108.
A B C D
80 Promedio de W
81 día Total
82 lunes -2331,37
83 martes -939,924
84 miércoles -1963,33
85 jueves 304,7803
86 viernes 3098,348
87 sábado 1898,394
88 Total general 11,14899
Los valores de las casillas C82 – C87 son, respectivamente, E*1, E*2, …, E*6; la casilla C88 (llamada Total general en B88) es la media de las anteriores, o sea, E * .
Para calcular los índices estacionales, en la casilla E81 se escribe Ind. Est. como título, y se define E82 con la expresión
= C82 - $C$88 (anclar la casilla C88 de la media para que no cambie al arrastrar la fórmula)
Arrastrar hasta E87
Los resultados obtenidos se pueden observar en las páginas 107 y 108.
Repetir los valores de la estacionalidad por los 72 valores de la serie. Situados en la columna H, en H1 escribir Ind. Est.
Seleccionar las casillas E82 – E87
Edición Copiar Situarse en H2
Edición Pegado especial Valores Aceptar
Llenar toda la columna (72 valores) cortando de H2 a H7 y pegando sucesivamente desde H8 hasta H73; también se puede hacer marcando como bloque H2−−−−H7 y, presionando la tecla de Ctrl, arrastrarlo desde el extremo inferior derecho del cuadro (+) hasta H73.
Gráfico de la estacionalidad
Seleccionar los valores de los índices estacionales, casillas E82 hasta E87 (o también desde H2 hasta H7).
Con el icono de gráficos
Fig. 1.11
Paso 2: Siguiente
Paso 3: Poner los títulos, por ejemplo Índices estacionales, quitar la leyenda y Siguiente
Paso 4: Situar el gráfico como Objeto en la hoja Gráficos. Terminar Situar el gráfico en la posición y con tamaño deseado.
Si se quiere que los valores del eje de abscisas queden fuera del gráfico, situar el cursor sobre el eje de ordenadas (Eje de valores), y haciendo doble clic sale la pantalla Formato de ejes (figura 1.12). En la pestaña Escala se debe entrar al Eje de categorías (X) cruza en:
y cambiar el 0 por −−−−3000
Fig. 1.12
El resultado es el gráfico de la página 113.
Conclusiones: Analizar qué se puede decir de cada día de la semana referente a la
1.5 Estimación de la tendencia
Observando el gráfico de las medias móviles, superpuesto a la evolución de los datos cronológicos de la página 111, no se ve nada claro si la tendencia es de tipo lineal o cuadrático; eso, en parte puede ser atribuido al hecho de que la escala vertical del citado gráfico no es adecuada para estudiar las medias móviles. De modo que, lo primero que se debe hacer es un nuevo gráfico con una escala lo más amplia posible.
Seleccionar, manteniendo presionada la tecla Ctrl, desde C2 hasta C73 (Tiempo), y desde F2 hasta F73 (Y móvil) de la hoja Datos.
Con el icono de gráficos
Asistente para Gráficos (figura 1.5) XY (Dispersión)
(3; 1), Dispersión con puntos conectados por líneas Siguiente
Paso 2: Siguiente
Paso 3: Poner los títulos, por ejemplo Media móvil (tendencia), sacar la leyenda y Siguiente
Paso 4: Situar el gráfico como Objeto en la hoja gráficos. Terminar Es necesario editar el gráfico, tal como se ha hecho con el anterior, para que la escala de ordenadas vaya desde cinco mil hasta siete mil. El resultado es el gráfico de la página 112. Con la nueva escala parece bastante claro que puede haber una tendencia cuadrática, por eso, se ha de proceder a ajustar un modelo parabólico con el bien entendido de que si el término cuadrático no fuese significativo ya se detectaría en el análisis de los resultados, y se procedería en consecuencia; es decir, se debería ajustar un nuevo modelo sin el término que ha resultado no significativo.
Para aligerar la presentación de la hoja de cálculo, realizaremos el estudio de la tendencia, de los residuos y de las previsiones en una nueva hoja, que ya tenemos preparada desde el inicio con el nombre Tendencia-Modelo.
En primer lugar copiaremos todo lo que nos haga falta de la hoja Datos. Situados aquí: Seleccionar las columnas Tiempo, Y, Y móvil y Ind. Est., es decir, C1−−−−C73, D1−−−−D73, (y manteniendo presionada la tecla Ctrl) F1−−−−F73 y H1−−−−H73
Edición Copiar
Acceder a la hoja de Tendencia-Modelo, haciendo clic sobre la pestaña con su nombre. Situados en la casilla A1
Edición Pegar
una columna con los valores del tiempo al cuadrado, que necesariamente ha de estar situada al lado de la columna del tiempo; por eso tendremos que insertarla entre las columnas A y B.
Hacer clic sobre la letra B del encabezado de la columna, que quedará toda negra; presionar el botón derecho para seleccionar Insertar (figura 1.4). La columna de los datos se ha desplazado a la C y ha dejado la B vacía; aquí se introducirán los valores del tiempo al cuadrado.
En B1 escribir Tiempo^2
Situados en B2 escribir la expresión =A2*A2
Arrastrar hasta B73; aquí habrá el valor 5184, que es el cuadrado de 72. En las páginas 109 y 110, se puede ver la disposición de los valores.
Abrir Herramientas Análisis de datos Regresión Llenar los campos según se presenta en la figura 1.13, es decir:
Rango Y de entrada D5:D70 (medias móviles) Rango X de entrada A5:B70 (tiempo y tiempo^2) Opciones de salida:
Rango de salida: A93 (casilla vacía, a partir de donde se presentarán los resultados de la regresión)
Los resultados obtenidos se muestran en la figura 1.14, extraída de la página 110.
Conclusiones: El nivel de significación (valor p) de los coeficientes asociados al tiempo y al tiempo^2 es inferior a 0,05. Por lo tanto, con un riesgo de la primera especie del 5%, se debe aceptar el modelo cuadrático, que en este caso es:
2
ˆ
T = 6311,51 − 27,30 t + 0,18 t con un R2
del 85,6 %.
92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113
A B C D E F G
Resumen
stad st cos
R 0,92513
R^2 0,85587
R^2 ajust 0,85130
Error típico 114,20021
n 66
ANOVA
u S de C C a o p
Regresión 2 4879153,47 2439576,7 187,059889 3,1646E-27
Residuos 63 821626,351 13041,688
Total 65 5700779,82
Coefs o t p co t Va o p
Ord. Origen 6311,5139 51,8296 121,7743 1,7087E-76
Tiempo -27,3032 3,2473 -8,4079 6,9185E-12
Tiempo^2 0,1832 0,0433 4,2298 7,7177E-05
Fig. 1.14
Una vez obtenida la ecuación de la tendencia, podemos calcular su valor para los diferentes tiempos de los que se dispone de información. Para ello crearemos una nueva columna.
Situados en F1 escribir Tendencia, que será el título de la columna En F2 escribir la expresión que acabamos de obtener,
= 6311,51 −−−− 27,3*A2 + 0,18*B2 y arrastrarla hasta F73
Con el icono de gráficos
Asistente para Gráficos (figura 1.5) XY (Dispersión)
(3; 1) Dispersión con puntos conectados por líneas Siguiente
Paso 2: Siguiente
Paso 3: Poner los títulos, por ejemplo Tendencia, sacar la leyenda y Siguiente Paso 4: Situar el gráfico como Objeto en la hoja Gráficos. Terminar
Es necesario editar el gráfico, tal como se ha hecho con el anterior, para que la escala de ordenadas vaya desde cinco mil hasta a siete mil.
Si se quiere, situados sobre uno de los puntos de la Serie “tendencia”, con el botón derecho seleccionar
Formato de punto de datos
Marcador Ninguno
El resultado es el gráfico de la página 112, donde se puede valorar el ajuste.
1.6 Modelo y residuos
En una serie aditiva, el modelo se obtiene como resultado de sumar la tendencia y la estacionalidad de cada punto.
Situados en G1 escribir, como título, Y mod.
En G2 la expresión = F2 + E2 (tendencia + estacionalidad) Arrastrar hasta G73, que evidentemente contendrá la expresión F73+E73 Los residuos son la diferencia entre los valores originales, Y, y el modelo, Y mod.
Situados en H1 escribir, como título, Residuos
En H2 la expresión = C2 −−−− G2 (Y − Y mod) Arrastrar hasta H73
Con el icono de gráficos
Asistente para Gráficos (figura 1.5) XY (Dispersión)
(3; 1), Dispersión con puntos conectados por líneas Siguiente
Paso 2: Siguiente
Paso 3: Poner los títulos, por ejemplo Modelo ajustado, sacar la leyenda y Siguiente
Paso 4: Situar el gráfico como Objeto en la hoja Gráficos. Terminar
Si se desea dejar con puntos la serie original y con línea la del modelo ajustado, hay que situarse sobre uno de los puntos de la Serie “Y”, y con el botón derecho del ratón seleccionar:
Formato de punto de datos
Línea Ninguna
Situarse, luego, sobre un punto de la Serie “Y mod”, y con el botón derecho seleccionar Formato de punto de datos
Marcador Ninguno
El resultado es el gráfico de la página 113, donde se puede valorar el modelo.
Para hacer la representación gráfica de los residuos en función del tiempo, seleccionar, de la hoja Tendencia−−−−Modelo, los valores del tiempo y de los residuos, o sea, A1−−−−A73 y H1−−−−H73.
Con el icono de gráficos
Asistente para Gráficos (figura 1.5) XY (Dispersión)
(3; 1), Dispersión con puntos conectados por líneas Siguiente
Paso 2: Siguiente
Paso 3: Poner los títulos, por ejemplo Residuos, sacar la leyenda y Siguiente Paso 4: Situar el gráfico como Objeto en la hoja Gráficos. Terminar
En la pestaña Escala, modificar Mínimo: −−−−800
Eje de Valores (X) cruza en: −−−−800
En la pestaña Número, en Posiciones decimales poner un cero. El resultado es el gráfico de la página 114.
Conclusiones: La correspondencia entre los datos y el modelo es lo suficientemente buena. No se detecta ningún punto especialmente alejado del comportamiento modelizado por el conjunto. La mayoría de los residuos se mueven en el intervalo de −400 a 400, el más alejado de cero correspondiendo a los valores del tiempo 2, 27 y 33, que no parecen especialmente anómalos en el gráfico del modelo ajustado.
1.7 Previsiones
Si se quieren conocer las previsiones de las ventas del supermercado que estamos estudiando, a lo largo de las tres próximas semanas (18 días) en la hoja Tendencia−−−−Modelo prolongar las columnas del tiempo, la tendencia, la estacionalidad y crear una nueva columna para las previsiones.
Para la columna A, Tiempo, arrastrar presionando la tecla Ctrl desde la casilla A73 hasta la A91, donde ha de aparecer el valor 90.
En la E74, Estacionalidad, el primer valor que se debe añadir es el que corresponde al tiempo 73, es decir múltiple de 6 más 1; por tanto, hay que copiar desde el primer valor del índice hasta el 18. Marcar como bloque las casillas E2−E19 y hacer Edición Copiar. Situados en E74, Edición Pegar.
En la F, Tendencia, arrastrar la expresión desde el último valor disponible, el 73, hasta el deseado, el 91.
Situados en I1, poner como título Previsiones.
En I74 escribir la expresión = E74 + F74 y arrastrarla hasta E91.
Haciendo eso, obtenemos los resultados que se muestran en la página 110.
El gráfico de las previsiones, junto con la serie original, se obtendrá seleccionando A1−−−−A91, C1−−−−C91 y I1−−−−I91 de la hoja Tendencia−−−−Modelo.
Con el icono de gráficos
Asistente para Gráficos (figura 1.5) XY (Dispersión)
Paso 2: Siguiente
Paso 3: Poner los títulos, por ejemplo Serie y previsiones, tiempo y ventas, sacar la leyenda y Siguiente
Paso 4: Situar el gráfico como Objeto en la hoja Gráficos. Terminar Puede ser necesario editar el gráfico por que la escala de abscisas vaya desde cero hasta ochenta, y también modificar los tipos de líneas y puntos de la serie Y y de la serie previsiones, para destacar claramente los dos grupos de puntos.
El resultado es el gráfico de la página 114.
1.8 Resultados 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
A B C D E F G H
Semana Dia Tiempo Y Y(p=6) Y móvil W Ind. Est
1 lunes 1 3968 -2342,52
1 martes 2 4572 -951,07
1 miércoles 3 3964 -1974,48
1 jueves 4 6326 6135,00 6103,75 222,25 293,63
1 viernes 5 9673 6072,50 6138,75 3534,25 3087,20
1 sábado 6 8307 6205,00 6188,25 2118,75 1887,24
2 lunes 7 3593 6171,50 6202,92 -2609,92 -2342,52
2 martes 8 5367 6234,33 6218,67 -851,67 -951,07
2 miércoles 9 3763 6203,00 6194,67 -2431,67 -1974,48
2 jueves 10 6703 6186,33 6196,67 506,33 293,63
2 viernes 11 9485 6207,00 6152,42 3332,58 3087,20
2 sábado 12 8207 6097,83 6079,08 2127,92 1887,24
3 lunes 13 3717 6060,33 5981,58 -2264,58 -2342,52
3 martes 14 4712 5902,83 5871,75 -1159,75 -951,07
3 miércoles 15 3538 5840,67 5781,83 -2243,83 -1974,48
3 jueves 16 5758 5723,00 5672,25 85,75 293,63
3 viernes 17 9112 5621,50 5626,42 3485,58 3087,20
3 sábado 18 7501 5631,33 5640,08 1860,92 1887,24
4 lunes 19 3108 5648,83 5720,33 -2612,33 -2342,52
4 martes 20 4771 5791,83 5774,75 -1003,75 -951,07
4 miércoles 21 3643 5757,67 5798,67 -2155,67 -1974,48
4 jueves 22 6616 5839,67 5882,17 733,83 293,63
4 viernes 23 8907 5924,67 5896,00 3011,00 3087,20
4 sábado 24 7993 5867,33 5923,25 2069,75 1887,24
5 lunes 25 3618 5979,17 5895,83 -2277,83 -2342,52
5 martes 26 4427 5812,50 5801,75 -1374,75 -951,07
5 miércoles 27 4314 5791,00 5735,08 -1421,08 -1974,48
5 jueves 28 5616 5679,17 5619,25 -3,25 293,63
5 viernes 29 8778 5559,33 5600,25 3177,75 3087,20
5 sábado 30 7322 5641,17 5633,83 1688,17 1887,24
6 lunes 31 2899 5626,50 5660,58 -2761,58 -2342,52
6 martes 32 4918 5694,67 5689,17 -771,17 -951,07
6 miércoles 33 4226 5683,67 5713,92 -1487,92 -1974,48
6 jueves 34 6025 5744,17 5786,58 238,42 293,63
6 viernes 35 8712 5829,00 5824,92 2887,08 3087,20
6 sábado 36 7685 5820,83 5767,75 1917,25 1887,24
7 lunes 37 3408 5714,67 5665,67 -2257,67 -2342,52
7 martes 38 4869 5616,67 5577,25 -708,25 -951,07
7 miércoles 39 3589 5537,83 5510,75 -1921,75 -1974,48
7 jueves 40 5437 5483,67 5442,58 -5,58 293,63
7 viernes 41 8239 5401,50 5348,83 2890,17 3087,20
7 sábado 42 7360 5296,17 5303,67 2056,33 1887,24
8 lunes 43 2915 5311,17 5363,08 -2448,08 -2342,52
8 martes 44 4237 5415,00 5458,00 -1221,00 -951,07
8 miércoles 45 3679 5501,00 5510,58 -1831,58 -1974,48
8 jueves 46 6060 5520,17 5525,50 534,50 293,63
8 viernes 47 8755 5530,83 5508,67 3246,33 3087,20
8 sábado 48 7475 5486,50 5454,17 2020,83 1887,24
9 lunes 49 2979 5421,83 5361,50 -2382,50 -2342,52
51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90
A B C D E F G H
9 martes 50 3971 5301,17 5270,92 -1299,92 -951,07
9 miércoles 51 3291 5240,67 5183,58 -1892,58 -1974,48
9 jueves 52 5336 5126,50 5173,17 162,83 293,63
9 viernes 53 8392 5219,83 5280,08 3111,92 3087,20
9 sábado 54 6790 5340,33 5326,08 1463,92 1887,24
10 lunes 55 3539 5311,83 5369,33 -1830,33 -2342,52
10 martes 56 4694 5426,83 5376,83 -682,83 -951,07
10 miércoles 57 3120 5326,83 5368,83 -2248,83 -1974,48
10 jueves 58 6026 5410,83 5387,08 638,92 293,63
10 viernes 59 7792 5363,33 5365,92 2426,08 3087,20
10 sábado 60 7294 5368,50 5377,42 1916,58 1887,24
11 lunes 61 3254 5386,33 5349,83 -2095,83 -2342,52
11 martes 62 4725 5313,33 5357,33 -632,33 -951,07
11 miércoles 63 3227 5401,33 5376,42 -2149,42 -1974,48
11 jueves 64 5588 5351,50 5349,42 238,58 293,63
11 viernes 65 8320 5347,33 5340,92 2979,08 3087,20
11 sábado 66 6995 5334,50 5353,08 1641,92 1887,24
12 lunes 67 3229 5371,67 5333,42 -2104,42 -2342,52
12 martes 68 4648 5295,17 5281,75 -633,75 -951,07
12 miércoles 69 3450 5268,33 5262,33 -1812,33 -1974,48
12 jueves 70 5129 5256,33 293,63
12 viernes 71 8159 3087,20
12 sábado 72 6923 1887,24
Promedio de W
dia Total Ind. Est
lunes -2331,37 -2342,5202
martes -939,924 -951,07323
miércoles -1963,33 -1974,4823
jueves 304,7803 293,631313
viernes 3098,348 3087,19949
sábado 1898,394 1887,24495
Total general 11,14899
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
A B C D E F G H I
Tiempo Tiempo^2 Y Y móvil Ind. Est Tendencia Y mod Residuos Previsiones
1 1 3968 -2342,52 6284,39 3941,87 26,13
2 4 4572 -951,07 6257,63 5306,56 -734,56
3 9 3964 -1974,48 6231,23 4256,75 -292,75
4 16 6326 6103,75 293,63 6205,19 6498,82 -172,82
5 25 9673 6138,75 3087,20 6179,51 9266,71 406,29
6 36 8307 6188,25 1887,24 6154,19 8041,43 265,57
7 49 3593 6202,92 -2342,52 6129,23 3786,71 -193,71
8 64 5367 6218,67 -951,07 6104,63 5153,56 213,44
9 81 3763 6194,67 -1974,48 6080,39 4105,91 -342,91
10 100 6703 6196,67 293,63 6056,51 6350,14 352,86
11 121 9485 6152,42 3087,20 6032,99 9120,19 364,81
12 144 8207 6079,08 1887,24 6009,83 7897,07 309,93
13 169 3717 5981,58 -2342,52 5987,03 3644,51 72,49
14 196 4712 5871,75 -951,07 5964,59 5013,52 -301,52
15 225 3538 5781,83 -1974,48 5942,51 3968,03 -430,03
16 256 5758 5672,25 293,63 5920,79 6214,42 -456,42
17 289 9112 5626,42 3087,20 5899,43 8986,63 125,37
18 324 7501 5640,08 1887,24 5878,43 7765,67 -264,67
19 361 3108 5720,33 -2342,52 5857,79 3515,27 -407,27
20 400 4771 5774,75 -951,07 5837,51 4886,44 -115,44
21 441 3643 5798,67 -1974,48 5817,59 3843,11 -200,11
22 484 6616 5882,17 293,63 5798,03 6091,66 524,34
23 529 8907 5896,00 3087,20 5778,83 8866,03 40,97
24 576 7993 5923,25 1887,24 5759,99 7647,23 345,77
25 625 3618 5895,83 -2342,52 5741,51 3398,99 219,01
26 676 4427 5801,75 -951,07 5723,39 4772,32 -345,32
27 729 4314 5735,08 -1974,48 5705,63 3731,15 582,85
28 784 5616 5619,25 293,63 5688,23 5981,86 -365,86
29 841 8778 5600,25 3087,20 5671,19 8758,39 19,61
30 900 7322 5633,83 1887,24 5654,51 7541,75 -219,75
31 961 2899 5660,58 -2342,52 5638,19 3295,67 -396,67
32 1024 4918 5689,17 -951,07 5622,23 4671,16 246,84
33 1089 4226 5713,92 -1974,48 5606,63 3632,15 593,85
34 1156 6025 5786,58 293,63 5591,39 5885,02 139,98
35 1225 8712 5824,92 3087,20 5576,51 8663,71 48,29
36 1296 7685 5767,75 1887,24 5561,99 7449,23 235,77
37 1369 3408 5665,67 -2342,52 5547,83 3205,31 202,69
38 1444 4869 5577,25 -951,07 5534,03 4582,96 286,04
39 1521 3589 5510,75 -1974,48 5520,59 3546,11 42,89
40 1600 5437 5442,58 293,63 5507,51 5801,14 -364,14
41 1681 8239 5348,83 3087,20 5494,79 8581,99 -342,99
42 1764 7360 5303,67 1887,24 5482,43 7369,67 -9,67
43 1849 2915 5363,08 -2342,52 5470,43 3127,91 -212,91
44 1936 4237 5458,00 -951,07 5458,79 4507,72 -270,72
45 2025 3679 5510,58 -1974,48 5447,51 3473,03 205,97
46 2116 6060 5525,50 293,63 5436,59 5730,22 329,78
47 2209 8755 5508,67 3087,20 5426,03 8513,23 241,77
48 2304 7475 5454,17 1887,24 5415,83 7303,07 171,93
49 2401 2979 5361,50 -2342,52 5405,99 3063,47 -84,47
51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112
A B C D E F G H I
50 2500 3971 5270,92 -951,07 5396,51 4445,44 -474,44
51 2601 3291 5183,58 -1974,48 5387,39 3412,91 -121,91
52 2704 5336 5173,17 293,63 5378,63 5672,26 -336,26
53 2809 8392 5280,08 3087,20 5370,23 8457,43 -65,43
54 2916 6790 5326,08 1887,24 5362,19 7249,43 -459,43
55 3025 3539 5369,33 -2342,52 5354,51 3011,99 527,01
56 3136 4694 5376,83 -951,07 5347,19 4396,12 297,88
57 3249 3120 5368,83 -1974,48 5340,23 3365,75 -245,75
58 3364 6026 5387,08 293,63 5333,63 5627,26 398,74
59 3481 7792 5365,92 3087,20 5327,39 8414,59 -622,59
60 3600 7294 5377,42 1887,24 5321,51 7208,75 85,25
61 3721 3254 5349,83 -2342,52 5315,99 2973,47 280,53
62 3844 4725 5357,33 -951,07 5310,83 4359,76 365,24
63 3969 3227 5376,42 -1974,48 5306,03 3331,55 -104,55
64 4096 5588 5349,42 293,63 5301,59 5595,22 -7,22
65 4225 8320 5340,92 3087,20 5297,51 8384,71 -64,71
66 4356 6995 5353,08 1887,24 5293,79 7181,03 -186,03
67 4489 3229 5333,42 -2342,52 5290,43 2947,91 281,09
68 4624 4648 5281,75 -951,07 5287,43 4336,36 311,64
69 4761 3450 5262,33 -1974,48 5284,79 3310,31 139,69
70 4900 5129 293,63 5282,51 5576,14 -447,14
71 5041 8159 3087,20 5280,59 8367,79 -208,79
72 5184 6923 1887,24 5279,03 7166,27 -243,27
73 -2342,52 5277,83 2935,31
74 -951,07 5276,99 4325,92
75 -1974,48 5276,51 3302,03
76 293,63 5276,39 5570,02
77 3087,20 5276,63 8363,83
78 1887,24 5277,23 7164,47
79 -2342,52 5278,19 2935,67
80 -951,07 5279,51 4328,44
81 -1974,48 5281,19 3306,71
82 293,63 5283,23 5576,86
83 3087,20 5285,63 8372,83
84 1887,24 5288,39 7175,63
85 -2342,52 5291,51 2948,99
86 -951,07 5294,99 4343,92
87 -1974,48 5298,83 3324,35
88 293,63 5303,03 5596,66
89 3087,20 5307,59 8394,79
90 1887,24 5312,51 7199,75
Resumen
stad st cos
R 0,92513
R^2 0,85587
R^2 ajust 0,85130
Error típico 114,20021
n 66
ANOVA
u S de C C Va o p
Regresión 2 4879153,47 2439576,7 187,059889 3,1646E-27
Residuos 63 821626,351 13041,688
Total 65 5700779,82
Coefs o t p co t Va o p
Ord. Origen 6311,5139 51,8296 121,7743 1,7087E-76
Tiempo -27,3032 3,2473 -8,4079 6,9185E-12
Tiempo^2 0,1832 0,0433 4,2298 7,7177E-05
Evolución cronológica
0
4000
8000
12000
0
12
24
36
48
60
72
tiempo
V
e
n
t
a
s
Medias móviles (p=6)
0
4000
8000
12000
0
12
24
36
48
60
72
Tiempo
Media móvil (tendencia)
5000
6000
7000
0
12
24
36
48
60
72
tiempo
Tendencia
5000 6000 7000
0 12 24 36 48 60 72
tiempo
Índices estacionales
-3000
-2000
-1000
0
1000
2000
3000
4000
1
2
3
4
5
6
Modelo ajustado
0
4000
8000
12000
0
12
24
36
48
60
72
tiempo
V
e
n
t
a
s
Residuos
-800
-400
0
400
800
0
12
24
36
48
60
72
tiempo
Serie y previsiones
0 2000 4000 6000 8000 10000 12000
0 30 60 90
tiempo V
e n t a s
PRÁCTICA 2. AUTOCORRELACIÓN Y CORRELOGRAMA
OBJETIVO: Con los datos del valor diario de la caja, resultado de las ventas de un supermercado a lo largo de 12 semanas, que han sido analizados en la práctica anterior, se han de calcular los coeficientes de autocorrelación, estudiar su significación estadística y obtener el correlograma; el objetivo final es ver hasta qué valor del tiempo se pueden hacer previsiones.
Todo eso se realizará mediante la hoja de cálculo Excel 97 de Microsoft.
2.1 Recuperacion de los datos
Desde Excel hay que recuperar el archivo que contiene los datos objeto de la práctica, y que se encuentra en el directorio habitual de la red, siguiendo la secuencia,
Archivo Abrir
y ahora ir al directorio donde se encuentra el archivo Practica 2.xls, seleccionarlo y presionar sobre Abrir.
Una vez tenemos el archivo abierto, observamos que consta de una hoja llamada Datos donde figuran 3 columnas de 72 valores cada una. En cada columna hay 72 valores, es decir, empieza en la fila 1 (con el título) y acaba en la 73. Recordemos que son los mismos valores de la práctica 1.
2.2 Cálculo de los coeficientes de autocorrelación
Según hemos visto en el texto sobre series temporales, el coeficiente de autocorrelación se calcula como k k 0 ˆ r s ˆ = γ γ donde N k
i i k
i 1 k
(Y Y ) (Y Y ) ˆ N − + = − − =
∑
γ y
N 2 i i 1 0 (Y Y) ˆ N = − =
∑
γcon la recomendación de N > 50 y k ≤ N/4.
En el caso de la práctica, el número de observaciones es N = 72, hecho que nos permite llegar hasta un valor de k igual a 18.
En primer lugar debemos disponer de los valores de la variable centrada, o sea de
i
Observaremos que se ha fijado el conjunto de valores de Y (columna C) con el símbolo $, con la finalidad de que al arrastrar la fórmula no vayan cambiando los valores con los que se calcula la media. Extendemos la expresión hasta D73.
En la casilla G1 escribimos como título gamma_0, y en la G2 la expresión que permite calcular su valor
= VARP(C2:C73)
Aquí se ha utilizado VARP en lugar de VAR para que el divisor sea N y no N−1 como sería en el otro caso.
Para comenzar a preparar la tabla de resultados, se titulan
F8 k G8 gamma_k H8 r_k I8 r_k^2
J8 V(r_k) K8 −−−−2S(r_k) L8 +2S(r_k)
En F9 se introduce el valor 1, y se arrastra en forma de incremento (presionando simultáneamente la tecla de Ctrl) hasta F26; aquí habrá un 18.
En la columna G introducimos la expresión de la covariancia, γk, de la página anterior, que en el numerador tiene el producto escalar de dos vectores: el primero va desde Y1−Y hastaY72 K− −Y, o sea, de D2 a D(73–k), y el segundo de Y1+k −Y hasta Y72 −Y , o sea, de D(2+k) a D73. Esto es un problema para arrastrar la fórmula de una casilla a las siguientes, ya que al aumentar el desplazamiento k un subíndice aumenta, (el 2+k), pero el otro disminuye, (el 73−k). Este hecho obligará a escribir la fórmula, arrastrarla y, después, manualmente, y casilla a casilla, modificar el contador que tiene que decrecer. Ateniéndonos a ello en G9 debemos introducir la expresión de la covariancia para Kl, que se puede hacer mediante la función SUMAPRODUCTO; es decir, presionaremos sobre el icono de funciones y seleccionaremos, de entre las Matemáticas y trigonométricas, la SUMAPRODUCTO (figura 2.1), que no es más que el producto escalar de dos vectores,
En el cuadro siguiente (figura 2.2), especificaremos los valores que vamos a utilizar, teniendo cuidado de fijar las posiciones inamovibles ($2 del primer vector y $73 por el segundo), ya que, como se ha comentado, el primer vector siempre empieza en el primer valor de la Y centrada, eso es, D2, mientras que el segundo siempre acaba en el último valor de Y centrada, o sea, D73. Así el primer vector va de D$2 hasta D72 y el segundo de D3 hasta D$73. Una vez se presiona la tecla Aceptar, en la ventana superior queda escrita la expresión = SUMAPRODUCTO(D$2... D$73): dicha expresión hay que ponerla entre paréntesis y dividirla por el número total de observaciones (72 en este caso) a fin de obtener la autocovariancia para k=1. En la figura 2.3 se muestra cómo finalmente queda definida la casilla G9.
Fig. 2.2
Fig. 2.3
Celda k expresión actual expresión definitiva
G10 2 =(SUMAPRODUCTO(D$2:D73 ... =(SUMAPRODUCTO(D$2:D71... G11 3 =(SUMAPRODUCTO(D$2:D74 ... =(SUMAPRODUCTO(D$2:D70...
••• ••• •••
G25 17 =(SUMAPRODUCTO(D$2:D88 ... =(SUMAPRODUCTO(D$2:D56... G26 18 =(SUMAPRODUCTO(D$2:D89 ... =(SUMAPRODUCTO(D$2:D55...
Observar los valores resultantes en la página 120.
En H9, escribir la expresión del coeficiente de autocorrelación, eso es: = G9/G$2, y arrastrar hasta H26, donde figurará =G26/G$2.
La columna I tiene los cuadrados de los coeficientes de autocorrelación; para ello hay que hacer I9 =H9*H9 y extenderlo hasta I26.
En la columna J se ha de calcular la variancia de cada coeficiente, que según el texto de teoría es
1
1 V(r )
N
≅ y
k 1 2
k i
i 1
1
V(r ) 1 2 r
N
−
=
≅ +
∑
Hacer J9 = 1/72
J10 = (1+2*SUMA(I$9:I9))/72
y arrastrar hasta J26 donde habrá la expresión =(1+2*SUMA(I$9:I25))/72. Los extremos del intervalo de no significación, ± 2S(r_k), estarán en las columnas K y L.
K9 = −−−−2*RAIZ(J9) L9 = 2*RAIZ(J9)
Arrastrar estas expresiones hasta K26 y L26. La tabla completa de resultados está en las páginas 120 y 121.
2.3 Autocorrelograma
El gráfico se obtiene seleccionando F9 − F26, H9 − H26, K9 − K26 y L9 − L26. Con el icono de gráficos
Asistente para Gráficos XY (Dispersión)
(3; 1), Dispersión con puntos conectados por líneas Siguiente
Paso 2: Siguiente
Paso 3: Poner los títulos, por ejemplo Autocorrelograma, quitar la leyenda y Siguiente
Paso 4: Situar el gráfico con Objeto en la actual hoja Datos. Terminar Situar el gráfico en la posición y el tamaño deseado, y editarlo para que presente el aspecto habitual de un correlograma.
Seleccionando un punto de la Serie 1, y haciendo clic con el botón derecho del ratón, sale el cuadro de la figura 2.4. Seleccionar:
Tipo de gráfico Columnas Aceptar
Fig. 2.4
Igualmente, sobre un punto de la Serie 2, en la pantalla de la figura 2.4, hacer Formato de serie de datos
Carpeta Tramas:
Línea suavizada Marcador Ninguno Repetir la misma operación, una vez situados en un punto de la Serie 3.
Si es necesario, se pueden quitar decimales del eje de ordenadas; para eso tendremos que situarnos sobre el Eje de valores, y entonces, con doble clic, o presionando el botón derecho del ratón, seguir la secuencia
Formato de ejes
Carpeta Número Posiciones decimales
y para quitar los valores de k de dentro del gráfico, en el Eje de categorías, eje de abscisas, Formato de ejes
Carpeta Tramas Rótulos de marca de graduación Ninguno El resultado es el gráfico de la página 121.
2.4 Resultados 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51
A B C D E F G H I J K L
Seman Dia Y Y-Ybar gamma_0
1 lunes 3968 -1653,36 4003801
1 martes 4572 -1049,36 1 miércoles 3964 -1657,36
1 jueves 6326 704,639
1 viernes 9673 4051,64
1 sábado 8307 2685,64
2 lunes 3593 -2028,36 k gamma_k r_k r_k^2 V(r_k) - 2S(r_k) + 2S(r_k)
2 martes 5367 -254,361 1 1089260,8 0,2721 0,0740 0,0139 -0,2357 0,2357 2 miércoles 3763 -1858,36 2 -1528643 -0,3818 0,1458 0,0159 -0,2525 0,2525 2 jueves 6703 1081,64 3 -2302898 -0,5752 0,3308 0,0200 -0,2828 0,2828 2 viernes 9485 3863,64 4 -1654914 -0,4133 0,1708 0,0292 -0,3417 0,3417 2 sábado 8207 2585,64 5 873463,57 0,2182 0,0476 0,0339 -0,3684 0,3684 3 lunes 3717 -1904,36 6 3551137,5 0,8869 0,7867 0,0353 -0,3755 0,3755 3 martes 4712 -909,361 7 978459,2 0,2444 0,0597 0,0571 -0,4779 0,4779 3 miércoles 3538 -2083,36 8 -1429667 -0,3571 0,1275 0,0588 -0,4848 0,4848 3 jueves 5758 136,639 9 -2118164 -0,5290 0,2799 0,0623 -0,4992 0,4992 3 viernes 9112 3490,64 10 -1510880 -0,3774 0,1424 0,0701 -0,5294 0,5294 3 sábado 7501 1879,64 11 775336,95 0,1937 0,0375 0,0740 -0,5442 0,5442 4 lunes 3108 -2513,36 12 3213971,1 0,8027 0,6444 0,0751 -0,5480 0,5480 4 martes 4771 -850,361 13 902365,54 0,2254 0,0508 0,0930 -0,6098 0,6098 4 miércoles 3643 -1978,36 14 -1276624 -0,3189 0,1017 0,0944 -0,6144 0,6144 4 jueves 6616 994,639 15 -1892155 -0,4726 0,2233 0,0972 -0,6236 0,6236 4 viernes 8907 3285,64 16 -1373896 -0,3431 0,1178 0,1034 -0,6432 0,6432 4 sábado 7993 2371,64 17 700396,45 0,1749 0,0306 0,1067 -0,6533 0,6533 5 lunes 3618 -2003,36 18 2879249,8 0,7191 0,5171 0,1075 -0,6559 0,6559 5 martes 4427 -1194,36
5 miércoles 4314 -1307,36 5 jueves 5616 -5,36111 5 viernes 8778 3156,64
5 sábado 7322 1700,64
6 lunes 2899 -2722,36
6 martes 4918 -703,361 6 miércoles 4226 -1395,36
6 jueves 6025 403,639
6 viernes 8712 3090,64
6 sábado 7685 2063,64
7 lunes 3408 -2213,36
7 martes 4869 -752,361 7 miércoles 3589 -2032,36 7 jueves 5437 -184,361 7 viernes 8239 2617,64
7 sábado 7360 1738,64
8 lunes 2915 -2706,36
8 martes 4237 -1384,36 8 miércoles 3679 -1942,36
8 jueves 6060 438,639
8 viernes 8755 3133,64
8 sábado 7475 1853,64
9 lunes 2979 -2642,36
9 martes 3971 -1650,36
52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76
A B C D E F G H I J K L
9 miércoles 3291 -2330,36 9 jueves 5336 -285,361 9 viernes 8392 2770,64
9 sábado 6790 1168,64
10 lunes 3539 -2082,36 10 martes 4694 -927,361 10 miércoles 3120 -2501,36 10 jueves 6026 404,639 10 viernes 7792 2170,64 10 sábado 7294 1672,64 11 lunes 3254 -2367,36 11 martes 4725 -896,361 11 miércoles 3227 -2394,36 11 jueves 5588 -33,3611 11 viernes 8320 2698,64 11 sábado 6995 1373,64 12 lunes 3229 -2392,36 12 martes 4648 -973,361 12 miércoles 3450 -2171,36 12 jueves 5129 -492,361 12 viernes 8159 2537,64 12 sábado 6923 1301,64
Hoja: Datos (continuación)
AUTOCORRELOGRAMA
PRÁCTICA 3. MODELIZACIÓN DE UNA SERIE CON VARIABLES CATEGÓRICAS
OBJETIVO: Se dispone de la evolución de un indicador económico a lo largo de 62 trimestres. Tenemos que analizar los datos de esta serie cronológica, estimar el modelo de comportamiento con variables categóricas, estudiar su ajuste y hacer las previsiones pertinentes. Todo esto se realizará mediante la hoja de cálculo Excel 97 de Microsoft.
3.1 Recuperación de los datos
Desde Excel recuperar el archivo que contiene los datos objeto de la práctica, y que se encuentran en el directorio habitual de la red. Por esto hemos de seguir la secuencia (figura 3.1):
Archivo Abrir
Y ahora ir al directorio donde se encuentra el archivo Practica 3.xls, seleccionarlo y Abrir.
Fig. 3.1
Una vez tenemos el archivo abierto, observamos que consta de una hoja denominada Datos donde figuran 2 columnas de 62 valores cada una, con la estructura mostrada parcialmente en la figura 3.2. En cada columna hay 62 valores, es decir, se empieza en la fila 1 (con el título) y se acaba en la 63.
La columna A, llamada t, contiene valores de 1 a 62, correspondientes a los 62 intervalos de tiempo (trimestres) en que se ha recogido la información, y la B, con el nombre de Y, contiene los valores del indicador económico que se está estudiando.
3.2 Análisis de la evolución de la serie cronológica
En primer lugar, hemos de analizar la evolución de la serie, cosa que ya hemos hecho en la práctica nº1, pero es suficientemente rápido como para hacerlo de nuevo. Se selecciona desde A1 hasta B63 (columnas t y Y) y se presiona el icono de gráficos , o también, en la barra de herramientas, Insertar y después Gráfico.
En el Asistente para Gráficos (figura 3.3) hemos de seleccionar XY (Dispersión) y ahora la opción (3; 1), es decir, Dispersión con puntos de datos conectados con líneas y Terminar.
Situar el gráfico en el lugar que se desee, y editarlo según convenga.
Fig. 3.3
El resultado es el primer gráfico de la página 134 de esta práctica.
Parece detectarse una estacionalidad de período 4, hecho que debemos confirmar mediante el correlograma. Este gráfico ha sido el objetivo de la práctica 2; aplicando la metodología expuesta a los datos actuales resulta el correlograma mostrado en la página 132, donde se puede ver, por una parte, la evidencia de una estacionalidad de período 4 y, por otra, que es admisible hacer previsiones para cinco intervalos de tiempo.
Conclusiones: Se detecta una clara estacionalidad, de período p=4, y posiblemente una tendencia creciente y cuadrática. El modelo que se tendrá que estudiar será
Y = αααα0 + αααα1 t + αααα2 t2
3.3 Modelización con variables categóricas
Creación de las variables
Para poder modelizar la serie, en primer lugar hemos de crear las variables categóricas, o indicatrices, teniendo en cuenta que, en el caso de la práctica, el período p es igual a 4. Para ello, se preparan los títulos de las columnas que contendrán los valores de las variables categóricas. Recordando que las representamos por Q y que sus índices van desde 2 hasta p (teoría de series temporales), en las casillas C1, D1 y E1 escribiremos Q2, Q3, y Q4, tal como muestra la figura 3.4.
A continuación rellenaremos cada variable categórica con sus valores. Al ser el período igual a 4, hay 4 combinaciones diferentes de ceros y unos, una para cada componente del período, y sabiendo que Qi vale la unidad si el orden del tiempo asociado es igual a i, y vale cero en cualquier otro caso, el conjunto de valores es el que se muestra en el bloque C2− E5, de la figura 3.4.
Una vez lleno el bloque anterior, sólo hemos de seleccionarlo y con Cortar y Pegar, llenar todas las casillas C ... E hasta la fila 63, o bien arrastrar el bloque presionando simultáneamente el Ctrl (página 130).
Además, para estudiar el modelo, es necesario disponer de las columnas con los valores tQ2, tQ3 y tQ4, tiempo (t) y tiempo al cuadrado (t^2). Estos valores están en las columnas F, ... J. Para llenar estas columnas, es ya evidente que lo que debemos hacer es definirlas como
F2 = A2*C2, G2 = A2*D2, H2 = A2*E2, I2 =A2 y H2 = A2*A2.
Después arrastrar hasta la fila 63. En la figura 3.4 se puede ver la estructura que toman estas columnas, y en la página 130 todos los valores.
Es una exigencia de Excel que todas las columnas de los términos que constituyen el modelo hayan de ser consecutivas y contiguas.
Obtención del modelo
De acuerdo con la naturaleza de los datos hay que plantear el modelo
2
0 1 2 ˆ2 2 3ˆ 3 4ˆ4 5 ˆ2 6 ˆ3 7 4ˆ
ˆ ˆ ˆ ˆ
Y = α +αt +α t + β Q + β Q + β Q + β Q t + β Q t + β Q t Para estimar los coeficientes y estudiar su significación, el procedimiento es:
Herramientas Análisis de datos Regresión
En este momento aparece la pantalla de la figura 3.5, donde debemos rellenar los campos siguientes
Rango Y de entrada: $B$1:$B$63 (los valores de Y)
Rango X de entrada: $C$1:$J$63 (los valores de los regresores) Rótulos
Rango de salida $A$125 (una casilla que esté vacía) Los resultados se pueden ver en la página 133, con el título •••• Primer paso
Conclusiones: El coeficiente del término Q2 no es significativo (su nivel de significación es p = 0,292 > 0,05). Debemos eliminarlo del modelo lineal y volver a estimar los coeficientes.
Para eliminar el término Q2 y rehacer la regresión, con la rutina Regresión de Excel, es necesario que todos los términos del modelo estén juntos; por tanto hemos de eliminar la columna de Q2. Hacerlo así directamente podría ocasionar problemas y modificaciones en otras columnas ligadas a ésta. Para evitarlo recomendamos lo siguiente:
Seleccionar con el ratón desde B1 hasta J63, presionar el botón derecho y hacer Copiar.
Situarse, por ejemplo, en la casilla S1 (fila a partir de la cual todo está vacío) y desplegar el menú Insertar (figura 3.6).
Fig. 3.6
Seleccionar Pegado especial y ahora Valores (figura 3.7)
Ahora eliminar la columna asociada a Q2 (la T en el caso del ejemplo): para ello se pincha sobre la letra T distintiva de la columna, que quedará enmarcada por una línea que parpadea; entonces se presiona el botón derecho y se selecciona Eliminar. De esta manera las columnas siguientes avanzan un lugar y vuelven a estar todas juntas, es decir, empiezan en la S (valores de Y) y acaban en la Z (valores de t^2)
Y ahora hay que proceder como antes:
Herramientas Análisis de datos Regresión modificando los campos siguientes:
Rango X de entrada: $S$1:$Z$63 (los nuevos regresores) Rango de salida $A$150 (una casilla que esté vacía) Los resultados se pueden ver en la página 133 con el título • Segundo paso
Conclusiones: El modelo definitivo es ˆ
Y = 97,81 + 2,03 t + 0,014 t2
− 7,75 Q3 + 20,57 Q4 + 0,39 t Q2 + 0,85 t Q3 + 0,99 t Q4
• Analizar y comentar los valores de los coeficientes del modelo, su significación y el valor del coeficiente de determinación (R^2) del ajuste.
3.4 Estimaciones y residuos
Valores estimados
Una vez establecido el modelo tenemos que examinar el ajuste entre los datos y los valores estimados según el modelo ajustado.
Por eso, en primer lugar cogemos un bloque con los términos y los coeficientes del modelo definitivamente obtenido y hacemos un Cortar y Pegar en L2; en L1 escribimos Modelo: resultarán las casillas destacadas en azul en la página 131.
Después, en N1 escribimos el título de la columna, Yest, y en N2 el modelo, es decir = M$2+M$3*D2+M$4*E2+M$5*F2+M$6*G2+M$7*H2+M$8*I2+M$9*J2
Debemos destacar la exigencia de fijar las celdas que contienen los coeficientes del modelo, para que al arrastrar la fórmula se mantengan constantes.
Gráfico de los valores reales enfrente de los estimados
Es necesario seleccionar desde B2 hasta B63 y, presionando la tecla Ctrl, desde N2 hasta N63. Después se presiona el icono de los gráficos y
Asistente para Gráficos Líneas
(2, 1) Línea con marcadores… Terminar
Situaremos el gráfico como Objeto en la misma hoja, y lo editaremos, en la posición, la amplitud y el estilo deseados. El resultado se puede ver en el segundo gráfico de la página 134.
Gráfico de residuos
En primer lugar debemos calcular los residuos; para esto es prepara la columna con el título y se calculan los valores:
O1 : Res O2 = B2 −−−− N2 (arrastrar hasta O63 (página 131))
Para hacer el gráfico se selecciona desde O2 hasta O63, se presiona y Asistente para Gráficos
Líneas
(2, 1) Línea con marcadores… Terminar
Situaremos el gráfico como Objeto en la misma hoja, y lo editaremos, en la posición, la amplitud y el estilo deseados. El resultado se puede ver en el primer gráfico de la página 135.
Conclusiones: Observar y analizar detalladamente la evolución de los dos gráficos.
3.5 Previsiones
Atendiendo a que, según el correlograma (página 132), se pueden hacer previsiones para los próximos 5 valores del tiempo, es necesario ampliar las columnas de las variables categóricas y del tiempo con los 5 valores nuevos, del 63 hasta el 67. Estos valores se han de incorporar al final de la columna A, es decir desde A64 hasta A68 (página 130).
Prepararemos la columna de las previsiones:
K1 = Y Prev (título) y en K64 escribiremos el modelo , es decir,
=M$2+M$3*D64+M$4*E64+M$5*F64+M$6*G64+M$7*H64+M$8*I64+M$9*J64
arrastraremos hasta K68 y tendremos los valores de las 5 previsiones (página131).
Para hacer el gráfico se selecciona desde B2 hasta B68 y, presionando el Ctrl, desde K2 hasta K68 y desde N2 hasta N68. Se presiona el icono de los gráficos y
Asistente para Gráficos Líneas
(2, 1) Línea con marcadores…
Terminar
Situaremos el gráfico como Objeto en la misma hoja, y lo editaremos, en la posición, el tamaño y el estilo deseados. El resultado se puede ver en el segundo gráfico de la página 135.
3.6 Resultados 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
A B C D E F G H I J
t Y Q2 Q3 Q4 tQ2 tQ3 tQ4 t t^2
1 105,86 0 0 0 0 0 0 1 1
2 97,79 1 0 0 2 0 0 2 4
3 96,1 0 1 0 0 3 0 3 9
4 127,44 0 0 1 0 0 4 4 16
5 108,78 0 0 0 0 0 0 5 25
6 112,61 1 0 0 6 0 0 6 36
7 111,43 0 1 0 0 7 0 7 49
8 145,71 0 0 1 0 0 8 8 64
9 118,37 0 0 0 0 0 0 9 81
10 121,89 1 0 0 10 0 0 10 100
11 124,25 0 1 0 0 11 0 11 121
12 159,55 0 0 1 0 0 12 12 144
13 125,41 0 0 0 0 0 0 13 169
14 135,4 1 0 0 14 0 0 14 196
15 137,86 0 1 0 0 15 0 15 225
16 171,44 0 0 1 0 0 16 16 256
17 132,38 0 0 0 0 0 0 17 289
18 147,59 1 0 0 18 0 0 18 324
19 153,92 0 1 0 0 19 0 19 361
50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68
A B C D E F G H I J
49 232,76 0 0 0 0 0 0 49 2401
50 256,84 1 0 0 50 0 0 50 2500
51 271,36 0 1 0 0 51 0 51 2601
52 311,42 0 0 1 0 0 52 52 2704
53 243,9 0 0 0 0 0 0 53 2809
54 268,42 1 0 0 54 0 0 54 2916
55 291,25 0 1 0 0 55 0 55 3025
56 331,96 0 0 1 0 0 56 56 3136
57 255,46 0 0 0 0 0 0 57 3249
58 283,53 1 0 0 58 0 0 58 3364
59 307,82 0 1 0 0 59 0 59 3481
60 354,72 0 0 1 0 0 60 60 3600
61 276,58 0 0 0 0 0 0 61 3721
62 304,72 1 0 0 62 0 0 62 3844
63 0 1 0 0 63 0 63 3969
64 0 0 1 0 0 64 64 4096
65 0 0 0 0 0 0 65 4225
66 1 0 0 66 0 0 66 4356
67 0 1 0 0 67 0 67 4489
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
K L M N O
Y Prev Modelo Y est Res Ord. Origen 97,8112 99,856 6,004
Q3 -7,7472 102,701 -4,911
Q4 20,5667 98,828 -2,728
tQ2 0,3863 130,677 -3,237
tQ3 0,8484 108,319 0,461
tQ4 0,9877 112,823 -0,213
t 2,0302 110,913 0,517
t^2 0,0143 143,433 2,277
117,238 1,132 123,402 -1,512 123,454 0,796 156,645 2,905 126,613 -1,203 134,437 0,963 136,451 1,409 170,314 1,126 136,445 -4,065 145,928 1,662 149,905 4,015 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68
K L M N O
231,526 1,234 254,283 2,557 273,960 -2,600 313,863 -2,443 245,464 -1,564 269,881 -1,461 291,520 -0,270 332,095 -0,135 259,859 -4,399 285,935 -2,405 309,537 -1,717 350,782 3,938 274,710 1,870 302,445 2,275 328,010 369,926 290,018 319,412 346,939
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
A B C D E F G H I J K
t Y Y-Ybar gamma_0
1 105,86 -94,918 4277,898
2 97,79 -102,988
3 96,1 -104,678
4 127,44 -73,338 k gamma_k r_k r_k^2 V(r_k) - 2S(r_k) + 2S(r_k)
5 108,78 -91,998 1 3562,405 0,833 0,693 0,016 -0,254 0,254
6 112,61 -88,168 2 3408,788 0,797 0,635 0,038 -0,392 0,392
7 111,43 -89,348 3 3149,145 0,736 0,542 0,059 -0,486 0,486
8 145,71 -55,068 4 3466,645 0,810 0,657 0,076 -0,553 0,553
9 118,37 -82,408 5 2791,202 0,652 0,426 0,098 -0,625 0,625
10 121,89 -78,888 6 2671,875 0,625 0,390 0,111 -0,667 0,667
11 124,25 -76,528 7 2435,804 0,569 0,324 0,124 -0,704 0,704
12 159,55 -41,228 8 2711,854 0,634 0,402 0,134 -0,733 0,733
13 125,41 -75,368 9 2076,334 0,485 0,236 0,147 -0,768 0,768
14 135,4 -65,378 10 1964,898 0,459 0,211 0,155 -0,787 0,787
15 137,86 -62,918 11 1745,883 0,408 0,167 0,162 -0,804 0,804
16 171,44 -29,338 12 1990,675 0,465 0,217 0,167 -0,818 0,818
17 132,38 -68,398 13 1385,336 0,324 0,105 0,174 -0,835 0,835
18 147,59 -53,188 14 1281,548 0,300 0,090 0,178 -0,843 0,843
19 153,92 -46,858 15 1082,582 0,253 0,064 0,180 -0,850 0,850
20 179,39 -21,388 16 1282,895 0,300 0,090 0,182 -0,854 0,854
21 141,59 -59,188 17 717,469 0,168 0,028 0,185 -0,861 0,861
22 159,6 -41,178 18 636,614 0,149 0,022 0,186 -0,863 0,863
23 163,23 -37,548
24 205,54 4,762
25 161,71 -39,068
26 172,24 -28,538
27 173,17 -27,608
Resultados de la regresión
Primer paso
Coef. de determinación
R^2 0,99803327
ANÁLISIS DE VARIANZA
nu S.C. C.M. F p-val
Regresión 8 264708,064 33088,508 3361,908 7,37E-69
Residuos 53 521,636 9,842
Total 61 265229,699
Coefs Error típico t p-val
Ord. Origen 98,9145 1,7628 56,1129 6,95E-49
Q2 -2,3396 2,1976 -1,0646 2,92E-01
Q3 -8,8900 2,2637 -3,9271 2,50E-04
Q4 19,4201 2,2980 8,4508 2,15E-11
tQ2 0,4416 0,0602 7,3341 1,31E-09
tQ3 0,8756 0,0633 13,8337 3,15E-19
tQ4 1,0150 0,0634 16,0203 5,78E-22
t 2,0067 0,0965 20,7852 3,82E-27
t^2 0,0142 0,0014 10,1580 4,85E-14
Segundo paso
Coef. de determinación
R^2 0,99799121
ANÁLISIS DE VARIANZA
nu S.C. C.M. F p-val
Regresión 7 264696,908 37813,844 3832,546 2,00E-70
Residuos 54 532,791 9,867
Total 61 265229,699
Coefs Error típico t p-val
Ord. Origen 97,8112 1,4278 68,5071 3,52E-54
Q3 -7,7472 1,9955 -3,8823 2,84E-04
Q4 20,5667 2,0325 10,1186 4,50E-14
tQ2 0,3863 0,0304 12,6897 7,78E-18
tQ3 0,8484 0,0580 14,6299 1,97E-20
tQ4 0,9877 0,0580 17,0261 2,29E-23
t 2,0302 0,0941 21,5725 3,26E-28
Evolución cronológica de los datos
90
130
170
210
250
290
330
370
0
8
16
24
32
40
48
56
64
t
Y
Datos reales y modelizados
90
130
170
210
250
290
330
370
0
8
16
24
32
40
48
56
64 t
Residuos
-10
-5
0
5
10
0
8
16
24
32
40
48
56
64
t
R
Valores reales, modelizados y previsiones
90
130
170
210
250
290
330
370
410
0
8
16
24
32
40
48
56
64
72
t
Y
Serie1
Serie2
Serie3
PRÁCTICA 4. MODELIZACIÓN Y PREVISIONES POR SUAVIZADO EXPONENCIAL (MÉTODO DE BROWN)
OBJETIVO: Se dispone de la evolución de un indicador económico durante 31 días. Hay que analizar los datos de esta serie cronológica, estimar el modelo de comportamiento y hacer las previsiones pertinentes. Todo esto se realizará mediante la hoja de cálculo Excel 97 de Microsoft.
4.1 Recuperación de los datos
Desde Excel recuperar el archivo que contiene los datos objeto de la práctica, y que se encuentran en el directorio habitual de la red. Para ello, debemos seguir la secuencia (figura 4.1)
Archivo Abrir
y ahora ir al directorio donde se encuentra el archivo Práctica 4.xls, seleccionarlo y presionar Abrir.
Fig. 4.1
Una vez está abierto el archivo, observamos que consta de una hoja denominada Datos con 2 columnas de 31 valores cada una y la estructura mostrada en la figura 4.2.
La columna A, llamada Tiempo, contiene valores de 1 a 31 y la B, llamada Y, contiene los valores del índice económico que se está estudiando.
4.2 Análisis de la evolución de la serie cronológica
En primer lugar, hay que analizar la evolución de la serie: para ello se selecciona desde B1 hasta B32 (valores de Y) y se presiona el icono de gráficos , o también, en la barra de herramientas, Insertar y después Gráfico. En el Asistente para Gráficos (figura 4.3), seleccionar Líneas.
Fig. 4.3
Y ahora la opción (2; 1), es decir, Línea con marcadores en cada valor de datos y Terminar.
Situar el gráfico como Objeto en la misma hoja, y editarlo en la posición y el tamaño deseados.
El resultado es el gráfico de la página 142.
Se muestra una tendencia creciente y no parece detectarse ningún tipo de estacionalidad, cosa que se confirmará mediante el correlograma. Este gráfico ha sido el objetivo de la práctica 2; para obtenerlo copiamos todos los datos de la práctica en una nueva hoja que llamaremos Correl. Aplicando la sistemática se obtienen los resultados mostrados en la páginas 143 y 144, que confirman la no estacionalidad y la posibilidad de hacer previsiones para los tres próximos días. Se debe recalcar que no se está en las mejores condiciones para hacer un correlograma, porque tan sólo se dispone de 30 valores.
4.3 Método de Brown
Para modelizar una serie con este procedimiento hemos de seleccionar un valor del parámetro λ y calcular la serie suavizada, la doble suavizada, la ordenada en el origen, la pendiente, el valor de la serie estimada, el error en cada instante y el error cuadrático medio. Cambiando el valor de λ se repite el proceso y se selecciona, como parámetro de modelización, el que minimice el error cuadrático medio.
Selección de λ
En la casilla A40 escribimos el valor inicial de λ, λ = 0,1 para empezar, y etiquetamos las columnas según el contenido que tenemos destinado. Así:
A B C D E F G H I
1 Tiempo Y S S(2) a^ b^ Y est Error Y prevista
2
Las expresiones utilizadas serán según el texto de series temporales, y su ubicación en la hoja de cálculo será:
casilla Expresión
S1 = Y1
St = λ Yt + ( 1 - λ) St−1
C2
C3
= B2
= $A$40*B3+(1−−−−$A$40)*C2 arrastrar hasta C32
S1 (2)
= Y1
St (2) = λ S
t + ( 1 - λ) St−1 (2)
D2
D3
= B2
= $A$40*C3+(1−−−−$A$40)*D2 arrastrar hasta D32
(2)
t t t
ˆ
a = 2S −S E2 = 2*C2−−−−D2 arrastrar hasta E32
(2)
t t
ˆ
b (S S )
1
λ λ
= −
− F2 = ($A$40/(1−−−−$A$40))*(C2−−−−D2) arrastrar hasta F32
t t 1 t 1
ˆ ˆ ˆ
Y = a − +b − G3 = E2+F2 arrastrar hasta G32
t t t ˆt
R = e = Y −Y H3 = B3−−−−G3 arrastrar hasta H32
La casilla G33 se etiqueta como ECM para escribir en H34 la expresión
= SUMA.CUADRADOS(H3:H32) / 30 (error cuadrático medio)
Se deben guardar los valores de cada λλλλ y su ECM, a fin de escoger la óptima. En casillas vacías preparamos una tabla como la que muestra la figura 4.4, donde etiquetamos C40 como Lambda y D40 como E.C.M. A continuación escribimos en C41 el valor 0,1 ( λ utilizada en los cálculos) y en D41 7,777, valor resultante de ECM según ha salido en la casilla H34. (Esto lo podemos hacer manualmente o con Cortar y Pegar sólo valores).
Sustituyendo el valor de λ de la casilla A40 por 0,2, automáticamente cambiarán todos los valores de los cálculos de las columnas C − H. Ahora anotamos 0,2 en C42 y el valor de H34 (2,679 en este caso) en D42.
Sucesivamente se van cambiando los valores de λ (A40) por 0,3,… 0,9, y anotando junto con sus ECM, desde C43−−−−D43 hasta C49−−−−D49. Con los datos actuales, se detecta que el óptimo estará entre 0,4 y 0,5; por tanto, ponemos 0,45 en A40 y lo pasamos a C50 juntamente con su ECM (H34), que en este caso es igual a 1,822 y que anotamos en D50.
39 40 41 42 43 44 45 46 47 48 49 50 51
A B C D E F G H
0,45 Lambda E.C.M.
0,1 7,777
0,2 2,679
0,3 1,989
0,4 1,836
0,5 1,833
0,6 1,917
0,7 2,077
0,8 2,321
0,9 2,669
0,45 1,822
0 2 4 6 8
0 0,5 1
ECM
λ
Fig. 4.4
Previsiones
Una vez escogida la λ de trabajo, se puede pasar a calcular los valores previstos para los próximos tres días, según se ha deducido del correlograma. Por eso prolongamos la columna A con los tres nuevos valores del tiempo (A33 = 32; A34 =33; A35 = 34).
El valor previsto para el instante t + T (31+T, en el caso de la práctica) es
t T ˆt ˆt
Y+ = a + b T
En la casilla I33, perteneciente a la columna I etiquetada como Y prevista, se deberá escribir la expresión
y arrastrarla hasta I35. Así acabamos de calcular los valores previstos para los próximos tres días del índice económico estudiado. Todos los resultados se muestran en la página 141.
Análisis de los gráficos
Seguidamente, y como ya es habitual, se procederá a la obtención del gráfico de los valores reales, los modelizados y los previstos, y del gráfico de los errores.
Para ello se selecciona, presionando la tecla Ctrl, desde B1 hasta B32 (valores de Y), desde G1 hasta G32 (valores de Y estimada) y desde I1 hasta I35 (valores de Y prevista), y se presiona el icono de gráficos , o también, en la barra de herramientas, Insertar y después Gráfico.
En el Asistente para Gráficos seleccionar XY (Dispersión)
(3,1) Dispersión con puntos de datos conectados por líneas Terminar
Situaremos el gráfico como Objeto en la misma hoja, y lo editaremos en la posición y el tamaño deseados. El resultado es el gráfico de la página 142.
Para obtener el gráfico de los errores se procede seleccionando desde H1 hasta H32 (valores de los errores) y exactamente igual que en el gráfico anterior. El resultado es el gráfico de la página 143.
