viernes, 21 de agosto de 2009

Trabajar con Escenarios en Excel.

Nos introduciremos hoy en una herramienta de Excel no excesivamente conocida, los ESCENARIOS.
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áologo 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.

Escenarios
Escenarios.xls
Hosted by eSnips


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.


haz click en la imagen


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.

15 comentarios:

  1. muy bien jejejejeje
    viva el junior

    ResponderEliminar
  2. No te comprendo viejo, y el file.xlsx no se descarga para mirar como mismo le haz hecho.

    ResponderEliminar
  3. Hola,
    he comprobado el sistema de descarga y, al menos, a mi me funciona...
    Respecto a la explicación, ¿que es exactamente lo que no comprendes?; si eres más concreto podría intentar explicarme mejor.
    Un cordial saludo

    ResponderEliminar
  4. Gracias por la aportación! Eres un crack!

    ResponderEliminar
  5. Gracias por la explicación, pero como puedo realizar un resumen para que se visualicen los diferentes escenarios, como tu lo muestras

    ResponderEliminar
  6. Hola,
    para realizar y obtener un Resumen tal cual aparece en este post, deberás navegar hasta el Administrador de Escenarios, y presionar la opción de Resumen, seleccionando la opción de Tipo de informe: Resumen.
    La otra opción de Tipo de informe es Tabla dinámica.
    Slds

    ResponderEliminar
  7. Hola,
    Necesito ayuda, cuando realizo un informe tipo resumen, no sé en que momento hay que cambiar el nombre de las celdas ya que siempre me salen las referencias. Por otra parte, no salen los datos de las celdas fijas.
    Gracias

    ResponderEliminar
    Respuestas
    1. Se cambian los nombres de las celdas antes de generar el informe tipo resumen

      Eliminar
  8. Hola,
    para que aparezcan los 'nombres' asignados en lugar de las referencias, basta con nombrarlas en cualquier momento anterior a la ejecución del Resumen.
    Es cierto, en los resúmenes de los escenarios sólo se muestran las celdas cambiantes, es decir, las que hemos seleccionado para análisis.
    Si quieres que te muestren todas las celdas 8en muchos casos será una locura) deberías marcar toda tu plantilla como cambiante, pero sólo modificar aquellas que realmente aporten algo al estudio del escenario.
    Slds

    ResponderEliminar
  9. Muchas gracias.

    ResponderEliminar
  10. Muchas gracias no tenía ni idea de que eran los escenarios y lo he entendido a la perfección mejor que en el curso que estoy dando..

    ResponderEliminar
  11. Gracias por el ejemplo...es un problema para mi no tener acceso permanente a Internet para utilizar mucho mas este sitio...
    Saludos
    Leo

    ResponderEliminar
    Respuestas
    1. Gracias a todos!!!
      una satisfacción personal que mis aportes sirvan a los demás
      Slds cordiales

      Eliminar
  12. hola todos buenas tardes, JB desde Perú.
    Favor si alguien sabe como hacer para que los valores de las celdas cambiantes en el administrador de escenarios, en vez de tener que introducirlos uno por unop manualmente, se puedan tomar de una tabla, porque estoy trabajando con 30 escenarios y 20 variables y es muy tedioso.
    muy agradecido

    ResponderEliminar
    Respuestas
    1. Hola JB,
      sería posible implantando un proceso o macro... el problema es que los valores de los escenarios no admite dependencia sobre referencias y hay que meterlos 'manualmente'.
      Al hacerlo con macros esas 20 variables si se podrían recuperar de ciertos rangos de celdas controlados por ti, para cada uno de los 30 escenarios.
      Saludos

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.