viernes, 8 de mayo de 2009

Análisis de Sensibilidad con dos Variables


Descargar el Archivo


La empresa Educando dedica su actividad a realizar cursos de formación para directivos. El Director de Marketing necesita realizar una cuenta de resultados previsional para un nuevo tipo de cursos cortos que desean desarrollar. Los datos de partida son los que se muestran en la imagen, y que se corresponden con la entrada de datos del modelo:


Quiere desarrollar una cuenta de resultados previsional con el siguiente detalle para analizar el margen y beneficio bruto que se podrían alcanzar:


La formulación de este modelo no es ningún problema ya que se trata de fórmulas muy sencillas que detallo en la siguiente imagen:


Y el resultado de aplicar dichas fórmulas será:


Hasta aquí ningún problema. Una vez desarrollada la cuenta de resultados inicial queremos realizar un análisis de sensibilidad para ver, por ejemplo, cómo afecta al Beneficio Bruto distintos números de alumnos y distintos precios de matrícula. Evidentemente podríamos resolver el análisis por la "vía dura", a saber, introduciendo distintos valores en la entrada de datos y anotando los resultados... ¡Por el amor de Dios no haga esto! (y si lo hacía por favor no lo haga más...).

Excel nos proporciona una herramienta denominada Tabla que resolverá por nosotros esta tediosa labor. Los pasos que debemos seguir son los siguientes:
1. Construimos la tabla con los valores que queremos analizar, como se muestra en la imagen:

Fíjese que hemos dispuesto en una columna (E) distintos números de alumnos (desde 10 hasta 30) y en una fila (3) distintos precios de matrícula (800€, 1.000€, 1.200€ y 1.400€). 
2. Nos situamos en la celda del vértice superior izquierdo de la tabla, es decir, en E3 y escribimos la fórmula: =B24
Esta celda (el vértice superior izquierdo de la tabla que generemos) se denomina celda de enlace y la dedicaremos siempre a indicar qué fórmula deseamos analizar. Es decir, como vamos a ver cómo afecta al BENEFICIO BRUTO distintos números de alumnos y precios de matrículas en esta celda escribiremos =B24 porque es dónde se encuentra (en nuestro modelo) la fórmula que queremos calcular (Beneficio Bruto).
3. Seleccionamos toda la tabla incluyendo la celda de enlace, es decir, E3:I24 (los rótulos no) y vamos al menú Datos/Tabla... Se nos abrirá la siguiente ventana:
4. En Celda de entrada (fila) debemos hacer mención a los datos que hemos dispuesto en nuestra tabla en una fila, es decir, el precio de la matrícula. En la entrada de datos de nuestro modelo el precio de la matrícula lo tenemos en B3. En Celda de entrada (fila) seleccionamos B3.
5. En Celda de entrada (columna) debemos hacer mención a los datos que hemos dispuesto en nuestra tabla en una columna, esto es, el número de alumnos. Tal dato lo tenemos en B4 por lo que en Celda de entrada (columna) escribimos B4:

6. Sólo nos queda pulsar Aceptar y Excel se encargará de rellenar la tabla con todas las combinaciones requeridas:


Esta herramienta nos permite además modificar los datos que queremos analizar. Pruebe a cambiar algunos (o todos) los números de alumnos y/o los precios de matrícula en la tabla y verá que automáticamente se recalcula el beneficio bruto resultante.

En próximas "entregas" verá que a este sencillo modelo le podemos sacar todavía muchísimo más partido...

12 comentarios:

  1. Oye me parece raro que nadie ha puesto ningun comentario para decir que es fenomenal!
    Muchisimas gracias por publicar este ayuda.
    Me ha salvado el pellejo cuando tenia 12 horas en preparar el P&G y Estudio de sensibilidad.

    Sin tu ayuda no lo hubiera conseguido!

    Sigue asi con las buenas ayudas!!

    Gracias
    AV

    ResponderEliminar
  2. Buenisimo... muchas gracias

    ResponderEliminar
  3. Me parece una información espectacular, mil gracias porque andaba loco buscando como se hacía el análisis de sensibilidad.

    Para los que usen excel 07 es exactamente lo mismo a diferencia que la herramienta tabla esta en la pestaña de datos / Analisis si / tabla de datos!!!

    Un saludo y ojala todo el mundo compartiera la buena información

    ResponderEliminar
  4. Muchas gracias a todos por vuestros comentarios tan positivos.

    ResponderEliminar
  5. Muy claro, gracias! =)

    ResponderEliminar
  6. Muy bueno el ejemplo! Gracias! Slds.

    ResponderEliminar
  7. Muchas gracias , el blog esta genial y este post merece 10 puntos :P

    ResponderEliminar
  8. Genial, muchas gracias,
    Ha sido muy preciso y concreto, la explicación es sencilla, sigue un hilo lógico y se entiende a la perfección!

    ResponderEliminar
  9. Hay un error, la esquina superior izquierda de la Tabla no es B3, sino F4. Aun así esta muy bien

    ResponderEliminar
  10. Gracias por la corrección. En concreto es en la celda E3 donde debemos escribir la fórmula. Ya lo he cambiado en el blog. Gracias de nuevo.

    ResponderEliminar
  11. Ojala alguien me pueda ayudar
    EL ejemplo es super bueno y muy claro aunque la tabla me sale en cero que hago o que me esta fallando
    Urgente

    ResponderEliminar