E16_UD13

Vista previa del material en texto

UNIDAD - ANÁLISIS DE DATOS 
 
Buscar objetivos 
 
BUSCAR OBJETIVO es una herramienta que pertenece a las Herramientas de Análisis de Datos 
de Excel. Estas Herramientas las podemos encontrar en la pestaña “Datos”, tenemos aquí un 
botón que es Análisis de Hipótesis en el grupo de las Previsiones. 
 
Sí desplegamos Análisis de Hipótesis, vemos que tenemos un administrador de escenarios, 
buscar objetivo y tabla de datos. Nosotros tratamos ahora de la herramienta “BUSCAR 
OBJETIVO”. 
 
¿Para qué nos sirve esta herramienta? 
 
Esta herramienta la podemos utilizar cuando conocemos el valor del resultado de una fórmula, 
pero no sabemos el valor de uno de sus argumentos. Vamos a ver, un ejemplo, muy frecuente 
que es un cálculo de cuotas para la amortización de un préstamo. 
Nosotros, por ejemplo, vamos a imaginarnos que solamente pudiéramos pagar 1800€ de cuota 
mensual, podríamos poner aquí lo de los 1800€ e ir haciendo pruebas variando la tasa de interés, 
hasta dar con lo que nos interesa. 
 
Esta herramienta, esto nos lo da de una forma rápida, por ejemplo, yo me puedo plantear 
distintas preguntas. ¿Sin variar tasa interés o cuotas cuanto puedo pedir? ¿Teniendo aquí una 
cuota de 1800€ o teniendo 1800€ de cuota sin variar el capital qué tasa necesito? ¿O sin variar 
el capital o la tasa, con 1800€ de cuota al mes cuantas cuotas necesitaría? 
 
Estas son las posibilidades que nos va a brindar “buscar objetivos”. 
 
Veamos ahora cómo tenemos que aplicar esta herramienta de Excel, para conseguir esto que 
hemos visto. Vamos a situar aquí una celda poniendo nuestro tope máximo, por ejemplo, de 
cuota mensual, vamos a poner que fueran 1800€ como hemos dicho hasta ahora. 
 
 
Tengo varias posibilidades, variar el capital, variar la tasa de interés o variar el número de cuotas. 
Vamos a ir viendo todas ellas. Vamos a ver en primer lugar, sin variar tasa interés o las cuotas, 
es decir, estas dos celdas y suponiendo que puedo pagar 1800€, qué capital me dejarían. 
 
Nos venimos a la pestaña de datos, al grupo de previsión, análisis de hipótesis y pulsamos en 
“buscar objetivo”. 
 
 
Lo primero que me pide es qué celda es la que quiero definir. 
Quiero definir la cuota mensual, con el valor de 1800€ pero como aquí la función pago me ha 
arrojado un valor negativo porque no lo hemos pasado a valores positivos, tendremos que poner 
-1800€. 
 
 
Cambiando la celda, ¿qué celda es la que queremos que varíe?, en este caso era el capital, 
teníamos una pregunta que decía sin variar tasa interés o cuotas ¿cuánto puedo pedir? ¿Qué 
capital puedo pedir? 
 
 
 
Cambiando la celda del capital y pulsamos aceptar, aparece el estado de la búsqueda de objetivo 
y dice que ha encontrado una solución, valor del objetivo 1800€. Valor actual 1800€. 
Sí miro la celda del capital veo que me ha dejado un capital de 26.1670,768€. 
 
 
 
Esto sería el capital que me dejarían con cuotas de 1800€, con una tasa de interés del 5,50% y 
con 20 años para pagar, con 240 cuotas. Sí yo pulso aceptar me deja estos valores, sí pulso 
cancelar, vuelve a los valores originales. 
 
Vamos a ver la segunda opción. Sin variar el capital ni el número de cuotas que tasa necesitaría 
para que aquí se quedaran 1800€. Volvemos a ir a Análisis de Hipótesis, buscar objetivo, definir 
la celda de la cuota mensual -1800€ y en este caso, la celda que vamos a cambiar es la tasa de 
interés 
 
 
 
Aceptar y ya ha encontrado una solución, tendría que ser, una tasa de interés del 3,89%. 
 
 
Vamos a ver nuestra última pregunta, sin variar capital o ni tasa cuántas cuotas necesitaríamos 
para que fueran de 1800€. Vamos a análisis de hipótesis, buscar objetivo, definir la celda de la 
cuota mensual con -1800€ cambiando en este caso las cuotas. 
 
 
 
Ya ha encontrado una solución y serían 315,6 o 316 cuotas las que necesitaríamos, para poder 
pagar los 300.000€ a un 5,50€ y con cuotas de 1800€. 
 
 
 
 
Tablas de datos 
 
Una TABLA DE DATOS, no es nada más que un rango de celdas en el que se pueden cambiar 
algunos valores de las celdas para ver cómo es afectado el resultado original. En realidad, lo que 
hace es analizar un conjunto de valores y determina unos posibles resultados, por ejemplo, 
nosotros aquí teníamos esto para la “función pago”, que nos estaba determinando las cuotas 
mensuales para un préstamo. 
 
 
 
