Pronóstico de la Demanda, técnica de Regresión Lineal en Excel

El pronóstico es un dato que, con base a una serie de estudios, determinan la demanda en un futuro de un determinado producto. Es predecir el futuro a partir de algunos indicios. El pronóstico es una inferencia a partir de ciertos datos, permite predecir lo que ocurrirá en el futuro, depende de los cambios en las variables externas al sistema de producción.

Los pronósticos tuvieron su origen en aspectos informales de la vida cotidiana; Otrora épocas los reyes, los políticos y personas adineradas acudían a los clarividentes para que les comentaran acerca de sus vidas en el futuro, al paso del tiempo estas ideas las adoptan los comerciantes y empresarios y se fue formalizando poco a poco para el concepto de los pronósticos hasta llegar a la que hoy se conoce como un importante tema.

La Técnica de Regresión Lineal o Mínimos Cuadrados

Esta es una técnica de tipo cuantitativo que permite el cálculo de los pronósticos para períodos futuros, para lo cual requiere de registros históricos que sean consistentes, reales y precisos.

Esta técnica como su nombre lo indica se trata de sacar el total de las desviaciones elevadas al cuadrado a un valor mínimo: su objetivo es determinar los coeficientes a y b, que son conocidos como coeficientes de regresión, donde x es la variable independiente (tiempo), y es la variable dependiente (pronóstico de la demanda).

La fórmula para el cálculo del pronóstico es Y= a + bX, donde los valores de a y b son:

a = (∑y)(∑x2) – (∑x)∑(xy)/ N (∑x2) – (∑x) 2 y b = N(∑xy) – (∑x)(∑y) / N (∑x2) – (∑x) 2

Uso de Excel para realizar pronósticos

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:

Seguidamente, se procede a crear un gráfico en Excel, como es de su conocimiento; es decir: 

1. Insertar la herramienta gráfico
2. 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.
3. 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:  

Luego, debe aplicarse la Línea de tendencia a la gráfica, pues 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í: 

1. Se selecciona la serie DEMANDA en el gráfico
2. Se toma la Pestaña PRESENTACION en la barra de menú de Excel
3. Se selecciona la lista desplegable del grupo LINEA DE TENDENCIA
4. Se elige la opción MAS OPCIONES DE LINEA DE TENDENCIA

Estos pasos se pueden observar mejor en la siguiente figuras: 

Las opciones de tendencia se muestran en una caja de diálogo, como la que sigue, se debe 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. Así:  

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. Ahora el gráfico debe verse así:

Para calcular el pronóstico se procede así:

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: 

1. 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.
2. 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.
3. 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
4. La celda A13 representa a la variable X en la fórmula de Regresión Lineal
5. Después se copia la fórmula en la celda de abajo, para pronósticar el periodo siguiente.

 El resultado final, se muestra de la siguiente manera:  

Después se escriben o se copian sendas fórmulas como periodos a pronosticar hayan.