Correlación y regresión en excel (Ejemplos)

Vista previa del material en texto

PRONOSTICOS, CORRELACION Y REGRESION 
TÉRMINOS CLAVE 
Serie de tiempo: Conjunto de observaciones de una variable medida en puntos sucesivos en el tiempo a lo largo de períodos de 
tiempo sucesivos. 
 
Pronóstico: Proyección o predicción de valores futuros de una serie de tiempo. 
 
Tendencia: Cambio o movimiento gradual de una serie de tiempo a valores relativamente superiores o inferiores durante plazos 
relativamente largos. 
 
Componente Cíclico: Componente de una serie de tiempo que explica el comportamiento periódico de la misma, a veces por encima 
de la tendencia y a veces por debajo de ella a lapsos de tiempo mayores a un año. 
 
Componente Estacional: Componente de una serie de tiempo que representa la variabilidad de los datos debido a influencias 
estacionales. Se supone de manera usual que este componente se relaciona con las estaciones del año (primavera, verano, etc.), pero 
puede tener duraciones menores (semanal, mensual, etc). 
 
Componente irregular: Componente de una serie de tiempo que explica su variabilidad aleatoria. 
 
Promedios Móviles: Método de suavización que usa el promedio de n datos más recientes en la serie de tiempo como el pronóstico 
para el siguiente periodo. 
 
Error cuadrado medio (ECM) Enfoque para medir la precisión de un modelo de pronóstico. Esta medida es el promedio de la suma del 
cuadrado de las diferencias entre los valores de la serie de tiempo real y los valores pronosticados. 
 
Promedios móviles ponderados: Método de suavización que emplea un promedio ponderado de los n datos más recientes como el 
pronóstico. 
 
Suavización exponencial: Método de suavización que usa un promedio ponderado de los valroes pasados de la serie de tiempo como 
pronóstico: es un caso especial del método de promedios móviles ponderados en el que seleccionamos sólo un peso: el peso para la 
observación más reciente. 
 
Constante de suavización: En el modelo de suavización exponencial, la constante de suavización es el peso dado al valor real de la 
serie de tiempo en el periodo t. 
 
Índice estacional: Medida del efecto estacional en una serie de tiempo. Un índice estacional arriba de 1 indica un efecto positivo (el 
dato mayor que el marcado por la tendencia), un índice estacional de 1 indica que no hay efecto estacional y un índice estacional 
menor que 1 indica un efecto negativo (el dato es menor que el indicado por la tendencia). 
 
Serie de tiempo desestacionalizada: Serie de tiempo a la que se le ha eliminado el efecto del componente estacional dividiendo cada 
observación original de la serie de tiempo entre el índice estacional correspondiente. 
 
Análisis de regresión: Técnica estadística usada para elaborar una ecuación matemática que muestra cómo se relacionan las 
variables. 
 
Diagramas de dispersión: Grafica que muestra la forma en que los puntajes de dos variables cualesquiera X y Y están dispersas. 
 
Correlación: Es la medida numérica de la intensidad de la relación lineal entre dos variables. 
 
Grados o tipos de correlación: 
 
 
Para interpretar un coeficiente de correlación hay que tener en cuenta por un lado su magnitud (relación entre las variables) y por otro 
su signo (tipo de relación). 
 
Regresión: Consiste en predecir o pronosticar los valores de una variable Y conociendo los valores de otra variable X.La regresión se 
identifica con una línea recta que se dibuja a través del diagrama de dispersión. 
 
 
FÓRMULA PARA ENCONTRAR EL COEFICIENTE DE CORRELACIÓN 
 
FÓRMULAS PARA LA REGRESIÓN 
 
 
EJEMPLO DE REGRESIÓN Y CORRELACIÓN UTILIZANDO MICROSOFT EXCEL 2007 
A continuación se presentan las ventas trimestrales en miles de dólares de diez restaurantes localizados cerca de una Universidad y la 
población estudiantil en miles que acude a dichos restaurantes: 
 
Calcule el coeficiente de correlación, elabore la ecuación de la regresión y pronostique las ventas trimestrales para una población 
estudiantil de 10 
Pasos: 
 
1. Encontrar el coeficiente de correlación 
 
a. Situe el cursor en la celda donde desea que Excel proporcione el resultado. 
 
b. Seleccione el menú fórmulas y click en insertar función 
 
c. Seleccione la categoría de estadísticas y posteriormente COEF.DE.CORREL presione aceptar 
 
d.En la matriz 1 elija la columna "x" y en la matriz 2 la columna "y" click en aceptar 
 
 
2. Encontrar los valores de la ecuación de regresión. 
 
a. Situe el cursor donde desea que aparezcan los valores. 
 
b. Agregue las herramientas de análisis de excel si no las tiene activas para ello: situe el cursor en la parte superior de los menus, click 
derecho, elegir personalizar barra de herramientas de acceso rápido 
 
c. Elegir Complementos, en la parte inferior elija complementos de excel ir a 
 
d. Elija herramientas para análisis y click en aceptar. 
 
e. Click en análisis de datos, a continuación elija regresión y click en aceptar 
 
f. En rango y de entrada elija la columna de datos "y" y en rango x de entrada elija la columna de datos "x" en rango de salida elija la 
celda donde desea el resultado y haga click en aceptar. 
 
g. En la imagen puede observar que obtuvo el valor de la correlación y los valores de las variables b0 y b1 para la ecuación de la 
regresión lineal en este caso los valores son: 60 y 5 respectivamente. 
 
