En esta introducción a Solver, veremos con la ayuda de un sencillo ejemplo, de qué manera esta potente tecnología incluida en Excel nos puede ayudar a resolver algunos problemas con los que nos podemos encontrar. De nuevo aquí lo importante será entender bien la cuestión que debe resolverse y plantearla correctamente en un modelo. Excel se encargará de hallar la respuesta. Ya sabemos que algunas preguntas admiten varias respuestas por lo que será necesario comprobar la que Solver ha calculado antes de dar como resuelta la cuestión.
Utilizando la funcionalidad Buscar objetivo, Excel puede calcular el valor necesario de una celda cambiante para obtener el valor de la celda de resultado que queremos. Extendiendo este concepto, Solver permite:
- Especificar varias celdas que se puede modificar.
- Especificar limitaciones sobre los valores que las celdas cambiantes pueden tener.
- Generar una solución qe maximice o minimice una celda de hoja particular.
- Generar múltiples soluciones a un problema.
Así, los problemas apropiados para Solver son:
- Por lo general, se quiere maximizar o minimizar la celda objetivo o igualarla a un valor.
- La celda objetivo depende de un grupo de celdas cambiantes que Solver puede ajustar.
- La solución tiene que cumplir ciertas limitaciones.
Introducción a Solver
Para llevara cabo esta introducción utilizaremos un enfoque muy didáctico explicando esta funcionalidad a partir de un ejemplo. El ejemplo es el que sigue…
Se quiere calcular cuál debería ser la producción óptima de tres productos que maximice el beneficio. Nos dan los siguientes datos:
Como vemos, la compañía tiene una empresa cuya actividad se realiza en una moneda distinta del euro. La empresa fabrica tres productos. Sabe cuáles son los costes de fabricación de cada producto en horas de producción, en materiales necesarios y los gastos indirectos imputables a cada producto. Cree que el tipo de cambio estará en el entorno de 7,985. Ha calculado sus ventas si el nivel de actividad para sus productos se incrementa en un 3%. No contenta con este escenario, la compañía desea saber el mix de producción óptimo si el Total de horas de producción aumenta a 200.000 y el número máximo de unidades producidas es de 40.000.
Configurar SOLVER
Veamos. Ya tenemos instalado en nuestro Excel el add-in Solver y lo encontramos en la pestaña Datos, grupo Análisis. Hacemos clic en él y tenemos la siguiente pantalla
En el cuadro Establecer objetivo, haremos referencia a la celda de nuestro modelo en la que tenemos el beneficio en euros que queremos obtener (D20). En la zona Para, seleccionaremos Max puesto que lo que queremos es maximizar el valor de la celda objetivo. En Cambiando las celdas de variables, seleccionamos las celdas en las que tenemos la unidades que queremos fabricar de cada producto (C9 a E9).
Restricciones:
- Total horas de producción, F14 = 200.000.
- Total uds vendidas, F10 <= 40.000 (asumimos que se venden todas las unidades que fabriquemos).
- No queremos perder en la fabricación de ningún producto.
Para introducir nuestras restricciones bastará con seleccionar los botones Añadir o Cambiar (por supuesto, se puede eliminar una restricción seleccionándola y presionando el botón Eliminar. Al seleccionar los botones Añadir o Cambiar, llegamos a esta ventana…
… en la que podemos agregar o cambiar nuestras restricciones al modelo. Una nota: Solver admite que se rellene el cuadro Restricciones con una referencia de celda o con un número. (Nota: En algún sitio, he visto que se dice que admite también fórmulas. Lo he intentado y me da un mensaje de error. Lo he investigado y nunca he visto fórmulas aquí.)
El botón de opción Convertir variables sin restricciones en no negativas deberá estar seleccionado para prevenir que la cantidad a fabricar de algún producto pueda ser negativa. En Método de resolución elegiremos Simplex LP (Programación Lineal simple).
Ahora, nuestra pantalla Pantalla Prámetros de Solver se verá como esta:
Resultados de Solver
Al presionar el botón Resolver, Solver nos presenta una ventana…
… y modifica nuestro modelo con los resultados encontrados.
Informes
Puedes seleccionar los informes que quieras tener seleccionándolos del cuadro Informes. Excel añade una hoja por informe a tu libro de trabajo con la información. Te dejo aquí un archivo con los informes que ha creado para este modelo y estas restricciones.
Simplex LP
Una palabra siquiera sobre éste método de cálculo en esta introducción a Solver. Un problema de optimización lineal es uno en el que la celda objetivo y las restricciones añadiendo términos que tienen la forma celda cambiante x restricción. La mayoría de los modelos que he visto al utilizar Solver son este tipo por lo que creo que empezar por aquí puede ser una buena manera.
Conclusión
Como hemos visto, y ya dijimos al principio de esta entrada, Solver se basa en el modelo que hemos construido y en las restricciones que le damos. Este es el archivo que he utilizado para el ejemplo.