Análisis de Tablas de Datos con MS Excel – Análisis de Sensibilidad

miércoles, mayo 03, 2006

Cuando hablo de análisis de sensibilidad me refiero a como influyen cambios en determinados datos en el resultado de algún modelo de cálculo construido en Excel.
Excel dispone de varias herramientas como el comando Buscar Objetivo (Goal Seek) y el Solver.
Una herramienta menos utilizada, según mi experiencia, es el comando Tabla del menú Datos. Esta herramienta puede ser muy útil y ahorrar mucho tiempo.
Expliquemos esto con un Tabla_Datos_01ejemplo del uso de tablas de datos en MS Excel. Supongamos que nuestro modelo mide la tasa de ganancia neta, dados el monto de las ventas sabiendo que los gastos variables representan el 35% de las ventas y los gastos fijos son 400 mil (qué moneda usar depende de ustedes, por supuesto).




Una tabla de análisis de sensibilidad nos muestra, por ejemplo, cuál será la tasa de ganancia neta de acuerdo a variaciones en el monto de las ventas y en el porcentaje de los gastos variables:



Excel nos permite construir esta tabla automáticamente usando el comando Tabla en el menú Datos



En nuestro ejemplo mostramos el caso de una Tabla de Datos de doble entrada (Two-Input), que enseguida explicaremos. Por supuesto existe también el caso de Tabla de Datos de entrada simple (One-Input Data Table).

Para generar nuestra Tabla de Análisis de Sensibilidad necesitamos un modelo de cálculo como en nuestro ejemplo.
Luego creamos una matriz para los resultados con el siguiente diseño:
1 – la fórmula que da el resultado aparece en el ángulo superior izquierdo de la matriz (o una referencia a la celda que la contiene, como en nuestro ejemplo)


2 – en la fila superior de la matriz anotamos los distintos valores para una de las variables de nuestro modelo (en nuestro ejemplo, el monto de las ventas);
3 – el la columna izquierda de la matriz anotamos los distintos valores de la segunda variable (en nuestro ejemplo, el porcentaje de los gastos variables respecto a las ventas);
4 – Seleccionamos toda la matriz (en nuestro ejemplo el rango B12:G17)

y activamos el menú Datos--->Tabla

5 – en la ventanilla "celda de entrada (fila)" señalamos la celda que contiene el valor de la variable correspondiente (en nuestro caso B4, el monto de las ventas)


6 - en la ventanilla "celda de entrada (columna)" señalamos la celda que contiene el valor de la variable correspondiente (en nuestro caso C6, el porcentaje de los gastos variables);
7 – apretamos "aceptar" y Excel calculará automáticamente los valores para cada una de las intersecciones en la matriz.
8 - La fórmula, o la referencia a ella, que aparece en la celda superior izquierda de la matriz puede causar confusión. Por lo tanto conviene ocultarla. Una de las formas de hacerlo es formarla el font con el mismo color del celda, de manera que se torne invisible.

Si usamos Tablas de Datos con una sola entrada, marcamos sólo la variable que corresponde a la fila (o columna).




Categorías: Manejo de Datos_

Technorati Tags: ,

35 comments:

Otto 29 noviembre, 2007 23:05  

Excelente ayuda. Gracias, ATT Otto M.

Lucía,  28 enero, 2009 22:43  

Querido Jorge:

Te cuento que me estoy matando el cerebro tratando de hacer un analisis de sensibilidad respecto al van, con variaciones respecto a los costos y los ingresos, sera que me ayudas pleaseeeeee

Jorge L. Dunkelman 29 enero, 2009 08:49  

Lucía

será un placer opder ayudarte, pero ¿cuál es la consulta? Ponte en contacto conmigo por mail.

Anónimo,  08 mayo, 2010 04:54  

Heyy mkkk me acabaste de salvar el parcial de mañana un abrazo y gracias por la Informacion me fuee muyy util

Anónimo,  07 diciembre, 2010 15:45  

me tira un error de referencia de dato de celda , alguine sabe que puede ser>?