A mí me interesaría, ver aquí en función de esta cuota con distintos intereses, qué cuota se 
quedaría, por ejemplo, esto sería una tabla de datos de una variable, aquí la tenemos construida 
pero ahora veremos cómo hacerla. 
 
Podemos trabajar también con tablas de datos de dos variables, es decir, me va a poner, la cuota 
mensual dependiendo del tipo de interés y del número de plazos, el número de cuotas 
mensuales. 
 
 
A continuación, veremos cómo trabajar con estas tablas de datos, que son más que 
herramientas de análisis de hipótesis de Excel. 
Las tenemos en la pestaña “datos”, en el botón análisis de hipótesis, sería esta tercera opción, 
“tabla de datos”. 
 
 
 
 
Aplicar tabla de datos de una variable 
 
Vamos a ver cómo tendríamos que trabajar con Tabla de datos de una única variable. 
 
 
 
En este caso, lo que me interesaría es una representación en esta columna de las cuotas 
mensuales que yo pagaría con estos distintos intereses sin tener que andar calculando. 
 
Para esto me sirve precisamente la herramienta de Tabla de Datos. Voy a insertar el interés y la 
variable va a ser la cuota mensual. Vamos a vincular esta celda con la cuota mensual para 
indicarle que esta va a ser lo que va ir variando. 
 
 
A partir de ahí seleccionamos el rango de nuestra tabla que queremos que vaya a ser nuestra 
tabla de datos, vamos a la pestaña datos y dentro de las Herramientas de previsión, en Análisis 
de hipótesis, seleccionamos tabla de datos. 
 
 
 
Sólo voy a trabajar con una única variable, con lo cual, como lo que quiero es una columna, voy 
a seleccionar la celda de entrada como columna. 
 
 
 
¿Qué es lo que me está variando aquí? El interés. Aceptar y vemos que inmediatamente 
tenemos ya nuestra tabla rellena con las cuotas que deberíamos pagar variando el interés. 
 
 
 
Podemos hacer una tabla más completa, por ejemplo, se han hecho unos cálculos. 
 
 
 
El pago mensual que la he vinculado con esta celda, el pago total que he multiplicado los pagos 
mensuales por el número de cuotas y los intereses que será el pago total menos el capital. 
 
 
 
 
 
Ahora lo que voy a hacer es vincular estas celdas, cada una de ellas con estos valores. Ya tenemos 
nuestra tabla preparada, 
 
 
 
 
 
 
Ahora voy a seleccionar esto, pestaña de datos, análisis de hipótesis y vuelvo a seleccionar tabla 
de datos. 
 
Como sólo voy a jugar con una variable que es el interés, marco en celda de entrada, tasa de 
interés y aceptar y ya tengo mis datos. 
 
 
 
Ahora los pondría con un formato de moneda para el que el Informe fuera más legible. Ya 
tenemos aquí los pagos mensuales que haríamos con cada tipo de interés, el pago total 
resultante y los intereses que pagaríamos sobre estos tipos de interés. 
 
 
 
 
 
Aplicar tabla de datos de dos variables 
 
En esta ocasión, vamos a trabajar con una tabla de datos de dos variables, ya no queremos 
únicamente nos ponga aquí las cuotas mensuales variando el interés sino bien, variando el 
número de cuotas. 
 
 
Aquí tengo la variación de interés y aquí tengo la variación del número de cuotas. Parto del 
número de cuotas 240 y voy incrementando. 
Lo primero, vamos a vincular esta celda con la celda de cuota mensual, ponemos igual y la 
vinculamos. 
 
 
 
Ahora ya únicamente seleccionamos el rango de nuestra tabla, pestaña de datos, análisis de 
hipótesis, tabla de datos. 
 
 
 
Ahora si tenemos filas y columnas. La columna como antes, es la tasa de interés y en esta 
ocasión, la celda de entrada (fila), van a ser lascuotas mensuales, el número de cuotas, los 
meses. 
 
 
Marcamos y automáticamente ya me está dando, vamos a cambiarle el formato para que sea 
más legible y ya tengo aquí las cuotas que resultarían dependiendo del interés y además de 
número de cuotas totales a pagar. 
 
 
 
Esto sería una tabla de datos de dos variables, intereses y número de cuotas. 
 
 
Escenarios 
 
Los ESCENARIOS son una herramienta más, dentro de los Análisis de hipótesis. Un escenario es 
un conjunto de valores que Excel, guardar y que puede sustituir automáticamente en la hoja de 
cálculo. 
Nos van a permitir un resultado generado en base a un conjunto de celdas variables. Sí remarcar, 
que el máximo de variables que admite son 32. Son especialmente adecuados para mostrar el 
mejor y el peor de los casos en que nos podemos poner en una determinada hipótesis. 
 
 
 
Dentro de una hoja de cálculo podemos crear tantos escenarios necesitemos. 
 
Aplicar Escenarios 
 
