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

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 ejemplo 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: Tabla de Datos, Analisis de Sensibilidad