Soporte » Aquí
Métodos y fórmulas de pronóstico con Excel
Por Guillaume Saint-Jacques, 18-06-2008 (última revisión: 22-02-2010)
Esta guía explica los
métodos de pronóstico básicos que pueden ser aplicados directamente en planillas de cálculo de Microsoft Excel y está dirigida a directores y ejecutivos que necesitan anticipar la demanda del cliente. La teoría se ilustra con
Microsoft Excel. Para los desarrolladores de software que deseen reproducir la teoría en una aplicación personalizada se encuentran disponibles notas avanzadas.
Beneficios de la realización de pronósticos
Realizar pronósticos puede ayudarlo a tomar las decisiones adecuadas y a ganar/ahorrar dinero. Aquí le presentamos algunos ejemplos:
- Defina mejores estrategias de ventas
Si un producto disminuye sus ventas, tal vez sea una buena idea considerar el cese de su producción.
Pero tal vez no: ¿no podría ser que sus ventas estén disminuyendo pero las de su competidor no? Y si este fuera el caso, ¿existe la posibilidad de que usted recupere su cuota de mercado?Las técnicas de pronóstico dan respuestas a estas preguntas, que son vitales para su negocio.
- Dimensione sus inventarios en modo óptimo
El tiempo es dinero. El espacio es dinero. Por esta razón, lo que usted quiere es utilizar todos los medios a disposición para reducir sus existencias sin caer en escasez de stock.
¿Cómo hacerlo? ¡Realizando pronósticos!
Cómo simplificar las cosas: etiquetas, comentarios, nombres de archivos
A medida que pase el tiempo, y que su información se vaya acumulando, será probable que se confunda y cometa errores. ¿La solución? No sea desordenado: hacer buen uso de sus etiquetas y comentarios y dar nombres adecuados a sus archivos puede ahorrarle muchos inconvenientes.
- Siempre etiquete sus columnas. Utilice la primera fila de cada columna para describir la información que contiene.
- Diferentes datos, diferentes columnas. No coloque datos numéricos diferentes (por ejemplo, sus costos y sus ventas) en la misma columna. Es muy probable que se confunda y que los cálculos y el manejo de los datos se haga muy engorroso.
- Dé a cada archivo un nombre claramente entendible. Requiere poco esfuerzo y acelera los procesos. Además, hace que sea más fácil identificarlos visualmente y encontrarlos utilizando la función de búsqueda de Windows.
- Utilice comentarios.
Aunque no trabaje frecuentemente con grandes cantidades de datos, es muy fácil confundirse, especialmente cuando consulta datos que ha creado hace mucho tiempo. Excel ofrece una excelente solución: los
comentarios.
 La utilidad de los comentarios |
Simplemente haga clic derecho en la celda sobre la que desea comentar y luego seleccione « insertar comentario ».
Puede utilizarlos:
- para explicar el contenido de una celda (por ejemplo, costo unitario según cálculos del Sr. Doe.).
- para dejar una advertencia para quienes utilicen la planilla en un futuro (por ejemplo: Tengo una duda acerca de este cálculo... ).
Pronósticos de ventas avanzados con su aplicación web
Salescast. Lokad se especializa en la optimización de inventario a través del pronóstico de la demanda. El contenido de este tutorial —y mucho más— son funciones nativas de Salescast.
Primeros pasos: un ejemplo de pronóstico simple utilizando líneas de tendencia
Visualización de sus datos
Hagamos ahora nuestro primer pronóstico. En esta parte, utilizaremos este archivo:
Example1.xls. Para repetir los pasos usted solo, puede descargar el archivo. Estos datos sirven sólo como ejemplo.
Nuestros datos: en la primera columna, datos acerca de los costos unitarios de productos similares. El costo unitario refleja la calidad del producto. En la segunda columna, los datos acerca de cuánto se ha vendido.
Lo que deseamos saber: Si vendemos otro producto con una calidad correspondiente a un costo de US$150/unidad, ¿cuántas unidades podemos esperar que se vendan?
Cómo llegamos allí: Es bastante simple. Queremos encontrar una relación matemática simple entre el costo unitario y las ventas, y luego utilizar esta relación para realizar nuestro pronóstico.
En primer lugar, es siempre útil crear un gráfico en Excel para poder observar los datos. Sus ojos son excelentes herramientas, que lo pueden ayudar a identificar tendencias en tan sólo segundos.
Para hacer esto, seleccionamos nuestro datos, luego usamos Insertar > Tabla, y elegimos la opción XY (Dispersión). Queremos calcular las ventas como una función de calidad; por lo tanto, colocamos el costo unitario en el eje horizontal y las ventas en el vertical.
Ahora, nos detenemos un momento y observamos atentamente lo que vemos: la relación parece ser creciente y lineal.
Para hacernos una idea de la forma exacta de la relación, hacemos clic derecho sobre la tabla y seleccionamos la opción “Línea de tendencia”.
 Creación de una línea de tendencia |