Jorge L. Dunkelman 08 diciembre, 2010 11:23  

Estás usando una referencia a una celda que no existe, pore ejemplo a una cuaderno que no existe.

Anónimo,  07 abril, 2011 02:51  

Hola, oye una pregunta, el análisis de sensibilidad es solo para el área de la economía? O también lo puedo aplicar a otros campos como ingeniería
? Sale, gracias...

Jorge L. Dunkelman 07 abril, 2011 06:03  

Por supuesto, en toda área donde queremos ver como cambie un resultado cuando algunas de las variables cambian.

Anónimo,  08 abril, 2011 00:20  

Hola, qué tal! Me podrías recomendar un buen libro sobre análisis de sensibilidad? Te lo agradecería mucho...

Jorge L. Dunkelman 08 abril, 2011 07:03  

No conozco ningún libor sobre el tema que pueda recomendarte.

Anónimo,  08 abril, 2011 19:36  

Hola, oye tengo un problemita nosé si me puedas apoyar. Se trata sobre un sistema de tratamiento de aguas residuales. Estos sistemas ocupan una gran área de tratamiento y por lo tanto, un costo elevado. Necesito optimizar esta área en base a los parámetros de diseño del sistema. Es decir, determinar cual de estos parámetros de diseño influyen en forma mas directa sobre dicha área. No tengo mucha idea de como inicar, podría ser con un análisis de sensibilidad? Cualquier información sería útil. De antemano, gracias...

Jorge L. Dunkelman 09 abril, 2011 07:35  

La herramienta de Excel para tratar problemas de optimización es el Solver.

Jan 11 abril, 2011 20:00  

Hola Jorge, en primer lugar felicitaciones por el contenido y por tu feedback. Te cuento que me encuentro desarrollando una herramienta financiera para evaluar un proyecto que voy a presentar en mi tesis de grado y quiero incluir varios análisis de sensibilidad. Anteriormente he usado mucho las tablas de datos, tanto de una como de dos variables, sin embargo no se que sucede, que cuando corro la tabla de datos no cambian los valores, y cuando hago la prueba manual, sí lo hacen. ¿Te ha pasado algo parecido alguna vez?

Jan 11 abril, 2011 20:44  

Acabo de darme cuenta de algo muy extraño: cuando corro la tabla de datos efectivamente no pasa nada, me da el mismo resultado para todos los valores, sin embargo si le doy a "guardar como" y le cambio el nombre al documento, reacciona y sí realiza los cambios. Puede tratarse de un virus? (El archivo no tiene macros)

Jorge L. Dunkelman 11 abril, 2011 21:14  

Jan,

pareciera ser que la opción de cálculo esta puesta a "manual". En Excel Clásico (97-2003) fijate en Herramientas-Opciones-Calculo; en Excel 2007/10 en Opciones de Excel-Fórmulas-Opciones de Cálculo.

Jan 12 abril, 2011 04:54  

En efecto era eso, estaba en opción "automático excepto para tabla de datos", no estaba al tanto de esa opción. Sin embargo, a pesar de que se actualiza, sigue mostrando resultados iguales para valores distintos en una celda que al cambiarla manualmente sí cambia el resultado. Pero ya lo veré con mente más fresca el día de mañana. Muchísimas gracias por la rápida respuesta, quizás quiera ver la herramienta cuando esté terminada, le puede interesar.

Jorge L. Dunkelman 12 abril, 2011 05:49  

Puedes enviaarme el archivo a la dirección que aparece en el enlace Ayuda.

Jan 13 abril, 2011 02:57  

Con gusto, en seguida la termine lo haré. Por cierto, hay alguna manera de establecer condiciones para la tabla de datos? Es decir, que dependiendo del valor de otra celda, cambie la celda de entrada de la tabla de datos.

Jorge L. Dunkelman 13 abril, 2011 07:02  

No, la celda de entrada hay que introducirla manualmente.

Anónimo,  28 julio, 2011 08:10  

Felicitraciones por compartir tu conocimiento. Puedes decirme cual la formaula del modelo?

