Análisis de sensibilidad dinámico con Excel

Hace dos años atrás comentaba el uso de la función Tabla para hacer análisis de sensibilidad. Esta herramienta se encuentra en el menú Datos en Excel 97-2003 o en la pestaña Datos—Análisis Y si en Excel 2007

Por ejemplo, este modelo analiza la sensibilidad del pago mensual por un préstamo en función de la tasa de interés anual y el número de cuotas



Podemos apreciar los valores de los parámetro de la tabla (la tasa de interés anual y el número de cuotas) se “distribuyen” hacia arriba y hacia abajo en relación al valor central. La tasa de interés crece o decrece en “saltos” de 0.25% y el número de cuotas en escalones de 5 años.

Estos valores son constantes y es evidente que lo ideal sería que nuestro modelo fuera dinámico. Si cambiamos la tasa de interés en la tabla superior, queremos que los parámetros de la tabla de sensibilidad se ajusten automáticamente. Lo mismo con el número de cuotas.

Parece bastante evidente que todo lo que tenemos que hacer determinas los valores de los parámetros con una fórmula y ligar el parámetro “central” a la celda correspondiente en la tabla superior. El problema es que TABLA no se comporta como hubiéramos supuesto. Veamos que pasa cuando determinamos los valores de los parámetros con fórmulas

Por ahora hemos dejado los valores de las celdas E7 y B10 constantes. En C7:D7 hemos puesto la fórmula “=E7-0.25%”, en F7:G7 “=E7+0.25%”. Lo mismo con el número de cuotas; en B11 ponemos “=B10+5”. Como podemos ver, los resultados se mantienen.

Ahora queremos ligar E7 a B2 y B10 a B3 para que cuando cambiemos alguno de estos valores, la tabla de sensibilidad se adapte automáticamente.

El resultado es desastroso!

La solución es programar un evento para cambiar los datos sin usar referencias a otras celdas en la hoja.

El primer paso es definir nombres para las celdas B2, B3, E7 y B10. Esto nos permitirá simplificar el código del evento y hacerlo más claro. La forma más práctica de definir los nombres es usando el cuadro de nombres

Los nombres que hemos definido son ("variable" es el nombre de la hoja que contiene la tabla) :

cuotas=variable!$B$3

paramCuotas=variable!$B$10

paramInteres=variable!$E$7

tasa_interes=variable!$B$2

Abrimos el editor de Vba usando la opción Ver Código de la hoja

En el módulo de la hoja ponemos este código


Private Sub Worksheet_Change(ByVal Target As Range)

If Union(Target, Range("tasa_interes")).Address = Range("tasa_interes").Address _
Or Union(Target, Range("cuotas")).Address = Range("cuotas").Address Then
Range("paramInteres") = Range("tasa_interes")
Range("paramCuotas") = Range("cuotas")
End If

End Sub



Como estamos usando el evento Worksheet_Change, cada vez que cambie el valor de la celda B2 o B3, la tabla de sensibilidad es vuelta a calcular. Usamos UNION para evitar que el cáclulo se efectúe sólo si el cambio se produjo en las celdas B2 o B3.

Technorati Tags: MS Excel