Análisis de hipótesis

ANALISIS DE HIPOTESIS_.xlsx

Descargar XLSX • 269KB

Vamos a hablar ahora de escenarios, tablas de datos, buscar objetivo, y, solver.

Empecemos por Buscar Objetivo.

Si ya sabemos el resultado de una fórmula, o, función, que hemos usado, pero no estamos del todo seguro de qué valor necesitamos en dicha fórmula para obtener ese resultado deseado, podemos usar Buscar objetivo.

Veamos un ejemplo, y, lo vamos a entender mejor, por ejemplo, solicitamos un préstamo de 180.000 euros, ya sé cuánto dinero he pagado al final, y, cuánto tiempo tomo pagar el préstamo, lo que no se es la tasa de interés que se me aplico.

Pues vamos a calcularlo con buscar objetivo.

El primer cuadro, de la izquierda, en la celda F9, he usado la función VF (valor final), para calcular el valor final del préstamo, multiplicado por -1, para que el resultado de positivo, si queremos el resultado en negativo, no hace falta multiplicar por -1.

La función VF, sabemos que tiene los argumentos, tasa, numero de periodos, pago, valor actual, y, tipo.

Como argumento tasa, he tomado el valor de la celda F6, que es el interés anual, pero, como es el que quiero saber, no hay nada en la celda.

