jueves, 3 de diciembre de 2009

Solver un ejemplo de programación lineal en Excel.

Me escribió un usuario solicitando ayuda con un problema de programación lineal:

...Se trata de solucionar un probelma de programación lineal mediante la herramienta solver de excel.
Datos:
Xij= Producción en el mes i [=1(enero), 2(febrero), 3(marzo), 4(abril)] propia (j =P) o adquirida al proveedor ajeno (j=A).
Ii = Unidades almacenadas en el mes i.

Min 120(X1P X2P X3P X4P)+125(X1A X2A X3A X4A)+6(I1+I2+I3+I4)

sa:
X1P+X1A-I1=2100
X2P+X2A+I1-I2=3750
X3P+X4A+I2-I3=6000
X4P+X4A+I3-IA=5000
XiP menor o igual 4000 i=1,2,3,4
XiA menor o igual 1000 i=1,2,3,4


En primer lugar construimos una plantilla donde se recojan la función a minimizar y todas las condiciones:


Se ha construido en la primera parte de la plantilla la función que define el número de unidades por mes, así como su coste asociado, tanto para producciones propias como ajenas; el objetivo a minimizar será precisamente el producto uno a uno de coste mensual por unidades de ese mes:
=SUMAPRODUCTO(B4:M4;B3:M3)
o lo que es lo mismo
=SUMAPRODUCTO(unidades; coste unitario)
La segunda parte del informe sirve para definir las condiciones particulares o restricciones existentes respecto al número de unidades posibles:
X1P+X1A-I1=2100 la producción propia de enero más la ajena de enero minorado por las unidades de almacén del primer mes deben sumar 2.100 unidades de producto.
X2P+X2A+I1-I2=3750 la producción propia de febrero más la ajena de febrero más las unidades de almacén del primer mes minorado por las de febrero deben sumar 3.750 unidades de producto.
X3P+X4A+I2-I3=6000 misma interpretación que las anteriores
X4P+X4A+I3-IA=5000 misma interpretación que las anteriores
XiP menor o igual 4000 i=1,2,3,4 en cada més la producción propia debe ser menor o igual que 4.000 unidades producidas
XiA menor o igual 1000 i=1,2,3,4 en cada més la producción ajena debe ser menor o igual que 1.000 unidades producidas
empleando de nuevo la función
=SUMAPRODUCTO(unidades; restricciones)
podremos determinar, una vez configuremos Solver, todas las restricciones.
Nuestro siguiente paso es, por tanto, diseñar Solver. Desde el menú Herramientas > Solver (para Excel 2003) o Menú Datos > grupo Análisis > Solver (para Excel 2007) accedemos a la siguiente ventana:


Seleccionamos como celda objetivo el resultado de multiplicar 'Unidades' por 'Coste unitario', marcando la opción de valor mínimo, y en la sección de 'Sujeta a las siguientes restricciones' agregamos todas las condiciones exitentes; es decir, cada una de las doce celdas del rango N7:N18, las cuales resumen las condiciones dadas, las comparamos con los valores dados restrictivos del rango O7:O18.
Una vez definidas las doce restricciones en Solver, podremos ejecutar la Resolución:


lo que nos devolverá un resultado en la celda objetivo N4 de 1.916.750 eur, el cual cumple todas las restricciones dadas; podremos analizar algunos detalles en los informes de respuesta, sensibilidad y límites generados.
Adjunto fichero para su análisis.

Ejercicio Solver minimizar
Ejercicio Solver m...
Hosted by eSnips

