Métodos y fórmulas de pronóstico en Excel
Esta guía explica los métodos de pronóstico elementales que se pueden aplicar fácilmente en hojas de cálculo de Microsoft Excel. Esta guía se aplica a gerentes y ejecutivos que necesitan anticipar la demanda del cliente. La teoría se ilustra con Microsoft Excel. Hay notas avanzadas disponibles para los desarrolladores de software que deseen reproducir la teoría en una aplicación personalizada.
Beneficios del pronóstico
El pronóstico puede ayudarte a tomar las decisiones correctas y ahorrar/ganar dinero. Aquí tienes un ejemplo.
- Dimensiona tus inventarios de manera óptima
El tiempo es dinero. El espacio es dinero. Entonces, lo que quieres hacer es utilizar todos los medios a tu disposición para reducir tus existencias, sin experimentar ninguna falta de stock, por supuesto.
¿Cómo? ¡Pronosticando!
Cómo facilitar las cosas: etiquetas, comentarios, nombres de archivos
Con el tiempo, a medida que tus datos se acumulen, es cada vez más probable que te confundas y cometas errores. ¿La solución? No seas desordenado: hacer un buen uso de las etiquetas, los comentarios y nombrar correctamente tus archivos puede ahorrarte muchos problemas.
- Siempre etiqueta tus columnas. Utiliza la primera fila de cada columna para describir los datos que contiene.
- Datos diferentes, columnas diferentes. No pongas números diferentes (por ejemplo, tus costos y tus ventas) en la misma columna. Es increíblemente probable que te confundas y dificulta los cálculos y el manejo de datos.
- Dale a cada archivo un nombre claramente comprensible. Requiere poco esfuerzo y acelera las cosas. Los hace fáciles de identificar visualmente y más fáciles de encontrar utilizando la función de búsqueda de Windows.
- Utiliza comentarios.
Incluso si no sueles trabajar con una gran cantidad de datos, aún es muy fácil confundirse. Esto se aplica especialmente si vuelves a los datos que has creado mucho tiempo antes. Excel tiene una gran solución que ofrecer: comentarios.
Simplemente haz clic derecho en la celda en la que deseas comentar y luego selecciona “insertar comentario”.
Puedes usarlos:
- para explicar el contenido de una celda (es decir, costo unitario según las estimaciones del Sr. Doe)
- para dejar advertencias a los futuros usuarios de la hoja (es decir, Tengo dudas sobre este cálculo…)
Primeros pasos: un ejemplo sencillo de pronóstico utilizando líneas de tendencia
Visualización de tus datos
Ahora hagamos nuestro primer pronóstico. En esta parte, utilizaremos este archivo: Ejemplo1.xls. Para repetir los pasos por ti mismo, puedes descargar el archivo. Estos datos solo sirven como ejemplo.
Nuestros datos: En la primera columna, datos sobre los costos unitarios de productos similares (el costo unitario refleja la calidad del producto). En la segunda columna, datos sobre cuánto se ha vendido.
Lo que queremos saber: Si vendemos otro producto, con una calidad correspondiente a un costo de $150/unidad, ¿cuántas unidades podemos esperar vender?
Cómo llegar allí: Aquí, es bastante simple. Queremos encontrar una relación matemática simple entre el costo unitario y las ventas, y luego usar esta relación para hacer nuestro pronóstico.
Primero, siempre es útil crear un gráfico en Excel para echar un vistazo a los datos. Tus ojos son excelentes herramientas que pueden ayudarte a identificar tendencias en unos pocos segundos.
Para hacer esto, seleccionamos nuestros datos, luego usamos Insertar > Gráfico y elegimos la opción XY (Dispersión). Queremos estimar las ventas como una función de la calidad, por lo tanto, colocamos el costo unitario en el eje horizontal y las ventas en el eje vertical.
Ahora, nos detenemos unos segundos y observamos lo que vemos: la relación parece estar aumentando y ser lineal.
Para tener una idea de la forma exacta de la relación, hacemos clic derecho en el gráfico y seleccionamos la opción “Línea de tendencia”.
Ahora, tenemos que seleccionar la relación que parece “ajustarse” (es decir, describir mejor) nuestros datos. Aquí nuevamente, usamos nuestros ojos: en este caso, los puntos están casi en una línea recta, por lo 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 ahora se muestra en el gráfico. Otro clic derecho nos permite mostrar la forma exacta de la relación: y = 102.4x - 191.64.
Entendamos: Número de unidades vendidas = 102.4 veces el costo unitario - 191.64.
Entonces, si decidimos producir a un costo unitario de $150, podemos esperar vender 102.4 * 150 - 191.64 = 15168 unidades.
Acabamos de completar nuestra primera previsión con éxito.
Sin embargo, ten cuidado: el software siempre puede encontrar una relación entre las dos columnas, ¡incluso si esta relación es en realidad muy débil! Por lo tanto, se requiere una verificación de robustez. Así es como puedes hacerlo rápidamente:
- Primero, siempre echa un vistazo al gráfico. Si encuentras que los puntos están cerca de la línea de tendencia, como en nuestro 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 están 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 confiarse ciegamente.
- Después de echar un vistazo al gráfico, puedes usar la función CORREL. En nuestro ejemplo, la función serí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 es útil, ya que aumenta el poder explicativo de la relación que encontraste.
Hay 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 y usar tu calculadora de bolsillo para hacer el cálculo. ¡Necesitas el Analysis Toolpak!
Pronóstico utilizando el Analysis Toolpak
Antes de continuar, debes verificar si el Excel ATP (Analysis Toolpak) está instalado. Consulta la sección Instalación del Analysis Toolpak para obtener más información.
Desafortunadamente, estos datos de ventas perfectos con una relación lineal tan agradable y simple son bastante raros en la vida real. Veamos qué ofrece Excel para situaciones más complicadas, con datos más complicados.
Yendo más allá: el ejemplo del ajuste exponencial
Como puedes imaginar, este 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 impulsados por ecuaciones exponenciales (por ejemplo, los cálculos de interés compuesto son un ejemplo clásico).
Aquí te mostramos cómo realizar un ajuste exponencial:
- Echa un vistazo a tus datos. Dibuja un gráfico simple y simplemente 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ía animarte a considerar el ajuste exponencial.
Como en el ejemplo anterior, siempre puedes dibujar un gráfico de tus datos, solicitar una línea de tendencia y elegir «exponencial» en lugar de lineal. Luego, recopila la ecuación que se muestra, como de costumbre.
- Afortunadamente, también puedes hacer todo esto directamente utilizando el Analysis Toolpak: coloca todos tus datos en una hoja de cálculo en blanco de Excel 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 que no siempre está instalado de forma predeterminada. Para instalarlo, se puede proceder de la siguiente manera:
- Asegúrate de tener tu CD de Office contigo. Es posible que Excel te solicite insertar el CD para instalar los archivos del ATP.
- Abre una hoja de cálculo de Excel y ve al menú Herramientas, luego selecciona Complementos. Marca la primera casilla de la ventana, etiquetada como «2. Analysis Toolpak».
- Inserta tu CD de Office si el software te lo solicita.
- ¡Eso es todo! Observa que tu menú «Herramientas» ahora incluye muchas más funciones, incluida la opción «Análisis de datos». Esta es la que utilizaremos más frecuentemente.
Uso del Analysis Toolpak (ATP)
… en un entorno lineal
Ahora, volvamos a nuestro ejemplo lineal. Si tus datos «se ven» bien (ver 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». Aparecerá una ventana que te pregunta qué tipo de análisis deseas realizar. Selecciona «regresión» para configuraciones lineales.
Ahora necesitas darle 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 costo unitario). En nuestro ejemplo (ver example1.xls), nuestros datos de ventas están en la columna B, desde la fila 3 hasta la fila 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 «Aceptar».
Aparecerá una nueva hoja que contiene los «resultados de regresión».
El resultado más importante se encuentra en la columna «Coeficientes» en la parte inferior de la hoja. La intersección es la constante y el coeficiente de la «variable X» es el coeficiente de X (aquí, tu costo unitario). Por lo tanto, encontramos la misma ecuación que encontramos usando la función de «línea de tendencia». Ventas = Intersección + CoeficienteX * costo unitarioVentas = -126 + 100 * costo unitario
Esta hoja también contiene un número útil que te brinda información sobre qué tan buena es tu estimación: el «R cuadrado». 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 debas intentar otro tipo de ajuste (ver ajuste exponencial a continuación).
Este método probablemente sea 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. Entonces, si no quieres pasar por la molestia de trazar y visualizar tus datos, asegúrate al menos de verificar el valor de “R cuadrado”.
… usando ajuste exponencial
Si la estimación lineal no funciona bien (por ejemplo, si obtienes un R cuadrado bajo, es decir, 0.1), es posible que desees utilizar el ajuste exponencial.
Abre el Analysis Toolpak, como de costumbre: abre tu hoja de datos, luego abre el menú “herramientas” y selecciona “Análisis de datos”. Aparecerá una ventana que te pregunta qué tipo de análisis deseas realizar.
En nuestra configuración 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 pronosticar (es decir, el costo 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 funcione mejor para ti. Esto solo se puede lograr a través de la automatización, ya que hay una gran cantidad de métodos disponibles. Si deseas que todos los modelos se comparen con tus datos, considera enviarlos a Lokad. Tenemos un sistema informático potente que “prueba” todos los modelos y selecciona solo los que funcionan mejor con los datos de tu negocio.