3. Pronostique para cada población estudiantil incluyendo la solicitada que es 10. 
 
4. Realice la gráfica tomando en cuenta las ventas trimestrales y el valor estimado (pronóstico) 
 
 
 
 
 
 
EJEMPLO DE MÉTODOS DE SUAVIZACIÓN 
A continuación se le presentan las ventas en miles de galones de gasolina para una empresa, los resultados contabilizados son para 
12 semanas: 
 
Se le solicita: 
 
1) Realizar un promedio móvil tomando 3 semanas como referencia. 
2) Realizar un promedio móvil ponderado asignandole un peso de 3 al dato mas reciente, 2 al siguiente y 1 al anterior. 
3) Realizar un promedio de suavizado exponencial usando 0.2 como valor de alfa. 
4) Determinar el error cuadrado medio para cada método con el fin de determinar el más confiable. 
5) Graficar cada uno de los pronósticos. 
 
PROMEDIO MÓVIL 
 
 
Lo que se realizo fue un promedio de las tres anteriores semanas (17+21+19)/3=19 para calcular la cuarta semana y asi 
sucesivamente hasta la doceava semana. 
 
Para calcular el error de pronóstico basta con restar el valor de las ventas - pronóstico. 
 
El error de pronóstico al cuadrado consiste en elevar cada dato del error de pronóstico al cuadrado. 
 
Para determinar el error cuadrado medio se realiza la sumatoria total del pronóstico al cuadrado dividido dentro del número de datos 
es decir 92/9 = 10.22 
 
 
 
 
 
 
 
 
PROMEDIO MÓVIL PONDERADO 
 
 
Para calcular el promedio movil ponderado se multiplican cada uno de los pesos asignados en este caso: 3,2 y 1 por las ventas y el 
resultado se divide dentro de la sumatoria de cada uno de los pesos (vea la barra de formulas en la imagen) 
 
SUAVIZADO EXPONENCIAL 
 
 
Para calcular un pronóstico de suavizado exponencial es necesario tomar en cuenta el valor de alfa que para este caso es 0.2 
 
La particularidad de este método es que siempre empieza en la segunda posición y alli se coloca el primer dato de las ventas que es 
17. 
 
Para el resto de pronósticos (semana 3 en adelante)el cálculo es:(valor de alfa 0.2 * ventas de la semana anterior 17) + (el 
complemento de alfa 0.8 * el suavizado exponencial anterior 17) = 17.80 y asi sucesivamente 
 
 
GRÁFICA UNIFICADA DE LOS PRONÓSTICOS 
 
 
De lo anterior se deduce que el método mas confiable es el de suavizacion exponencial porque tiene el menor error y se observa en la 
grafica con la línea de color purpura 
 
EJEMPLO DE TENDENCIA Y DESESTACIONALIZACION 
A continuación se le presentan las ventas de cuatro años de una empresa que vende televisores: 
 
Se le solicita: 
 
1)Desestacionalizar las ventas y brindar un pronóstico de venta de televisores para el quinto año. 
 
PASOS 
 
1) Calcular el promedio móvil. 
 
Se toman encuenta los datos del año 1 y los cuatro trimestes para el primer promedio (4.8+4.1+6+6.5)/4=5.35, para el segundo 
promedio se toma las ventas del segundo trimestre del primer año hasta las ventas del primer trimestre del segundo año 
(4.1+6+6.5+5.8)/4=5.6 
y asi sucesivamente 
 
2. Calcular el promedio móvil centrado 
 
Para este caso se agrupan los pronosticos de dos en dos y se realiza un promedio de ambos por ejemplo para el primer y segundo 
promedio centrado sería: 
 
(5.35+5.6)/2=5.475 
(5.6+5.875)/2=5.7375 
Y asi sucesivamente hasta terminar la serie. 
 
3. Determinar el valor estacional irregular 
 
Se divide cada una de las ventas dentro del promedio movil centrado para el primer y segundo índice sería: 
 
(6/5.475)=1.096 
(6.5/5.7375)=1.133 
Y así sucesivamente hasta terminar la serie. 
 
 
4. Calcular el índice estacional 
 
Para determinar los índices estacionales de la serie se toma en cuenta los valores estacionales irregulares de cada uno de los 
trimestres y se realiza un promedio: 
 
Trimestre 3 (1.096+1.075+1.109)/3=1.09 
Trimestre 4 (1.133+1.156+1.141)/3=1.14 
Trimestre 1 (0.971+0.918+0.908)/3=0.93 
Trimestre 2 (0.840+0.839+0.834)/3=0.84 
 
5. Desestacionalizar las ventas 
 
Para ello se divide las ventas dentro del índice estacional: 
 
4.8/0.93=5.16 
4.1/0.84=4.88 
Asi sucesivamente hasta terminar la serie 
 
 
6. Realizar el pronóstico para el quinto año 
 
Para ello se calculan los valores de la ecuación de la regresión lineal (veáse el tema de regresión) y se efectua el pronóstico para cada 
trimestre en este caso la ecuación quedaría de la siguiente forma: 
 
Y=5.1+0.15x 
 
 
7. Efectuar la gráfica de las ventas desestacionalizadas 
 
 
 
Fuente: http://metodoscuantitativo2.galeon.com/enlaces2217022.html 
http://metodoscuantitativo2.galeon.com/enlaces2217022.html