Métodos y fórmulas de forecast en Excel
Esta guía explica métodos elementales de forecast que pueden aplicarse fácilmente en hojas de cálculo de Microsoft Excel. Esta guía aplica a directivos y ejecutivos que necesitan anticipar la demanda de los clientes. La teoría se ilustra con Microsoft Excel. Notas avanzadas están disponibles para desarrollador de software que quiera reproducir la teoría en una aplicación hecha a la medida.
Beneficios del forecast
El forecast puede ayudarte a tomar las decisiones correctas, y ganar/ahorrar dinero. Aquí hay un ejemplo.
- Dimensiona tus inventarios de manera óptima
El tiempo es dinero. El espacio es dinero. Así que lo que quieres hacer es utilizar todos los medios a tu disposición para reducir tus existencias – sin experimentar ningún faltante de stock, por supuesto.
¿Cómo? ¡Con forecast!
Cómo facilitar las cosas: etiquetas, comentarios, nombres de archivos
Con el tiempo, a medida que se acumulan tus datos, es cada vez más probable que te confundas y cometas errores. ¿La solución? No seas desordenado: hacer un buen uso de etiquetas, comentarios y nombrar correctamente tus archivos puede ahorrarte muchos problemas.
- Etiqueta siempre tus columnas. Usa la primera fila de cada columna para describir los datos que contiene.
- Datos distintos, columnas distintas. No pongas números diferentes (por ejemplo, tus costes y tus ventas) en la misma columna. Es muy probable que te confundas, y esto hace que los cálculos y el manejo de datos sean más difíciles.
- Dale a cada archivo un nombre claramente comprensible. Requiere poco esfuerzo y agiliza las cosas. Los hace fáciles de identificar visualmente y más sencillos de encontrar usando la función de búsqueda de Windows.
- Usa comentarios.
Incluso si no sueles trabajar con una gran cantidad de datos, sigue siendo muy fácil confundirse. Esto se aplica especialmente si vuelves a consultar datos que creaste hace mucho tiempo. Excel ofrece una gran solución: comentarios.
Solo haz clic derecho en la celda que deseas comentar, y luego selecciona « insertar comentario ».
Puedes usarlos:
- para explicar el contenido de una celda (es decir, coste unitario según las estimaciones del Sr. Doe)
- para dejar advertencias a futuros usuarios de la hoja (es decir, tengo una duda sobre este cálculo… )
Empezando: un ejemplo sencillo de forecast usando líneas de tendencia
Visualizando tus datos
Ahora hagamos nuestro primer forecast. En esta parte, utilizaremos este archivo: Example1.xls. Para repetir los pasos por ti mismo, puedes descargar el archivo. Estos datos sirven solo como ejemplo.
Nuestros Datos: En la primera columna, datos sobre los costes unitarios de productos similares (el coste unitario refleja la calidad del producto). En la segunda, datos sobre cuánto se ha vendido.
Lo que queremos saber: Si vendemos otro producto, con una calidad correspondiente a un coste de $150/unidad, ¿cuántas unidades podemos esperar vender?
Cómo lo logramos: Aquí, es bastante sencillo. Queremos encontrar una relación matemática simple entre el coste unitario y las ventas, y luego usar esa relación para hacer nuestro forecast.
Primero, siempre es útil crear un gráfico en Excel para observar los datos. Tus ojos son herramientas excelentes que pueden ayudarte a identificar tendencias en pocos segundos.
Para ello, seleccionamos nuestros datos, usamos Insertar > Gráfico, y elegimos la opción XY (Dispersión). Queremos estimar las ventas en función de la calidad, por lo que colocamos el coste unitario en el eje horizontal y las ventas en el eje vertical.
Ahora, nos detenemos unos segundos y observamos detenidamente lo que vemos: la relación parece ser creciente y lineal.
Para tener una idea de la forma exacta de la relación, haz clic derecho en el gráfico y selecciona la opción « Línea de tendencia ».
Ahora, tenemos que seleccionar la relación que parece “ajustar” (es decir, describir mejor) nuestros datos. Una vez más, usamos nuestros ojos: en este caso, los puntos están casi en línea recta, así que usamos la configuración “lineal”. Más adelante, utilizaremos otras configuraciones – más complejas, pero a menudo más realistas – como “exponencial”.
Nuestra línea de tendencia se muestra ahora en el gráfico. Otro clic derecho nos permite mostrar la forma exacta de la relación: y = 102.4x - 191.64.
Entiende: Número de unidades vendidas = 102.4 por el coste unitario - 191.64.
Así, si decidimos producir a un coste unitario de $150, podemos esperar vender 102.4*150 - 191.64 = 15168 unidades
Acabamos de completar nuestro primer forecast con éxito.
Sin embargo, ten cuidado: el software siempre es capaz de encontrar una relación entre las dos columnas, ¡aunque en realidad esta relación sea muy débil! Por lo tanto, se requiere una comprobación de robustez. Así es como lo haces rápidamente:
- Primero, siempre observa el gráfico. Si encuentras que los puntos están cerca de la línea de tendencia, como en el ejemplo anterior, hay una buena probabilidad de que la relación sea robusta. Sin embargo, si los puntos parecen estar ubicados casi al azar y, en general, bastante lejos de la línea de tendencia, entonces debes tener cuidado: la correlación es débil y la relación estimada no debe ser confiada ciegamente.
- Después de observar el gráfico, puedes usar la función CORREL. En nuestro ejemplo, la función se leería: CORREL(A2:A83,B2:B83). Si el resultado está cerca de 0, entonces la correlación es baja, y la conclusión es: simplemente no hay una tendencia real. Si está cerca de 1, entonces la correlación es fuerte. Esto último es útil, ya que aumenta el poder explicativo de la relación encontrada.
Existen formas más sutiles de asegurarse de que la correlación sea alta; volveremos a esto más adelante.
Por supuesto, estos últimos pasos se pueden automatizar: no tienes que anotar la relación ni usar tu calculadora de bolsillo para hacer el cálculo. ¡Necesitas el Analysis Toolpak!
Forecasting usando el Analysis Toolpak
Antes de continuar, debes verificar si el Excel ATP (Analysis Toolpak) está instalado. Consulta la sección de Instalación del Analysis Toolpak para obtener más información.
Desafortunadamente, datos de ventas tan perfectos con una relación lineal tan agradable y simple son bastante inusuales en la vida real. Echemos un vistazo a lo que Excel tiene para ofrecer en situaciones más complicadas, con datos más complejos.
Avanzando: el ejemplo del ajuste exponencial
Como puedes imaginar, un modelo lineal de tus datos no siempre es probable. De hecho, hay muchas razones para creer que debería seguir un modelo exponencial. Muchos comportamientos en la economía están regidos por ecuaciones exponenciales (por ejemplo, los cálculos de interés compuesto son un ejemplo clásico).
Aquí se muestra cómo realizar un ajuste exponencial:
- Observa tus datos. Traza un gráfico simple y obsérvalo. Si siguen una evolución exponencial, deberían verse así:
Este es el caso perfecto. Por supuesto, los datos nunca se verán exactamente así. Pero si los puntos parecen seguir aproximadamente esta distribución, deberías considerar el ajuste exponencial.
Como en el ejemplo anterior, siempre puedes trazar un gráfico de tus datos, solicitar una línea de tendencia y elegir « exponencial » en lugar de lineal. Luego, recopila la ecuación mostrada, como de costumbre.
- Por suerte, también puedes hacer todo esto directamente, utilizando el Analysis Toolpak: Coloca todos tus datos en una hoja de Excel en blanco y ve a Herramientas => Análisis de datos
Instalación del Analysis Toolpak (ATP)
El ATP es un complemento que viene con Microsoft Excel, pero no siempre se instala por defecto. Para instalarlo, se puede proceder de la siguiente manera:
- Asegúrate de tener tu CD de Office a mano. Es posible que Excel te pida insertar el CD para instalar los archivos del ATP
- Abre una hoja de Excel, ve al Menú de Herramientas, y luego selecciona Complementos. Marca la primera casilla de la ventana, etiquetada « 2.Analysis Toolpak ».
- Inserta tu CD de Office si el software te lo solicita.
- ¡Eso es todo! Observa que el menú « Herramientas » ahora incluye muchas más funciones, incluida la opción « Análisis de datos ». Esta es la que usaremos más.
Uso del Analysis Toolpak (ATP)
… en un entorno lineal
Ahora, volvamos a nuestro ejemplo lineal. Si tus datos « se ven » bien (ver la ilustración anterior), puedes utilizar el ATP para obtener una estimación directa de la forma funcional, sin pasar por el proceso de « línea de tendencia ».
Abre tu hoja de datos, luego abre el menú « Herramientas » y selecciona « Análisis de datos ». Se abrirá una ventana preguntándote qué tipo de análisis deseas realizar. Selecciona « regresión » para entornos lineales.
Ahora debes proporcionar a Excel dos argumentos: un « rango Y » y un « rango X ». El rango Y indica lo que deseas estimar (es decir, tus ventas), y el rango X contiene los datos que crees que pueden explicar tus ventas (aquí, tu coste unitario). En nuestro ejemplo (ver example1.xls), nuestros datos de ventas están en la columna B, desde la fila 3 hasta la 90, por lo que debes poner « $B$3:$B$90 » como el rango Y, y « $A$3:$A$90 » como el rango X. Cuando hayas terminado, haz clic en « ok ».
Aparece una nueva hoja, que contiene los « resultados de la regresión ».
El resultado más importante se encuentra en la columna « Coefficients » en la parte inferior de la hoja. La intersección es la constante, y el coeficiente de la « X variable » es el coeficiente de X (aquí, tu coste unitario). Por lo tanto, encontramos la misma ecuación que hallamos usando la función de « línea de tendencia ». Ventas = Intersección + CoeficienteX * coste unitario. Ventas = -126 + 100 * coste unitario
Esta hoja también contiene un número útil que te brinda información sobre la calidad de tu estimación: el « R Square ». Si está cerca de 1, entonces tu estimación es buena, lo que significa que la ecuación que encontraste es una representación bastante buena de tus datos. Si está cerca de 0, entonces la estimación no es buena, y probablemente deberías intentar otro tipo de ajuste (ver ajuste exponencial a continuación).
Este método es probablemente más rápido que las técnicas de « línea de tendencia ». Sin embargo, es un poco más técnico y mucho menos visual. Así que, si no deseas pasar por el esfuerzo de trazar y examinar tus datos a simple vista, asegúrate de al menos verificar el valor de « R Square ».
… utilizando ajuste exponencial
Si la estimación lineal no resulta bien (por ejemplo, si obtienes un R-Squared bajo, es decir, 0.1), podrías querer utilizar el ajuste exponencial.
Inicia el Analysis Toolpak, como de costumbre: abre tu hoja de datos, luego abre el menú « Herramientas » y selecciona « Análisis de datos ». Se abrirá una ventana preguntándote qué tipo de análisis deseas realizar.
En nuestro entorno exponencial, lo que queremos seleccionar es « exponencial ».
Observa que Excel solo te pide un rango de entrada. Selecciona la columna que contiene los datos que deseas forecast (es decir, el coste unitario), y elige un “factor de suavizado”.
¿Cómo sé qué modelo elegir?
Ten en cuenta que no necesitas probar cada método de estimación para encontrar el que mejor funcione para ti. Esto solo se puede lograr mediante la automatización, ya que existe una gran cantidad de métodos disponibles. Si deseas que todos los modelos se comparen con tus datos, puedes considerar enviarlos a Lokad. Contamos con un sistema informático potente que “prueba” todos los modelos y selecciona únicamente aquellos que funcionan mejor con los datos de tu negocio.