Regresión Lineal Con Excel

Descargar como pdf o txt
Descargar como pdf o txt
Está en la página 1de 14

Se recolectan datos de una muestra de 10 restaurantes Armand’s Pizza Parlors ubicados todos cerca de campus

universitarios.
a) Elabore un diagrama de dispersión.
b) Calcule la ecuación de la recta de regresión para pronosticar las ventas trimestrales a partir de la población
c) Estime las ventas semanales si los costos de publicidad son de $35.
d) Grafique los residuales en comparación con los costos de publicidad. Comente sus resultados.

(X) Población estudiantes *1000 (Y) Ventas Trimestral *1000


(X) Población
(Y) Ventas Trimestral
estudiantes
*1000
*1000

1 2 58

2 6 105
3 8 88
4 8 118
5 12 117
6 16 137
7 20 157
8 20 169
9 22 149
10 26 202

Resolver usando Excel


Hoja de Excel

Se colocan los datos identificados en cualquier columna de Excel, con su identificación:


Se va a la barra de herramientas, se ubica en datos, aparece la siguiente pantalla:

Se ubica en análisis de datos se da enter, aparece la siguiente pantalla


Se coloca aceptar, aparece la siguiente pantalla

Entrada:
En el Rango Y de entrada, se coloca los datos de la variable Y con el título (rótulo); se coloca el cursor en la
columna D y fila 4 y se arrastra hasta la columna D y fila 14.
En el Rango X de entrada, se coloca los datos de la variable X con el título (rótulo); se coloca el cursor en la
columna C y fila 4 y se arrastra hasta la columna C y fila 14.
Se marca rótulo.
Opciones de salida:

Se marca en una hoja nueva

Residuales:

Se marca residuos, residuos estándar, gráficos de residuales, curva de regresión ajustada.

Probabilidad normal:

Gráfico de probabilidad normal.

Se da aceptar y los resultados se presentan en otra hoja


Los resultados que dan son:

Resumen

Estadísticas de la regresión
Coeficiente de correlación múltiple 0,95012296
Coeficiente de determinación R^2 0,90273363
R^2 ajustado 0,89057533
Error típico 13,8293167
Observaciones 10

Probabilidad
Coeficientes Error típico Estadístico t (p-valor) Inferior 95% Superior 95%
Intercepción 60 9,2260348 6,5033355 0,0001874 38,7247256 81,2752744
(X) Población
estudiantes
*1000 5 0,5802652 8,6167492 0,0000255 3,6619060 6,3380940

ANÁLISIS DE VARIANZA
Grados de Suma de Promedio de Valor crítico
libertad cuadrados los cuadrados F de F
Regresión 1 14200 14200 74,248366 2,5489E-05
Residuos 8 1530 191,25
Total 9 15730
Interpretación de los datos de la tabla Estadística de la regresión

Resumen
Estadísticas de la regresión
Coeficiente de correlación múltiple 0,95012296
Coeficiente de determinación R^2 0,90273363
R^2 ajustado 0,89057533
Error típico 13,8293167
Observaciones 10

Coeficiente de regresión → 𝑹 = 0,95012296, indica que existe muy alta asociación entre las ventas trimestrales
y la población estudiantil.

Coeficiente de determinación 𝑹𝟐 mide la bondad del ajuste de la recta de regresión a la nube de puntos, el rango
de valores es de 0 a 1. Valores pequeños de 𝑹𝟐 indican que el modelo no se ajusta bien a los datos. El valor de
𝑹𝟐 = 0,90273363, indica que el 90,27% de la variabilidad en las ventas trimestrales se explica por la relación
lineal que existe con el tamaño de la población estudiantil.

𝑹𝟐 𝒂𝒋𝒖𝒔𝒕𝒂𝒅𝒐 = 0,89057533 → ajustado indica que el 89,05% de la variación de la variabilidad en las


ventas se explica por la variación de la población estudiantil, teniendo en cuenta el número de variables
independientes en el modelo (en este caso, solo una). Este valor es menor que el R cuadrado, porque
penaliza la inclusión de variables que no mejoran el ajuste del modelo. El R cuadrado ajustado es más
adecuado para comparar modelos con diferentes números de variables independientes, ya que evita el
sobreajuste
𝒏−𝟏 𝟏𝟎 − 𝟏
𝑹𝟐 𝒂𝒋𝒖𝒔𝒕𝒂𝒅𝒐 = 𝟏 − ( ) ∗ (𝟏 − 𝑹𝟐 ) = 𝟏 − ( ) ∗ (𝟏 − 𝟎. 𝟗𝟎𝟐𝟕) = 𝟎, 𝟖𝟗𝟎𝟓
𝒏−𝒌 𝟏𝟎 − 𝟐
Error típico es el error típico de estimación (raíz cuadrada de la varianza residual) con un valor igual a 13,8293
(de los valores en la tabla de análisis de varianza se obtiene 𝜎
̂ = 𝑆𝑒 )

𝑛
1 𝑆𝐶𝐸 1533
𝜎̂ = 𝑆𝑒 = √ ∑(𝑦𝑖 − 𝑦̂𝑖 )2 = √ =√ = √191,25 = 13,8293
𝑛−2 𝑛−2 10 − 2
𝑖

Interpretación de los datos de la tabla de análisis de varianza

ANÁLISIS DE VARIANZA
Grados de Suma de Promedio de Valor crítico
libertad cuadrados los cuadrados F de F
Regresión 1 14200 14200 74,248366 2,5489E-05
Residuos 8 1530 191,25
Total 9 15730
En la Tabla ANOVA, se muestra la descomposición de la Variabilidad Total (SCT = 15730) en la Variabilidad debida
a la Regresión (SCR = 14200) y la Variabilidad Residual (SCE = 1530) es decir en Variabilidad explicada por el modelo
de regresión y la Variabilidad no explicada. SCT = SCR + SCE. La Tabla de Análisis de la Varianza (Tabla ANOVA) se
construye a partir de esta descomposición y proporciona el valor del estadístico F que permite contrastar la
hipótesis nula de que la pendiente de la recta de regresión es igual a cero contra la alternativa de que la pendiente
es distinta de cero, es decir:

𝐻0 : 𝛽1 = 0
{
𝐻1 : 𝛽1 ≠ 0

donde 𝐻0 se conoce, en general, como hipótesis de no linealidad entre la población estudiantil (X) y las ventas
trimestrales (Y).

La Tabla ANOVA muestra el valor del estadístico de contraste, F = 74,24, que se define como el cociente entre el
Cuadrado medio debido a la regresión (CMR = 14200) y el Cuadrado medio residual (CME = 191.25), por tanto,
cuanto mayor sea su valor, mejor será la predicción mediante el modelo lineal. El p-valor asociado a F, en la
columna valor crítico de F, es menor que 0.001, menor que el nivel de significación α = 0.05, lo que conduce a
rechazar la hipótesis nula, es decir existe una relación lineal significativa entre las ventas trimestrales y la población
estudiantil. Esto indica que es válido el modelo de regresión considerado, en este caso el modelo lineal simple.

Para encontrar el p valor de 75,248, se realiza la siguiente instrucción en Excel,

DISTR.F.CD(valor Fisher; grado de libertad de la regresión ; grado de libertad del error) =DISTR.F.CD(74,248;1;8)

grado de libertad de la regresión = 1 grado de libertad del error = n - 2 = 10 - 2 = 8 valor Fisher =74,248

Interpretación de los datos de la siguiente tabla


Probabilidad
Coeficientes Error típico Estadístico t (p-valor) Inferior 95% Superior 95%
Intercepción 60 9,2260348 6,5033355 0,0001874 38,7247256 81,2752744
(X) Población
estudiantes
*1000 5 0,5802652 8,6167492 0,0000255 3,6619060 6,3380940

En la columna de coeficientes de la tabla se tiene el coeficiente de intercepción de 60, este es el valor de 𝛽0 =


60, el coeficiente de población estudiantil es 𝛽1 = 5

𝛽0 = 60, indica que las ventas trimestrales son de 60000 $ cuando la población estudiantil es cero.

𝛽1 = 5, indica que las ventas trimestrales aumentan 5000 cuando la población estudiantil aumenta en 1000
estudiantes. Esto significa que hay una relación positiva en ambas variables, a mayor población, mayores ventas
trimestrales.

Por tanto, la ecuación de regresión es:

𝑉𝑒𝑛𝑡𝑎𝑠 𝑡𝑟𝑖𝑚𝑒𝑠𝑡𝑟𝑎𝑙𝑒𝑠 ∗ 1000 = 60 + 5 ∗ 𝑃𝑜𝑏𝑙𝑎𝑐𝑖ó𝑛 𝑒𝑠𝑡𝑢𝑑𝑖𝑎𝑛𝑡𝑖𝑙 ∗ 1000

𝑉𝑒𝑛𝑡𝑎𝑠 𝑡𝑟𝑖𝑚𝑒𝑠𝑡𝑟𝑎𝑙𝑒𝑠 = 60000 + 5000 ∗ 𝑃𝑜𝑏𝑙𝑎𝑐𝑖ó𝑛 𝑒𝑠𝑡𝑢𝑑𝑖𝑎𝑛𝑡𝑖𝑙


𝑌 = 60 + 5𝑋

La tabla anterior presenta los resultados de los dos contrastes individuales de la significación de los parámetros
de 𝛽0 y 𝛽1

𝐻 : 𝛽 =0 𝐻0 : 𝛽1 = 0
{ 0 0 {
𝐻1 : 𝛽0 ≠ 0 𝐻1 : 𝛽1 ≠ 0

El primero de estos contrastes carece de interés en la mayoría de los casos ya que raramente el punto de corte de
la recta de regresión con el eje de ordenadas (ordenada en el origen) será el punto (0,0). Además, dicho punto de
corte carece de significado casi siempre. En nuestro caso, la interpretación de 𝛽0 indica el valor de Y que
correspondería a un valor de X igual a 0.

Usando la Prueba de bondad de ajuste de t- Student, se realiza los siguientes:


Supóngase que se desea probar la hipótesis de que la ordenada es igual a una constante, por ejemplo 𝛽0,0. Las
hipótesis apropiadas son
𝐻0 : 𝛽0 = 𝛽0,0
𝐻1 : 𝛽0 ≠ 𝛽0,0
El estadístico de prueba es:
𝛽̂0 − 𝛽0,0 𝛽̂0 − 𝛽0,0
𝑡= =
𝑠𝑒(𝛽̂0 )
1 𝑥̅ 2
√𝜎̂ 2 [𝑛 + 𝑆 ]
𝑥𝑦

Donde 𝑠𝑒(𝛽̂0 ) es el error estándar de la ordenada al origen


De la tabla se obtiene el error típico de intercepción (𝛽0 ): 𝑠𝑒(𝛽̂0 ) = 9,2260
60 − 0
𝑡= = 6,5033 → 𝑣𝑎𝑙𝑜𝑟 𝑞𝑢𝑒 𝑎𝑝𝑎𝑟𝑒𝑐𝑒 𝑒𝑛 𝑙𝑎 𝑡𝑎𝑏𝑙𝑎 𝑐𝑜𝑚𝑜 𝐸𝑠𝑡𝑎𝑑í𝑠𝑡𝑖𝑐𝑜 𝑡
9,2260
La probabilidad de 0,0002 es el p valor de 6,5033
Para obtener el p valor por Excel, se realiza lo siguiente:

DISTR.T.CD(valor t-Student; grado de libertad) =DISTR.T.CD(6,5033; 8) = 9,3722E-05 9,3722*10-5 este


es el valor que da con Excel.
grado de libertad de t-Student = n - 2 = 10 - 2 = 8 valor t-Student = 6,5033
Se rechaza 𝐻0 si el p-valor es menor que el nivel de significancia 𝛼. Para un nivel de significancia de 0,05
se rechaza 𝐻0 , es decir, 𝛽0 ≠ 0
H0 puede rechazarse si |𝑡| > 𝑡(𝛼⁄ . Si consideramos |𝑡| > 𝑡(0,05⁄ → |𝑡| > 𝑡0,025,8)=2,306
2,𝑛−2) 2,10−2)

(buscando en la tabla t-Student). Es decir, con 0,05⁄2 = 0,025 y 8 grados de libertad se va a la tabla de
la distribución t-Student y su valor es 2,306. Si se desea buscar este valor con Excel, se realiza lo siguiente:
DISTR.T.2CD(nivel de significación; grado de libertad) =DISTR.T.2CD(0,05;8) = 2,30600414 este es el valor
que da con Excel.
Probabilidad
Coeficientes Error típico Estadístico t (p-valor) Inferior 95% Superior 95%
Intercepción 60 9,2260348 6,5033355 0,0001874 38,7247256 81,2752744
(X) Población
estudiantes
*1000 5 0,5802652 8,6167492 0,0000255 3,6619060 6,3380940

El segundo contraste, el contraste de la pendiente de la recta, es una alternativa equivalente al


contraste en la Tabla ANOVA. El estadístico de contraste que aparece en la columna t vale 8,6167 tiene
un p-valor asociado en la columna probabilidad de 0.0000, menor que el nivel de significación α=0.05
que conduce al rechazo de la hipótesis nula y podemos afirmar que existe una relación lineal significativa
entre las Ventas trimestrales *1000 y la Población estudiantil * 1000. En la última columna de la tabla se
muestran los intervalos de confianza para 𝛽0 y 𝛽1 , al 95%. El intervalo para 𝛽1 es (3,6619, 6,3381), no
incluye el cero, es decir, no pertenece al intervalo, esto indica que hay evidencia empírica para concluir
que la Población estudiantil * 1000 influye en las Ventas trimestrales *1000 y por tanto al nivel de
confianza del 95% el parámetro 𝛽1 no podría considerarse igual a cero.

El intervalo para 𝛽0 es (38,7247; 81,2753), puesto que el cero no pertenece al intervalo, hay evidencia
empírica para concluir que 𝛽0 ≠ 0, es decir, la ecuación de regresión no pasa por el origen (punto (0, 0)).

38,724 ≤ 𝛽0 ≤ 81,275

3,661 ≤ 𝛽1 ≤ 6,338
Intervalo de confianza para pendiente 𝜷𝟏 y la ordenada al origen 𝜷𝟎

Bajo la hipótesis de que las observaciones están distribuidas de manera normal e independiente, el intervalo de
confianza para 𝛽1 de 100(1- α) por ciento en una regresión lineal simples es

𝜎̂ 2 𝜎̂ 2
𝛽̂1 − 𝑡(𝛼⁄ √ < 𝛽1 < 𝛽̂1 + 𝑡(𝛼⁄ √
2,𝑛−2) 𝑆𝑥𝑥 2,𝑛−2) 𝑆𝑥𝑥

5 − 2,306 ∗ 0,5802 < 𝛽1 < 5 + 2,306 ∗ 0,5802


5 − 1,338 < 𝛽1 < 5 + 1,338
3,662 < 𝛽1 < 6,338 → 5 ∓ 1,338

De la tabla se obtiene el error típico de intercepción (𝛽1 ): 𝑠𝑒(𝛽̂1 ) = 0,5802652

𝑡(𝛼⁄ = 𝑡(0,05⁄ = 2,306


2,𝑛−2) 2,10−2)

Con 𝛼⁄2 = 0,025 y 8 grados de libertad se va a la tabla de la distribución t-Student y su valor es 2,306.

De la tabla se obtiene el error típico de intercepción (𝛽0 ): 𝑠𝑒(𝛽̂0 ) = 9,2260


El intervalo de confianza para 𝛽0 de 100(1- α) por ciento en una regresión lineal simples es

1 𝑥̅ 2 1 𝑥̅ 2
𝛽̂0 − 𝑡(𝛼⁄ √𝜎̂ 2 [ + ] < 𝛽0 < 𝛽̂0 + 𝑡(𝛼⁄ √𝜎̂ 2 [ + ]
2,𝑛−2) 𝑛 𝑆𝑥𝑦 2,𝑛−2) 𝑛 𝑆𝑥𝑦

60 − 2,306 ∗ 9,2260 < 𝛽0 < 60 + 2,306 ∗ 9,2260


60 − 21,275 < 𝛽0 < 60 + 21,275
38,725 < 𝛽0 < 81,275

Diagrama de dispersión

Ventas Trimestral vs Población estudiantil


250
Ventas trimestrales *1000

200

150

100

50

0
0 5 10 15 20 25 30
Población estudiantil *1000

Diagrama de dispersión

Con la ecuación de regresión


Ventas Trimestral vs Población estudiantil
250
Ventas trimestrales *1000

200

150

100

y = 5x + 60
50 R² = 0,9027

0
0 5 10 15 20 25 30
Población estudiantil *1000

Ventas trimestrales vs Población estudiantil


250
Ventas trimestrales *1000

200

150

100

50

0
0 5 10 15 20 25 30
Población estudiantil * 1000

(Y) Ventas Trimestral *1000 Pronóstico (Y) Ventas Trimestral *1000


Ventas trimestrales vs Población estudiantil

250
Ventas trimestrales *1000

200

150
y = 5x + 60
100 R² = 0,9027

50

0
0 5 10 15 20 25 30
Población estudiantil * 1000

(Y) Ventas Trimestral *1000


Pronóstico (Y) Ventas Trimestral *1000
Lineal ((Y) Ventas Trimestral *1000)
ANÁLISIS DE VARIANZA
Grados de Suma de Promedio de
libertad cuadrados los cuadrados F Valor crítico de F
Regresión 1 14200 14200 74,248366 2,5489E-05
Residuos 8 1530 191,25
Total 9 15730

Estadístico Inferior Superior Inferior Superior


Coeficientes Error típico t Probabilidad 95% 95% 95,0% 95,0%
Intercepción 60 9,2260348 6,5033355 0,0001874 38,7247256 81,2752744 38,7247256 81,2752744
(X) Población
estudiantes *1000 5 0,5802652 8,6167492 0,0000255 3,6619060 6,3380940 3,6619060 6,3380940

Análisis de los residuales Resultados de datos de probabilidad

Pronóstico (Y) Ventas Trimestral Residuos


Observación *1000 Residuos estándares Percentil (Y) Ventas Trimestral *1000
1 70 -12 -0,92035799 5 58
2 90 15 1,15044748 15 88
3 100 -12 -0,92035799 25 105
4 100 18 1,38053698 35 117
5 120 -3 -0,2300895 45 118
6 140 -3 -0,2300895 55 137
7 160 -3 -0,2300895 65 149
8 160 9 0,69026849 75 157
9 170 -21 -1,61062648 85 169
10 190 12 0,92035799 95 202

También podría gustarte