=VF(F6;

Como argumento numero de periodos, he usado el valor de la celda F7, que es de 20 años.

=VF(F6;F7;;F5;0)*-1

El argumento pago, lo he omitido, por lo que he puesto, punto y coma, para pasar al siguiente argumento.

=VF(F6;F7;;

Como argumento valor actual, he puesto la celda F5, que es el importe del préstamo.

=VF(F6;F7;;F5;

Como argumento tipo, he puesto cero, es decir, las cuotas se pagarán cada principio de mes.

=VF(F6;F7;;F5;0

Cierro paréntesis, y, multiplico por -1.

=VF(F6;F7;;F5;0)*-1

El resultado, de esta función, es el mismo que el importe solicitado, porque aun no se le aplicado ningún tipo de interés, que es el que vamos a calcular.

Cuando vamos a usar la herramienta de buscar objetivo, siempre nos debemos de poner en la celda que contiene la función, en este caso, la celda que contiene la función VF, es decir, F9.

Pues, nos colocamos en dicha celda, vamos a la pestaña datos, dentro del grupo previsión, desplegamos análisis de hipótesis, y, seleccionamos buscar objetivo.

Se abre la ventana de buscar objetivo, como estamos en la celda F9, podemos ver, como en la ventana definir la celda, ya aparece dicha celda.

En la ventana con el valor, debemos de poner el resultado que queremos obtener, en este caso, tengo un cuadro al lado donde ya he calculado el valor final con la función VF, pues ese es el valor.

Y en la ventana cambiando la celda, debemos de poner la celda que se ha de modificar, en este caso, es la celda G6, que corresponde a la tasa de interés.

Aceptamos.

Nos aparece una ventana con el estado de la búsqueda de buscar objetivo.

Simplemente aceptamos.

Podemos ver que es la misma tasa de interés que aparece en el cuadro de al lado.

El siguiente ejemplo, es de un profesor que imparte clases, cobra 45 euros cada ocho horas, quiere saber a cuanto ha de cobrar las ocho horas para conseguir 500 euros.

Tenemos un cuadro ya hecho.

Al lado, tenemos otro, donde lo haremos con buscar objetivo.

En la celda F14, tengo la formula donde multiplico las horas por el coste, pues esa será la celda para poner en definir la celda de buscar objetivo.

Pues, colocado en la celda F14, vamos a la pestaña datos, dentro del grupo previsión, desplegamos análisis de hipótesis, y, seleccionamos buscar objetivo.

Vemos que en la ventana definir la celda, ya aparece la celda F14, recordar, que debe de ser la celda que contiene la función, o, formula.

En la ventana con el valor, ponemos 500, que es lo que queremos conseguir.

Y, en la ventana, cambiando la celda, ponemos F13, que es el coste de las ocho horas.

Aceptamos, y, podemos ver que nos aparecen los 62.5 euros del primer cuadro.

Lo siguiente es que tenemos una cantidad que hemos prestado a alguien, esa cantidad es de 1000 euros, y, quiero que me devuelva en vez de 1000 euros, 2000 euros, donde le voy a aplicar un interés del 12%, lo que quiero es cambiar el número de periodos que me debe de pagar hasta llegar a los 2000 euros.

En la celda C7, tengo calculado el valor final con la función VF.

=VF(C6;C5;;C4)

Pues vamos de nuevo a buscar objetivo.

Nos colocamos en la celda C7, vemos que en la ventana, definir la celda, ya aparece la celda C7 que es quien contiene la formula, donde calculo el valor final.

En la ventana, con el valor, ponemos 2000, que es lo que queremos conseguir.

Y, en la ventana, cambiando la celda, ponemos la celda C5, que es el número de periodos, que es la que queremos calcular.

Aceptamos.

Aparece la ventana de estado de la búsqueda de objetivo, pues, aceptamos.

Y vemos que hacen falta seis periodos a un interés del 12% para conseguir una cantidad final de 2000 euros.

Vamos a ver ahora tablas de datos.

Una tabla de datos es un rango de celdas en las que podemos cambiar los valores de algunas de las celdas y así obtener respuestas diferentes a un problema.

En Microsoft Excel, las tablas de datos forman parte de un conjunto de comandos denominado análisis de hipótesis, porque estamos haciendo exactamente un análisis de hipótesis.

Vamos a ver varios ejemplos, así conocemos sus argumentos, lo haremos con una variable y con dos variables.

El primer ejemplo, va a ser de una variable.

Tenemos un préstamo de 10000 euros, a un tipo de interés del 12%, que vamos a pagar en cinco periodos.

Mediante la función VF he calculado el valor final.

=VF(J8;J9;;J7)

Y mediante la función PAGO, he calculado la letra de cada periodo.

=PAGO(J8;J9;;J10)

Pues, quiero calcular cual seria la letra de cada periodo en diferentes porcentajes, al 5%, al 8%, y, mismo 12%.

Para ello, seleccionamos el rango desde K7 a L10, vemos que seleccionamos la celda con el pago de cada periodo.

Vamos a la pestaña datos, dentro del grupo previsión, desplegamos análisis de hipótesis, y, seleccionamos tabla de datos.

Se abre la ventana tabla de datos.

Donde vemos celda de entrada (fila), y, celda de entrada (columna), debemos de rellenar las dos ventanas, cuando usemos dos variables, pero, en este caso, vamos a usar una sola variable, si nos fijamos en el modelo, vemos que los porcentajes, están en columnas, que es la columna, ya que el porcentaje del préstamo, esta en la celda J8, que es la celda que tenemos que seleccionar, pues, ,en la ventana celda de entrada (columna), ponemos la celda J8.

Aceptamos, y, podemos ver el pago de cada periodo en cada porcentaje, es decir, al 5%, al 8%, y, al 12%, que es el porcentaje inicial del préstamo.

Ahora, vamos a ver un ejemplo, pero de dos variables.

Tenemos el mismo prestamos que el anterior, pero en este caso, queremos ver como seria la cuota a pagar en diferentes porcentajes, y, en diferentes periodos, es decir, vamos a calcular la cuota para tres años, a un interés del 5%,8%, y, 12%, el ultimo porcentaje, debe ser el mismo al que se solicito el prestamos, y, a cinco periodos, y, un interés del 5%, 8%, y, 12%.

Para ello, seleccionamos el rango, desde K6 a M9

Vamos a la pestaña datos, dentro del grupo previsión, desplegamos análisis de hipótesis, y, seleccionamos tabla de datos.

Se abre la ventana, tabla de datos, donde podemos ver celda de entrada (fila), y, celda de entrada (columna), ya dijimos anteriormente, que debemos de usar estas dos ventanas, cuando tenemos dos variables, y, es el caso, porque tenemos el interés, y, los periodos.

Si nos fijamos en el modelo, el periodo lo tenemos en filas, que es la fila 6.

Y, los porcentajes, los tenemos en columna, que es la columna D.

Pues, en la ventana, celda de entrada (fila), seleccionamos la celda J8 que es el periodo.

En la ventana, celda de entrada (columna), va a ser la celda J7, que es el porcentaje.

Aceptamos, y, podemos ver las cuotas en diferentes periodos, y, diferentes porcentajes.

Ahora, vamos a ver que es un escenario.

Un escenario, es un conjunto de valores que Excel guarda y que pueden ser sustituidos automáticamente en la hoja de cálculo.

Podemos crear diferentes escenarios, y, después ir cambiando entre los diferentes escenarios.

Los escenarios, también se encuentran dentro del grupo análisis de hipótesis.

Vamos a crear nuestro primer escenario.

Tenemos un capital de 30000 euros, una salida de 12000 euros, y, tenemos como beneficio, pues la resta de 30000 menos 12000, es el beneficio, que es de 18000.

Vamos a crear un escenario, pero este escenario va a consistir en que tenemos un capital mayor, de 45000, y, unos gastos, también, mayor de 15000.

Antes de seguir, debemos de habilitar la opción escenario, para cambiar entre los distintos escenarios, vamos a ponerla, en la barra de acceso rápido, para ello, hacemos clic en la flecha con la punta hacia debajo de la barra de acceso rápido, y, selecciono más comandos.

Se abre la ventana opciones de Excel, donde vemos marcado en la ventana de la izquierda, barra de herramientas de acceso rápido.

En la ventana central, vemos una ventana llamada comandos disponibles en, y, como valor aparece comandos mas utilizados, es decir, aparecen los comandos que frecuentemente usamos.

Pero, el comando que buscamos, no se encuentra en comandos más utilizados, por lo que vamos a desplegar, y, seleccionamos todos los comandos.

Aparecen todos los comandos ordenados de menor a mayor, pues buscamos escenario, una vez lo hemos encontrado, hacemos clic en agregar, y, aceptamos.

Lo podemos ver, ahora, en la cinta de acceso rápido.

Pues, vamos a crear nuestro primer escenario, para ello, vamos a la pestaña datos, dentro del grupo previsión, desplegamos análisis de hipótesis, y, seleccionamos administrador de escenarios.

Se abre la ventana, de momento, administrador de escenarios.

Para agregar nuestro primer escenario, hacemos clic en agregar.

Se abre la ventana, agregar escenario.

En la ventana, nombre del escenario, damos un nombre, por ejemplo, escenario1.

En la ventana, celdas cambiantes, debemos de especificar cuales son aquellas celdas que van a cambiar de valor, en este caso, son las celdas desde B3 a B4, es decir, las celdas con el capital, y, lo gastado.

Podemos, insertar un comentario con una breve descripción, de lo que hace este escenario.

Hacemos clic en evitar cambios, para evitar que se edite el escenario cuando la hoja de cálculo está protegida.

Si queremos evitar que se muestre el escenario cuando la hoja de cálculo esté protegida, hacemos clic en oculto, yo, de momento, no la voy a marcar.

Aceptamos.

Se abre la ventana, valores del escenario, donde nos pide que valores va a tener las celdas cambiantes.

Pues a B3, ponemos 45000, y, a B4, ponemos 15000, y, aceptamos.

Aparece de nuevo la ventana, administrador de escenarios, donde podemos ver el escenario, que acabamos de crear, simplemente, cerramos, aunque, podemos probarlo desde esta ventana, para ello, hacemos clic en la pestaña mostrar.

Vamos a probar el escenario, para ello, vamos a la barra de acceso rápido, desplegamos escenario, de momento, solo tengo uno.

Lo seleccionamos, aparece la siguiente ventana, donde nos pregunta, si deseamos redefinir el escenario, hacemos clic en NO.

Podremos ver cómo han cambiado los valores.

Pero, aquí hemos tenido un olvido, y, es que también quiero volver a las cantidades iniciales, por lo que voy a tener que crear otro escenario, con dichas cantidades.

Volvemos a la pestaña datos, dentro del grupo previsión, desplegamos análisis de hipótesis, y, seleccionamos administrador de escenarios.

Hacemos clic en agregar.

En la ventana nombre de escenario, lo vamos a llamar escenario2.

Las celdas cambiantes van a ser las mismas, es decir, las celas B3, y, B4.

Hacemos clic en aceptar.

Y en la siguiente ventana, ponemos los valores que deseemos, en este caso, 30000 de capital, y, 12000 de gasto.

Aceptamos.

En la ventana, valores del escenario, ponemos los valores iniciales.

Hacemos clic en aceptar.

Aparece la ventana, de nuevo, administrador de escenarios, hacemos clic en cerrar.

Si, ahora, desplegamos, escenario, desde la barra de acceso rápido, podemos ver que ahora aparecen dos escenarios.

Seleccionamos escenario2.

Nos vuelve a aparecer la ventana, donde nos pregunta, si deseamos redefinir el escenario, hacemos clic en NO.

Vemos, que nos aparecen los datos iniciales.

Veamos otro ejemplo.

Tenemos un cuadro de amortización, ya hecho, es un préstamo de 10000 euros, a una tasa de interés del 2,50%, y, un plazo de 12 mensualidades.

He construido este cuadro, con la función PAGOINT, para calcular el interés de cada periodo, con la función PAGOPRINC, he calculado la amortización de cada periodo, con la función PAGO, he calculado la cuota a pagar en cada periodo, y, para saber lo que resta de préstamo en cada periodo, he ido restando al capital inicial la amortización.

Lo que queremos es ver como quedaría este cuadro de amortización a diferentes porcentajes, ahora, esta al 2,5%, pues quiero verlo también al 4,5%, y, al 6,5%.

Vamos a crear el primer escenario, para ello, volvemos a la pestaña datos, dentro del grupo previsión, desplegamos análisis de hipótesis, y, seleccionamos administrador de escenarios.

Hacemos clic en agregar.

En la ventana, nombre del escenario, lo voy a llamar, por ejemplo, primer porcentaje.

En la ventana, celda cambiante, hacemos clic en la celda C3, que es quien contiene el porcentaje.

Aceptamos.

Se abre la ventana, valores del escenario, pues vamos a poner el primer porcentaje, que es el 2,5%, debemos de ponerlo en formato general, por lo que ponemos 0,025.

Aceptamos.

Volvemos a la ventana administrador de escenarios, pues, vamos a seguir los pasos anteriores, donde vamos a añadir dos escenarios más, uno para el interés del 4,5%, y, otro para el interés del 6,5%.

Aceptar, volemos a la ventana, administrador de escenarios, donde podemos ver los tres escenarios.

Hacemos clic en cerrar.

Vamos a desplegar el icono de escenario desde la barra de acceso rápido, podremos ver los tres escenarios.

Vamos a seleccionar, por ejemplo, el primer escenario, es decir, el escenario primer porcentaje.

Podemos ver como han cambiado las cuotas, ahora, se han calculado al 2,5%, además, si miramos la ultima cuota, podremos ver que es cero, de esta manera, sabemos que el cuadro de amortización está bien creado.

Si vamos cambiando de escenario, podremos ver como queda el cuadro de amortización.

Bien, pues esto son los escenarios.