17 comentarios:

  1. Tengo un problema el Solver, sería muy bueno si me podes ayudar.
    La función objetivo a minimizar es:
    Z = X1*C1 + X2*C2
    X1, X2: Cantidad de Camiones
    C1, C2: Costo de mover los camiones

    Necesito utilizar un condicional para que si X1<30 use C1 y si X1>=30 use C1'. Lo mismo para X2.

    El problema es que no me resuelve porque es una referencia circular.

    Me puedes dar una idea de cómo resolver este problema?

    Cristian

    ResponderEliminar
  2. Hola Cristian,
    no termino de ver claro el planteamiento. Supongo quieres minimizar X1, X2, C1 y C2, con la condición que comentas (X1<30 entonces C1 y si X1>=30 entonces C1???)... tal cual lo planteas me faltan datos para interpretar y plantear el problema.
    Por favor, envíame a excelforo@gmail.com todo el planteamiento del problema y lo revisaré detenidamente.
    Slds

    ResponderEliminar
  3. buenos dias tengo el plantaemiento de un ejercicio pero no lo entiendo bien!! como hago para enviartelo

    ResponderEliminar
  4. Para conctactar conmigo envíame un email a

    excelforo@gmail.com

    Slds

    ResponderEliminar
  5. hey bien esta bueno el block.
    no se si sepen interpretar el analisis de sensibilidad del nuevo excel microsoft 12.0 no se bien que mirar en el.
    gracias

    ResponderEliminar
  6. Lo único que conozco de las librerías Ms-Excel 12.0 es que sirven para automatizar códigos o macros...
    Lo siento, intentaré ponerme al día con este punto.
    Saludos

    ResponderEliminar
  7. buenas, podrias apoyarme en el planteamiento de un problema que tengo en la universidad.

    Un fabricante tiene cuatro órdenes de producción: A, B, C y D. La tabla que se
    incluye indica el número de horas-hombre que se requieren para fabricar estas
    órdenes en cada uno de los tres talleres (X, Y, Z) de la industria.
    Es posible dividir una orden entre varios talleres, por ejemplo, parte de la orden A
    puede ser procesada en X, parte en Y, y parte en Z. Así mismo, cualquier taller
    puede ejecutar fracciones de varias órdenes.

    Taller H-H necesarias Costo por H-H
    A B C D H-H Disponibles
    X 71 298 133 144 89 320
    Y 39 147 61 126 81 160
    Z 46 155 57 121 84 160

    Si el fabricante desea minimizar los costos de producción, establezca el
    planteamiento del problema (Función objetivo y restricciones). Defina las variables
    a emplear y explique su significado.

    ResponderEliminar
    Respuestas
    1. y como da el modelo para este problema?

      Eliminar
  8. Hola,
    lo primero que tendrías que elaborar en tu hoja de cálculo es una función que replique la función objetivo basada en las distintas ordenes de producción.
    Una vez tengas dicha función podrás aplicar la herramienta Solver para minimizar el resultado de la función de producción, sujeta a las condiciones o restricciones impuestas en la tabla que tienes.
    Si tienes algún problema con la manera en que se configura Solver, envíame un correo a
    excelforo@gmail.com
    con el fichero excel donde tengas trabajado ya la función de restricción.
    Slds

    ResponderEliminar
  9. podrias hacer la hoja de calculo ????

    ResponderEliminar
    Respuestas
    1. Hola...
      ¿hacer la hoja de cálculo??
      ¿a qué te refieres?
      Slds

      Eliminar
  10. hola! podrías explicarme cuál es la interpretación del reducido coste y precio sombra en el informe de sensibilidad para un modelo de minimización de costos? sé explicarlo para maximización pero nunca vimos en clase un ejemplo de min. y no se como se interpreta
    gracias!!

    ResponderEliminar
    Respuestas
    1. Hola Magui...
      me haces una pregunta que queda un poco fuera de mi memoría, no lo toco desde de mis tiempos en la universidad, pero por lo que recuerdo la interpretación sería algo assí como que son los costes mínimos necesarios para cumplir con unas condiciones mínimas de producción.
      Quizá en un foro económico te puedan dar una respuesta más acertada.
      Saludos

      Eliminar
    2. hola, gracias por tu respuesta. estuve buscando en libros y encontre que para modelos de minimización el reducido coste representa en cuanto puede reducirse ese costo sin alterar la solución óptima, se relaciona directamente con el límite inferior; en cuanto al precio sombra, representa cuanto disminuiría el valor óptimo por cada unidad menos de recurso que se utilizara. De esto último me queda una duda, en mi problema en particular uno de los precios sombra es negativo...si tenes alguna idea de que puede querer decir esto te lo agradecería

      Eliminar
    3. Hola Magui,
      lo siento, pero no quiero meter la pata con esto... mejor consulta un foro específico del tema...
      Mucha suerte

      Eliminar
  11. Hola necesito su ayuda, tengo el sgte ejercicio y debo plantearlo en Solver:

    DADO EL SIGUIENTE PROBLEMA DESARROLLE LA SOLUCIÓN POR SOLVER,

    MinZ = 25X + 50Y + 300W
    Sujeto A
    0.8X + 0.2Y >= 3
    X + 1.5Y + 3W >= 6
    0.1X + 0.6Y + 2W >= 4
    X1 + X2 + X3 <= 6
    X1,X2,X3 >= 0

    ResponderEliminar
    Respuestas
    1. Hola Lore,
      lo primero que tienes que hacer es plantear en la hoja de cálculo la ecuación objetivo a minimizar, como suma de tres productos; esa suma será tu celda objetivo que posteriormente en Solver señalaremos a minimizar.
      El resto de condiciones/restricciones suele ser más cómodo apoyarse también en la hoja de cálculo, de forma parecida a la ecuación principal, para ir añadiendo las desigualdades en Solver.
      Intenta seguir el ejemplo planteado en este post, y si te trabas en algo, envíame lo que hayas logrado a
      excelforo@gmail.com
      Slds

      Eliminar