Análisis de sensibilidad

Por favor compra el curso antes de comenzar esta lección.

Análisis de sensibilidad

Vamos a realizar un análisis de sensibilidad de una fórmula en función de una o dos variables de la misma, es decir, cómo varía el resultado de una fórmula al modificar una o dos de las variables de que depende, y además esta variación se muestra en una tabla con todos los resultados.

Veamos su funcionamiento a través del ejemplo de la empresa de renting:

Sabiendo que el coste del autobús es de 180.000 € y la empresa decide cobrar por el alquiler una renta mensual de 2.400 €, vamos a calcular a partir de qué tipo de interés de mercado le interesa realizar esta operación financiera.

Para hallarlo, efectuaremos un análisis de sensibilidad del valor actual de contrato en función del tipo de interés anual aplicado, variando este último entre 5 por 100 y 7,25 por 100, con un incremento de 0,25 por 100.

Antes debemos incluir en la hoja de cálculo las condiciones de cobro mencionadas: a la celda B3 le damos el valor 2.400 € y, a la celda B4, el valor 12, que indica la periodicidad mensual.

tabla-valor-actual
Veremos cómo se modifican el valor actual conforme varía el tipo de interés de la operación.

A continuación, daremos los siguientes pasos:

  • En la celda A10 introducimos el título “Análisis de sensibilidad del valor actual de las rentas constantes en función del tipo e interés anual aplicado“.
  • En la celda A11 introducimos el título de la variable: “Tipo de interés“.
  • El rango B11:K11 recogerá los valores que irá tomando la variable. Para ello damos a la celda B11 el valor 5% y, mediante el comando de “Rellenar Series” que encontramos en la pestaña “Inicio” asignamos los valores restantes.
serie-datos
Crearemos una serie para establecer los distintos tipos de interés a aplicar.

 

Creando la serie de datos con los tipos de interés a comparar

Para ello completamos los campos del cuadro de diálogo “Serie” para que se incremente en un 0,25% hasta alcanzar el valor 7,25%.

serie-tipo-interés
Delimitaremos los distintos tipos de interés a comparar mediante la creación de una serie.

Podemos comprobar que el rango B11:K11 ha tomado los valores prefijados para el análisis, a los que aplicamos un formato de porcentaje con dos decimales.

La celda A12 deber recoger la fórmula objeto del análisis de sensibilidad. Para ello introducimos la fórmula = E3. Ahora, seleccionamos el rango A11:K12, abrimos la pestaña “Datos” para a continuación dirigirnos al conjunto de iconos del grupo “Herramientas de datos” y desplegar el botón “Análisis de Hipótesis” y seleccionar por último la opción “Tabla de datos”.

hipotesis-datos
Por supuesto, podríamos haber escrito los primeros tipos de interés y arrastrado valores, pero de ese modo no habríamos revisado la herramienta para crear series 🙂

En la nueva ventana seleccionaremos la celda B8 (correspondiente al tipo de interés) en el campo “Celda de entrada (fila)”. De momento dejaremos en blanco el campo de Celda de entrada correspondiente a columnas.

hipotesis-celda-entrada
En celda de entrada estableceremos el dato que irá variando, es decir, el tipo de interés. Como hemos colocado la serie de tipos de interés en una única fila, lo especificaremos en el campo correspondiente.

Observamos que en el rango B12:K12 aparecen los diferentes valores que va tomando la fórmula de cálculo de la actualización de la renta constante (incluyendo el valor de rescate) según el tipo de interés anual.

resultados-analisis-hipotesis
Valor actual para los distintos tipos de interés aplicados.

 

Análisis de sensibilidad en función de dos variables

A continuación, haremos un ejemplo de Análisis de sensibilidad en función de dos variables. Para analizar la sensibilidad del valor actual del contrato de alquiler en función del importe del alquiler mensual y del tipo de interés anual aplicado, procederemos como sigue:

  • En la celda A14 introducimos el título “Análisis de sensibilidad del valor actual de las rentas constantes en función de su importe y del tipo de interés anual aplicados“.
  • A continuación insertaremos en fila los datos de la variable “Tipo de interés” y en columna los datos de la variable “importe de la renta“.
  • Haremos variar el tipo de interés entre 5 por 100 y 7,25 por 100, con un incremento de 0,25 por 100, y el importe de la renta entre 2.300 € y 2.550 €, con un incremento de 25€.
  • En la celda B15 introducimos el primer valor que tomará el tipo de interés: 5%, y mediante el menú “Inicio”, pestaña “Rellenar” / “Series”, asignamos los valores restantes. Para ello completamos los campos del cuadro de diálogo Serie, tal como se ha hecho anteriormente:
serie-interes
Al igual que en el caso anterior, estableceremos el rango con los tipos de interés
  • En la celda A16 introducimos el primer valor que tomara el importe de la renta: 2.300€.
  • Abrimos el menú Series igual que hemos hecho con el tipo de interés y damos los siguientes valores a los campos del cuadro de diálogo:
serie-rentas
…y también crearemos la serie con el rango de los datos de rentas a comparar
  • En la celda A15 introducimos la fórmula: =E3.

Ahora seleccionamos el rango A15:K26, y abrimos la pestaña “Datos” para a continuación dirigirnos al conjunto de iconos del grupo “Herramientas de datos” y desplegar el botón “Análisis de Hipótesis” y seleccionar por último la opción “Tabla de datos”.

Introducimos la celda B8 (correspondiente al interés) en el campo de “Celda de entrada (fila)”  y en “Celda de entrada (columna)” introducimos la celda B3 (correspondiente al importe). Por último aceptamos.

resumen-final
Y aquí los resultados en la tabla de dos dimensiones.

Vemos que en el rango B16:K26 aparecen los distintos valores que va tomando la fórmula de cálculo de la actualización de la renta constante (incluyendo el valor de rescate) según varían el tipo de interés anual y el importe de la renta.

video-excel

 

Descargar archivo adjunto
Podemos descargar el archivo Excel que hemos utilizado en esta lección desde el siguiente enlace:

 

Etiquetas de lecciones: Excel, hipótesis, sensibilidad
Volver a: Excel para Contables y Controllers > Escenarios y análisis de sensibilidad