Análisis Y si ... con el Administrador de escenarios
El Administrador de escenarios es útil en los casos en los que tiene más de dos variables en el análisis de sensibilidad. El Administrador de escenarios crea escenarios para cada conjunto de valores de entrada para las variables consideradas. Los escenarios le ayudan a explorar un conjunto de posibles resultados, que respaldan lo siguiente:
- Variando hasta 32 conjuntos de entrada.
- Fusionar los escenarios de varias hojas de trabajo o libros de trabajo diferentes.
Si desea analizar más de 32 conjuntos de entrada y los valores representan solo una o dos variables, puede utilizar Tablas de datos. Aunque está limitado a solo una o dos variables, una tabla de datos puede incluir tantos valores de entrada diferentes como desee. Consulte Análisis Y si ... con tablas de datos en este tutorial.
Escenarios
Un escenario es un conjunto de valores que Excel guarda y puede sustituir automáticamente en su hoja de trabajo. Puede crear y guardar diferentes grupos de valores como escenarios en una hoja de trabajo y luego cambiar entre estos escenarios para ver los diferentes resultados.
Por ejemplo, puede tener varios escenarios presupuestarios diferentes que comparen varios niveles de ingresos y gastos posibles. También puede tener diferentes escenarios de préstamos de diferentes fuentes que comparan varias tasas de interés y tenencias de préstamos posibles.
Si la información que desea usar en escenarios proviene de diferentes fuentes, puede recopilar la información en libros de trabajo separados y luego fusionar los escenarios de los diferentes libros de trabajo en uno.
Una vez que tenga todos los escenarios que necesita, puede crear un informe de resumen de escenarios:
- Eso incorpora información de todos los escenarios.
- Eso le permite comparar los escenarios uno al lado del otro.
Administrador de escenarios
El Administrador de escenarios es una de las herramientas de análisis hipotético de Excel.
Para crear un informe de análisis con Scenario Manager, debe seguir estos pasos:
Step 1 - Defina el conjunto de valores iniciales e identifique las celdas de entrada que desea variar, llamadas celdas cambiantes.
Step 2 - Cree cada escenario, nombre el escenario e ingrese el valor para cada celda de entrada cambiante para ese escenario.
Step 3- Seleccione las celdas de salida, llamadas celdas de resultado que desea rastrear. Estas celdas contienen fórmulas en el conjunto inicial de valores. Las fórmulas utilizan las celdas de entrada cambiantes.
El administrador de escenarios crea un informe que contiene los valores de entrada y salida para cada escenario.
Valores iniciales para escenarios
Antes de crear varios escenarios diferentes, debe definir un conjunto de valores iniciales en los que se basarán los escenarios.
Los pasos para configurar los valores iniciales para los escenarios son:
- Defina las celdas que contienen los valores de entrada.
- Nombra las celdas de entrada de manera apropiada.
- Identifique las celdas de entrada con valores constantes.
- Especifique los valores para las entradas constantes.
- Identifique las celdas de entrada con valores cambiantes.
- Especifique los valores iniciales para las entradas cambiantes.
- Defina las celdas que contienen los resultados. Las celdas de resultados contienen fórmulas.
- Nombra las celdas de resultado de manera apropiada.
- Coloque las fórmulas en las celdas de resultados.
Considere el ejemplo anterior de préstamo. Ahora, proceda de la siguiente manera:
Defina una celda para Monto del préstamo.
Este valor de entrada es constante para todos los escenarios.
Nombra la celda Loan_Amount.
Especifique el valor como 5.000.000.
Defina las celdas para Tasa de interés, No. de pagos y Tipo (Pago al principio o al final del mes).
Estos valores de entrada cambiarán según los escenarios.
Nombra las celdas Interest_Rate, NPER y Type.
Especifique los valores iniciales para el análisis en estas celdas como 12%, 360 y 0 respectivamente.
Defina la celda para el EMI.
Este es el valor del resultado.
Nombra la celda EMI.
Coloque la fórmula en esta celda como:
=PMT (Interest_Rate/12, NPER, Loan_Amount, 0, Type)
Su hoja de trabajo se ve como se muestra a continuación:
Como puede ver, las celdas de entrada y las celdas de resultado están en la columna C con los nombres que se dan en la columna D.
Crear escenarios
Después de configurar los valores iniciales para los escenarios, puede crear los escenarios usando el Administrador de escenarios de la siguiente manera:
- Haga clic en la pestaña DATOS en la cinta.
- Haga clic en Análisis hipotético en el grupo Herramientas de datos.
- Seleccione Administrador de escenarios en la lista desplegable.
Aparece el cuadro de diálogo Administrador de escenarios. Puede observar que contiene un mensaje:
“No Scenarios defined. Choose Add to.”
Debe crear escenarios para cada conjunto de valores cambiantes en el Administrador de escenarios. Es bueno tener el primer escenario definido con valores iniciales, ya que le permite volver a los valores iniciales cuando lo desee mientras se muestran diferentes escenarios.
Cree el primer escenario con los valores iniciales de la siguiente manera:
- Haga clic en el Add en el cuadro de diálogo Administrador de escenarios.
los Add Scenario aparece el cuadro de diálogo.
- En Nombre del escenario, escriba Escenario 1.
- En Cambiar celdas, ingrese las referencias para las celdas, es decir, C3, C4 y C5 con la tecla Ctrl presionada.
El nombre del cuadro de diálogo cambia a Editar escenario.
Edite el texto en el Comment as – Initial Values caja.
Seleccione la opción Evitar cambios en Protección y luego haga clic en Aceptar.
los Scenario Valuesaparece el cuadro de diálogo. Los valores iniciales que ha definido aparecen en cada uno de los cuadros de celdas cambiantes.
Scenario 1 con los valores iniciales se crea.
Cree tres escenarios más con valores variables en las celdas cambiantes de la siguiente manera:
- Haga clic en el Add en el cuadro de diálogo Valores de escenario.
Aparece el cuadro de diálogo Agregar escenario. Tenga en cuenta que C3, C4, C5 aparecen en el cuadro Cambiar celdas.
En el cuadro Nombre del escenario, escriba Escenario 2.
Edite el texto en el Comment como - Tasa de interés diferente.
Seleccione Evitar cambios en Protección y haga clic en Aceptar.
los Scenario Valuesaparece el cuadro de diálogo. Los valores iniciales aparecen en las celdas cambiantes. Cambiar el valor deInterest_Rate a 0.13 y haga clic en Add.
los Add Scenarioaparece el cuadro de diálogo. Tenga en cuenta que C3, C4, C5 aparecen en el cuadro debajo de las celdas cambiantes.
En el cuadro Nombre del escenario, escriba Escenario 3.
Edite el texto en el Commentcaja como - Diferente no. de Pagos.
Seleccione Evitar cambios en Protección y haga clic en Aceptar.
Aparece el cuadro de diálogo Valores de escenario. Los valores iniciales aparecen en las celdas cambiantes. Cambie el valor de NPER a 300 y haga clic enAdd.
los Add Scenarioaparece el cuadro de diálogo. Tenga en cuenta que C3, C4, C5 aparecen en el cuadro Cambiar celdas.
En el cuadro Nombre del escenario, escriba Escenario 4.
Edite el texto en el Comment cuadro como - Diferentes tipos de pago.
Seleccione Evitar cambios en Protección y haga clic en Aceptar.
los Scenario Valuesaparece el cuadro de diálogo. Los valores iniciales aparecen en las celdas cambiantes. Cambie el valor de Tipo a 1. Haga clic en Aceptar ya que ha agregado todos los escenarios que deseaba agregar.
los Scenario Manageraparece el cuadro de diálogo. En el cuadro de Escenarios, encontrará los nombres de todos los escenarios que ha creado.
- Haga clic en Escenario 1. Como sabe, el Escenario 1 contiene los valores iniciales.
Ahora, haga clic en Summary. Aparece el cuadro de diálogo Resumen del escenario.
Informes de resumen de escenarios
Excel proporciona dos tipos de informes de resumen de escenarios:
- Resumen del escenario.
- Informe de tabla dinámica de escenario.
En el cuadro de diálogo Resumen del escenario, puede encontrar estos dos tipos de informes.
Seleccione Resumen de escenario en Tipo de informe.
Resumen del escenario
En el Result cells cuadro, seleccione la celda C6 (Aquí, habíamos puesto el PMTfunción). Haga clic en Aceptar.
El informe Resumen del escenario aparece en una nueva hoja de trabajo. La hoja de trabajo se denomina Resumen de escenario.
Puede observar lo siguiente en el informe Resumen de escenario:
Changing Cells- Enlista todas las celdas utilizadas como celdas cambiantes. Como ha nombrado las celdas, Interest_Rate, NPER y Type, estos parecen hacer que el informe sea significativo. De lo contrario, solo se enumerarán las referencias de celda.
Result Cells - Muestra la celda de resultado especificada, es decir, EMI.
Current Values - Es la primera columna y enlista los valores de ese escenario que se selecciona en el cuadro de diálogo Administrador de escenarios antes de crear el informe de resumen.
Para todos los escenarios que ha creado, las celdas cambiantes se resaltarán en gris.
En la fila EMI, se mostrarán los valores de resultado para cada escenario.
Puede hacer que el informe sea más significativo mostrando los comentarios que agregó al crear los escenarios.
Haga clic en el botón + a la izquierda de la fila que contiene los nombres de los escenarios. Los comentarios de los escenarios aparecen en la fila debajo de los nombres de los escenarios.
Escenarios de diferentes fuentes
Suponga que obtiene los escenarios de tres fuentes diferentes y necesita preparar el informe de resumen del escenario en un libro maestro. Puede hacer esto fusionando los escenarios de diferentes libros de trabajo en el libro de trabajo maestro. Siga los pasos que se indican a continuación:
Suponga que los escenarios están en los libros de trabajo, Bank1_Scenarios, Bank2_Scenarios y Bank3_Scenarios. Abra los tres libros de trabajo.
Abra el libro de trabajo maestro, en el que tiene los valores iniciales.
Haga clic en DATOS> Análisis hipotético> Administrador de escenarios en el libro de trabajo maestro.
los Scenario Manager Aparece el cuadro de diálogo.
Como puede observar, no hay escenarios ya que aún no ha agregado ninguno. Hacer clicMerge.
Aparece el cuadro de diálogo Combinar escenarios.
Como puede ver, en Fusionar escenarios de, tiene dos cuadros:
- Book
- Sheet
Puede seleccionar una hoja de trabajo específica de un libro de trabajo específico que contiene los escenarios, que desea agregar a sus resultados. Haga clic en la flecha desplegable deBook para ver los libros de trabajo.
Note - Los libros de trabajo correspondientes deben estar abiertos para aparecer en esta lista.
Seleccione el libro - Bank1_Scenarios.
Se muestra la hoja Bank1. En la parte inferior del cuadro de diálogo, se muestra el número de escenarios encontrados en la hoja de origen. Haga clic en Aceptar.
Aparece el cuadro de diálogo Administrador de escenarios. Los dos escenarios que se fusionaron en el libro maestro se enumerarán en Escenarios.
Haga clic en el Mergebotón. losMerge Scenariosaparece el cuadro de diálogo. Ahora, seleccioneBank2_Scenarios de la lista desplegable en el cuadro Libro.
Se muestra la hoja Bank2. En la parte inferior del cuadro de diálogo, se muestra el número de escenarios encontrados en la hoja de origen. Haga clic en Aceptar.
los Scenario ManagerAparece el cuadro de diálogo. Los cuatro escenarios que se fusionaron en el libro maestro se enumeran en Escenarios.
Haga clic en el Mergebotón. losMerge Scenariosaparece el cuadro de diálogo. Ahora, seleccioneBank3_Scenarios de la lista desplegable en el cuadro Libro.
Se muestra la hoja Bank3. En la parte inferior del cuadro de diálogo, se mostrará el número de escenarios encontrados en la hoja de origen. Haga clic en Aceptar.
Aparece el cuadro de diálogo Administrador de escenarios. Los cinco escenarios que se fusionaron en el libro maestro se enumerarán en Escenarios.
Ahora, tiene todos los escenarios necesarios para producir el informe de resumen de escenarios.
Haga clic en el botón Resumen. losScenario Summary aparece el cuadro de diálogo.
- Seleccione Resumen de escenario.
- En el cuadro de celdas de resultado, escriba C6 y haga clic en Aceptar.
El informe de resumen del escenario aparece en una nueva hoja de trabajo en el libro maestro.
Visualización de escenarios
Suponga que está presentando sus escenarios y le gustaría cambiar dinámicamente de un escenario a otro y mostrar el conjunto de valores de entrada y valores de resultado del escenario correspondiente.
Haga clic en DATOS> Análisis Y si ...> Administrador de escenarios en el grupo Herramientas de datos. Aparece el cuadro de diálogo Administrador de escenarios. Aparece la lista de escenarios.
Seleccione el escenario que desea mostrar. Hacer clicShow.
Los valores de la hoja de trabajo se actualizan a los del escenario seleccionado. Los valores de resultado se vuelven a calcular.
Informe de tabla dinámica de escenario
También puede ver el informe de escenario en forma de tabla dinámica.
Haga clic en el botón Resumen en el Scenario ManagerCaja de diálogo. Aparece el cuadro de diálogo Resumen del escenario.
Selecciona el Scenario PivotTable report en Tipo de informe.
Escriba C6 en el Result cells caja.
El informe de tabla dinámica de escenario aparece en una nueva hoja de trabajo.
