Utilizar las herramientas de análisis y de simulación

1. Crear una tabla de datos con una variable

Una tabla de datos es un rango de celdas que indicará cómo la modificación de una o dos variables en las fórmulas puede afectar al resultado de sus fórmulas. Las tablas de datos ofrecen una técnica simple y rápida que permite calcular varios resultados en una sola acción, así como un modo de vista y de comparación de los resultados de todas las diferentes variaciones.

Además, utilizar una tabla de datos permite librarse de la gestión de direccionamiento (relativo, absoluto y mixto), ya que Excel genera una fórmula matricial que gestionará automáticamente las referencias de cada uno de los rangos en cuestión.

Para familiarizarse con esta técnica, vamos a empezar con la creación de una tabla de datos con una variable.

La primera hoja de nuestro libro TD1V contiene una tabla que permite calcular los distintos elementos de una inversión financiera. Mediante la introducción de los datos relativos al importe del capital invertido, el tipo de interés anual y la duración para la que queremos comparar los valores del capital obtenido y de los intereses adquiridos cuando el tipo varía.

La mensualidad se calcula con la siguiente fórmula: =ABS(PAGO(B4/12;12;B5*12;B3))

El total de los intereses pagados a plazos...

1. Principio

Cuando dispone de datos que corresponden a valores anteriores, puede utilizar Excel para realizar una previsión para el futuro. Por ejemplo, conoce los volúmenes de ventas obtenidos en los 6 primeros meses del año, puede estimar cuál será el volumen de ventas estimado del mes 11 (por supuesto, considerando que la progresión sigue el mismo ritmo).

La técnica consiste en realizar un gráfico de líneas de esta evolución y, a partir de estas líneas, obtener su ecuación y utilizarla para obtener el valor futuro deseado.

2. Realizar una simulación para el futuro a partir de datos anteriores conocidos

Ha registrado durante un año el número de conexiones semanales realizadas a su página web. El detalle se ha registrado en 52 semanas en la hoja Previsiones.

La curva de conexiones se ha insertado en la hoja.

Quiere conocer cuáles serán las conexiones para una semana futura en concreto, si la progresión sigue el mismo ritmo.

El gráfico nos muestra que nuestra curva podría parecerse a una ecuación lineal y=ax+b.

La función ESTIMACION.LINEAL calcula los parámetros de una recta mediante el método de mínimos cuadrados para calcular una recta que se ajusta lo más posible a sus datos, y devuelve una matriz de dos variables que describe esta recta.

1. ¿Para qué crear un diagrama de Pareto?

Un diagrama de Pareto es un gráfico que representa la importancia de los distintos factores en un fenómeno. Los datos se presentan en forma de histograma cuyos datos representados en abscisas se clasifican por valores decrecientes. Generalmente, se añade al gráfico una línea que representa los porcentajes acumulados.

Este tipo de representación puede utilizarse en muchos ámbitos; a continuación, le presentamos algunos ejemplos:

Son muchos los casos en los que crear un diagrama de Pareto le podrá ser útil en el análisis de datos. Así podrá centrar sus tomas de decisiones en los elementos más importantes.

2. Presentación

Dirige una pequeña empresa que comercializa sorbetes de fruta en Internet. La lista de ventas se ha almacenado en la hoja Sorbetes.

A partir de esta lista, quiere ver cuáles son los productos que suponen la mayor parte de su volumen de ventas.

Antes de crear el gráfico, hay que hacer un cuadro resumen. Las familias de productos deberán clasificarse automáticamente por valores decrecientes para que nuestro histograma esté acorde con nuestras necesidades.

Serán necesarias dos fases:

1. Con el valor objetivo

A veces en las tablas de cálculo necesitará realizar cambios sucesivos de un dato hasta que llegue al resultado esperado. Puede hacerlo por tanteo escribiendo a mano diferentes valores cada vez más precisos (por dicotomía) para aproximarse cada vez más al valor buscado. Este procedimiento puede ser largo y tedioso si se hace manualmente.

Excel dispone de una función que hace este trabajo por usted: buscar objetivos. Esta técnica permite determinar, cuando conoce el valor del resultado esperado, el valor de un parámetro. Excel variará este parámetro hasta que el resultado de su fórmula sea prácticamente igual al valor buscado.

Vamos a usar cuatro pequeñas tablas que le permitirán asimilar rápidamente esta técnica.

a. Cálculo de una tasa de margen

La tabla de la hoja VC1 calcula el precio de venta con IVA de un producto.

Las fórmulas:

B4: =B2*B3

B6: =B4*(1+B5)

La competencia vende un producto equivalente a 3.200,00 € IVA incluido, queremos ofrecerlo por 3.150,00 € IVA incluido. No podemos fabricar esta máquina a un precio inferior. El tipo de IVA también nos lo imponen.

La pregunta que nos plantearemos para solucionar nuestro problema será: ¿Cuál debe ser el coeficiente de margen que aplicaremos para obtener un precio de venta IVA incluido de 3.150 €?

Utilizaremos la técnica buscar objetivo.

 Coloque el cursor en la celda que contiene el valor que desea alcanzar (B6).

 Pestaña Datos - grupo Previsión, despliegue el menú Análisis de hipótesis y seleccione Buscar objetivo.

 En el cuadro de diálogo Buscar objetivo, haga clic en la zona Con el valor y escriba 3150.

 Haga clic en la zona Cambiando la celda y coloque el cursor en el coeficiente de margen (B3).

 Para terminar, confirme con el botón Aceptar.

Excel ha encontrado una solución:

La tabla muestra ahora un coeficiente de margen de 1,32. Si hace clic en B3, el valor calculado real se ve en la barra de fórmulas: 1,32108706592854. No habría llegado jamás a este resultado de forma manual.

La celda que contiene el valor buscado siempre debe contener una fórmula para que Excel pueda realizar una simulación.