Análisis de Regresión con Excel

ANÁLISIS DE REGRESIÓN Y DE CORRELACIÓN LINEAL SIMPLE con Excel 2007. Estadística II Prof. Titular: Marcelo Dreyfus, MBA __________________________________________________________________________________________________

1

Ejemplo

Un gerente de ventas sostiene que la experiencia es fundamental a la hora de concretar una operación. A fin de darle mayor crédito a su aseveración, selecciona un grupo de diez vendedores de su empresa con distintos años de experiencia

en el rubro y estudia los reportes de ventas anuales (en miles de $) de los mismos.

Aquí lo que interesa es determinar si las ventas están relacionadas con la experiencia de los vendedores y eventualmente hallar un modelo que permita predecir las ventas anuales (Y, variable dependiente o explicada) en función de los años de

experiencia de los vendedores (X, variable independiente o explicativa). 𝑌 = 𝛽0 + 𝛽1. 𝑥 + 𝜀𝑖

A-Procedimiento para realizar el gráfico de Dispersión:

1. Se registran los datos en una tabla, y se selecciona el rango:

2. Se crea el gráfico desde el Menú, Insertar>Dispersión:


2

3. Se obtiene el gráfico de dispersión:

4. Se edita el Diseño, haciendo click en cada uno de los íconos. Por ejemplo, si se selecciona el Diseño 3, se obtiene

el gráfico con la línea de tendencia:


3

5. Se edita el nombre del eje, haciendo click con el botón derecho, en Título del eje. Se despliega un menú para

cambiar el formato, el texto, la fuente, el color, etc.

6. Para el ejemplo, se modifican ambos títulos:


4

7. Se edita el formato de la línea de tendencia, haciendo click con el botón derecho sobre la recta:

8. Se despliega un cuadro, en él se tildan las opciones: Presentar ecuación en el gráfico, y Presentar el valor de

R_cuadrado en el gráfico.


5

9. Se obtiene la ecuación de la recta, y el valor del R2, pero no pueden considerarse estadísticamente significativos,

sin realizar los test correspondientes (se desarrolla en el ítem B).


6

B-Procedimiento para el análisis de Regresión:

1. Se accede al menú: Datos> Análisis de datos

2. Se despliega un cuadro, en él se elige la opción: Regresión. Y luego se hace click en el botón Aceptar.


7

3. Se abre un nuevo cuadro como indica la figura:

4. Se completa cada ítem: a. Entrada

Rango Y de Entrada: Se seleccionan los valores de la variable dependiente o respuesta.

Click para ingresar

el rango


8

Se marca el rango que contiene a la variable, y se presiona Enter, o bien, se completa el cuadro: $C$1:$C$11.

Rango x de Entrada: Se repite el mismo procedimiento, pero para la variable independiente.

Click para

ingresar el

Rango


9

Se selecciona el Rango con el mouse y se presiona Enter, o se ingresa como $B$1:$B$11.

En este mismo bloque, se tildan las opciones, según corresponda: Rótulos: si el Rango elegido anteriormente incluye el nombre de la variable (como en este ejemplo). Nivel de Confianza: se puede editar el porcentaje, que por defecto es del 95%. Constante Igual a cero: Sólo se selecciona en el caso que la ecuación de la recta estimada incluya al origen de

coordenadas, es decir, sólo si b=0 (No es el caso para este ejemplo).

Click para regresar


10

b. Opciones de Salida: Se marca una de tres alternativas, que la salida sea en la misma hoja de cálculo, en una hoja nueva o en

un libro nuevo.


11

Para este ejemplo, se elige una celda de la hoja activa:

c. Residuales. Se tildan las opciones: Residuos: muestra el listado de Residuos ɛi. Los Residuos resultan de la diferencia entre el valor de la variable, Yn, y el valor estimado por la ecuación de la recta de Regresión, �̂�. Residuos estándares: muestra el listado de Residuos ei estandarizados, es decir, los que resultan luego de restar la Esperanza (que es 0), y dividir por el Desvío Estándar. Gráfico de Residuales: incluye el gráfico de los Residuos, según los valores de la variable independiente (en este caso, los Años de experiencia) Curva de regresión ajustada: incluye el gráfico de dispersión entre la variable independiente (Años de experiencia) y la dependiente (Ventas Anuales), pero incluye además los valores pronosticados por la regresión (las Ventas Anuales pronosticadas por el modelo de regresión). Gráfico de Probabilidad Normal: genera un gráfico con probabilidad normal.

Click para regresar


12

5. Se obtiene la salida con las tablas y tres gráficos.


13

C- Análisis de la salida

Tabla Estadísticas de la Regresión

Estadísticas de la regresión

Coeficiente de correlación múltiple 0,820197259

Coeficiente de determinación R^2 0,672723544

R^2 ajustado 0,631813988

Error típico 8,091455674

Observaciones 10

Coeficiente de determinación R2: Es el primer método de validación y es una medida de la bondad de ajuste del modelo, e indica el porcentaje de variabilidad de la variable Y, que es explicada por X. Para el ejemplo, el 67% de la variabilidad de las ventas Anuales, está explicada por los años de experiencia del vendedor.

Para el ejemplo: 𝑟2 =𝑆.𝐶.𝑅

𝑆.𝐶.𝑇.=

1076,626761

1600,4= 𝟎, 𝟔𝟕𝟐𝟕𝟐𝟑𝟓𝟒𝟒

Coeficiente de correlación múltiple: Es una medida del grado de asociación lineal entre ambas variables. Se calcula como la raíz cuadrada del r2 y lleva el mismo signo que la pendiente. Cabe aclarar que el Excel omite el signo si la correlación es inversa.

R2 ajustado: sólo tiene sentido en los modelos de regresión múltiple; en este caso no se lo utiliza ya que se trata de un modelo simple.

Error típico: Es la raíz cuadrada de la varianza residual.

Para el ejemplo: 𝐸𝑟𝑟𝑜𝑟 𝑡í𝑝𝑖𝑐𝑜 = √65,47165493 = 8,091455674 (aprox.) Observaciones: tamaño de la muestra, n.

Tabla Análisis de Variancia

ANÁLISIS DE VARIANZA Grados de libertad Suma de cuadrados Promedio de los cuadrados F Valor crítico de F

Regresión 1 1076,626761 1076,626761 16,44416598 0,003658017

Residuos 8 523,7732394 65,47165493

Total 9 1600,4

Grados de libertad: como el tamaño de muestra es 10, resultan 9 g.l. , corresponde 1 para la regresión (una variable independiente), y 8 para los residuos.

Suma de cuadrados: Se calcula para la regresión y para los residuos (denominados “error”) como: o Suma de cuadrados de la Regresión: S.C.R.: ∑(�̂� − 𝑦)2

o Suma de cuadrados del error: S.C.E.: ∑(𝑦 − �̂�)2 Promedio de Cuadrados1: Es el cociente entre la suma de cuadrados y g.l. Se denomina también Cuadrado

Medio. Para el ejemplo:

o El cuadrado medio de la Regresión: 𝐶. 𝑀. 𝑅. =𝑆𝐶𝑅

𝑔 .𝑙.=

1076 ,626761

1= 1076,626761

o El cuadrado medio de los Residuos, o del Error: 𝐶. 𝑀. 𝐸. =𝑆𝐶𝐸

𝑔 .𝑙 .=

523,7732394

8= 65,47165493

Test de significatividad de la pendiente: Corresponde al segundo método de validación y contrasta la hipótesis de que el coeficiente de regresión del modelo (β1) es igual a 0. Es decir:

o H0) β1=0 (no hay asociación lineal entre las variables) o H1) β1≠0 (se prueba la asociación lineal)

1 Los g.l. resultan de calcular: g.l. =n-(k+1), k es la cantidad de variables colineales.


14

𝑆𝑏1= √

𝑆.𝐶. 𝐸.[𝑛 − (𝑘 + 1)]. ∑(𝑥𝑖 − 𝑥)2 = √

