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

Uso de la función SUBTOTALES de MS Excel

Ya hemos hablado sobre el uso del comando Subtotales del menú Datos. Por lo general, esta es la forma más sencilla y segura de utilizar la función SUBTOTALES para consolidar datos.
Pero la función tiene algunas características que la tornan muy útil, en especial en combinación con el comando AUTOFILTRO.
La sintaxis de la función SUBTOTALES es la siguiente:

SUBTOTALES(núm_función;ref1,ref2,...)

Donde núm_función es el argumento que determina que tipo de operación efectuará SUBTOTALES

1 - PROMEDIO
2 - CONTAR
3 - CONTARA
4 - MAX
5 - MIN
6 - PRODUCTO
7 - DESVEST
8 - DESVESTP
9 - SUMA
10 - VAR
11 - VARP

Una característica importante de SUBTOTALES es que la función pasa por alto las filas ocultas. Esto es importante cuando sólo desea obtener el subtotal de los datos visibles que resulta de una lista filtrada.

En todos los ejemplos que aparecen en el archivo con ejemplos de la función SUBTOTALES, se puede apreciar la importancia de esta característica. Por ejemplo, si aplicamos AUTOFILTRO a una lista de ventas por productos, podemos obtener un total de cada producto al ocultar los restantes con el filtro usando la función SUMA (9). Lo mismo se puede hacer con PROMEDIO (1) y demás funciones.
Por ejemplo, si tenemos esta lista de edades de empleados por departamentos



Aplicando PROMEDIO (1), al auto filtrar la lista podemos ver la edad promedio de cada departamento:



Un uso interesante es con CONTARA (3). En el archivo podemos ver un ejemplo donde cada vez que filtramos la lista, el número de orden se mantiene correlativo. La función utilizada en la celda A7 es =SUBTOTALES(3,$C$7:C7) que luego copiamos al resto del rango.
Prestar atención a la notación del rango en el segundo argumento de la función: el primer miembro del rango ($C$7) es una referencia absoluta, mientras que el segundo es una referencia relativa que cambia al copiar la función a lo largo del rango.

Categorías: Funciones&Formulas_

Technorati Tags: SUBTOTALES, SUBTOTALS

Usar la función BUSCARV(VLOOKUP) de MS Excel con varios criterios.

Por lo general usamos la función BUSCARV(VLOOKUP) de MS Excel con un criterio de búsqueda, es decir, la búsqueda se efectúa a lo largo de una única columna.
Pero existen situaciones en las cuales debemos efectuar la búsqueda en más de una columna.
En este ejemplo del uso de BUSCARV con más de una columna de búsqueda (enlace para descargar el archivo), tenemos una tabla donde vemos las ventas de ciertos agentes por producto (una columna para cada producto):




En la celda B18 tenemos una fórmula que nos permite extraer las ventas de cada agente por producto
=BUSCARV(B16,$A$6:$D$13,COINCIDIR(B17,$B$5:$D$5,0)+1,0)



La fórmula combina las funciones BUSCARV (VLOOKUP) y COINCIDIR (MATCH). La función COINCIDIR le pasa a BUSCARV el valor del argumento "indicador de columnas", de acuerdo al valor presente en la celda B17 (aquí hemos utilizado validación de datos para generar una lista). La fórmula funciona de la siguiente manera:









Categorías: Funciones&Formulas_, LOOKUPS_

Technorati Tags: BUSCARV, VLOOKUP