Solver y Buscar Objetivo

Descargar el fichero: doblar.xlsx

En Excel disponemos de dos magníficas herramientas para resolver ecuaciones sin necesidad de despejar la variable que deseamos obtener. Se trata de Solver y de Buscar Objetivo, aunque son mucho más que eso. En especial, Solver es una estupenda herramienta de optimización (cálculo de máximos y mínimos).


Buscar Objetivo
Utilicemos la ley de la capitalización compuesta.
Vamos a contestar a la pregunta siguiente:
¿En cuanto tiempo se doblara un capital en compuesta trabajando al 5%?
Para ello podemos utilizar la función financiera de Excel:
=NPER(tasa; pago; va; vf; tipo)
=NPER(5%;;-1000;2000)
Que arroja un resultado de 14,20669908 años.
Pero en este caso vamos a resolverlo con Buscar Objetivo.

 
Pasos:

1. Poner unos datos arbitrarios en las celdas C6:C8
2. Calculamos en C9 el capital final aplicando la ley de la compuesta
3. En C8 se pone el tipo de interés del 5% que es un dato del enunciado
4. Se lanza ‘Buscar Objetivo’ que esta en el menú Herramientas (en Excel 2007 esta en Datos, ‘Análisis Y si’)
5. En ‘Definir la celda’ siempre se debe poner la celda que lleva la fórmula, en este caso la C9
6. En ‘Con el valor’ se debe poner a mano (no deja pinchar una celda) el valor al que se quiere llegar
7. En ‘Para cambiar la celda’ se debe poner la celda de la variable que queremos despejar (calcular), en nuestro caso C7.

Solver

Vamos ahora a resolver la siguiente cuestión:
Calcular a qué tipo se ha de trabajar en compuesta para doblar el capital en 10 años.
Se puede responder facilmente utilizando una función de Excel que calcula el tipo de interés de este tipo de operaciones. La función es:
=TASA(nper;pago;va;vf;tipo;estimar)
En nuestro caso:
=TASA(10;;-1000;2000)
que  da un resultado de: 7,17735% anual.
Podemos resolverlo con Solver al igual que lo haríamos con ‘Buscar Objetivo’. Aunque Solver es mucho mejor que ‘Buscar Objetivo’, no en vano es una potente herramienta de optimización.
Nosotros no la vamos a utilizar para calcular máximos y mínimos, sino para obtener valores a los que puede llegar una celda objetivo.
Solver sólo estará disponible si se activa el Complemento que permite su utilización. En Excel 2003 se activa en: Herramientas, Complementos, Solver. En Excel 2007 se activa pulsando el botón del Office (ese botón redonde que tienes arriba a la izquierda, le denominan The Ribbon), luego elige ‘Opciones de Excel’, y a la izquierda veras ‘Complementos’, selecciona Solver y activalo. Cuando actives Solver, de paso marca también ‘Herramientas para Análisis’ que permite disponer de muchas más funciones en Excel.
Una vez activado el complemento para usarlo en una hoja, en Excel 2003 lo tiene en Herramientas, y en Excel 2007 esta en Datos.



Pasos:

1. Poner unos datos arbitrarios en las celdas C16:C18
2. Calculamos en C19 el capital final aplicando la ley de la compuesta
3. En C17 se ponen los años que según el enunciado son 10
4. Se lanza ‘Solver’ que esta en el menú Herramientas (en Excel 2007 esta en Datos).
5. En ‘Celda Objetivo’ siempre se debe poner la celda que lleva la fórmula, en este caso la C19
6. Valor de la Celda Objetivo, marcar la casilla que pone ‘Valor de’ (no marcar ni Máximo, ni Mínimo) y poner el valor 2000
7. En ‘Cambiando las celdas’ se debe poner la celda de la variable que queremos despejar (calcular), en nuestro caso C18.

---

La versión de Office 2010 Starter es una versión básica que ahora viene instalada en muchos ordenadores nuevos pero que no tiene muchas de las opciones de menú, ni herramientas que necesitamos.

Esta es una imagen de la versión Starter donde puedes ver los recortados menús de que disfruta.

---

Activar las Macros para que funcione Solver

