EVALUAR HERRAMIENTAS DE EXCEL - campusdi.cl

Grupo DI – Manual Microsoft Excel – Página 114

Te ayudamos a construir tu mejor

experiencia en el desarrollo de tus

competencias.

EVALUAR HERRAMIENTAS DE EXCEL

PARA LA CREACIÓN DE REPORTES COMPLEJOS

Módulo 5. Herramientas de análisis

Objetivos de este módulo Al finalizar este módulo, el participante estará en condiciones de:

• Conocer las opciones disponibles en buscar objetivo para la obtención de un valor específico.

• Conocer las opciones disponibles en solver para la estructuración de distintas condiciones que permitan la obtención de un valor específico.

• Evaluar el potencial de las tablas en la construcción de cálculos laborales complejos.

Buscar Objetivo versus Solver Buscar Objetivo es una opción que se utiliza para buscar un valor específico como resultado de una fórmula, modificando el contenido de una celda. Excel buscará qué valor debería tomar esa celda para conseguir el resultado esperado. Solver es un complemento desarrollado por Microsoft como un complemento de Excel mediante el cual será posible ejecutar un análisis y si (What-If). Cuando implementamos y usamos Solver, será posible detectar un valor óptimo, ya sea mínimo o máximo, destinada a una fórmula en una celda. Veamos un ejemplo. Tenemos la planilla que muestra la imagen donde tenemos tres tablas. En la primera utilizaremos una función financiera para encontrar el valor total de un préstamo que se solicitó a un plazo de 24 meses. En la segunda tabla, utilizando el valor futuro encontrado en la primera tabla, utilizaremos la herramienta Buscar Objetivo para encontrar la tasa de interés que se aplicó. En la tercera tabla, utilizando el valor futuro encontrado en la primera tabla, utilizaremos la herramienta Solver para encontrar la tasa de interés que se aplicó.




competencias.



Calculando el Valor Futuro en la Tabla Nº1 Para calcular este valor utilizaremos una función financiera llamada VF cuya sintáis es:

VF (tasa,núm_per,pago,[va],[tipo])

1. Abra Microsoft Excel. 2. Abramos el archivo Buscar Objetivo y Solver.xlsx. 3. Seleccione la celda B6 y escriba la siguiente expresión:

=VF(B3;B4;;-B2;0)

4. El resultado obtenido es: $ 5.426.178.




competencias.



Calculando la Tasa de interés en la Tabla Nº2 Para calcular este valor utilizaremos Buscar Objetivo

1. Abra Microsoft Excel. 2. Abramos el archivo Buscar Objetivo y Solver.xlsx. 3. Seleccione la celda E7 y seleccione el menú Datos ► Análisis de

hipótesis ► Buscar objetivo:

4. Aparecerá el siguiente cuadro de diálogo:

Definir la celda: en esta casilla debe seleccionar la celda que debe tomar un valor específico al que queremos llegar. Con el valor: en esta casilla debe escribir el valor específico al que se quiere llegar. Cambiando la celda: en esta casilla debe seleccionar la celda que debe cambiar para lograr el resultado escrito en “Con el valor”.




competencias.



Una vez completado el cuadro Buscar objetivo dar clic en Aceptar. Aparece un cuadro que muestra la solución y da la posibilidad de Aceptarla o Cancelarla.

5. Demos clic en Aceptar. 6. Si seleccionar la celda E4 donde está el valor calculado para la tasa de

interés vemos que llegó al valor 2,5% casi de forma exacta (2,50000011904741%).

Calculando la Tasa de interés en la Tabla Nº2 Para calcular este valor utilizaremos Buscar Objetivo

1. Abra Microsoft Excel. 2. Abramos el archivo Buscar Objetivo y Solver.xlsx. 3. Seleccione la celda E15 y seleccione el menú Datos ► Solver:

4. Aparecerá el siguiente cuadro de diálogo:




competencias.



Establecer objetivo: en esta casilla debe seleccionar la celda que debe tomar un valor específico al que queremos llegar. Para: en esta casilla debe escribir el valor específico al que se quiere llegar, o dependiendo del caso establecer un valor máximo o mínimo. Cambiando las celdas de variables: en esta casilla debe seleccionar la(s) celda(s) que debe(n) cambiar para lograr el resultado escrito en “Para”.

