Cómo crear escenarios desplegables en su modelo financiero

El método más utilizado para crear escenarios es utilizar una combinación de fórmulas y cuadros desplegables. En el modelo financiero, crea una tabla de posibles escenarios y sus entradas y vincula los nombres de los escenarios a un cuadro desplegable de celda de entrada. Las entradas del modelo están vinculadas a la tabla de escenarios. Si el modelo se ha construido correctamente con todas las entradas fluyendo a través de las salidas, los resultados del modelo cambiarán a medida que el usuario seleccione diferentes opciones en el cuadro desplegable.

Los cuadros desplegables de validación de datos se utilizan para varios propósitos diferentes en el modelado financiero, incluido el análisis de escenarios.

Uso de validaciones de datos para modelar escenarios de rentabilidad

Descargar archivo 0801.xlsx . Ábralo y seleccione la pestaña etiquetada 8-1-start.

La forma en que se ha modelado esto, las entradas están alineadas en la columna B.Puede realizar un análisis de sensibilidad simplemente cambiando una de las entradas; por ejemplo, cambie los clientes por operador de llamada en la celda B3 de 40 a 45, y obtendrá ver cómo cambian todos los números dependientes. Este sería un análisis de sensibilidad, porque solo está cambiando una variable. En su lugar, cambiará varias variables a la vez en este ejercicio completo de análisis de escenarios, por lo que deberá hacer más que modificar algunos números manualmente.

Para realizar un análisis de escenario utilizando los cuadros desplegables de validación de datos, siga estos pasos:

Tome el modelo descargado y corte y pegue las descripciones de la columna C a la columna F. Puede hacer esto resaltando las celdas C6: C8, presionando Ctrl + X, seleccionando la celda F6 y presionando Enter.

Las entradas en las celdas B3 a B8 son el rango activo que impulsa el modelo y lo seguirá siendo. Sin embargo, deben convertirse en fórmulas que cambien según el cuadro desplegable que creará.

Copie el rango de la columna B en las columnas C, D y E.

Puede hacer esto resaltando B3: B8, presionando Ctrl + C, seleccionando las celdas C3: E3 y presionando Enter. Estas cantidades serán las mismas para cada escenario hasta que las cambie.

En la fila 2 Introduzca los títulos mejor caja , caso base , y el peor de los casos.

Configuración del modelo para el análisis de escenarios.

Tenga en cuenta que las fórmulas aún se vinculan a las entradas en la columna B, como puede ver al seleccionar la celda C12 y presionar la tecla de método abreviado F2.

Edite las entradas debajo de cada escenario.

Puede poner lo que crea probable, pero para hacer coincidir los números con los de este ejemplo, introduzca los valores. Ignore la columna B por ahora.

Entradas para el análisis de escenarios.

Ahora debe agregar el cuadro desplegable en la parte superior, que impulsará sus escenarios. Realmente no importa dónde coloque exactamente el cuadro desplegable, pero debe estar en una ubicación que sea fácil de encontrar, generalmente en la parte superior de la página.

En la celda E1, ingrese el título Escenario .

Seleccione la celda F1 y cambie el formato de entrada para que el usuario pueda ver que esta celda es editable.

La forma más sencilla de hacerlo es seguir estos pasos:

Haga clic en una de las celdas que ya están formateadas como entrada, como la celda E3.

Presione el icono Copiar formato en la sección Portapapeles en el lado izquierdo de la pestaña Inicio. Tu cursor se convertirá en un pincel.

Seleccione la celda F1 para pegar el formato.

Format Painter es normalmente de un solo uso. Una vez que haya seleccionado la celda, el pincel desaparecerá del cursor. Si desea que Format Painter se vuelva "pegajoso" y se aplique a varias celdas, haga doble clic en el icono cuando lo seleccione en la pestaña Inicio.

Ahora, en la celda F1, seleccione Validación de datos en la sección Herramientas de datos de la pestaña Datos.

Aparece el cuadro de diálogo Validación de datos.

