Predicción de un valor a partir de valores conocidos usando la función PRONOSTICO (FORECAST) de MS Excel.

domingo, abril 23, 2006

Excel's FORECAST function post in English

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: ,

30 comments:

Anónimo,  07 mayo, 2006 06:43  

Jorge:

Mi 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.

benjamín,  17 diciembre, 2006 03:24  

EXCELENTE EJEMPLO ME SIRVIO PARA MI TRABAJO, GRACIAS!!!!!

Fabián,  19 julio, 2007 01:29  

Jorge:
De 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.

FxXx 19 julio, 2007 22:51  

Jorge:

Habí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!!!

Químico 20 marzo, 2009 16:03  

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.

Elizabeth 23 julio, 2009 23:45  

que 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?
el excel no me acepta poner un rango

Anónimo,  10 febrero, 2011 19:50  

te pasaste muy buena explicación fácil de entender y aplicar

Roy

Anónimo,  05 junio, 2011 10:49  

Sr.:

Qué 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.

Jorge L. Dunkelman 05 junio, 2011 15:35  

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).
PRONOSTICO calcula un único valor a partir de valores existentes en una serie.

Anónimo,  05 junio, 2011 19:41  

¡ Gracias !

Anónimo,  12 julio, 2011 20:24  

Tengo 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.

Jorge L. Dunkelman 13 julio, 2011 06:31  

¿Podrías ampliar la consulta? No me resulta claro que tipo de cálculo quieres hacer.

Anónimo,  26 septiembre, 2011 19:31  

SUPER BNO

Anónimo,  07 febrero, 2012 20:30  

Jorge tengo una duda.

Se 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.

Jorge L. Dunkelman 08 febrero, 2012 07:10  

Siempre y cuando sepas cuál es la relación entre la variable dependiente y la independiente. No tiene nada que ver con Excel.

Anónimo,  27 junio, 2012 16:54  

Podés subir nuevamente el archivo? gracias.

Jorge L. Dunkelman 29 junio, 2012 18:23  

Veré si lo encuentro, es una nota de hace más de 6 años.

Jorge L. Dunkelman 29 junio, 2012 18:34  

Lo encontré! Enlace corregido.

inma 27 octubre, 2013 19:20  

hola, 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?

Podrí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

Jorge Dunkelman 27 octubre, 2013 21:13  

PRONOSTICO (el tema de la nota). También con el Data Analysis (Regresión y Estadística Descriptiva)

Anónimo,  06 diciembre, 2013 04:24  

Consulta, 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.

Jorge Dunkelman 06 diciembre, 2013 10:36  

En 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.
De 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.).

Anónimo,  06 diciembre, 2013 14:56  

Muchas gracias por la respuesta. Ha sido de gran ayuda.

Unknown 05 noviembre, 2015 21:03  

Tengo un problema con la función pronóstico dándome un resultado ilógico:
FECHA 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

Jorge Dunkelman 07 noviembre, 2015 17:39  

Usando tus datos obtengo 42338.92976 como resultado.

Anónimo,  10 junio, 2016 15:31  

Hola y gracias de antemano:
Tengo 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

Jorge Dunkelman 11 junio, 2016 09:27  

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.

Leydi Anilu Gonzalez Zavala 21 marzo, 2017 23:56  

Dependiendo 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

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP