Cómo Construir Escenarios Desplegables en Su Modelo Financiero

  1. Software
  2. Oficina de Microsoft
  3. Excel
  4. Cómo Construir Escenarios Desplegables en Su Modelo Financiero

Libro Relacionado

Modelado Financiero en Excel para Dummies

Por Danielle Stein Fairhurst

El método más utilizado para construir escenarios es utilizar una combinación de fórmulas y cuadros desplegables. En el modelo financiero, se crea una tabla de escenarios posibles y sus entradas y se enlazan los nombres de los escenarios a una casilla desplegable de celda de entrada. Las entradas del modelo están vinculadas a la tabla de escenarios. Si el modelo ha sido construido apropiadamente con todas las entradas fluyendo a través de las salidas, entonces 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 una serie de fines diferentes en la modelización financiera, incluido el análisis de escenarios.

Uso de validaciones de datos para modelar escenarios de rentabilidad

Descargar archivo 0801.xlsx. Ábrelo y selecciona la pestaña 8-1-start.

La forma en que esto ha sido modelado, las entradas están alineadas en la columna B. Usted podría realizar el análisis de sensibilidad simplemente cambiando una de las entradas – por ejemplo, cambiar los clientes por operador de llamada en la celda B3 de 40 a 45, y verá que todos los números dependientes cambian. Esto sería un análisis de sensibilidad, porque estás cambiando sólo una variable. En su lugar, va a cambiar varias variables a la vez en este ejercicio completo de análisis de escenarios, por lo que tendrá que hacer algo más que ajustar unos pocos números manualmente.

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

  1. 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 maneja el modelo y permanecerán así. Sin embargo, deben convertirse en fórmulas que cambien dependiendo del cuadro desplegable que se cree.
  2. Puede hacerlo resaltando B3:B8, pulsando Ctrl+C, seleccionando las celdas C3:E3 y pulsando Intro. Estos importes serán los mismos para cada escenario hasta que los modifique.
  3. En la fila 2 escriba los títulos Mejor Caso, Caso Base y Peor Caso, configurando el modelo para el análisis de escenarios, tenga en cuenta que las fórmulas todavía se enlazan con las entradas en la columna B, como puede ver seleccionando la celda C12 y presionando la tecla de acceso directo F2.
  4. Puedes poner lo que creas que es probable, pero para que los números coincidan con los de este ejemplo, introduce los valores. Ignore la columna B por ahora. entradas para el análisis de escenarios. ahora necesita añadir el cuadro desplegable en la parte superior, que va a dirigir sus escenarios. En realidad no importa dónde pones exactamente el cuadro desplegable, pero debería estar en una ubicación que sea fácil de encontrar, normalmente en la parte superior de la página.
  5. En la celda E1, introduzca el título Escenario.
  6. La manera más fácil de hacer esto es seguir estos pasos:Haga clic en una de las celdas que ya están formateadas como una entrada, como la celda E3.Presione el icono Copiar formato en la sección Portapapeles en el lado izquierdo de la pestaña Inicio. Seleccione la celda F1 para pegar el formato. Después de seleccionar la celda, el pincel desaparecerá del cursor. Si desea que el Pincel de Copiar Formato se vuelva «pegajoso» y se aplique a varias celdas, haga doble clic en el icono cuando lo seleccione en la ficha Inicio.
  7. Ahora, en la celda F1, seleccione Validación de datos de la sección Herramientas de datos de la pestaña Datos.
  8. En la ficha Configuración, cambie el menú desplegable Permitir a lista, utilice el ratón para seleccionar el rango =$C$2:$E$2 y haga clic en Aceptar.Creación de los escenarios desplegables de validación de datos.
  9. Haga clic en el cuadro desplegable, que ahora aparece junto a la celda F1, y seleccione uno de los escenarios (por ejemplo, Caso Base).

Aplicación de fórmulas a escenarios

Las celdas de la columna B siguen impulsando el modelo y deben ser reemplazadas 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. Siga estos pasos:

  1. Seleccionar las celdas B3:B8, y seleccionar el Color de Relleno del grupo Fuente en la pestaña Inicio.
  2. Es muy importante distinguir entre fórmulas y celdas de entrada en un modelo. Debe dejar claro a cualquier usuario que abra el modelo que las celdas de este rango contienen fórmulas y que no deben ser sustituidas.

Ahora necesita 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; una HLOOKUP, una sentencia IF anidada, una IFS y una SUMIF harán el truco. Añada las fórmulas siguiendo estos pasos:

  1. Seleccionar la celda B3, y agregar una fórmula que cambiará el valor dependiendo de lo que está en la celda F1. Aquí está lo que será la fórmula bajo las diferentes opciones:=HLOOKUP($F$1,$C$2:$E$8,2,0)Nótese que con esta solución, se necesita cambiar el número de índice de la fila de 2 a 3 y así sucesivamente a medida que se copia la fórmula hacia abajo. En su lugar, podría utilizar una función ROW en el tercer campo como la siguiente: =HLOOKUP($F$1,$C$2:$E$8,ROW(A3)-1,0)=IF($F$1=$C$2,C3,IF($F$1=$D$2,D3,E3))=IFS($F$1=$C$2,C3,$F$1=$1=$D$1=$D$2,$F$1=$D$2,E3)=SU$D$2,D3Como 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 la fila en el HLOOKUP no es robusto, y añadir la fila puede ser confuso para un usuario. La sentencia nested IF 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. El SUMIF es bastante simple de construir y seguir, y es fácil de expandir si necesita añadir escenarios adicionales en el futuro, tenga en cuenta que IFS es una nueva función que sólo está disponible con Office 365 y Excel 2016 o posterior instalado. Si utiliza esta función y alguien abre este modelo en una versión anterior de Excel, puede ver la fórmula, pero no podrá editarla.
  2. Copiar la fórmula de la celda B3 en la columna de abajo, el análisis del escenario completado, usando una copia y pegado ordinario, perderá todo su formato. Es importante mantener el formato del modelo para que pueda ver de un vistazo qué entradas están en valores en dólares, porcentajes o números de clientes. Utilice Pegar Fórmulas para conservar el formato. Puede acceder a ella copiando la celda en el portapapeles, resaltando el rango de destino, haciendo clic con el botón derecho del ratón, y seleccionando el icono Pegar Fórmulas para pegar sólo fórmulas, y dejando el formato intacto. Es hora de probar la funcionalidad del escenario en el modelo.
  3. Haga clic en la celda F1, cambie el cuadro desplegable y observe cómo cambian las salidas del modelo mientras alterna entre los diferentes escenarios.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *