instructivo regresiÓn lineal en excel
TRANSCRIPT
UNIVERSIDAD SIMON BOLIVAR PROGRAMA DE ADMINISTRACION DE EMPRESAS ADMINISTRACION DE LA PRODUCCION
Regresión Lineal en Excel Yesid Ariza Osorio
1
INSTRUCTIVO PARA EL CALUCLO DE PRONOSTICOS CON LA TÉCNICA DE
REGRESIÓN LINEAL EN EXCEL
1. OBJETIVO
Proporcionar habilidades de tecnología informática en el uso de la herramienta
estadística de la aplicación Excel para el cálculo del pronóstico de la demanda usando
la técnica de Regresión Lineal.
2. ALCANCE
Todos los ejercicios de correlación lineal entre dos series de datos
3. DEFINICIONES
Regresión Lineal: En estadística la regresión lineal o ajuste lineal es un método
matemático que modela la relación entre una variable dependiente Y, las variables independientes Xi y un término aleatorio ε. Este modelo puede ser expresado como:
Donde β0 es la intersección o término "constante", las βi son los parámetros respectivos a cada variable independiente, y p es el número de parámetros independientes a tener en cuenta en la regresión.
Correlación: En probabilidad y estadística, la correlación indica la fuerza y la dirección
de una relación lineal entre dos variables aleatorias. Se considera que dos variables
cuantitativas están correlacionadas cuando los valores de una de ellas varían
sistemáticamente con respecto a los valores homónimos de la otra: si tenemos dos
variables (A y B) existe correlación si al aumentar los valores de A lo hacen también los
de B y viceversa.
UNIVERSIDAD SIMON BOLIVAR PROGRAMA DE ADMINISTRACION DE EMPRESAS ADMINISTRACION DE LA PRODUCCION
Regresión Lineal en Excel Yesid Ariza Osorio
2
Técnica de pronóstico: es el proceso de estimación en situaciones de incertidumbre.
El término predicción es similar, pero más general, y generalmente se refiere a la
estimación de series temporales o datos instantáneos. El pronóstico ha evolucionado
hacia la práctica del plan de demanda en el pronóstico diario de los negocios. La
práctica del plan de demanda también se refiere al pronóstico de la cadena de
suministros
Gráfico: es una representación de datos, generalmente numéricos, mediante líneas,
superficies o Símbolos, para ver la relación que esos datos guardan entre sí. También
puede ser un conjunto de puntos, que se plasman en coordenadas cartesianas, y sirven
para analizar el comportamiento de un proceso, o un conjunto de elementos o signos
que permiten la interpretación de un fenómeno
Serie: Un conjunto de datos que tienen características comunes y que representan una
realidad que puede proyectarse en el futuro, se utilizan en los pronósticos. También se
considera cada una de las categorías de los datos que permiten crear los gráficos en
Excel.
Tendencia: Dirección o rumbo que tomaran los datos, a través del tiempo, de acuerdo
a su comportamiento actual.
4. PROCEDIMIENTO
4.1. ENTRADA DE DATOS
En primera instancia debe diseñarse la tabla de datos con la información de la situación
observada y/o estudiada en la empresa. Una vez realizada la tabla de datos, se
identifica claramente cuál sección de ella es la de los datos reales o calculados de la
situación analizada y cual sección se destinará a los datos pronosticados.
A continuación un ejemplo de la tabla de datos:
UNIVERSIDAD SIMON BOLIVAR PROGRAMA DE ADMINISTRACION DE EMPRESAS ADMINISTRACION DE LA PRODUCCION
Regresión Lineal en Excel Yesid Ariza Osorio
3
PERIODO DEMANDA TIPO DATO
1 450
DEMANDA REAL
2 500
3 498
4 460
5 470
6 450
7 460
8 505
9 480
10 478,04
PRONOSTICOS
11 478,86
12 479,67
11 480,49
12 479,67 Tabla No.1 Tabla de datos
Fuente: Creación del autor
4.2. CREACION DEL GRÁFICO EN EXCEL
Se procede a crear un gráfico en Excel, como es de su conocimiento; es decir:
Insertar la herramienta gráfico
Seleccionar los datos del gráfico; los datos son las dos columnas de información,
pues cada una es una serie de datos que se relacionarán en el cálculo del
pronóstico.
Colocar el formato del gráfico; es decir, el título, los rótulos de eje, la
identificación de las series
Para la tabla anterior el gráfico queda como sigue:
UNIVERSIDAD SIMON BOLIVAR PROGRAMA DE ADMINISTRACION DE EMPRESAS ADMINISTRACION DE LA PRODUCCION
Regresión Lineal en Excel Yesid Ariza Osorio
4
Figura No. 1 Gráfico de la demanda de periodos
Fuente: Creación del Autor
4.3. LINEA DE TENDENCIA DE LOS DATOS
La Línea de tendencia es la gráfica que permite conocer el comportamiento futuro de
los datos en el tiempo; la tendencia mostrará hacia donde se dirigen los valores al pasar
los periodos. Para agregar la línea de tendencia en Excel, se procede así:
Se selecciona la serie DEMANDA en el gráfico
Se toma la Pestaña PRESENTACION en la barra de menú de Excel
Se selecciona la lista desplegable del grupo LINEA DE TENDENCIA
Se elige la opción MAS OPCIONES DE LINEA DE TENDENCIA
Seleccionar el tipo de regresión LINEAL en el cuadro de diálogo, además activar
la casilla de verificación PRESENTAR ECUACION EN EL GRAFICO del mismo
cuadro de diálogo.
Estos pasos se pueden observar mejor en las siguientes figuras:
0
100
200
300
400
500
600
1 2 3 4 5 6 7 8 9
DEM
AN
DA
PERODOS
GRAFICA DE CORELACION
PERIDOS
DEMANDA
UNIVERSIDAD SIMON BOLIVAR PROGRAMA DE ADMINISTRACION DE EMPRESAS ADMINISTRACION DE LA PRODUCCION
Regresión Lineal en Excel Yesid Ariza Osorio
5
Figura No. 2 Selección de la serie DEMANDA en el gráfico
Fuente: Creación del Autor
Figura No. 3 Selección de la Opción para crear la línea de tendencia
Fuente. Creación del Autor
UNIVERSIDAD SIMON BOLIVAR PROGRAMA DE ADMINISTRACION DE EMPRESAS ADMINISTRACION DE LA PRODUCCION
Regresión Lineal en Excel Yesid Ariza Osorio
6
Figura No. 4 Diligenciamiento del cuadro de diálogo de las opciones de Línea de tendencia
Fuente. Creación del Autor
Después de realizar este procedimiento, el gráfico en Excel se actualiza de manera que
muestra la línea de tendencia sobre él y además, muestra la fórmula de regresión lineal
a un lado del Gráfico.
Con la fórmula de Regresión Lineal que aparece en el gráfico se procede a pronosticar
los valores de los futuros periodos en la tabla de datos.
UNIVERSIDAD SIMON BOLIVAR PROGRAMA DE ADMINISTRACION DE EMPRESAS ADMINISTRACION DE LA PRODUCCION
Regresión Lineal en Excel Yesid Ariza Osorio
7
Figura No.5 Línea de Tendencia y fórmula de Regresión Lineal en el gráfico de Excel
Fuente: Creación del Autor
4.4. CALCULO DE LOS PRONOSTICOS
Se toma los valores de la fórmula de la Regresión Lineal obtenida en el proceso anterior
y se procede a diseñar una fórmula para calcular los valores de los periodos futuros de
la tabla de datos.
Debe tenerse en cuenta las siguientes recomendaciones
La variable Y, es la variable dependiente y se refiere al periodo que se pretende
pronosticar, en el caso de este instructivo será cada uno de los periodos 10 al
14.
y = 0,8167x + 470,69
0
100
200
300
400
500
600
1 2 3 4 5 6 7 8 9
DEM
AN
DA
PERODOS
GRAFICA DE CORELACION
PERIDOS
DEMANDA
Lineal (DEMANDA)
UNIVERSIDAD SIMON BOLIVAR PROGRAMA DE ADMINISTRACION DE EMPRESAS ADMINISTRACION DE LA PRODUCCION
Regresión Lineal en Excel Yesid Ariza Osorio
8
La variable X se refiere al periodo anterior al que se pretende calcular, en el caso
de los cálculos en Excel, deben considerarse la fila y columna anteriores a la
celda donde se escribirá la fórmula para calcular el pronóstico.
Para calcular el valor pronosticado en el periodo 10 de la tabla de datos, se
escribe en la celda B14, la siguiente fórmula:
=0,8167*A13+470,69
La celda A13 representa a la variable X en la fórmula de Regresión Lineal
Después de escriben o se copian sendas fórmulas como periodos a pronosticar
hayan.
Figura No. 6 Fórmula de cálculo de pronóstico
Fuente: Creación del Autor
UNIVERSIDAD SIMON BOLIVAR PROGRAMA DE ADMINISTRACION DE EMPRESAS ADMINISTRACION DE LA PRODUCCION
Regresión Lineal en Excel Yesid Ariza Osorio
9
Al final del ejercicio, la tabla queda así:
PERIODO DEMANDA TIPO DATO
1 450
DEMANDA REAL
2 500
3 498
4 460
5 470
6 450
7 460
8 505
9 480
10 478,04
PRONOSTICOS
11 478,86
12 479,67
13 480,49
14 481,31 Tabla No. 2 Tabla de datos reales y pronosticados
Fuente. Creación del Autor