Tabla de datos en Excel manejar variables con tabla de doble entrada

Mediante la tabla de datos en Excel podremos realizar simulaciones con dos variables para obtener resultados en una tabla de doble entrada de forma muy sencilla. Deberemos tener un cálculo en el que las dos variables que vamos a simular en nuestra tabla de datos intervengan. Estas dos variables como su nombre indican cambian su valor para cada uno de los escenarios de la simulación.

Supuesto para el manejo de tabla de datos en Excel

Vimos en artículos anteriores el manejo del administrador de escenarios en donde habíamos ejemplificado el pago de la letra de un vehículo que adquirimos en el concesionario. En aquel ejemplo habíamos definido la siguiente tabla:

Estábamos calculando la cuota mensual que tendremos que pagar en base a los valores iniciales:

• Coste del vehículo = 20.000€
• Tasación (monto que nos descuentan del coste inicial por la entrega de nuestro vehículo viejo) = 1.000€
• Plazo en meses (duración del préstamo) = 72 meses
• Tipo de interés (interés que pagamos al banco que nos presta) = 7%

En base a esto datos calculamos el valor de la cuota de forma muy sencilla dividiendo el coste del vehículo menos la tasación entre el número de cuotas. Al resultado le aplicamos el interés.
Hasta aquí nada nuevo…¿Qué ocurre si vamos a visitar varios bancos y tenemos diferentes ofertas de tipo de interés? ¿Nos podría interesar un préstamo a menos tiempo pero que tenga una tipo de interés inferior? En esta situación existen dos variables que alterarán la cuota mensual:

• Plazo en meses
• Tipo de interés.

Veamos entonces como montar una tabla de datos en Excel que nos ayude de un vistazo a simular diferentes escenarios con los diferentes valores que hemos recuperado al visitar varios bancos.

¿Cómo defino mi tabla de datos en Excel con la información anterior?

Tendremos que crear una tabla de doble entrada. En nuestro ejemplo hemos puesto la variable “Tipo de interés” en la columna F (Rango F8:F11) y la variable “Plazo meses” en la fila 7 (Rango G7:I7). También necesitaremos que exista un cálculo en la celda F7 precisamente es el cálculo de la cuota que habíamos definido como =((D7-D8)/D9)*(1+D10).

Lo que hacemos en esta celda F7 es decir que es igual a la celda D11 que refleja el cálculo descrito. ¡Ojo! Este detalle es clave para que nuestro simulador con tabla de datos en Excel funcione correctamente.

Nuestro simulador utilizará las variables que hemos definido en F8:F11 y G7:I7 y los colocará dentro del cálculo de forma transparente para nosotros y nos presentará el resultado en el espacio G8:I11.

Para ello debemos seleccionar todo el rango incluyendo el cálculo es decir el rango F7:I11 y haremos clic en la pestaña Datos-Previsión-Análisis de hipótesis-Tabla de datos. Se nos presenta un asistente muy sencillo que nos pregunta:

• Celda de entrada (fila): ¿Qué variable definimos en nuestra tabla en la fila 7? La variable “Plazo meses”
• Celda de entrada (columnas): ¿Qué variable definimos en nuestra tabla en la columna F? La variable “Tipo de interés”

Nótese que hemos seleccionado entonces las celdas:

Esto es así porque en nuestro cálculo original así están definidas.

 

Finalmente y tras hacer clic en “aceptar” nuestra tabla de datos se rellena automáticamente con los resultados de la sustitución de las variables dentro del cálculo. Ahora ya podemos comenzar a analizar los resultados para tomar una decisión con respecto a la entidad con la que queremos negociar nuestro préstamo.

Descarga el ejemplo

Ejemplo_Tabla_datos_en_excel