Jorge L. Dunkelman 28 julio, 2011 09:01  

Excel introduce las fórmulas automáticamente. Puedes verlas en la barra de las fórmulas seleccionando alguna de la celdas de la tabla.

Anónimo,  20 noviembre, 2011 15:44  

gracias por el material.Ahora, cual es la subestimación de la formula en cuanto a al porcentaje especultivo de la parte variable del caso descripto al inicio? Gracias

Jorge L. Dunkelman 20 noviembre, 2011 18:29  

¿Podrías volver a formular la consulta? Esta vez en castellano, por favor :)

GGJ,  22 febrero, 2012 17:27  

Tengo exactamente el mismo problema que JAN. ¿llegasteis a solucionarlo?

Gracias.

GGJ,  22 febrero, 2012 18:55  

Cuando corro la tabla de datos efectivamente no pasa nada, me da el mismo resultado para todos los valores.

Tengo un excel modelo, el cual la tabla funciona perfectamente, pero necesito modificar un Flujo de caja actualizado que es sobre el que quiero hacer el analisis de sensibilidad en funcion de una tasa de creciemiento y una wacc a la que se actualizan los flujos. Cuando modifico ese valor, los datos que la matiz me devuelve son siempre ese nuevo valor. Es decir, en lugar de devolvermelo en el punto central de la matiz, toda la madrid me devuelve ese valor.

Gracias por tu rapida atencion.

GGJ,  22 febrero, 2012 18:56  

Por cierto, tengo la opcion en automático. Gracias.

Jorge L. Dunkelman 22 febrero, 2012 20:19  

Te sugiero que me mandes el archivo al mail que aparece ne el enlace Ayuda.

Dnl,  17 marzo, 2012 19:24  

Hola, tengo días tratando de hacer un análisis de sensibilidad, pero por mas que trato no puedo, tengo el VAN y el TIR, pero no se como construir la tabla, para el análisis, es la primer vez que utilizo esta función de excel, y para ser sincero no la entiendo,

Jorge L. Dunkelman 19 marzo, 2012 19:45  

A veces es más sencillo construir una matriz /tabla con fórmulas que la funcionalidad "tabla". Sencillamente usas las misma funciones pero las variables que quieres analizar las pones en la fila superior e izquierda de la matriz.

Kunnington 20 abril, 2012 06:46  

Hola Jorge, felicitaciones por tu blog, es de muchisima utilidad.

Estoy viendo que a mi tambien me esta pasando el mismo problema que "GGJ" y que "JAN". Sigo cada uno de los pasos y los datos de la tabla me tira todos ceros.

La opcion de recalculo tambien esta en automatico. Pudieron finalmente como era la soluciion?

Gracias

Jorge L. Dunkelman 20 abril, 2012 21:26  

Kunnington,
como en los casos anteriores tendría que ver el archivo. Puedes enviarlo siguiendo als indicaciones que aparecen en el enlace Ayuda.

Juliana Rendon 26 abril, 2014 08:24  

Estimado Jorge, ante todo gracias por tan valiosa información, me has ayudado mucho con este Blog. Te cuento que presento el mismo problema de GGJ, toda la información de las celdas me proporcionan el mismo valor. Necesito una solución urgente, ya le he hecho todas las configuraciones posibles a mi Excel 2013 y nada ! :(

¿Me puedes ayudar?


Juliana Rendon

Jorge Dunkelman 27 abril, 2014 08:40  

Juliana, como en los casos anteriores te sugiero que me mandes el archivo (fijate ne el enlace Ayuda, en la parte superior de la plantilla).
Todo depende de cómo estén organizados los datos. Muchas veces tiene que ver con la confusión entre cuál es el dato de las filas y cual de las columnas.

Juliana Rendon 02 mayo, 2014 01:39  

Estimado Jorge, te cuento que logré solucionar el ejercicio dándole formato general a toda la tabla y realizando de manera correcta los cálculos mediante las fórmulas. Obtuve 9/10. De nuevo muchas gracias por tu ayuda mediante el Blog.

Juliana Rendon

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP