Como hacer una regresión lineal en Excel

Como hacer una regresión lineal en Excel
En el siguiente artículo les mostraremos como hacer una regresión lineal univariada utilizando el complemento de análisis de datos de Microsoft Excel, les explicaremos que es este complemento, como activarlo y que otros usos tiene. Finalmente utilizaremos esta herramienta para calcular el coeficiente Beta (β), que es una medida de volatilidad ampliamente utilizada en modelos vistos en cursos financieros, para la empresa chilena CCU (Compañía de Cervecerías Unidas) en el periodo 2010.

¿Qué es el complemento de análisis de datos y como activarlo?Análisis de datos es una herramienta de Excel que permite desarrollar análisis estadísticos complejos entre los que podemos encontrar:

1. Análisis de Fourier
2. Correlación
3. Covarianza
4. Estadística descriptiva
5. Generación de números aleatorios
6. Histograma
7. Jerarquía y percentil
8. Media móvil
9. Muestreo
10. Prueba t
11. Prueba t para varianzas de dos muestras
12. Prueba z
13. Regresión
14. Suavización exponencial
15. Varianza

Nosotros nos enfocaremos en la herramienta de regresión, esta confecciona el análisis de regresión lineal que utiliza el procedimiento de "mínimos cuadrados" para descubrir como una variable es explicada por otra u otras. Esta herramienta usa la función ESTIMACION.LINEAL.

Para activar el complemento:
Se debe hacer clic en el botón de inicio de Excel , luego seleccionar Opciones de Excel y, a continuación, haga clic en la categoría Complementos.

En el cuadro Administrar, seleccione Complementos de Excel y, a continuación, hacer clic en Ir.

En el cuadro Complementos disponibles, activar la casilla de verificación Herramientas para análisis y, a continuación, haga clic en Aceptar.

¿Cómo funciona?
Para mostrar cómo funciona debemos tener una base de datos con por lo menos una variable dependiente y una independiente como la siguiente:

Luego vamos a datos y hacemos clic en análisis de datos y finalmente en regresión


La ventana de Regresión pedirá los siguientes datos:

• Rango Y: donde se indicará la localización de los datos de las variables dependientes
• Rango X: donde se indicará la localización de los datos de las variables independientes
• Rango de Salida: indicar las celdas donde se quieren colocar los resultados.
• en una hoja nueva: esta opción permite entregar los resultados de la regresión en una hoja nueva la cual nosotros podemos nombrar en la casilla de al lado.

Luego de ingresar los datos se debe presionar Aceptar para que los datos sean procesados. Una vez hecho esto aparecerá la hoja de resultados, en el ejemplo al final de este artículo veremos aquella hoja en profundidad.

Aplicaciones profesionales del complemento

Las herramientas de varianza, correlación, covarianza, histograma, jerarquia y percentil, y prueba t para varianzas de dos muestras pueden ser usadas para análizar la dispersión de datos en muestras, por su parte la herramienta de estadisticas descriptivas puede ser usado para obtener medidas de tendencia central, esto es bastante usado en el campo de investigación de mercados si es que no se cuenta con un programa más sofisticado como SPSS o Stata.

La herramienta de generación de números aleatorios puede ser usado para hacer simulaciones, algo que es usado en el área de gestión de operaciones. Las herramientas de suavización exponencial y media movil son usadas en una variedad de áreas para hacer pronosticos con datos de series de tiempo.

Por su parte, las herramientas de prueba t y prueba z sirven mucho para hacer contrastes de hipotesis, en particular la primera sirve para muestras pequeñas y la segunda para muestras de 30 datos o más. También la prueba t es usada para probar diferencia de medias.

Finalmente la herramienta de regresión tiene variados usos en marketing mostrando, por ejemplo, la influencia de la inversión publicitaria sobre variables de marketing o en finanzas ayudandonos a estimar datos necesarios para los modelos, como el que aborda el siguiente ejemplo.

Ejemplo: Calculando el beta de CCU
A continuación calcularemos el coeficiente Beta (β) para la acción de CCU durante el 2010. Para ello utilizaremos el siguiente modelo de regresion lineal que dice que el retorno de la acción (Ri) es explicado por el retorno de mercado (Rm), donde el coeficiente Beta indica el grado de volatilidad respecto al mercado de la acción. Lo anterior queda enunciado en la siguiente ecuación:
En nuestra hoja de cálculo (descargar aquí) tenemos los últimos 120 retornos semanales para la acción de CCU y para el índice IGPA respecto al último día del 2010. El índice IGPA, en este caso, será utilizado como una medida del mercado y nuestra acción a investigar será la de la compañia CCU.

A continuación abriremos el complemento de análisis de datos como lo indicado anteriormente (Datos-> Análisis-> Análisis de datos-> Regresión). Se nos abrirá una ventana con las opciones del complemento, donde nos pide la ubicación de las celdas del Rango Y (variable dependiente) y del Rango X (variable independiente), que bajo nuestro modelo es Ri y Rm respectivamente.

Rellenamos los datos, donde el Rango Y llevará los datos para los retornos de la acción de CCU y el Rango X llevará los datos de los retornos del IGPA. Solicitaremos los resultados en una hoja nueva, de nombre "beta2010". También es recomendado pedir la curva de regresión ajustada, que es un gráfico nos permitiría ver de manera visual que tanto se ajusta nuestra regresión a los datos pedidos, pero los omitiremos esta vez del análisis.

Al apretar aceptar se generará una nueva hoja con los resultados de nuestra regresión, en la siguiente imagen hemos puedo en negrita y se enumeraron los resultados más relevantes para nuestro análisis

1. El R^2 ajustado, indice que el 35% de la varianza total es explicada por el modelo. Los resultados cercanos al 40% son comunes en estos ejemplos.
2. Los coeficientes de regresión nos entregan en nuestro caso el coeficiente beta y alpha. El coeficiente beta en este caso sería el asociado a la variable X1 (retornos del mercado) y toma un valor de 0,914.
3. La probabilidad o más conocido como p-value, deben ser menor al nivel de riesgo asumido en el caso del coeficiente beta. Por lo general se asume un 5% de riesgo, por lo que nuestro coeficiente beta es significativo (dado que 0,05>0,000000000000461). Por su parte el coeficiente alpha no es significativo (dado que 0,05<0,7739) , lo que es normal en este tipo de modelos pues se asume que el alpha es cero.

En conclusión, hemos podido encontrar que CCU tuvo un beta durante 2010 de 0,914, el cual es significativo por lo que es posible de usar en nuestros modelos financieros.

Fuentes

• http://support.microsoft.com/kb/914208/es
• http://blog.neuronaltraining.net/?p=11438

Autores:

• Edith Araya
• Sebastián Cisterna A. (@scisterna)
• Jorge Urbina