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...