Análisis hipotético con tablas de datos
AREAtutorial » Big Data & Analitycs » Análisis hipotético con tablas de datos
Con una hoja de datos en Excel, puede cambiar fácilmente una o dos entradas y realizar análisis hipotéticos. Una tabla de datos es un rango de celdas donde puede cambiar los valores en algunas de las celdas y obtener diferentes respuestas a un problema.
Hay dos tipos de tablas de datos:
Si tiene más de dos variables en su tarea de análisis, debe utilizar la herramienta Administrador de escenarios de Excel. Para obtener más información, consulte el capÃtulo Análisis «Y si…» con el Administrador de scripts de este tutrial.
Puede usar una tabla de datos de una sola variable cuando desee ver cómo diferentes valores de la misma variable en una o más fórmulas cambiarán los resultados de esas fórmulas. En otras palabras, con una hoja de datos de una sola variable, puede definir cómo cambiar una entrada cambia cualquier número de salidas. Entenderás esto con un ejemplo.
Ejemplo
Existe un préstamo por un monto de 5.000.000 por un perÃodo de 30 años. Desea conocer los pagos mensuales (EMI) para diferentes tasas de interés. También puede estar interesado en conocer el interés y el monto principal pagado en el segundo año.
El análisis que utiliza una tabla de datos de una sola variable debe realizarse en tres pasos:
Paso 1 – Establecer el fondo requerido.
Paso 2 – Crea una tabla de datos.
Paso 3 – Realizar un análisis.
Tratemos estos pasos en detalle:
Supongamos que la tasa de interés es del 12%.
Enumere todos los valores requeridos.
Nombra las celdas que contienen los valores para que las fórmulas tengan nombres en lugar de referencias de celda.
Configure los cálculos para EMI, interés acumulado y principal acumulativo utilizando las funciones de Excel PMT, CUMIPMT y CUMPRINC, respectivamente.
Su hoja de trabajo debe tener el siguiente aspecto:
Puede ver que las celdas de la columna C se nombran como se indica en las celdas correspondientes de la columna D.
Ingrese una lista de valores, es decir, las tasas de interés que desea reemplazar en la celda de entrada en la columna E, de la siguiente manera:
Como puede ver, hay una lÃnea en blanco sobre los valores de la tasa de interés. Esta lÃnea es para las fórmulas que desea utilizar.
Ingrese la primera función (DEMANDAR) en la celda una fila arriba y una celda a la derecha de la columna de valor. Ingrese otras funciones (CUMIPMT y CUMPRINC) en las celdas a la derecha de la primera función.
Ahora, las dos lÃneas sobre los valores de la tasa de interés se ven asÃ:
La tabla de datos se ve a continuación:
Seleccione el rango de celdas que contiene las fórmulas y los 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 Y si… en el grupo Herramientas de datos.
Seleccione Tabla de datos de la lista desplegable.
Tabla de datos Un cuadro de diálogo aparecerá.
Puede ver que la celda de entrada de la columna se acepta como $ C $ 2. Haga clic en Aceptar.
La hoja de datos se completa con resultados calculados para cada uno de los valores de entrada como se muestra a continuación:
Si puede pagar 54,000 EMI, puede notar que la tasa de interés del 12.6% es adecuada para usted.
Puede usar una tabla de datos con 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, al usar una tabla de datos con dos variables, puede determinar cómo cambiar dos entradas cambia una salida. Entenderás esto con un ejemplo.
Ejemplo
Hay un préstamo de 50 millones. Quiere saber cómo las diferentes combinaciones de tipos de interés y condiciones del préstamo afectarán al pago mensual (EMI).
El análisis mediante una tabla de datos con dos variables debe realizarse en tres pasos:
Paso 1 – Establecer el fondo requerido.
Paso 2 – Crea una tabla de datos.
Paso 3 – Realizar un análisis.
Supongamos que la tasa de interés es del 12%.
Enumere todos los valores requeridos.
Nombra las celdas que contienen los valores para que la fórmula tenga nombres en lugar de referencias de celda.
Establecer el cálculo de EMI usando la función de Excel – DEMANDAR…
Su hoja de trabajo debe tener el siguiente aspecto:
Puede ver que las celdas de la columna C se nombran como se indica en las celdas correspondientes de la columna D.
Un tipo = EMI en la celda F2.
Ingrese la primera lista de valores de entrada, es decir, tasas de interés, en la columna F, comenzando en la celda debajo de la fórmula, es decir, F3.
Ingrese la segunda lista de insumos, es decir, el número de pagos en la lÃnea 2, comenzando en la celda a la derecha de la fórmula, es decir, G2.
La tabla de datos se ve asÃ:
Seleccione el rango de celdas que contiene la fórmula y los dos conjuntos de valores que desea reemplazar, es decir seleccione el rango – F2: L13.
Haga clic en la pestaña DATOS en la cinta.
Haga clic en Análisis Y si… en el grupo Herramientas de datos.
Seleccione Tabla de datos de la lista desplegable.
Aparece el cuadro de diálogo Tabla de datos.
Verá que la celda de entrada de la fila se acepta como & dollar; C & dólar; 3, y la celda de entrada de la columna se acepta como & dollar; C & dólar; 2. Haga clic en Aceptar.
La hoja de datos se completa con resultados calculados para cada combinación de dos valores de entrada:
Si puede pagar un EMI de 54.000, una tasa de interés del 12,2% y un EMI de 288 está bien. Esto significa que el plazo del préstamo será de 24 años.
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 que contiene una tabla de datos, debe cambiar las opciones de cálculo a Recalcular automáticamente hoja de trabajo, pero no tablas de datos, como se indica en la siguiente sección.
Puede acelerar los cálculos en una hoja que contiene tablas de datos de dos formas:
Aparece el cuadro de diálogo Opciones de Excel.
En el panel izquierdo, seleccione Fórmulas…
Elige una opcion Automáticamente, excepto para tablas de datos debajo Cálculo del libro de trabajo Para obtener más información, consulte Opciones de cálculo. Haga clic en Aceptar.
Haga clic en la pestaña FÓRMULAS en la cinta.
Haga clic en el icono Opciones de cálculo en el grupo Cálculos.
Elegir Automáticamente, excepto para tablas de datos en la lista desplegable.
🚫