Análisis Y si ... con tablas de datos

Con una tabla de datos en Excel, puede variar fácilmente una o dos entradas y realizar análisis hipotéticos. Una tabla de datos es un rango de celdas en el que puede cambiar valores en algunas de las celdas y obtener diferentes respuestas a un problema.

Hay dos tipos de tablas de datos:

• Tablas de datos de una variable
• Tablas de datos de dos variables

Si tiene más de dos variables en su problema de análisis, debe utilizar la herramienta Administrador de escenarios de Excel. Para obtener más detalles, consulte el capítulo Análisis Y si ... con el Administrador de escenarios de este tutorial.

Tablas de datos de una variable

Se puede usar una tabla de datos de una variable si desea ver cómo los diferentes valores de una variable en una o más fórmulas cambiarán los resultados de esas fórmulas. En otras palabras, con una tabla de datos de una variable, puede determinar cómo cambiar una entrada cambia cualquier número de salidas. Entenderás esto con la ayuda de un ejemplo.

Example

Hay un préstamo de 5.000.000 para una tenencia de 30 años. Desea conocer los pagos mensuales (EMI) para tasas de interés variadas. También puede estar interesado en conocer la cantidad de intereses y el capital que se paga en el segundo año.

Análisis con tabla de datos de una variable

El análisis con tabla de datos de una variable se debe realizar en tres pasos:

Step 1 - Establecer el fondo requerido.

Step 2 - Cree la tabla de datos.

Step 3 - Realizar el análisis.

Entendamos estos pasos en detalle:

Paso 1: establece el fondo requerido

• Suponga que la tasa de interés es del 12%.

• Enumere todos los valores requeridos.

• Nombra las celdas que contienen los valores, de modo que las fórmulas tengan nombres en lugar de referencias de celda.

• Establezca los cálculos para EMI, interés acumulado y capital acumulado con las funciones de Excel: PMT, CUMIPMT y CUMPRINC respectivamente.

Su hoja de trabajo debe verse de la siguiente manera:

Puede ver que las celdas de la columna C se nombran como se indica en las celdas correspondientes de la columna D.

Paso 2: crear la tabla de datos

• Escriba la lista de valores, es decir, las tasas de interés que desea sustituir en la celda de entrada de la columna E de la siguiente manera:

Como puede observar, hay una fila vacía sobre los valores de la Tasa de interés. Esta fila es para las fórmulas que desea utilizar.

• Escriba la primera función (PMT) en la celda una fila arriba y una celda a la derecha de la columna de valores. Escriba las otras funciones (CUMIPMT and CUMPRINC) en las celdas a la derecha de la primera función.

  Ahora, las dos filas sobre los valores de la Tasa de interés tienen el siguiente aspecto:

La tabla de datos se ve como se indica a continuación:

Paso 3: Realice el análisis con la herramienta Tabla de datos de análisis Y si ...

• Seleccione el rango de celdas que contiene las fórmulas y valores que desea sustituir, es decir, seleccione el rango - E2: H13.

• Haga clic en la pestaña DATOS en la cinta.

• Haga clic en Análisis hipotético en el grupo Herramientas de datos.

• Seleccione Tabla de datos en la lista desplegable.

Data Table aparece el cuadro de diálogo.

• Haga clic en el icono en el cuadro de celda de entrada Columna.
• Haga clic en la celda Interest_Rate, que es C2.

Puede ver que la celda de entrada de la columna se toma como $ C $ 2. Haga clic en Aceptar.

La tabla de datos se llena con los resultados calculados para cada uno de los valores de entrada como se muestra a continuación:

Si puede pagar una EMI de 54.000, puede observar que la tasa de interés del 12,6% es adecuada para usted.

Tablas de datos de dos variables

Se puede usar una tabla de datos de dos variables si desea ver cómo los diferentes valores de dos variables en una fórmula cambiarán los resultados de esa fórmula. En otras palabras, con una tabla de datos de dos variables, puede determinar cómo cambiar dos entradas cambia una sola salida. Comprenderá esto con la ayuda de un ejemplo.

