H E R R A M I E N T A S D E A N Á L I S I S D E D A T O S

1

HERRAMIENTAS DE ANÁLISIS DE DATOS

Una situación que se nos plantea algunas veces es la de resolver un problema hacia atrás, esto es, encontrar el valor que debemos dar a una celda para lograr que otra, cuyo valor depende de la primera, alcance determinado valor objetivo.

Otra situación que interesa es conocer cómo afecta el cambio de uno o varios datos a uno o más resultados. Se trata de responder a la pregunta ¿qué pasa si? Cada conjunto de datos que aplicamos al modelo constituye un escenario y queremos calcular los resultados del modelo en los diferentes escenarios.

En muchas situaciones de la vida real se nos plantean problemas de optimización. Disponemos de unos recursos limitados y tratamos de sacarles el mayor fruto posible: qué solución da el máximo beneficio o produce el mínimo gasto.

Buscar objetivo

Cuando en una fórmula no es fácil, o no es posible, despejar una variable para resolver el problema inverso ¿Qué valor de la variable produce tal resultado? todos hemos utilizado, alguna vez, el método prueba y error. Probamos con diferentes valores del dato hasta logar el resultado deseado.

El comando Datos >Análisis Y si > Buscar objetivo… encuentra la mayoría de las veces el valor que debe tener una celda, dato inicial, para alcanzar determinado resultado, valor objetivo, en otra celda. Necesariamente la celda dato inicial, cuyo valor queremos determinar, debe contener un valor, no puede ser una fórmula, y la celda objetivo debe contener una fórmula cuyo valor varía con el dato inicial.

En algunos casos el proceso de búsqueda de objetivo puede concluir que no hay ningún valor del dato inicial que de cómo resultado el valor objetivo propuesto. En otras ocasiones puede ocurrir que el valor logrado para el dato inicial sea absurdo.

Ejemplo 1

Queremos comprar un coche y pagarlo en seis años. La financiera nos presta el dinero a una tasa de interés anual del 6,75%. ¿Cuánto dinero nos financian pagando 300,00€ al mes?

Ninguna de las funciones financieras de Excel resuelve este problema. Sin embargo la función PAGO resuelve el problema directo “conocida la cantidad prestada hallar el pago mensual”.

Para resolver el problema inverso partimos del problema directo. Supongamos que el coche de nuestros sueños cuesta 30.000€ ¿Cuál es el pago mensual?

C U R S O D E E X C E L A V A N Z A D O

2

Para comprar el coche de nuestros sueños tenemos que pagar 507,88€ al mes y nuestro objetivo es pagar 300€ al mes.

Seleccionamos, aunque no es necesario, la celda objetivo B4 y ejecutamos el comando Datos >Análisis Y si > Buscar objetivo…

Entramos el valor objetivo -300€ en el cuadro de diálogo Con el valor:, la referencia de la celda cuyo valor queremos determinar en el cuadro de diálogo Para cambiar la celda:

y pulsamos el botón Aceptar.

Excel encuentra la solución, pagando 300€ al mes podemos financiar un coche de 17.720,85€.

Si hacemos clic en el botón Aceptar dejará el valor objetivo en la celda objetivo y si hacemos clic en el botón Cancelar volveremos a la situación inicial.

Ejemplo 2

En un problema anterior obteníamos las previsiones de Ventas, Costes, Beneficio Bruto, Impuestos y Beneficio Neto de una empresa hasta el año 2010.

¿Cuál debería ser el porcentaje de Costes/Ventas si queremos lograr un Beneficio Neto de 80.000€ en el año 2010?

H E R R A M I E N T A S D E A N Á L I S I S D E D A T O S

3

Si el porcentaje de Costes/Ventas fuese del 32,04% en lugar del 45,00% el Beneficio Neto en el año 2010 sería de 80.000,00€ en lugar de 64.741,81€.

Escenarios

Si no conocemos la herramienta Escenarios de Excel y queremos estudiar el comportamiento de un modelo ante diferentes conjuntos de datos (ESCENARIOS), podemos copiar el modelo varias veces, en la misma hoja o en hojas diferentes, y entrar un conjunto diferente de datos en cada copia del modelo. Para ver los resultados del modelo en los diferentes escenarios podemos abrir varias ventanas con los resultados de cada escenario, si son pocos resultados, o copiarlos juntos en algún sitio.

El comando Datos >Análisis Y si > Administrador de escenarios: desde el Administrador de escenarios podemos crear escenarios, guardar cada conjunto de datos que constituye un escenario con un nombre; cambiar de un escenario a otro, eliminar, modificar y combinar escenarios y, crear informes resumen con todos los escenarios.

Al igual que ocurre con Buscar objetivo, las celdas que contienen los datos del escenario debe contener un valor, no pueden ser una fórmula.

Ejemplo 3

Queremos solicitar un préstamo de 15.000€ a devolver inicialmente en 60 meses a una tasa de interés anual del 6,50%.

C U R S O D E E X C E L A V A N Z A D O

4

Supongamos que el préstamo es a interés variable y que se actualiza cada seis meses. Además, la entidad financiera permite, en el momento de la actualización, que podamos personalizar el plazo restante. Cada seis meses, la entidad financiera actualiza la tasa de interés y nosotros actualizamos la duración.

Calcular el pago mensual que debemos efectuar durante el siguiente periodo de seis meses en diferentes escenarios:

Escenario MM: tasa de interés 6,50%, número de pagos 54.

Escenario AA: tasa de interés 6,75%, número de pagos 60.

Escenario AD: tasa de interés 6,75%, número de pagos 48.

Escenario DA: tasa de interés 6,25%, número de pagos 60.

Escenario DD: tasa de interés 6,25%, número de pagos 48.

M (Mantener), A (Aumentar) y D (Disminuir).

Si la situación no cambia, Escenario MM, el pago mensual sería el mismo.

Cada escenario son dos datos la tasa de interés, celda B13, y el número de pagos, celda B12.

Para crear los escenarios, seleccionamos las celdas B12 y B13, datos de entrada del escenario, y ejecutamos el comando Datos >Análisis Y si > Administrador de escenarios

H E R R A M I E N T A S D E A N Á L I S I S D E D A T O S

5

Pulsamos el botón Agregar… del Administrador de escenarios.

Entramos el nombre del primer escenario MM

C U R S O D E E X C E L A V A N Z A D O

6

y pulsamos el botón Aceptar.

Se abre la ventana Valores del escenario en la que podemos ver las referencias de las celdas que constituyen el escenario con los valores actuales de dichas celdas.

Como los valores que aparecen son los del primer escenario hacemos clic en el botón Agregar para crear el siguiente escenario.

H E R R A M I E N T A S D E A N Á L I S I S D E D A T O S

7

Cuando entremos los datos del último escenario pulsamos el botón Aceptar en lugar de Agregar.

C U R S O D E E X C E L A V A N Z A D O

8

Si ahora seleccionamos un escenario, por ejemplo DA, y hacemos clic en el botón Mostrar las celdas de datos del escenario cambian lo datos que contiene por los del escenario y vemos los resultados para ese escenario.

Podemos crear un informe resumen en el que aparezcan los datos de entrada y los resultados que nos interesen para cada escenario. Para ello, hacemos clic en el botón Resumen…

Excel selecciona una serie de celdas resultado. Nosotros queremos saber cuál será el pago mensual durante el segundo semestre, celda B16, y cuánto deberemos al final del segundo semestre, celda J17 en cada escenario.

H E R R A M I E N T A S D E A N Á L I S I S D E D A T O S

9

Si hacemos clic en el botón Aceptar, Excel crea la siguiente hoja de cálculo.

El resumen incluye una columna Valores actuales. Podemos modificar el aspecto de la hoja Resumen de escenario.

C U R S O D E E X C E L A V A N Z A D O

10

En lugar de elegir Resumen como tipo de informe podemos elegir Informe de tabla dinámica de escenario.

Optimización con Solver

Los problemas de optimización se caracterizan por tener un único objetivo a optimizar (maximizar, minimizar o igual a). Este objetivo depende de uno o más valores iniciales de entrada. Además suele haber una serie de reglas o restricciones que deben satisfacerse. Resolver un problema de este tipo consiste en hallar los valores de entrada que cumplen las restricciones y dan el valor objetivo óptimo.

La celda con el valor a optimizar, función objetivo, debe ser una fórmula que depende, de modo directo o indirecto, de las celdas de entrada.

Las celdas de entrada, cuyos valores queremos determinar, deben contener valores, no pueden ser fórmulas.

H E R R A M I E N T A S D E A N Á L I S I S D E D A T O S

11

Las restricciones se introducen en la ventana Parámetros de Solver que se abre cuando ejecutamos el comando Datos > Análisis > Solver…

Puede que no aparezca la herramienta Solver. Esto se debe a que al instalar Excel en nuestro ordenador, salvo que hagamos una instalación personalizada, sólo se instalan las herramientas más comunes. Este problema ya apareció con las funciones, en principio Excel no carga todas las funciones.

Para añadir la herramienta Solver seleccionamos Opciones de Excel > Complementos > Ir… activamos el complemento Solver y hacemos clic en el botón Aceptar.

Ejemplo 4

El Servicio de Formación de la Universidad de Alicante tiene un presupuesto de 300.000 € para organizar curso de formación para el personal de la universidad.

Se pueden organizar dos tipos de grupos: grupos pequeños de 20 alumnos con un coste de 15.000€ y grupos grandes de 30 alumnos con un coste 20.000€.

Para atender los grupos se contratan profesores, uno cuando el grupo es pequeño y dos cuando el grupo es grande. Motivos laborales impiden contratar más de 25 profesores sociales por temporada.

¿Cuántos grupos de cada tipo, pequeños y grandes, podemos organizar de modo que el número de alumnos asistentes a los cursos sea el máximo posible?

Para plantear y resolver este tipo de problemas se requiere unos datos de entrada iniciales, solución inicial. Por ejemplo, 1 grupo pequeño y 1 grupo grande. También podemos utilizar como solución inicial 0 grupos pequeños y 0 grupos grandes.

C U R S O D E E X C E L A V A N Z A D O

12

Ahora seleccionamos la celda objetivo B5 que queremos maximizar, ejecutamos el comando Solver… y completamos la ventana Parámetros de Solver.

Por defecto está seleccionada la opción Máximo en Valor de la celda Objetivo:.

Entramos las celdas cuyo valor queremos determinar en el cuadro de texto Cambiando las celdas.

Para entrar las restricciones hacemos clic en el botón Agregar… y entramos la restricción de que los recursos utilizados sean menores o iguales que los recursos disponibles.

H E R R A M I E N T A S D E A N Á L I S I S D E D A T O S

13

Además de esta restricción debemos agregar la restricción de no negatividad de la solución, el número de grupos debe ser mayor o igual que cero.

Después de la última restricción pulsamos el botón Aceptar.

Ahora hacemos clic en el botón Resolver.

C U R S O D E E X C E L A V A N Z A D O

14

La solución obtenida dice que pueden asistir a los cursos un total de 425 alumnos organizando 10 grupos pequeños y 7,5 grupos grandes. Además nos ofrece la posibilidad de generar tres tipos de informes: Respuestas, Sensibilidad y Límites.

Como el número de grupos debe ser entero pulsamos el botón Cancelar y añadimos la restricción de integridad de la solución, esto es que la solución deben ser números enteros.

H E R R A M I E N T A S D E A N Á L I S I S D E D A T O S

15

Según vemos pueden asistir a los cursos un máximo de 420 alumnos organizando 9 grupos pequeños y 8 grupos grandes y todavía disponemos de 5.000€.

Informe Respuestas

Como ya hemos dicho Solver facilita tres tipos de informes. El informe Respuestas es el más intuitivo y fácil de interpretar. Los otros, Sensibilidad y Límites requieren conocimientos teóricos avanzados.

Para generar el Informe Respuestas, una vez que Solver ha hallado la solución. Seleccionamos la opción Respuestas en el cuadro desplegable Informes y hacemos clic en el botón Aceptar.

Excel muestra el Informe Respuestas en una hoja nueva.

C U R S O D E E X C E L A V A N Z A D O

16

Podemos modificar el aspecto de la hoja Informe de respuestas 1.