Saludos a todos los lectores de Excel Free Blog, hoy veremos como realizar un pronóstico a través de una regresión lineal, algo que no resulta tan complicado como suena, así que manos a la obra.
¿Qué es una Regresón Lineal?
La regresión lineal es un método estadístico que ayuda a predecir el comportamiento de una variable Dependiente (Y) con respecto a una variable Independiente (X), en el mundo real, resulta muy útil entender la relación que existe entre las diferentes variables de negocio, como por ejemplo, las ventas con los ingresos, y si nos detenemos a pensar, no es difícil entender que estas dos variables están fuertemente relacionadas.
– Características
- Para aplicar este método, es importante que los datos tengan un patrón de linealidad, sin importar, que sea creciente o decreciente, recordemos que la ecuación de una Linea Recta es Y = aX + b, donde a es la pendiente de la recta y b el punto donde corta la recta en el eje de las ordenadas, en el eye vertical.
- Una medida apropiada para saber si es el escenario adecuado para aplicar la regresión lineal es el coeficiente de correlación R.
- Este método sirve para determinar la relación que tiene una variable independiente con una o variable Dependiente.
Algunos Ejemplos de Variables Dependientes e Independientes:
Ejemplo – Planta de Producción de Computadores
Supongamos que queremos hacer un pronóstico para estimar los gastos operacionales en una fábrica de computadores para el último periodo, esto para poder establecer un presupuesto, se cuenta con los siguientes datos:
Creación del Gráfico
Primero, debemos insertar un gráfico de dispersión con la variable dependiente en el eje Y (Costos de Operación) y la variable Independiente en el Eje X (Unidades Producidas), para que el gráfico cumpla estas condiciones debemos asegurarnos que en la tabla de datos, se encuentre a la izquierda de los datos de la variable dependiente, los datos de la variable independiente.
Insertar Grafico
Seleccionamos los datos sin incluir el periodo para el cual vamos a hacer la predicción -> Insertar -> Gráficos -> Insertar Gráfico de Dispersión (X,Y) o de burbujas -> Dispersión
Luego de insertar el gráfico, vamos a modificar el título y agregar las etiquetas de los ejes.
Si observamos el gráfico es razonable pensar que existe una relación lineal entre las unidades producidas y los costos de operación de la planta, para visualizar mejor está afirmación vamos a agregar una línea de tendencia ( Clic Izquierdo sobre algún punto del gráfico para elegirlo -> Clic derecho sobre el punto -> Agregar Línea de Tendencia -> Cuadro de dialogo Formato de Linea de Tendencia -> Opciones de Linea de Tendencia -> Elegimos la opción Lineal -> y hablitamos las opciones Presentar ecuación en el gráfico y Presentar el Valor de R en el gráfico).
Y automáticamente se agregan dichos elementos en el gráfico.
Nota: ¿Cómo Excel determina la Línea de Tendencia ?. Excel escoge la línea que minimiza (Sobre todas las líneas que pueden ser dibujadas), la suma de la distancia vertical al cuadrado de cada punto a la línea, la distancia vertical de cada punto a la línea se llama error o residuo. La línea creada por Excel se hace a través del método de mínimos cuadrados.
Con la ecuación de la recta que aparece en el gráfico, podemos pronosticar el costo de operación para el mes número 11:
Procedimiento
1)Mostrar la mayor cantidad de decimales posibles de la ecuación para hacer más preciso el pronóstico ( Seleccionamos la ecuación del gráfico -> Clic derecho -> Formato de Etiqueta de Línea de Tendencia -> Categoría -> Número -> Posiciones Decimales : 5 ).
2) Crear una nueva columna en la Tabla llamada Pronóstico.
3) En la primera celda de la nueva columna, copiamos la siguiente formula: =(16,90193*C4) – 271,13621, que representa la ecuación de la recta.
4) Arrastrar la fórmula para todos los valores de la Tabla.
Como se puede apreciar, el pronóstico del costo de operación para el mes número 11, es de $ 1504, por la fabricación de 105 computadores.
Nota: No se debe utilizar este método para hacer predicciones de valores que se encuentren por fuera de los rangos con los que se creó la línea, para este ejemplo, no se pueden hacer predicciones de valores de la variable dependiente, que se encuentran por fuera del rango de 75 a 105 unidades producidas.
La intersección de la línea con el eje Y es en -271,13621, y se podría interpretar que al no producir ningún computador no se incurre en ningún costo de producción en la planta, además, la pendiente en la gráfica nos da a entender que por la producción extra de cada computador se incurre en un costo variable de $16,90193. Y podríamos concluir que este es el costo variable estimado por producir un computador.
Ahora vamos agregar una nueva columna a la tabla donde vamos a computar los errores, el error para cada punto se calcula así: Costos de operación observados – Pronóstico:
gLos errores positivos indican que el punto se encuentra sobre la línea de tendencia central y los negativos por debajo.
Para verificar que la línea de tendencia central pasa por el medio de los puntos vamos a sumar en una celda todos los errores y el resultado de esta suma debe ser 0.
Evidentemente se cumple con esta condición.
Con el coeficiente de determinación R2 podemos concluir que el modelo lineal explica los costos de producción en relación con las unidades producidas en un 68%, es decir que 32% de los costos están dados en otros factores, que se pueden determinar a través de una regresión múltiple, además, se puede interpretar como la confiablidad que me proporciona el modelo y siempre debe expresarse en porcentaje.
No existe un coeficiente de determinación mejor que otro cuando se habla de una sola variable, sin embargo, un R2 más grande indica que los datos tienen un ajuste más apropiado que con un en uno R2 pequeño.
Tambien, podemos utilizar el coeficiente de relación de Pearson R, para determinar qué tan relacionadas se encuentran estas variables, dicho coeficiente varía entre -1 y 1, para encontrarlo solo debemos encontrar la raíz cuadrada del coeficiente de determinación R2 con la función =RAIZ(0,67598).
existe un coeficiente de determinación mejor que otro cuando se habla de una sola variable, sin embargo, un R2 más grande indica que tiene un ajuste más apropiado los datos que en uno pequeño.
Análisis
- Si -1 ≤ R < – 0,75% -> existe una relación inversa entre las variables.
- Si -0,75 ≤ R < -0,25 -> existe poca relación inversa entre las variables.
- Si -0,25 ≤ R < 0,25 -> No hay relación.
- Si 0,25 ≤ R < 0,75 -> existe poca relación directa entre las variables.
- Si 0,75 ≤ R ≤ 1 -> existe una relación directa entre las variables.
Para este ejemplo podemos concluir que existe una relación directa entre las unidades producidas y los costos de operación.
Bueno estimados lectores por el día de hoy es todo de mi parte, hasta la próxima, espero sea de gran utilidad esta publicación.