Example

Hay un préstamo de 50.000.000. Quiere saber cómo las diferentes combinaciones de tasas de interés y tenencia de préstamos afectarán el pago mensual (EMI).

Análisis con tabla de datos de dos variables

El análisis con tabla de datos de dos variables debe realizarse en tres pasos:

Step 1 - Establecer el fondo requerido.

Step 2 - Cree la tabla de datos.

Step 3 - Realizar el análisis.

Paso 1: establece el fondo requerido

• Suponga que la tasa de interés es del 12%.

• Enumere todos los valores requeridos.

• Nombra las celdas que contienen los valores, de modo que la fórmula tenga nombres en lugar de referencias de celda.

• Establezca el cálculo de EMI con la función de Excel - PMT.

Su hoja de trabajo debe verse de la siguiente manera:

Puede ver que las celdas de la columna C se nombran como se indica en las celdas correspondientes de la columna D.

Paso 2: crear la tabla de datos

• Tipo =EMI en la celda F2.

• Escriba la primera lista de valores de entrada, es decir, las tasas de interés en la columna F, comenzando con la celda debajo de la fórmula, es decir, F3.

• Escriba la segunda lista de valores de entrada, es decir, el número de pagos en la fila 2, comenzando con la celda a la derecha de la fórmula, es decir, G2.

  La tabla de datos tiene el siguiente aspecto:

Realice el análisis con la tabla de datos de la herramienta de análisis Y si ...

• Seleccione el rango de celdas que contiene la fórmula y los dos conjuntos de valores que desea sustituir, es decir, seleccione el rango - F2: L13.

• Haga clic en la pestaña DATOS en la cinta.

• Haga clic en Análisis hipotético en el grupo Herramientas de datos.

• Seleccione Tabla de datos de la lista desplegable.

Aparece el cuadro de diálogo Tabla de datos.

• Haga clic en el icono en el cuadro de celda de entrada Fila.
• Haga clic en la celda NPER, que es C3.
• Nuevamente, haga clic en el icono en el cuadro de celda de entrada Fila.
• A continuación, haga clic en el icono en el cuadro de celda de entrada de columna.
• Haga clic en la celda Interest_Rate, que es C2.
• Nuevamente, haga clic en el icono en el cuadro de celda de entrada de columna.

Verá que la celda de entrada Fila se toma como $ C $ 3 y la celda de entrada Columna se toma como $ C $ 2. Haga clic en Aceptar.

La tabla de datos se llena con los resultados calculados para cada combinación de los dos valores de entrada:

Si puede pagar un EMI de 54.000, la tasa de interés del 12,2% y 288 EMI son adecuados para usted. Esto significa que la tenencia del préstamo sería de 24 años.

Cálculos de tablas de datos

Las tablas de datos se vuelven a calcular cada vez que se vuelve a calcular la hoja de trabajo que las contiene, incluso si no han cambiado. Para acelerar los cálculos en una hoja de trabajo que contiene una tabla de datos, debe cambiar las opciones de cálculo aAutomatically Recalculate la hoja de trabajo pero no las Tablas de datos, como se indica en la siguiente sección.

Acelerar los cálculos en una hoja de trabajo

Puede acelerar los cálculos en una hoja de trabajo que contenga tablas de datos de dos maneras:

• Desde Opciones de Excel.
• De la cinta.

Desde Opciones de Excel

• Haga clic en la pestaña ARCHIVO en la cinta.
• Seleccione Opciones de la lista en el panel izquierdo.

Aparece el cuadro de diálogo Opciones de Excel.

• En el panel izquierdo, seleccione Formulas.

• Seleccione la opción Automatic except for data tables debajo Workbook Calculationen la sección Opciones de cálculo. Haga clic en Aceptar.

De la cinta

• Haga clic en la pestaña FÓRMULAS en la cinta.

• Haga clic en el Calculation Options en el grupo Cálculos.

• Seleccione Automatic Except for Data Tables en la lista desplegable.