Escenarios

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

La herramienta “Escenarios” permite cambiar las variables de una hoja de cálculo en un cuadro de diálogo manteniendo los valores originales en la misma. Para ello, ésta debe contener celdas de datos susceptibles de ser modificados y celdas de cálculo de contenido invariable cuyas fórmulas utilizan las celdas de datos como parámetros.

Creación de escenarios

Crear un escenario consiste en definir las celdas de datos variables para habilitar un cuadro de diálogo en el que poder insertar valores nuevos sin modificar la hoja de cálculo. La ventaja de esta herramienta es poder guardar los escenarios creados y compararlos para su análisis.

Además, en esta unidad aprenderemos también a nombrar celdas, que veremos será de gran utilidad para el manejo de datos en libros extensos.

Supongamos el siguiente caso:

Una empresa financiera que se dedica al renting analiza la posibilidad de comprar y alquilar un autobús. El autobús tiene un coste de 180.000 €, un plazo de amortización de 8 años y un valor de recate de 2.500 €.

El contrato de alquiler tendría una duración de 8 años y contemplaría una renta constante anual de 29.500 € pospagable.

Vamos a calcular el valor actual del contrato de alquiler si el tipo de interés anual aplicado es del 5,50%.

Lo primero será diseñar una hoja de cálculo que nos permita calcular el valor actual de este contrato cambiando cualquier dato de partida y sin tener que modificar la fórmula. Además vamos a poder contrastar los distintos resultados en un resumen de las distintas alternativas.

Para ello hay que separar las celdas de datos de las celdas de cálculo.

Los datos de la renta constante en el caso de cobro anual son:

  • Importe: 29.500 €.
  • Perioricidad: 1 (es decir, un pago al año)
  • Pos/prepagable (0/1): 0 (es decir, pospagable)
  • Duración en años: 8.
  • Valor de rescate: 2.500 €.
  • Tipo de interés anual aplicado: 5,50%.

Para ello hemos creado la siguiente plantilla en la que hemos rellenado en el rango B3:B8 los valores dados en el enunciado, mientras que en la celda E3 introducimos la fórmula de cálculo del valor actual. Ahora bien, en vez de incluir en los campos los datos, vamos a insertar las celdas que contienen estos datos:

=VA(B8/B4; B6*B4; B3; B7; B5)

creacion-escenarios
Hemos utilizado la función VA que ya vimos en el módulo de fórmulas financieras.

La función “Valor actual (VA) tiene una sintaxis similar a la de la función “PAGO()”, compuesta por los siguientes argumentos:

VA(tasa, nper, pago, [vf], [tipo])

 

  • Tasa”: Es la tasa de interés por período. En el ejemplo es el tipo de interés nominal anual aplicado dividido entre el número de pagos al año (B8/B4).
  • Nper”: Es el número total de períodos de pago en una anualidad. En el ejemplo, si obtiene un préstamo a ocho años para comprar el vehículo y efectúa pagos anuales, el préstamo tendrá 8*1 períodos. La fórmula tendrá 8 (o vinculándolo, B6*B4) como argumento nper.
  • Pago”: Es el pago efectuado en cada período, que no puede variar durante la anualidad. En nuestro caso se trata del capital a desembolsar en cada cuota (B3).
  • Vf”: Es el valor futuro residual, de rescate o un saldo en efectivo que desea lograr después de efectuar el último pago. Si omite el argumento vf, se supone que el valor es 0 (por ejemplo, el valor futuro de un préstamo es 0). Como el autobús al final de los ocho años tendrá un valor de venta de 2.500 euros, pondremos esta cantidad (B7).
  • Tipo”: Es el número 0 o 1 e indica cuándo vencen los pagos, es decir prepagables o pospagables. En nuestro caso es una operación que cada cuota se devenga al final de la misma (pospagable, B5).

Al igual que en la función PAGO(), VA() nos devuelve el  valor en negativo, si quisiéramos ponerlo correctamente bastaría con añadir un signo (menos) delante de la fórmula.

El valor actual del contrato de alquiler en el caso de cobro anual es de 188.498,69€. Acabamos de crear un modelo para el cálculo del valor actual de una renta constante.

Ahora supongamos que la empresa financiera, antes de firmar el contrato, quiere calcular su valor actual en los siguientes casos y comparar los resultados para hallar las condiciones de cobro más rentables:

  • Si se pacta un alquiler semestral de 12.900 €.
  • En el caso de un alquiler trimestral de 7.100 €.
  • Finalmente si se opta por un alquiler mensual de 2.000 €.

Los cobros vencerían en los tres casos al final de cada período.