Solver es un complemento de Excel, que está programado en lenguaje de macros. Las macros las usaremos más adelante para hacer algunas formulas personalizadas. Eso ya os lo contaré más adelante, cuando veamos rentas. De momento tienes que saber que una Macro es un programa y que como tal hay gente que en su día programaron virus en lenguaje de macros. Por este motivo Microsoft estableció varios niveles de seguridad,
para indicarle a Excel si quieres que al abrir un fichero que lleve macros las habilite o no.

Si tienes Excel 2003 o versiones anteriores para habilitar las macros debes hacer lo siguiente:
Herramientas, Macros, Seguridad, Nivel bajo o Nivel Medio.

Lo recomendable suele ser poner nivel medio de seguridad, de esa forma cuando abras un fichero de Excel que lleva macros te preguntará que si quieres habilitarlas. Si te fías de la fuente o has pasado un antivirus le dices que SI quieres habilitar las macros, y si no te fías no las habilitas, pero entonces no son operativas.

Como todos tenemos antivirus más o menos actualizados y efectivos, puedes poner el nivel bajo de seguridad, así no te estarán preguntando cada vez que abras un fichero que lleve macros.

Para Excel 2007, la cosa se complica. Primero debemos obtener la pestaña PROGRAMADOR que es una pestaña que de entrada no aparece. Las que aparecen con la instalación son: Inicio, Insertar, Diseño de página, Fórmulas, Datos, Revisar, Vista.

Para obtener la pestaña PROGRAMADOR (en Excel 2007) debes ir al botón redondo de la esquina superior izquierda (se llama 'el botón de Office' porque es común a todos los programas de Microsoft Office: Word, PowePoint,..). Pulsando en ese botón redondo, verás abajo que pone 'Opciones de Excel'. En la ventana 'Mas frecuentes' verás una opción que esta desmarcada y pone: "Mostrar ficha Programador en la cinta de opciones". Marca esa opción y pulsa Aceptar. Así conseguirás tener la pestañita (ficha) PROGRAMADOR.

Se supone que ya tenemos la ficha PROGRAMADOR. Pulsa en ella, y luego en 'Seguridad de Macros', y elige 'Habilitar todas las macros'.

De esta forma tendremos habilitadas las macros. Para que esto sea efectivo debemos salirnos del fichero y volver a entrar.

Y para todos, si es importante aprender a conseguir la ficha PROGRAMADOR (si tienes Excel 2007), y conseguir poner un nivel bajo de seguridad o nivel medio.

---

Buscar Objetivo

Puedes usar la función TASA que esta disponible en todos los ordenadores con Excel,
sean de la versión que sean. O puedes usar 'Buscar Objetivo' que aunque menos preciso
que Solver, para este ejemplo es más que suficiente. Y también esta disponible en todos
los ordenadores. No es necesario instalar nada adicional.

---

Para Excel 2003 y anteriores


¿Dónde esta Buscar Objetivo?


Esta en el menú Herramientas.

¿Dónde esta SOLVER?


Al ser Solver un complemento, primero se ha de instalar. Para instalarlo haz: Herramientas, Complemento, y marca Solver, y ya que estamos marca 'Herramientas para Análisis'. Al aceptar veras que el ordenador carga el complemento. Si en su día realizaste una instalación completa, tarda en cargar los complementos un segundo. Si en su día no realizaste una instalación completa de Excel te pedirá que introduzcas el disco con el programa Office que instalaste en su día.

Comprobemos ahora que ya tengo Solver disponible. Lo encontrarás en el menú Herramientas, Solver. Pruebalo con un ejemplo. Verás que es mucho mejor que Buscar Objetivo. Es importante conseguirlo ya que en la práctica 2 y sucesivas lo usaremos de forma intensa.

'Herramientas para Análisis' lo que hace es aumentar muchísimo las funciones disponibles. Esto nos resultará necesario para las próximas prácticas, ya que utilizaremos algunas de estas funciones complementarias.

Vídeo: Solver y Buscar objetivo

Vídeo: Buscar objetivo y Solver

Caso práctico: Equilibrar una operación financiera

Veamos un caso práctico de tipo financiero. Vamos a calcular la cuantía necesaria para cuadrar una operación financiera. La calcularemos por tres métodos siendo uno de ellos Solver y otro Buscar objetivo.

Caso práctico: Efectivo de una Letra del Tesoro