Ahora debemos seleccionar la relación que parece "ajustarse" (es decir, que mejor describe) a nuestros datos. Una vez más, utilizamos la vista. En este caso, los puntos se encuentran casi en una línea recta, por lo que utilizamos la configuración “lineal”. Más tarde, utilizaremos otras configuraciones —más complejas pero a menudo también más realistas—, como “exponencial”.
Nuestra línea de tendencia se muestra ahora en la tabla. Con otro clic derecho podremos hacer visible la forma exacta de la relación: y = 102.4x - 191.64.
Léase: Cantidad de unidad vendida = 102.4 veces el costo unitario – 191.64.
Así, si decidimos producir a US$150 de costo unitario, podemos esperar vender 102.4*150 - 191.64 = 15168 unidades.
 Línea de tendencia lineal |
Hemos completado nuestro primer pronóstico con éxito.
Sin embargo, debemos ser cautelosos: el software siempre puede encontrar una relación entre dos columnas, incluso cuando esta relación sea muy débil en la realidad. Por lo tanto, es necesario verificar la solidez de la misma. Aquí le presentamos un modo rápido para hacerlo:
- En primer lugar, observe siempre la tabla. Si encuentra puntos que se hallan cercanos a la línea de tendencia, como sucede en el ejemplo anterior, hay posibilidades de que la relación sea sólida. Sin embargo, si los puntos parecen estar ubicados casi al azar y se encuentran en su mayoría alejados de la línea de tendencia, entonces debería proceder con cautela: la correlación es débil, y la relación calculada debería estar sujeta a verificación.
 Los puntos se encuentran por todas partes: no existe una relación evidente, los pronósticos no son confiables. |
Los puntos “tienen sentido” y permiten la realización de pronósticos más confiables. |
- Después de haber observado la tabla, puede utilizar la función CORREL. En nuestro ejemplo, la función debería verse así: CORREL(A2:A83,B2:B83). Si el resultado es cercano a 0, entonces la correlación es baja y la conclusión es: simplemente no existe una tendencia real. Si es cercano a 1, entonces la correlación es sólida. Esta última es útil, ya que aumenta el poder explicativo de la relación que ha encontrado.
Existen modos más sutiles de asegurarse de que la correlación sea alta, y volveremos sobre este punto más adelante.
Por supuesto, estos últimos pasos pueden automatizarse: no necesita anotar la relación y utilizar la calculadora de bolsillo para hacer los cálculos. ¡Lo único que necesita es el Analysis Tool Pack!
Realización de pronósticos utilizando el Analysis Tool Pack
Antes de continuar, debería verificar que el
Excel ATP (Analysis Tool Pack) se encuentre instalado. Consulte la sección “Instalación del Analysis Tool Pack” para más información.
Lamentablemente, los datos de venta tan perfectos con una relación simple linear tan bonita son bastante raros en la vida real. Echemos un vistazo a lo que Excel tiene para ofrecer para situaciones más complicadas, con datos más complejos.Pasos avanzados: el ejemplo del ajuste exponencial
Como puede imaginar, un modelo tan lineal de sus datos no siempre es probable. De hecho, existen muchas razones para creer que debería seguir un modelo exponencial. Muchos comportamientos en economía se ven accionados por ecuaciones exponenciales (los cálculos del interés compuesto son un clásico ejemplo).
Aquí le presentamos el modo de realizar un ajuste exponencial:
1) Observe sus datos. Dibuje un gráfico simple, y obsérvelo. Si siguen una evolución exponencial, deberían verse así:
 forma exponencial perfecta |