Los datos variables son el importe y la periodicidad de la renta: las celdas B3 y B4. El primer paso será nombrar estas celdas de forma que en cuadro de diálogo titulado “Valores del escenario” del menú Escenarios aparezcan los nombres y no las direcciones de las celdas. Esto es muy práctico cuando el número de celdas variables es elevado.

 

Definiendo nombres

Para asignar un nombre a dicha celda, nos situamos en la celda B3, utilizaremos, la pestaña “Fórmulas” y dentro del grupo de botones ”Asignar Nombre a un Rango” elegimos el primero correspondiente a ”Definir nombre”.

definir-nombre
Poner un nombre a una celda, elemento o rango puede sernos muy útil.

A continuación, se abre el cuadro de diálogo en el que definiremos el nombre del rango (o celda) así como su ámbito de aplicación:

nombre-nuevo
Estableceremos un nombre para el elemento, en este caso la celda “B3” de la pestaña “Escenarios”

Comprobamos que nos propone por nombre de la celda el título “Importe”, que figura en la celda A3. Aceptamos y repetimos la operación para la celda B4 referente a la “Periodicidad”.

 

Creación de los escenarios a comparar

El paso siguiente es crear 3 escenarios para las tres formas de cobro con sus importes respectivos. Para crear un escenario, abriremos el desplegable de “Análisis de hipótesis” que se encuentra en la pestaña “Datos”. Por último, elegiremos la opción de “Administrador de escenarios”.

administrador-escenarios

Aparece un cuadro de diálogo como muestra la imagen, llamado “Administrador de escenarios“, donde seleccionamos la opción “Agregar”.

agregar-escenario
Agregaremos un escenario al que tendremos que definir y dar un nombre

Cumplimentaremos la ventana titulada “Agregar escenario” como aparece en la imagen inferior y pulsaremos sobre el botón Aceptar:

celdas-escenarios
Es importante definir las celdas que van a verse alteradas entre los distintos escenarios

Aparecerá una ventana titulada “Valores del escenario” con los dos campos variables Importe y Periodicidad. Introduciremos los valores respectivos 12.900 y 2, correspondientes a la primera alternativa propuesta en el ejemplo de una cuota semestral de 12.900 euros. Una vez hecho, pulsaremos sobre Aceptar.

.

Primer escenario
Primer escenario

Volveremos a la ventana titulada “Administrador de escenarios”, donde seleccionamos Agregar y repetimos la operación para los dos escenarios siguientes, a los que asignamos los nombres de “Renta trimestral” y “Renta mensual”.

Para renta trimestral:

Segundo escenario
Segundo escenario

Y para el último escenario planteado correspondiente a una renta mensual:

Tercer escenario
Tercer escenario

Con lo que al finalmente contaremos con los tres escenarios correspondientes a las tres alternativas propuestas.

ventana-administrador-escenario
Nos aparecerán los tres escenarios creados en la ventana del Administrador.

 

… y por último comparamos los resultados

Una vez hayamos terminado y nos encontremos nuevamente en la ventana del “Administrador de escenarios”, elegimos la opción “Resumen”. En la ventana siguiente, titulada “Resumen del escenario”, comprobaremos si está señalado el campo Resumen y aparecerá E3 en el campo “Celdas resultantes“, tal y como se muestra en la imagen, haremos clic en Aceptar.

resumen-escenario

En este momento, Excel crea una nueva hoja de cálculo, llamada “Resumen de escenarios“, con la información de los tres escenarios creados y los valores iniciales de la hoja de cálculo para poder comparar y analizar los datos resultantes:

comparativa-escenarios
Tabla comparativa en la que podemos ver los resultados de los diferentes escenarios.

El valor actual de contrato es cada caso es de:

  • 798,68 € en el caso de cobro semestral.
  • 423,22 € en el caso de cobro trimestral.
  • 658,62 € en el caso de cobro mensual.

Por tanto, la mejor de estas tres alternativas corresponde al cobro trimestral, aunque de todos modos no es tan buena como la opción de partida de un cobro anual de 29.500 euros. Por tanto, desecharíamos estas tres últimas alternativas quedándonos con la que mayor valor actual nos ofrece, 188.498,69 euros en nuestro ejemplo.

En el caso de una renta constante positiva (cobro), la función “VA” que calcula su valor actual nos devuelve un importe negativo, ya que representa un pago o inversión (flujo negativo). Sin embargo, en los resultados citados hemos omitido este signo.

ejercicio-excel

Descargar archivo adjunto
Podemos descargar el archivo Excel que hemos utilizado en esta lección desde el siguiente enlace:
Etiquetas de lecciones: escenarios, Excel
Volver a: Excel para Contables y Controllers > Escenarios y análisis de sensibilidad