ESCENARIOS EN EXCEL
Esta herramienta avanzada nos permitirá analizar las variaciones de determinadas variables dentro de un informe sobre unos resultados finales o intermedios.
Un escenario es un conjunto de valores que Excel guarda y sustituye en nuestro informe. Utilizaremos los escenarios para observar la variación en el resultado de nuestro modelo. Tenemos la posibilidad de crear y guardar diferentes grupos de valores, en distintos escenarios y, a continuación, pasar a cualquiera de estos nuevos escenarios para ver distintos resultados. En definitiva es el análisis normal de un modelo, financiero o no, con varios escenarios posibles.
Activaremos esta herramienta avanzada escenarios desde el menú Herramientas > Escenarios, activando así el administrador de escenarios, desde el cual crearemos todos nuestros escenarios. Lógicamente, el paso previo será disponer de un modelo en nuestra hoja de cálculo sobre el que analizar la variación de algunas celdas resultantes según la variabilidad de otras celdas vinculadas.
Propondremos el siguiente modelo presupuestario, sencillo, para explicar el uso de los escenarios.
En este ejemplo disponemos de una distribución de gastos calsificados como fijos y variables, así como unos ingresos que dependen del número de unidades vendidas y su precio unitario, todos ellos estimados para el ejercicio 2010. Seguramente cualquier controller financiero necesitara analizar en qué forma varían el total de gastos o ingresos, o simplemente el resultado final de acuerdo a la modificación de algunas de las premisas establecidas en este primer modelo.
En primer lugar activamos el Administrador de Escenarios y creamos un Escenario que llamaremos normal, y que resume la situación tal cual la hemos planteado, es decir, consideramos los valores asignados en nuestro informe como una Situación inicial.
Es muy importante tener muy claro desde un principio cuáles serán las variables a estudiar que en uno u otro escenarios de los que en un futuro se vayan a generar; ya que es muy útil para realizar las comparaciones entre los diferentes escenarios.
Desde el administrador podremos agregar todos los Escenarios que necesitemos (podremos ejecutar este administrador de manera independiente en cada Hoja de cálculo de nuestro libro).
Los pasos son dar un nombre al Escenario. Identificar las celdas cambiantes, es decir, cuáles son las variables de estudio. Detallar, si es necesario, una descripción del escenario creado. Y por último asignar valores a esas variables.
y desde este cuadro diálogo cambiaremos los valores de las variables a analizar.
Del mismo modo agregaremos a nuestro Administrador tantos Escenarios como sean necesarios. Para nuestro ejemplo crearemos tres escenarios, un Escenario Normal, un Escenario Optimista y un Escenario Pesimista; en todos ellos analizaremos cómo cambian nuestros resultados en función a la variación de los Costes Fijos del 3T/2010 y del precio unitario de cada unidad vendida del mismo trimestre, ya que entendemos será un trimestre complicado en cuanto a costes estructurales y sabemos que de alguna manera afectará al precio unitario final de cada unidad vendida.
Una vez generados nuestros escenarios podremos visualizar sobre nuestro modelo la variabilidad definida.
En el fichero adjunto podemos ver los escenarios generados.
Por último mencionar la posibilidad de ver en una hoja resumen todos los escenarios con sus distintos valores por cada variable definida, y cómo afecta a las celdas de resultado seleccionadas. Hemos aprovechado la funcionalidad de Asignar nombres a rangos para dar nombre a las celdas cambiantes (variables) y las celdas de resultado, con el fin de identificar en esta hoja resumen qué corresponde a que.
Existen dos tipos de informes, sin embargo, el más clarificador de las situaciones, a mi modo de ver, es el tipo Resumen, en vez del tipo Tabla dinámica.
Un escenario es un conjunto de valores que Excel guarda y sustituye en nuestro informe. Utilizaremos los escenarios para observar la variación en el resultado de nuestro modelo. Tenemos la posibilidad de crear y guardar diferentes grupos de valores, en distintos escenarios y, a continuación, pasar a cualquiera de estos nuevos escenarios para ver distintos resultados. En definitiva es el análisis normal de un modelo, financiero o no, con varios escenarios posibles.
Activaremos esta herramienta avanzada escenarios desde el menú Herramientas > Escenarios, activando así el administrador de escenarios, desde el cual crearemos todos nuestros escenarios. Lógicamente, el paso previo será disponer de un modelo en nuestra hoja de cálculo sobre el que analizar la variación de algunas celdas resultantes según la variabilidad de otras celdas vinculadas.
Propondremos el siguiente modelo presupuestario, sencillo, para explicar el uso de los escenarios.
En este ejemplo disponemos de una distribución de gastos calsificados como fijos y variables, así como unos ingresos que dependen del número de unidades vendidas y su precio unitario, todos ellos estimados para el ejercicio 2010. Seguramente cualquier controller financiero necesitara analizar en qué forma varían el total de gastos o ingresos, o simplemente el resultado final de acuerdo a la modificación de algunas de las premisas establecidas en este primer modelo.
En primer lugar activamos el Administrador de Escenarios y creamos un Escenario que llamaremos normal, y que resume la situación tal cual la hemos planteado, es decir, consideramos los valores asignados en nuestro informe como una Situación inicial.
Es muy importante tener muy claro desde un principio cuáles serán las variables a estudiar que en uno u otro escenarios de los que en un futuro se vayan a generar; ya que es muy útil para realizar las comparaciones entre los diferentes escenarios.
Desde el administrador podremos agregar todos los Escenarios que necesitemos (podremos ejecutar este administrador de manera independiente en cada Hoja de cálculo de nuestro libro).
Los pasos son dar un nombre al Escenario. Identificar las celdas cambiantes, es decir, cuáles son las variables de estudio. Detallar, si es necesario, una descripción del escenario creado. Y por último asignar valores a esas variables.
y desde este cuadro diálogo cambiaremos los valores de las variables a analizar.
Del mismo modo agregaremos a nuestro Administrador tantos Escenarios como sean necesarios. Para nuestro ejemplo crearemos tres escenarios, un Escenario Normal, un Escenario Optimista y un Escenario Pesimista; en todos ellos analizaremos cómo cambian nuestros resultados en función a la variación de los Costes Fijos del 3T/2010 y del precio unitario de cada unidad vendida del mismo trimestre, ya que entendemos será un trimestre complicado en cuanto a costes estructurales y sabemos que de alguna manera afectará al precio unitario final de cada unidad vendida.
Una vez generados nuestros escenarios podremos visualizar sobre nuestro modelo la variabilidad definida.
En el fichero adjunto podemos ver los escenarios generados.
Existen dos tipos de informes, sin embargo, el más clarificador de las situaciones, a mi modo de ver, es el tipo Resumen, en vez del tipo Tabla dinámica.
El Administrador de escenarios en Excel
Ahora me interesa saber cómo cambia la mensualidad si cambio el plazo de pago. Para iniciar con la creación de escenarios debo pulsar el botón Análisis Y si que se encuentra en la ficha Datos y dentro de las opciones mostradas seleccionar Administrador de escenarios.
Al seleccionar esta opción se mostrará el cuadro de diálogo Administrador de escenarios y lo primero que debemos hacer es pulsar el botón Agregar para mostrar el cuadro de diálogo Agregar escenario.
En este cuadro de diálogo comenzaré por asignar un nombre a mi escenario, que en este caso será 12 Meses. En el cuadro de texto Celdas cambiantes debo seleccionar aquellas celdas que afectan el resultado de la fórmula PAGO, que es el rango $B$1:$B:$3. Al oprimir el botón Aceptar se mostrará un nuevo cuadro de diálogo llamado Valores del escenario que me permitirá ingresar los valores específicos del escenario recién creado. Es posible cambiar todos los valores, pero para este ejemplo solamente modificaré el plazo que tendrá el valor 12:
Ya que voy a agregar escenarios adicionales oprimiré el botón Agregar y Excel mostrará de nueva cuenta el cuadro de diálogo Agregar escenario y volveré a repetir estos mismos pasos para crear nuevos escenarios para los plazos de 18, 36 y 48 meses. Una vez que he terminado de crear el último escenario debes pulsar el botón Aceptar para regresar al Administrador de escenariosdonde podrás ver una lista de todos los escenarios creados:
Probar los escenarios creados
Ya con los escenarios creados será suficiente seleccionar alguno de ellos y oprimir el botón Mostrar para aplicar los valores del escenario al resultado de la fórmula PAGO. Observa cómo va cambiando el resultado de la celda B4 de acuerdo al escenario elegido:
.jpg)
.jpg)
.jpg)
.jpg)
.jpg)
.jpg)
.jpg)
.jpg)
.jpg)
.jpg)
.jpg)
.jpg)