Tablas de datos

Una tabla de datos analiza un conjunto de valores y determina posibles resultados.

Esta herramienta permite generar tablas utilizando utilizando una o dos variables.

Para utilizar esta herramienta es indispensable estructurar el modelo de manera adecuada así:

Al momento de estructurar el modelo se debe tener en cuenta lo siguiente:

- Los datos de ejemplo pueden estar en cualquier parte de la hoja
- La fórmula o función siempre se ubica en la esquina superior izquierda de la tabla
- La fórmula o función siempre se hace con los datos de ejemplo

- Los resultados de la tabla se calculan como un TODO, es decir, no se puede cambiar un dato de la tabla individual sino que deben cambiarse todos a la vez o cambiar cualquiera de sus componentes: variables o fórmula.

Pasos para crear una tabla de datos:

1. Estructurar los datos según las instrucciones anteriores
2. Seleccionar la información desde la fórmula a la derecha y abajo (se deben incluir los valores de las variables)
3. Ir al menú datos a la opción análisis de hipótesis (o análisis y si... en otras versiones) y elegir la opción tablas de datos

Sale el siguiente cuadro de diálogo:

Donde:

- Celda de entrada (fila) es la referencia (celda) que, en el ejemplo, representa la primera fila de la tabla.
- Celda de entrada (columna) es la referencia (celda) que, en el ejemplo, representa la primera columna de la tabla.

Por ejemplo:

Al aceptar se rellenarán los datos de la tabla para cada combinación de variable1 y variable2.

Ahora, revisemos algunos ejemplos:

Ejemplo1. Se tiene un proyecto con los siguientes datos:

Tasa de oportunidad 10% anual
Inversión Inicial -45000000
Pérdida año1 -10000000
Utilidad año2 4000000
Utilidad año3 17000000
Utilidad año4 25000000
Utilidad año5 32000000

Ahora vamos a calcular los dos principales indicadores financieros que nos permitirán determinar si el proyecto es rentable o no: tasa interna de retorno (TIR) y Valor presente neto (VNA).

Para estos cálculos utilizaremos las funciones de Excel que tienen el mismo nombre:

Función	descripción	Sintaxis y solución
TIR	Tasa Interna de Retorno. Calcula el rendimiento generado por el proyecto	=TIR(valores, [estimación]) Aquí los valores son los valores invertidos y generados en el proyecto y estimación (opcional) es una tasa que el usuario supone como resultado, la mayoría de las veces no es necesario. En nuestro ejemplo será: =TIR(B2:B7) = 9,43%
VNA	Valor Neto Actualizado. Permite saber el valor en pesos que se dejaría de ganar (o perder) si no se invierte en el proyecto.	=VNA(tasa;valor1;[valor2];...) Donde: tasa es la tasa de oportunidad esperada para el proyecto y valor1, valor2... son los valores generados por el proyecto (o un rango con los mismos valores). En nuestro ejemplo será: =VNA(B1;B3:B7)+B2 = ($ 1.067.953,63)

Nota: Ver más de funciones financieras aquí

El ejemplo queda planteado así:

Ahora, estructuraremos una tabla para obtener la TIR al variar la inversión, así:

Esta es una tabla de una sola entrada. Lo siguiente es seleccionar el rango e ir a la herramienta tabla de datos:

Los resultados de la tabla serán los siguientes:

Ahora, estructuraremos una tabla de doble entrada para obtener el VNA para diferentes inversiones y tasas de oportunidad, así:

Esta es una tabla de doble entrada. Lo siguiente es seleccionar el rango e ir a la herramienta tabla de datos:

Los resultados de la tabla serán los siguientes:

En el siguiente vínculo se pueden descargar algunos ejemplos de tablas de datos solucionados: Tablas de datos