Ejemplo de Solver.

Tras comentar en una entrada anterior otra herramienta de Excel como Buscar objetivo, ha llegado el momento de avanzar algo más con una herramienta, sin duda, más potente que ésta, hablamos de Solver.
La diferencia principal entre Solver y Buscar objetivo, a mi modo de ver, es la posibilidad que nos ofrece Solver para agregar Condicionales o inecuaciones a nuestro Sistema de ecuaciones planteado. Recordemos que cuando hablabamos de Buscar objetivo, decíamos que lo que realmente estabamos planteando era la solución a una ecuación, i.e., buscabamos obtener un resultado que nos resolviera una ecuación.
Solver, como herramienta más completa que es, nos ofrece la posibilidad de incluir a nuestro sistema de ecuaciones todas las restricciones (y por tanto nuevas inecuaciones) que pueda necesitar nuestro modelo planteado.
Plantearemos un ejercicio para que lo resolvamos con Solver.
Un ganadero, productor de leche, dispone de distintas cabezas de ganado vacuno y otras tantas de caprino. Después de algunos meses nuestro ganadero ha reconocido cuántos son sus gastos fijos para una producción determinada (de acuerdo a su ganadería) y también ha definido su cuenta de resultados, conociendo sus costes de producción y distribución. Nuestro ganadero ha visto que en su situación inicial genera pérdidas, por lo que quiere analizar cuál debe ser su producción de leche para al menos no perder dinero, es decir, cuál es su punto muerto. Trabajaremos bajo las condiciones de que toda la leche producida es vendida y distribuida, y que se debe mantener fija la proporción entre leche de oveja y leche de cabra, independientemente de la cantidad total de litros obtenida.

Para obtener el número de litros de leche necesarios de oveja y cabra, y alcanzar un resultado CERO, y puesto que hablamos de dos variables cambiantes, no podremos emplear la herramienta Buscar objetivo; es Solver la herramienta adecuada, ya que analizaremos todas las celdas o variables que deseemos para alcanzar un resultado final forzado por nosotros.
Desde el menú Herramientas - Solver (es necesario tener instalado este Complemento) ejecutamos la utilidad:

Estudiemos en primer lugar cuáles son los argumentos a completar en esta ventana de diálogo. Tenemos que determinar cuál es la Celda objetivo, es decir, la celda sobre la que pretendemos forzar un resultado. Nos ofrecen distintas posibilidades de asignar un valor a esta Celda objetivo, podremos Maximizarla, Minimizarla o darle un Valor definido por nosotros. En tercer lugar deberemos seleccionar aquellas celdas de nuestra hoja de cálculo que representen las variables a cambiar para alcanzar-Celdas cambiantes- el resultado final, determinado por la Celda objetivo. Por último, añadiremos todas las restricciones que entendamos necesarias para la correcta solución.
Volviendo a nuestro ejercicio concreto; hemos replicado la misma cuenta de resultados para aplicar Solver sobre la réplica, y así poder observar mejor el antes y después de la solución dada por Solver.
Así, comenzamos a definir los argumentos necesarios en la Ventana de parámetros de Solver. Nuestra celda objetivo será el resultado final, es decir, la celda J19. Como pretendemos conocer la producción de litros de leche que nos dará un resulado CERO; en segundo lugar definimos el Valor de la celda objetivo, determinado como un valor fijo igual a cero. Pasamos entonces a seleccionar las celdas cambiantes, es decir, qué variables de toda nuestra formulación deseamos variar para que el resultado sea el buscado, en nuestro ejemplo las celdas que identifican los litros de leche (D13) de oveja y de cabra (G13). Quedaría por último incluir una restricción o condición que conocemos por el planteamiento del problema (que se debe mantener fija la proporción entre leche de oveja y leche de cabra, independientemente de la cantidad total de litros obtenida), para ello Agregaremos la restricción; por comodidad hemos incluido en celdas continuas la proporción en que se distribuye el total de producción de leche (leche oveja = 51,28%; leche cabra = 48,72%). Pues serán precisamente estas restricciones las que incluiremos en el apartado correspondiente.

En nuestro ejemplo seleccionaremos como 'Referencia de la celda' la D12, con el 'operador' de igualdad, y como 'restricción' le asignaremos el valor 51,28% (o bien la celda D1). No es necesario en este caso incluir una segunda restricción con los datos de la leche de cabra, ya que al ser complementarios entre sí, estaríamos duplicando información.
Sólo nos queda Resolver:

Observamos la propuesta de cambio que nos ofrece Solver, habiendo cumplido las restricciones, si es lo buscado Aceptamos.
Para nuestro ganadero la solución para no tener pérdidas pasa por incrementar su producción de leche de oveja de 1.000 litros a 1.256 litros e incrementar al tiempo la leche de cabra producida de 950 litros a 1.193 litros. siuación que seguirá respetando la condiciones del planteamiento inicial (toda la leche producidaes vendida y mantiene la proporción entre tipos de leches producidas).
Adjunto fichero con el ejercicio resuelto.

Solver.xls

Hosted by eSnips