Una vez completado el cuadro Solver dar clic en Aceptar. Aparece un cuadro que muestra la solución y da la posibilidad de Aceptarla o Cancelarla.




competencias.



7. Seleccionar la opción Conservar solución de Solver y dar clic en Aceptar. 8. Si seleccionar la celda E12 donde está el valor calculado para la tasa de

interés vemos que llegó al valor 2,5% de forma exacta.




competencias.



Tabla de Datos Una tabla de datos es un rango de celdas en las que puede cambiar los valores de algunas de las celdas y obtener respuestas diferentes a un problema. Un buen ejemplo de una tabla de datos emplea la función pago con diferentes cantidades de préstamos y tasas de interés para calcular la cantidad económica de un préstamo hipotecario. Experimentar con diferentes valores para observar la variación correspondiente en los resultados es una tarea común en el análisis de datos. Para calcular una tabla de datos.

1. Abra Microsoft Excel. 2. Abramos el archivo Tabla de datos.xlsx. 3. Seleccione la celda B6 y Calculemos el Valor Futuro:

VF(tasa,núm_per,pago,[va],[tipo])

Escriba la siguiente expresión:

=VF(B3;B4;;-B2) El resultado es $ 3.629.491,2. La tabla debe quedar como se muestra a continuación:

4. Ahora calcularemos la tabla de datos. Seleccione el rango B6:N18.




competencias.



5. Seleccione el menú Datos ► Análisis de hipótesis ► Tabla de datos, aparece el siguiente cuadro de diálogo:

6. En Celda de entrada (fila) debe seleccionar la celda B4, y en Celda de entrada (columna) debe seleccionar la celda B3.

7. Haga clic en Aceptar.




competencias.



Escenarios Un escenario es un conjunto de valores que Excel guarda y puede sustituir automáticamente en la hoja de cálculo. Puede crear y guardar diferentes grupos de valores como escenarios y, a continuación, cambiar entre estos escenarios para ver los distintos resultados. Si varias personas tienen información específica que desea usar en los escenarios, puede recopilar la información en libros independientes y, a continuación, combinar los escenarios de los diferentes libros en uno solo. Una vez que tenga todos los escenarios que necesita, puede crear un informe de Resumen de escenario que incluya información de todos los escenarios. Los escenarios se administran con el Asistente de administración de escenarios del grupo análisis de hipótesis de la pestaña datos. Para calcular una tabla de datos.

1. Abra Microsoft Excel. 2. Abramos el archivo Administrador de escenarios.xlsx. 3. En primer lugar, debe colocar en su barra de Acceso directo el botón

Escenario:




competencias.



4. Ahora procederemos a crear los escenarios, seleccione la hoja Escenarios parte 1. Seleccione el menú Datos ► Análisis de hipótesis ► Administrador de escenarios, aparece el siguiente cuadro de diálogo:

5. Efectúe un clic en el botón Agregar… aparece el siguiente cuadro de diálogo:




competencias.



6. Asignaremos un nombre al escenario que estamos creando Escen_pago1. 7. Como celdas cambiantes seleccionaremos el rango B2:B3. 8. Clic en Aceptar, aparecerá el siguiente cuadro de diálogo:

Ingrese $ 2500000 (préstamo) y 1% (tasa).

9. Clic en Aceptar, aparece el siguiente cuadro de diálogo:




competencias.



10. Repetimos los pasos 6 a 9 para crear otro escenario llamado Escen_pago2. 11. Ingrese $ 2800000 (préstamo) y 1,4% (tasa). 12. Clic en botón Cerrar. 13. Ahora seleccionaremos la hoja Escenario parte 2 y crearemos los

escenarios: - Escen_pago3: $ 3000000 y 1,6% - Escen_pago4: $ 3200000 y 1,8%

14. Ahora, dado que las tablas de ambas hojas son iguales, juntaremos en una

de ellas los escenarios. Seleccionaremos la hoja Escenario parte 1. 15. Vamos a Administrador de escenarios ► Combinar, aparece el siguiente

cuadro de diálogo:

16. Seleccionamos Escenarios parte 2, y efectuamos clic en Aceptar. 17. Vemos como los cuatro escenarios quedaron combinados.




competencias.



Documents

EVALUAR HERRAMIENTAS DE EXCEL - campusdi.cl