Tabla de datos de dos variables en Excel

¿Cómo crear una tabla de datos de dos variables en Excel?

La tabla de datos de dos variables nos ayuda a analizar cómo la combinación de dos variables diferentes impacta en la tabla de datos general. La palabra en sí sugiere dos variables involucradas en esta tabla de datos. En términos simples, cuando las dos variables cambian, cuál es el impacto en el resultado. En una tabla de datos de variables, solo cambia una variable, pero aquí dos variables cambian simultáneamente.

Ejemplos

Tomemos algunos ejemplos para ver cómo podemos crear una tabla de datos de dos variables en Excel.

Puede descargar esta plantilla de Excel de tabla de datos de dos variables aquí - Plantilla de Excel de tabla de datos de dos variables

Ejemplo 1

Suponga que está pidiendo un préstamo al banco y está discutiendo con el gerente del banco su tasa de interés y el período de reembolso. Debe analizar a las diferentes tasas de interés y en los diferentes períodos de reembolso cuál es la cantidad mensual de EMI que debe pagar.

Además, suponga que es una persona asalariada y después de todos sus compromisos mensuales, puede ahorrar un máximo de Rs. 18, 500 / -.

La propuesta inicial forma el banco es como fuelle.

A una tasa de interés de 22% PA, la EMI mensual durante 3 años es 19.095.

Crea una tabla como esta.

Ahora, a la celda F8, proporcione un enlace a la celda B5 (que contiene el cálculo de EMI).

Seleccione la tabla de datos que hemos creado para crear escenarios.

Vaya a Datos y luego seleccione ¿Qué pasa si análisis y tabla de datos?

Ahora haga clic en Tabla de datos y se abrirá el cuadro de diálogo a continuación.

Hemos organizado nuestras nuevas tablas como diferentes tipos de interés verticalmente y diferentes años horizontalmente.

En nuestro cálculo original, la tasa de interés está en la celda B4 y la celda de número de años está en la celda B2.

Por lo tanto, para la celda de entrada de fila, proporcione un enlace a B2 (que contiene años y en nuestra tabla los años están allí horizontalmente) y para la celda de entrada de columna proporcione un enlace a B4 (que contiene la tasa de interés y en nuestra tabla la tasa de interés está verticalmente)

Ahora haga clic en Aceptar. Esto crearía una tabla de escenarios instantáneamente.

Así que ahora tienes todos los escenarios frente a ti. Sus ahorros mensuales son 18500 por mes.

Opción 1: si no quiere dinero extra.

Debe negociar con el banco una tasa de interés del 18,5% anual durante 3 años. Si puede negociar esta tasa, debe pagar una EMI mensual de Rs. 18202.

Opción 2: si necesita algo de dinero extra.

En este mundo volátil, necesita algo de efectivo todo el tiempo. Por lo tanto, no puede gastar todo el dinero de 18500 ahorros para su salario.

Si quiere dejarnos decir 3000 por mes como efectivo adicional, debe negociar con el banquero un máximo del 15,5% durante 3,5 años. En este caso, debe pagar una EMI mensual de 15,499 por mes.

¡¡Guau!! Una herramienta tan útil que tenemos en Excel. Podemos analizar y elegir el plan o idea según nuestros deseos.

Ejemplo # 2

Suponga que está invirtiendo dinero en fondos mutuos a través de la planificación SIP. Mensualmente estás invirtiendo en 4500. Necesitas hacer un análisis para saber cuál es el retorno de la inversión después de ciertos años.

No está seguro de cuándo dejar de invertir dinero y cuál es el porcentaje que espera.

A continuación se muestran los detalles básicos para realizar el análisis de sensibilidad.

Aplicar la función FV para conocer el valor futuro después de 25 años de inversión.

Ok, el valor futuro de su inversión después de 25 años es de 65 mil rupias.

Ahora necesita saber en diferentes años y a diferentes tasas cuál sería el retorno de la inversión. Crea una tabla como esta.

Ahora proporcione un enlace a la celda F4 de B5 (que contiene el valor futuro de nuestra inversión original).

Seleccione la tabla que hemos creado.

Vaya a Datos y luego seleccione ¿Qué pasa si análisis y tabla de datos?

Ahora haga clic en Tabla de datos y se abrirá el cuadro de diálogo a continuación.

En la FILA, seleccione la celda de entrada para dar el enlace a la celda B2 (que contiene el número, años). La razón por la que hemos seleccionado esta celda es porque hemos creado una nueva tabla y en esa tabla, nuestros años en el formato de fila, es decir, horizontalmente.

En la COLUMNA, seleccione la celda de entrada para dar el enlace a la celda B4 (que contiene el porcentaje de retorno esperado). La razón por la que hemos seleccionado esta celda es porque hemos creado una nueva tabla y en esa tabla, nuestros porcentajes esperados están en formato de columna, es decir, verticalmente.

Haga clic en Aceptar para crear una tabla de escenarios para usted.

Mira las celdas que he resaltado. En el primer intento, tenemos que esperar 25 años para obtener la suma de 65 lakh con un rendimiento del 10,5%. Sin embargo, a una tasa de retorno del 13% obtenemos esa cantidad en 22 años. De manera similar, a una tasa de retorno del 15% obtenemos esa cantidad en solo 20 años.

Así es como podemos hacer un análisis de sensibilidad utilizando la tabla de datos de dos variables en Excel.

Cosas para recordar

  • No podemos deshacer la acción (Ctrl + Z) realizada por la tabla de datos. Sin embargo, puede eliminar manualmente todos los valores de la tabla.
  • No podemos eliminar celdas una vez a la vez porque es una fórmula de matriz.
  • La tabla de datos es una fórmula vinculada, por lo que no requiere actualización manual.
  • Esto es muy útil para observar el resultado cuando dos variables cambian a la vez.