Este es el caso perfecto. Desde luego, los datos nunca se verán exactamente así. Pero si los puntos parecen seguir en forma aproximada esta distribución, entonces usted debería inclinarse por considerar el ajuste exponencial.
Utilizando líneas de tendencia |
Al igual que en el ejemplo anterior, siempre podrá dibujar una tabla de sus datos, solicitar una línea de tendencia y elegir « exponencial » en lugar de lineal.
Luego, reúna la ecuación mostrada del modo habitual.
2) Afortunadamente, también puede hacer todo esto directamente, utilizando el Analysis Tool Pack. Coloque todos los datos en una planilla de Excel en blanco y vaya a
Herramientas => Análisis de datos.
Instalación del Analysis Tool Pack (ATP)
El ATP es un agregado que viene con Microsoft Excel, pero que no siempre se encuentra instalado por defecto. Para instalarlo, puede seguir estos pasos:
- Asegúrese de tener el CD de Office. Excel puede pedirle que introduzca el CD para poder instalar los archivos ATP.
- Abra la planilla de Excel y vaya al Menú Herramientas y luego seleccione Complementos.
Seleccione la primera casilla de la ventana, llamada « Analysis ToolPack ». - Introduzca su CD de Office si el software lo solicita.
- ¡Eso es todo! Note que su menú « Herramientas » ahora incluye muchas más funciones, incluida la opción «Análisis de datos». Esta es la función que más usaremos.
Uso del Analysis Tool Pack (ATP)
...en un escenario lineal
Volvamos ahora a nuestro ejemplo lineal. Si sus datos «se ven» bien (ver la ilustración más arriba), puede utilizar el ATP para obtener una estima directa de la forma funcional, sin pasar por el proceso de « línea de tendencia ».
Abra su planilla de datos, luego abra el menú « Herramientas » y seleccione « Análisis de datos ». Aparecerá una ventana que le preguntará qué tipo de análisis desea realizar. Seleccione « regresión » para escenarios lineales.
Ahora tiene que darle a Excel dos argumentos: un « rango Y » y un « rango X ». El rango Y indica lo que desea calcular (por ejemplo, sus ventas), y el rango X contiene los datos que usted considera que pueden explicar sus ventas (aquí, su costo unitario). En nuestro ejemplo (ver ejemplo .xls), nuestros datos de ventas se hallan en la columna B, desde la fila 3 hasta la fila 90, por lo que usted debe colocar « $B$3:$B$90 » como el rango Y, y «$A$3:$A$90 » como el rango X. Cuando haya terminado, haga clic en « Aceptar ».
Aparecerá una nueva planilla que contiene los « resultados de regresión ».
 El Analysis ToolPack Output, en el caso de una regresión de mínimos cuadrados ordinarios |
El resultado más importante se encuentra contenido en la columna « coeficientes » al final de la planilla. La intersección es la constante, y el coeficiente de la « variable X » es el coeficiente de X (aquí, su costo unitario). Así, encontramos la misma ecuación que habíamos encontrado utilizando la función « línea de tendencia ». Ventas = Intersección + coeficienteX * Ventas costo unitario= -126 + 100 * costo unitario
Esta planilla también contiene un número útil que le proporciona información acerca de la calidad de su cálculo: el « R Square ». Si este valor es cercano a 1, entonces su cálculo es bueno, lo que significa que la ecuación que encontró es una representación de sus datos bastante buena. Si el valor es cercano a 0, entonces el cálculo no es bueno, y probablemente debería probar con 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. Así, si desea ahorrarse el trabajo de trazar y observar sus datos, asegúrese al menos de verificar el valor « R square ».
...utilizando el ajuste exponencial
Si el cálculo lineal no sale bien (si obtiene, por ejemplo un valor de « R square » bajo, como 0.1), entonces tal vez quiera utilizar el Ajuste Exponencial.
Ejecute el Analysis Tool Pack del modo habitual. Abra su planilla de datos, luego abra el menú « Herramientas » y seleccione « Análisis de datos ». Aparecerá una ventana que le preguntará qué tipo de análisis desea realizar.
En nuestro escenario exponencial seleccionaremos « exponencial ».
Note que Excel sólo le pregunta acerca de un rango de entrada. Seleccione la columna que contiene los datos que desea pronosticar (por ejemplo, costo unitario) y elija un “factor de alisado”.
Cómo saber qué modelo elegir
Tenga en cuenta que no necesita probar cada método de cálculo para encontrar el que mejor funciona para usted. Esto puede lograrse mediante la automatización, ya que existe una gran cantidad de métodos disponible. Si desea que todos los modelos sean probados con sus datos, puede considerar enviarlos a Lokad. Contamos con un potente sistema informático que “prueba” todos los modelos y selecciona los que mejor funcionan con los datos de su negocio (más información acerca de
lo que Lokad tiene para ofrecer).
Una potente herramienta que, no obstante, le permite seleccionar el modelo adecuado es su vista: trace sus datos (ver sección 1), compárelos con las siguientes ilustraciones y elija el modelo que más “se asemeja” a sus datos.