viernes, 17 de julio de 2009

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.

Ejemplo de Solver


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
Solver.xls
Hosted by eSnips


23 comentarios:

  1. no entiendo para qué utilizan los nombres de las celdas si en este ejemplo no se sabe que hay en cada celda, sería bueno que la imagen de la pantalla de excel mostrara también las filas y columnas

    ResponderEliminar
  2. Hola Galo,
    gracias por tu apreciación.
    Procederé al cambio de la imagen.... de todas formas hay un fichero adjunto que puedes descargar y ver el ejemplo completo.
    Slds

    ResponderEliminar
  3. ocupo ayuda, con una tarea si me pueden ayudar con Hacer un diagrama que explique los pasos a
    usar al plantear Solver como solucionador.

    ResponderEliminar
  4. existe en el blog una etiqueta dedicada en pleno a esta herramienta, con disitintos ejemplos...
    El uso es bien sencillo, con pasos simples como puedes ver en esta misma entrada; lo complicado es aplicarlo con las restricciones correctas a un modelo particular... y a eso nadie te puede ayudar a construirlo :-(
    Slds

    ResponderEliminar
  5. como puedo hacer para graficar esto en excel:
    X1 X2 Formula Signo TotalRequerimiento
    R1 1 1 5 <= 5
    R2 1 3 9 <= 9
    F.O 1 2 7
    Solucio 3 2

    ResponderEliminar
  6. Lo siento, pero no veo de qué quieres hacer un gráfico... o qué tipo de gráfico pretendes...
    ¿podrías ser más claro?
    muchas gracias y un saludo

    ResponderEliminar
  7. como puedo puedes descargar y ver el ejemplo completo...

    ResponderEliminar
  8. Hola,
    para descargarte los ficheros enlazados en los post sólo debes pinchar sobre ellos, activándose de inmediato una página de descarga gratuita.
    Slds

    ResponderEliminar
  9. hola.
    olle disculpa no abre el link no me lo podrias enviar a este correo ingenieria1a1@hotmail.com
    te lo agradeceria mucho gracias

    ResponderEliminar
  10. Hola,
    no se qué problema has tenido en la descarga, quizá sea algo puntual de la web, por que yo lo he descargado sin ninguna incidencia.. en todo caso ya te lo he enviado.
    Un saludo

    ResponderEliminar
  11. Pues la verdad me parece muy interesante la forma en que lo explicas y resuelves, pero tampoco he podido ver el documento en excel ni abrir el link, me harias el favor de enviarmelo a jose_remon@hotmail.com.

    gracias

    ResponderEliminar
  12. Hola José Remón
    ya te he enviado el fichero

    Slds

    ResponderEliminar
  13. deben ser mas explicitos y asi poder entender la utilidada de esta herramientas, ponen las celdas pero en la imagen no estan.

    ResponderEliminar
    Respuestas
    1. Correcto, queda ya modificada la imagen...
      Slds

      Eliminar
  14. Hola, muy buen aporte y lo felicito por su tiempo y detalle; ya que muchos no comparten sus conocimientos; pero le quedaría muy agradecida si para la próxima podría ser más detallista iniciando por cómo empieza o cómo debe quedar la plantilla antes de los resultados, pues me presenta todo resuelto y no sé en sí que valores debe quedarme al finalizar. Estoy en los pininis en esta herramienta así que no entiendo varias cosas, si es su voluntad enviarme a mi correo algo más específico e incluso subirlo para aquellos que recien estamos queriendo aprender de esta herramienta, le quedaría en deuda. gracias por su gentileza. Helens. mi correo es: helensdefarro03@gmail.com; gracias

    ResponderEliminar
    Respuestas
    1. Hola Helens,
      gracias por tuss palabras.
      Intentaré en próximas entradas ser más claro y comenzar desde cero.

      En todo caso, cualquier duda que te surja puedes plantearla.
      Slds

      Eliminar
  15. Muchas gracias por el aporte. Es una Excelente labor la que haces, mis felicitaciones y agradecimiento reiterado por compartir tus conocimientos. Hay algunos valores que desconozco su referencia en el ejercicio, quise descargar el fichero pero no pude, si no es mucha molestia, podrías enviármelo a mi correo: alvarezodair@gmail.com

    Éxitos.

    ResponderEliminar
    Respuestas
    1. Muchas gracias Odair,
      agradezco tus ánimos.
      Te envio el fichero al correo aportado.
      cordiales saludos.

      Eliminar
  16. por favor si me lo envias!

    ramon@aranceta.es

    ResponderEliminar
    Respuestas
    1. Hola Ramón,
      lo siento, pero este archivo es muy viejo, y en un cambio de equipo perdí todos los ejemplos.

      Intenta seguir las indicaciones del post, y si te atrancas en algún punto me comentas y te doy la solución.
      Slds cordiales

      Eliminar
  17. No se puede abrir el archivo de solver.xls, se puede habilitar el acceso directo, me lo puedes enviar al correo nlorenzana@hotmail.com de antemano muchas gracias
    saludos

    ResponderEliminar
    Respuestas
    1. Hola,
      lo siento pero perdí los ficheros más viejos...

      Slds

      Eliminar