Funciones Estadísticas Inferenciales en Excel

Dentro de Excel, encontramos muchas funciones estadísticas, ya vimos las funciones de estadística descriptiva, las funciones estadística de conteo y en esta oportunidad veremos las funciones que nos permitan predecir valores futuros, también conocidas como estadística inferencial. Estas nos ayudarán a tomar decisiones más acertadas a la hora de hacer cambios o ajustes. Veamos….

=pronóstico(x, conocidos_y, conocidos_x)

Esta función calcula o predice un valor futuro en una tendencia lineal usando valores existentes. Los valores que debemos proporcionar son los datos conocidos. En otras palabras para permitir que Excel haga un pronóstico deberemos tener valores conocidos previos. Veamos un ejemplo:

Se tienen los importes de producción de varios años, y en este caso queremos calcular el pronóstico de producción para el año 2015 y 2020 respectivamente. Observemos que los años son valores que no dependen de los esfuerzos que se hagan para mejorar la producción por lo tanto se considerarán variables independientes (valores de x conocidos). Por otro lado la producción sí es una variable dependiente (valores de y conocidos).

Queremos calcular el pronóstico para el año 2015, (x=2015). por lo tanto deberemos utilizar la función siguiente que es muy simple cuando se tienen los datos a la mano: =pronóstico(D5, B5:B16, A5:A16).

Si queremos calcular el segundo pronóstico para el año 2010, solo deberemos utilizar como nuevo valor de x, precisamente ese año. los demás valores de x e y no cambian…

Cálculo de Tendencias en forma automática

En Excel existe la posibilidad de calcular la tendencia de diversos valores en forma automática. Para ello solo tenemos que seleccionar los datos que disponemos y arrastramos el pequeño cuadro inferior con el botón derecho del mouse. Al hacerlo, Excel nos mostrará un menú contextual donde podemos elegir que los valores sean rellenados con tendencia lineal o tendencia geométrica. Vamos a ver un ejemplo sencillo, tenemos los datos de ventas del año desde enero a junio. Queremos calcular las tendencias (lineal y geométrica) para los siguientes meses…

Como vemos en la imagen, ya tenemos seleccionados los datos existentes y queremos calcular la tendencia lineal. Solo queda arrastrar con el botón derecho del mouse el pequeño cuadro de la esquina inferior y elegir Tendencia Lineal…

Excel mostrará el resultado del cálculo en forma automática. hacemos lo mismo con los datos de la derecha pero esta vez elegiremos tendencia geométrica…

Si apreciamos bien, los datos son distintos, no son iguales las proyecciones finales a diciembre. La respuesta está en el cálculo y la fórmula empleada para determinar las tendencias…

¿Cuál es la diferencia entre tendencia lineal y tendencia geométrica?

La tendencia lineal se hace en base a la proyección de una línea recta trazada entre los valores proporcionados. Entonces la proyección puede ser mas alta o más baja dependiendo siempre de los valores originales. Observemos el siguiente gráfico donde vemos los valores de una empresa 

Podemos ver que en algunos meses han subido los ingresos, y en otros han bajado, si trazamos una línea recta promedio (tendencia lineal) entre todas obtendremos esto como resultado:

Ahora en base a esta línea es como se proyectan los siguientes meses, si calculamos la tendencia lineal de los demás meses con el proceso anterior y graficamos obtendremos el siguiente resultado:

Sin embargo, los matemáticos y sobre todo los estadistas coinciden en que a veces la línea recta puede no ajustarse bien a la varianza entre los datos existentes, por ello otra forma es hacerlo con una línea geométrica o exponencial usando métodos cuadráticos.

Entonces si calculamos con el método indicado los valores proyectados con tendencia geométrica y luego lo graficamos tendremos el siguiente resultado que se ajusta a la línea de tendencia exponencial. Definitivamente en este caso una tendencia geométrica se ajusta más a la realidad del progreso de la empresa.

Existen otros métodos de calcular las tendencias por ejemplo tenemos la tendencia logarítmica:

Agregar líneas de tendencia en gráficos de Excel

Para agregar una línea de tendencia en un gráfico de Excel basta con hacer clic derecho sobre una serie de datos y elegir la opción Agregar línea de Tendencia

Entonces Excel mostrará un panel derecho con las opciones respectivas donde podemos escoger el tipo de tendencia, colores, tipo de línea, etc.

De esta forma podemos proyectar valores futuros con los métodos mostrados. ¿Comentarios?