En la pestaña Configuración, cambie el menú desplegable Permitir a Lista, use el mouse para seleccionar el rango = $ C $ 2: $ E $ 2 y haga clic en Aceptar.

Creación de escenarios desplegables de validación de datos.

Haga clic en el cuadro desplegable, que ahora aparece junto a la celda F1, y seleccione uno de los escenarios (por ejemplo, Caso base).

Aplicar fórmulas a escenarios

Las celdas de la columna B todavía controlan el modelo y deben reemplazarse por fórmulas. Sin embargo, antes de agregar las fórmulas, debe cambiar el formato de las celdas en el rango para mostrar que contienen fórmulas, en lugar de números codificados. Sigue estos pasos:

Seleccione las celdas B3: B8 y seleccione el Color de relleno del grupo Fuente en la pestaña Inicio.

Cambie el color de relleno a un fondo blanco.

Es muy importante distinguir entre fórmulas y celdas de entrada en un modelo. Debe dejar en claro a cualquier usuario que abra el modelo que las celdas de este rango contienen fórmulas y no deben anularse.

Ahora debe reemplazar los valores codificados en la columna B con fórmulas que cambiarán a medida que cambie el cuadro desplegable. Puede hacer esto usando varias funciones diferentes; un HLOOKUP, una instrucción IF anidada, un IFS y un SUMIF harán el truco. Agregue las fórmulas siguiendo estos pasos:

Seleccione la celda B3 y agregue una fórmula que cambiará el valor según lo que haya en la celda F1.

A continuación, se muestra cuál será la fórmula en las diferentes opciones:

• = HLOOKUP ($ F $ 1, $ C $ 2: $ E $ 8,2,0)

  Tenga en cuenta que con esta solución, debe cambiar el número de índice de fila de 2 a 3 y así sucesivamente mientras copia la fórmula. En su lugar, podría usar una función FILA en el tercer campo como esta: = HLOOKUP ($ F $ 1, $ C $ 2: $ E $ 8, FILA (A3) -1,0)

• = SI ($ F $ 1 = $ C $ 2, C3, SI ($ F $ 1 = $ D $ 2, D3, E3))
• = SI ($ F $ 1 = $ C $ 2, C3, $ F $ 1 = $ D $ 2, D3, $ F $ 1 = $ E $ 2, E3)
• = SUMIF ($ C $ 2: $ E $ 2, $ F $ 1, C3: E3)

  Como siempre, hay varias opciones diferentes para elegir y la mejor solución es la más simple y fácil de entender. Cualquiera de estas funciones producirá exactamente el mismo resultado, pero tener que cambiar el número de índice de fila en HLOOKUP no es robusto y agregar la FILA puede ser confuso para un usuario. La instrucción IF anidada es difícil de construir y seguir, y aunque la nueva función IFS está diseñada para simplificar una función IF anidada, sigue siendo bastante difícil de manejar. SUMIF es bastante simple de construir y seguir, y es fácil de expandir si necesita agregar escenarios adicionales en el futuro.

  Tenga en cuenta que IFS es una función nueva que solo está disponible con Office 365 y Excel 2016 o posterior instalado. Si usa esta función y alguien abre este modelo en una versión anterior de Excel, puede ver la fórmula, pero no podrá editarla.

Copie la fórmula de la celda B3 en la columna.

El análisis del escenario completo.

Al usar una copia y pegar normales, perderá todo su formato. Es importante conservar el formato del modelo para que pueda ver de un vistazo qué entradas están en valores monetarios, porcentajes o números de clientes. Utilice Pegar fórmulas para conservar el formato. Puede acceder a él copiando la celda en el portapapeles, resaltando el rango de destino, haciendo clic con el botón derecho y seleccionando el icono Pegar fórmulas para pegar fórmulas únicamente y dejar el formato intacto.

¡Ahora viene la parte divertida! Es hora de probar la funcionalidad del escenario en el modelo.

Haga clic en la celda F1, cambie el cuadro desplegable y observe cómo cambian las salidas del modelo a medida que alterna entre los diferentes escenarios.