65,47165493

[10 − 1]. 15,77778= 𝟎, 𝟔𝟕𝟗𝟎𝟐 (𝒂𝒑𝒓𝒐𝒙.)

𝑡𝑐𝑎𝑙𝑐 =𝑏1 − 0

𝑆𝑏1

=2,753521127

0,679019876= 𝟒, 𝟎𝟓𝟓𝟏𝟒𝟎𝟔𝟖𝟓 (𝑎𝑝𝑟𝑜𝑥 . )

Error típico: Para la variable años de experiencia:

Estadístico t: Realiza la prueba t para analizar la nulidad del coeficiente lineal:


15

El p-value obtenido (0,003658017), es menor que el riesgo prefijado α (0,05). Por lo expuesto, se puede concluir que existe evidencia para rechazar la hipótesis nula. Luego, con un 5 % de riesgo, puede afirmarse que el coeficiente no es nulos. Una vez realizada la prueba, tiene sentido analizar la siguiente tabla:

Coeficientes Error típico Estadístico t Probabilidad Inferior 95% Superior 95% Inferior 95,0% Superior 95,0%

Intercepción 89,12535211 5,398101252 16,51050026 1,82769E-07 76,67730831 101,5733959 76,67730831 101,5733959

Años de experiencia 2,753521127 0,679019876

4,055140685 0,003658017 1,187698485 4,319343768 1,187698485 4,319343768

Coeficientes: muestra el valor de los coeficientes. La ecuación de la recta, resulta:

�̂� = 𝑏0 + 𝑏1. 𝑥 �̂� = 89,125 + 2,754. 𝑥

Lo que indica que las Ventas Anuales (Y) pueden explicarse a partir de los Años de experiencia (X). Por cada año de experiencia, las ventas se incrementan en 2,754 miles de pesos.

Tabla Análisis de Residuales

Observación Pronóstico Ventas Anuales Residuos Residuos estándares

1 124,9211268 -2,921126761 -0,382912907

2 91,87887324 -8,878873239 -1,163877996

3 119,4140845 -2,414084507 -0,316447793

4 100,1394366 -8,13943662 -1,066949705

5 105,6464789 9,353521127 1,226096728

6 97,38591549 -0,385915493 -0,050587337

7 116,6605634 -6,66056338 -0,873093122

8 111,1535211 -0,153521127 -0,02012416

9 100,1394366 13,86056338 1,816897741

10 116,6605634 6,33943662 0,83099855

Por ejemplo, para el primer vendedor, que corresponde al primer dato de la tabla:

Vendedor Años de

experiencia Ventas Anuales

1 13 122 Pronóstico de Ventas Anuales: 𝑦1̂ = 89,125 + 2,754. 𝟏𝟑 = 124,92 (aprox.)

Residuos: 𝑦1 − 𝑦1̂ = 𝟏𝟐𝟐 − 124,92 = −2,92 (aprox.)

Gráficos


16

Este gráfico muestra, para cada uno de los valores de la variable predictora, (los Años de Experiencia) el residuo para esa estimación. Los valores pueden observarse en la tabla anterior, de Análisis de Residuales. Se indica en rojo el valor que

se ha calculado como ejemplo.

Este gráfico de dispersión incluye la variable original (Ventas Anuales), y los valores estimados por el modelo (Pronóstico

Ventas Anuales).

-12

-10

-8

-6

-4

-2

0

2

4

6

8

10

12

14

16

0 1 2 3 4 5 6 7 8 9 10 11 12 13 14

Re

sid

uo

s

Años de experiencia

Años de experiencia Gráfico de los residuales

0102030405060708090

100110120130140

0 1 2 3 4 5 6 7 8 9 10 11 12 13 14

Ve

nta

s A

nu

ale

s

Años de experiencia

Años de experiencia Curva de regresión ajustada

Ventas Anuales Pronóstico Ventas Anuales

Análisis de Regresión con Excel

Documents

Transcript of Análisis de Regresión con Excel