Análisis de sensibilidad dinámico con Excel

viernes, mayo 29, 2009

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


Análisis de sensibilidad Excel

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

Análisis de sensibilidad Excel

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

Análisis de sensibilidad Excel

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.

Análisis de sensibilidad Excel

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

Análisis de sensibilidad Excel

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

Análisis de sensibilidad Excel


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:

5 comments:

Colombiano 31 mayo, 2009 01:12  

Hola Jorge, creo que una opción más simple que la programación del evento es hacer la tabla de datos manualmente, es decir, seleccionar el rango C8:G12 y escribir la fórmula de B4 dejando fija la fila de la tasa de interés, fija la columna para el número de cuotas y fijo del monto, al terminar de escribir la fórmula se oprime Ctrl+Intro

Me parece que así es más fácil para las personas que no manejan VBA, también queda dinámico en función del número de cuotas y la tasa de interés anual, inclusive del monto de B2.

Cordialmente,


CESAR MERA

Anónimo,  10 agosto, 2009 04:26  

hola, estuve tratando de realizar el ejercicio, pero pareciera que existe una falla, xq los valores que tienen que cambiar dinamicamente, si cambian, pero se repiten los mismos valores en algunas celdas.

Podria usted ayudarme???

Jorge L. Dunkelman 12 agosto, 2009 18:53  

Tendrías que ser más explícito con el problema o enviarme el archivo por mail.

Anónimo,  25 septiembre, 2013 09:41  

Hola, sigo los pasos pero el resultado es que se repite el encabezado en todas las celdas. En el ejemplo 3.50 3.75 se repite en todas las celdas de la columna

Jorge Dunkelman 25 septiembre, 2013 18:01  

¿Incluyendo las fórmulas?

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP