En la primera entrada de este blog, había explicado como encontrar la intersección de dos líneas en un gráfico de Excel. Para hacer esto utilizamos la opción "línea de tendencia" (trendline) y el SOLVER.
Con Excel podemos, dada una tabla de valores, predecir un valor futuro a partir de valores existentes utilizando la función PRONOSTICO (FORECAST) (apretar el enlace para descargar el archivo).
La descripción de la función PRONOSTICO de acuerdo a la ayuda online de MS Excel es la siguiente:
Calcula o pronostica un valor futuro a través de los valores existentes. La predicción del valor es un valor y teniendo en cuenta un valor x. Los valores conocidos son valores x y valores y existentes, y el nuevo valor se pronostica utilizando regresión lineal. Esta función se puede utilizar para realizar previsiones de ventas, establecer requisitos de inventario o tendencias de los consumidores.
La sintaxis es: PRONOSTICO(x;conocido_y;conocido_x), donde
X es el punto de datos cuyo valor desea predecir.
Conocido_y es la matriz o rango de datos dependientes.
Conocido_x es la matriz o rango de datos independientes.
En nuestro ejemplo tenemos una tabla que muestra cantidades vendidas a cada nivel de precios:
Los valores de "precios" corresponden al argumento Conocido_X; los valores de "cantidad" corresponden a Conocido_y.
Para calcular la cantidad prevista si el precio fuera 8, aplicamos la siguiente fórmula: =PRONOSTICO(A11,B4:B10,A4:A10)
También podemos usar la técnica sugerida en mi primer entrada, averiguando la ecuación de la línea de tendencia (trendline) en el gráfico, y luego aplicándola como fórmula en la hoja de cálculo.
Para ver la ecuación de la línea de tendencia, creamos el gráfico a partir de la tabla y luego abrimos el menú "agregar línea de tendencia", apretando el botón derecho del mouse sobre la curva del gráfico.
Una vez que aparece la línea de tendencia, abrimos el menú "formato línea de tendencia":
- en la pestaña "tipo" elegimos "lineal"
- en la pestaña "opciones", chequeamos "presentar ecuación en el gráfico"
Finalmente insertamos la función de la línea de tendencia como formula en una celda de la hoja de cálculo.
En nuestro ejemplo en anotamos =-417.86*B16+10771 en la celda B17), donde la celda B16 contiene el valor para el cual queremos pronosticar el resultado.
Categorías: Funciones&Formulas_
Technorati Tags: Función PRONOSTICO, FORECAST Function
Jorge:
ResponderBorrarMi nombre es Raúl, soy de Iquique-Chile.
Excelente explicación de la fórmula PRONOSTICO de MS Excel sobretodo el apoyo gráfico.
felicitaciones y gracias.
Gracias Raúl
ResponderBorrarEXCELENTE EJEMPLO ME SIRVIO PARA MI TRABAJO, GRACIAS!!!!!
ResponderBorrarJorge:
ResponderBorrarDe verdad agradezco el tiempo y la dedicación que le dedicas a tu blog. Salen datos interesantísimos.
Hice todo lo que aparece, pero resulta que depues de crear el gráfico, hago botón derecho sobre la curva, y la opción de "Agregar línea de tendencia" aparece en gris, osea, no me es posible elegirla. A que se podrá deber??... Desde ya muchas gracias. Saludos desde Chile.
Jorge:
ResponderBorrarHabía intentado hacer la línea de tendencia, pero no me la admitía. Ahora me funcionó, y lo único que hice fue cambiar el tipo de gráfico a XY (Dispersión). Saludos!!!
Quiero felicitarlo por el extenso trabajo que ha realizado para facilitar nuestro uso del excel; sin embargo quiero hacer una crítica respecto al ejemplo que propone para la función PRONÓSTICO, pues los datos se deberían ajustar a un polinomio de orden dos y no a una recta; es en este sentido que deseo solicitar su ayuda para emplear la función pronóstico en el ajuste de una serie de datos a un polinomio n, pues la información que da Microsoft al respecto NO FUNCIONA.
ResponderBorrarque pasa si tienes en vez de una columna de x conocidos, dos columnas... y por ende para tu valor "x" del primer argumento necesitas 2 datos numericos?
ResponderBorrarel excel no me acepta poner un rango
Elizabeth
ResponderBorrarfijate en esta nota sobre regresión lineal con Excel
te pasaste muy buena explicación fácil de entender y aplicar
ResponderBorrarRoy
Sr.:
ResponderBorrarQué diferencia hay entre la función Pronóstico y Tendencia... Se podría haber utilizado la función Tendencia para el ejemplo expuesto, ¿verdad? Gracias.
No hay grandes diferencias y ciertamente se puede usar una en lugar de la otra. TENDENCIA puede mostrar varios valores. TENDENCIA es matricial (se anota apretando Ctrl+Mayusc.+Enter).
ResponderBorrarPRONOSTICO calcula un único valor a partir de valores existentes en una serie.
¡ Gracias !
ResponderBorrarTengo 12 datos (1 año) mensuales entre ellos tengo varios ceros y necesito calcular las ventas para 12 meses más cual seria el modelo para el pronostico mas adecuado y si hay un proceso estandar para hacerlo.
ResponderBorrar¿Podrías ampliar la consulta? No me resulta claro que tipo de cálculo quieres hacer.
ResponderBorrarSUPER BNO
ResponderBorrarJorge tengo una duda.
ResponderBorrarSe puede predecir un valor de y (en tu ejemplo "cantidad") a partir de un valor de precio; es decir, predecir un valor de x a parir de un valor de y.
Siempre y cuando sepas cuál es la relación entre la variable dependiente y la independiente. No tiene nada que ver con Excel.
ResponderBorrarPodés subir nuevamente el archivo? gracias.
ResponderBorrarVeré si lo encuentro, es una nota de hace más de 6 años.
ResponderBorrarLo encontré! Enlace corregido.
ResponderBorrarhola, tengo varias series de datos y me gustaria tener una línea de tendencia para cada una de mis series. ¿Hay alguna función de excel que la calcule?
ResponderBorrarPodría ir creando graficos para cada serie y añadirle la linea de tendencia pero es muy costoso si tengo más de 100 series e ir una a una
PRONOSTICO (el tema de la nota). También con el Data Analysis (Regresión y Estadística Descriptiva)
ResponderBorrarConsulta, cuando tienes una serie de tiempo de meses (Enero a Diciembre) como Matriz X y lo colocas como números del 1 al 12 y tienes los datos de ventas como matriz Y y quieres pronosticar el siguiente mes que vendría a ser Enero del siguiente año, colocas como dato conocido Y "1" nuevamente por ser Enero pero del sgte año o colocas 13 para seguir la serie numérica. Probe con ambos casos y salen resultados distintos y no se como funciona la función de excel en estos casos, porque salen datos distintos? Cual debería ser el mas acertado? Espero haberme dejado entender y puedas resolver mi duda.
ResponderBorrarEn el caso de ventas la relación entre los valores de X (meses) y los de Y (ventas) están influenciados, por lo general, por cuestiones estacionales. Por ejemplo, la industria turística vende más en los meses de verano; si se trata de esquí, en los de invierno. Siguiendo esta lógica tendría que volver a usar 1 para Enero, no 13.
ResponderBorrarDe todas maneras, hay que considerar los factores que influyen, más allá de la estacionalidad (creciemiento de la población, situación económica, etc.).
Muchas gracias por la respuesta. Ha sido de gran ayuda.
ResponderBorrarTengo un problema con la función pronóstico dándome un resultado ilógico:
ResponderBorrarFECHA PU
30-06-2014 10.180,53
30-09-2014 12.679,08
30-12-2014 15.595,12
30-03-2015 19.641,35
30-06-2015 26.535,13
30-09-2015 41.835,95
30-12-2015 41.498,88 PRONÓSTICO
Usando tus datos obtengo 42338.92976 como resultado.
ResponderBorrarHola y gracias de antemano:
ResponderBorrarTengo un problema con la fución pronóstico, que creo que posiblemente no se pueda hacer con la función asi tal cual_
La cuestión es la siguiente;
Tengo en la columna A, una serie de datos que van desde el 100 al 1000, (nunca son fuera de esos intervalos), en la columna B tengo una serie de números (FIJOS) que son los valores que sólo pueden ser como opción de pronóstico, (por ejemplo 101; 200; 213; 347; 616; 823 y no pueden ser como pronóstico ninguno que no sean esos.
Supongo que me he explicado bien, el problema básicamente es que el pronóstico está condicionado a que debe tomar valores determinados, y que a su vez los datos de la columna A que son los valores que historicamente han tenido, se ha observado que en lo sucesivo sólo podrá tomar cualquiera de los valores de la columna B.
Es posible que con la función pronóstico no se pueda hacer, o que se pueda pero haciendo algo previo, la verdad no se, como hacerlo.
Gracias
No estoy seguro de haber entendido pero de todas maneras lo que se me ocurre es usar la función BUSCARV, con el cuarto parámetro en 1, para obtener el valor "fijo" más cercano al resultado de usar PRONOSTICO.
ResponderBorrarDependiendo del comportamiento de los datos será la medida en la que sirva esta fórmula. Es decir, si tenemos una serie de datos que se comporta de manera polinómica lo mejor es aplicar una regresion polinomial al orden que mejor se adapte a las necesidades de exactitud que se requieran. Es una manera rápidade obtener un valor para cuando se tiene una propocion lineal. Saludos
ResponderBorrar