En alguna ocasión, por ejemplo ayer mismo, en televisión se puede oír que en este mes de Septiembre se han vendido menos, en este caso coches, que en el mes de Agosto. ¿ Como pueden hacer esas afirmaciones, si aun no ha terminado Septiembre?
Entre otras posibilidades se puede utilizar la estimación lineal, que como su nombre indica, consiste en aproximar las ventas diarias de un periodo a una línea del tipo y=mX+c. Los acumulados de ventas, o de cualquier actividad diaria, tienden a ser lineales.
He preparado un libro excel, partiendo de datos del año 2007, en el que inserto en el gráfico una línea de tendencia y utilizo las funciones ESTIMACION.LINEAL e INDICE para calcular las constantes "m" y "c" de y=mX+c
Funciones utilizadas:
ESTIMACION.LINEAL()
INDICE()
Variables y rangos con nombre:
PDiaMes =FECHA(2007;Tendencia!$D$2;1) Primer día del mes
UDiaMes=DIA(PDiaMes+31-DIA(PDiaMes+31)) Último día del mes
UDM_3=UDiaMes-3 Último día del mes menos tres
NV_3=DESREF(Tendencia!$I$2;0;0;UDM_3;1) Ventas del mes menos tres últimos días.
DM_3=DESREF(Tendencia!$J$2;0;0;UDM_3;1) Días del mes menos tres.
Utilizo variables y rangos con nombre, desplegables con rango de entrada variables y gráficos con rango variable.
Supuesto: Partiendo de los datos acumulados de los primeros n-3 días del mes (n=número de días del mes) hay que calcular el estimado a fin de mes.
Parto de unos datos reales del año 2007, columnas A y B de la página ventas. Preparo un desplegable con los meses con datos del año 2007 y paso los datos de los n-3 primeros días de ese mes a la columnas G (G2:G32) y H (H2:H32) de la hoja Tendencias.
La selección del mes, las variables con nombre, gráficos con rango variable, y en general todo aquello relacionado con el manejo de datos lo he explicado con anterioridad, no es necesario repetirlo.
El supuesto consiste en estimar los tres últimos días del mes. La estimación es lineal, por lo que hay que calcular una pendiente (m) y una constante ("c"). Además de estimar estos datos les doy continuidad en los gráficos, tanto para la línea de tendencia como con los datos reales. Para dar continuidad a la línea tendencia utilizo directamente los valores que calcula la función Estimacion.Lineal (Tendencias!o2), pero para dar continuidad a la serie con los datos calculo la constante "c" que iguala la estimación con el último dato de la serie ((Tendencias!p2).
La función Estimacion.Lineal devuelve una matriz, para recuperar los valores devueltos hay que utilizar también la función INDICE:
=INDICE(ESTIMACION.LINEAL(NV_3;DM_3;VERDADERO;FALSO);1)
=INDICE(ESTIMACION.LINEAL(NV_3;DM_3;VERDADERO;FALSO);2)
etc..
El supuesto consiste en estimar los tres últimos días del mes. La estimación es lineal, por lo que hay que calcular una pendiente (m) y una constante ("c"). Además de estimar estos datos les doy continuidad en los gráficos, tanto para la línea de tendencia como con los datos reales. Para dar continuidad a la línea tendencia utilizo directamente los valores que calcula la función Estimacion.Lineal (Tendencias!o2), pero para dar continuidad a la serie con los datos calculo la constante "c" que iguala la estimación con el último dato de la serie ((Tendencias!p2).
La función Estimacion.Lineal devuelve una matriz, para recuperar los valores devueltos hay que utilizar también la función INDICE:
=INDICE(ESTIMACION.LINEAL(NV_3;DM_3;VERDADERO;FALSO);1)
=INDICE(ESTIMACION.LINEAL(NV_3;DM_3;VERDADERO;FALSO);2)
etc..