Como ya hemos dicho nosotros podemos crear tantos escenarios como necesitemos. Tenemos 
aquí una hoja de cálculo con un préstamo de 250.000€ al 4% de interés a pagar en 30 años. 
Con una cuota mensual que daría de 1.266,71€. Vamos a ver, cómo podríamos trabajar aquí con 
escenarios, nos vamos a ir a Análisis de Hipótesis a Administrador de Escenarios. 
 
 
 
 
Vamos a generarnos tres escenarios, un escenario normal que vamos a coger los datos que 
tenemos actualmente, qué celdas van a ser las que van a ir cambiando, estas dos celdas son las 
que me van a cambiar. Voy a jugar con estas dos celdas en mis escenarios. Aceptar. 
 
 
 
Aquí vamos a dejar estos valores porque este es el escenario normal y me voy a referir a estos 
valores. Aceptar. 
 
 
 
Vamos a agregar un escenario “optimista”, las celdas cambiantes van a seguir siendo esas, pero 
en este caso vamos a suponer que me lo deja a un 4% en lugar de 4,5% y que me dan 40 años 
para pagar, 480 mensualidades. 
 
 
 
 
Y vamos a generar uno más que será el PESIMISTA, las celdas cambiantes serán las mismas. Y en 
este caso lo vamos a dejar al 5% a pagar en 30 años, en 360 cuotas. Aceptar. 
 
 
 
 
 
Ya tengo aquí mis tres escenarios ¿cómo trabajo ahora con ellos? 
 
 
 
Por ejemplo, si yo me vengo a “optimista” y le digo mostrar, vemos que inmediatamente me 
está reflejando esos datos aquí, y me recalcula la cuota mensual. 
 
 
 
Voy al “pesimista” sería una cuota de 1.342€ al 05€ al 5% y en 30 años. 
 
 
 
Vamos a volver a colocar el normal que era el que teníamos al principio, me puedo generar 
cuantos escenarios necesite y puedo marcar como celdas cambiantes hasta 32 celdas que luego 
les iré dando valor según los escenarios. 
 
 
 
Además del botón de agregar, vemos que tenemos un botón para eliminar escenarios, si pulso 
cualquier de ellos me lo borraría, modificar que me va a permitir modificarlos, me permitiría 
cambiarle el nombre, las celdas cambiantes, el comentario o incluso los valores que había 
introducido para este escenario. 
 
Permitiría también “combinar” varios escenarios ya montados y el botón resumen me va a 
permitir generar unas tablas, con un resumen de todos estos escenarios. 
 
Informes de Escenarios 
 
Cuando trabajamos con escenarios para poder visualizarlos, ya hemos visto que, con irnos a la 
pestaña de datos, análisis de hipótesis, administrador de escenarios podríamos ir mostrando, 
aquí cada uno de nuestros escenarios para visualizarlos en esta zona. 
 
Sí tenemos muchos, esto es muy incómodo y además es que tampoco nos va a ser rentable 
porque no nos va a dejar comparar entre unos y otros. Para eso tenemos la herramienta de 
RESUMEN DE LOS ESCENARIOS. 
 
 
 
Sí pulso el botón “resumen”, en resumen, del escenario, de tipo resumen, está pidiendo las 
celdas donde están los resultados que va a haber dentro de mi escenario. 
 
 
 
En este caso sería esa celda, damos a aceptar. Y nos genera una nueva hoja que se llama 
RESUMEN DEL ESCENARIO. 
 
 
 
Y vemos que tenemos aquí todos los datos, los escenarios que teníamos, el normal, el optimista 
y el pesimista. Otra columna con los valores actuales, son los valores que había cuando se generó 
el resumen. En este caso teníamos seleccionado el “escenario normal” con lo cual me aparecen 
repetidos los del normal. 
 
Aquí lo que pasa, es que no queda muy claro. En tanto en cuanto, no se ven los nombres de lo 
que es, siempre podría seleccionar la celda, y cambiar el valor por el nombre deseado 
 
 
 
 
Ahora ya tendríamos un Informe donde podríamos ver las cuotas que quedarían dependiendo 
del escenario que aplicáramos y los podemos ver todos juntos, con lo cual, podemos analizarlo 
mucho mejor. 
Nos informa que los valores actuales representan los valores de las celdas cambiantes en el 
momento que se creó el Informe resumen. 
Las celdas cambiantes de cada escenario se muestran en gris 
 
Puesto con esto, hemos visto cómo hacer uno de tipo resumen. Podemos también hacerlo de 
tipo de Tabla Dinámica, si pulsamos, en resumen, nuevamente, en lugar de decirle resumen, 
informe de tabla dinámica de escenario. 
 
 
 
La celda de resultados, sería donde tengo la cuota mensual, aceptamos y vemos que me ha 
generado una tabla dinámica, sí me meto dentro, me abre las herramientas y me dejaría mostrar 
la lista de campos y trabajar con ello ya como tabla dinámica, vemos que lo que me está dando 
son las celdas de resultados de las cuotas mensuales.