• Inicio
  • Programas
  • Excel
  • Programación lineal, ejemplo de política de asignación de préstamos con Excel 2007 y WinQSB

Programación lineal, ejemplo de política de asignación de préstamos con Excel 2007 y WinQSB

Los bancos, a menudo tienen el problema de asignación de préstamos y trabajan de acuerdo a un monto determinado el que tendrán que distribuir según la prioridad y el beneficio que genere. En esta publicación desarrollaremos un ejemplo clásico de política de préstamos muy utilizado.

Tenemos al banco ABC que desarrolla una política de préstamos por un máximo de $12 millones, la siguiente tabla muestra los tipos de préstamos disponibles:

Las deudas impagables no se recuperan ni producen ingresos.

La competencia aplica un porcentaje considerable al sector agrícola y comercial, por lo que debemos invertir un mínimo de 40% de los fondos para los préstamos dirigidos a estos sectores.

Se requiere apoyar a la industria de la construcción por lo que se asignara cuando menos el 50% de los tipos de préstamo personal, automóvil y casa para la edificación de viviendas (tipo de préstamo = casa).

También se tiene una política explícita que no permite que la relación general de préstamos impagables entre todos los préstamos sea mayor que 4%.

Solución

Primero tenemos que definir las variables, las unidades a manejar serán “millones de dólares”

  • X1 = préstamos personales
  • X2 = préstamos para automóvil
  • X3 = préstamos para casa (construcción)
  • X4 = préstamos agrícolas
  • X5 = préstamos comerciales

Como es evidente el banco ABC espera maximizar el retorno neto que viene a ser la diferencia entre el retorno por intereses y los préstamos impagables.

  • Maximizar z = 0.14((1-0.1)X1) + 0.13((1-0.07)X2) + 0.12((1-0.03)X3) + 0.125((1-0.05)X4) + 0.1 ((1-0.02)X5) – 0.1X1 – 0.07X2 – 0.03X3 – 0.05X4 – 0.02X5

Simplificando tenemos:

  • Maximizar z = 0.026X1 + 0.0509X2 +0.0864X3 +0.06875X4 +0.078X5

A continuación las restricciones son:

  • X1 + X2 + X3 + X4 + X5 <= 12 (fondos totales)
  • X4 + X5 >= 0.4 x 12 (préstamos agrícolas y comerciales)
  • X3  >= 0.5 (X1 + X2 + X3) (préstamos para casa)
  • (0.1X1 – 0.07X2 – 0.03X3 – 0.05X4 – 0.02X5)/(X1 + X2 + X3 + X4 + X5) <= 0.04 (límite de deudas impagables)
  • X1 >= 0,  X2 >= 0,  X3 >= 0,  X4 >= 0,  X5 >= 0 (no negatividad)

Observación: Los préstamos se entregan al mismo tiempo.

Ahora procesamos nuestra solución utilizando el complemento SOLVER del programa Excel 2007.

Si no estás familiarizado con los componentes en Excel te preguntarás ¿Cómo instalo Solver?
Bueno solo tienes que verificar si aparece en la cinta “Datos”, si no aparece dirígete al botón microsoft office, luego opciones de Excel, complementos, fíjate en la parte inferior y encontraras la opción “administrar”, selecciona “complementos de Excel” y presiona el botón “ir”, activa el complemento “solver” y listo.

Primero empezaremos ingresando los datos, las formulas para las celdas las encontraras en el archivo adjunto, es importante destacar que las formulas obligatoriamente deben ser ingresadas para la función objetivo y las restricciones, las variables tienen que ingresarse en posición horizontal con el titulo arriba del valor de la variable. Inicialmente colocaremos ceros a los valores de las variables.

Una vez ingresado los valores utilizamos nuestro complemento solver, los datos para el componente fueron ingresados como se ve en la siguiente imagen:

No hay que olvidar las opciones que tiene este complemento y que deben estar configuradas como se ve en la imagen:

Al pulsar el botón resolver nos mostrará la última ventana donde debemos seleccionar los informes que generará solver:

Nuestra hoja principal quedará de la siguiente manera:

Para nuestro caso los informes son los siguientes:

Informe de respuestas.

Informe de sensibilidad.

Informe de límites.

Ahora verificaremos los resultados obtenidos utilizando el módulo “Linear and Integer Progamming” del programa WinQSB, para esto procedemos a ingresar los datos de la siguiente manera:

Luego tendremos que ingresar los valores de la función objetivo y las restricciones:

Una vez ingresado los datos presionamos el botón de la persona corriendo para resolver el ejercicio y los resultados obtenidos son los siguientes:

También podemos obtener el análisis de sensibilidad de las variables y restricciones por separado, sólo pulsamos “Results” en el menú principal y elegimos que tipo de reporte queremos mostrar.

La ventaja principal del WinQSB es que podemos observar la resolución del problema paso a paso, y tendremos los valores del proceso calculado en forma tabular, para probar esta opción dirigete a la opción “Solve and Analyze” en el menú principal y elige “Solve and Display Steps”, luego si quieres ver la siguiente tabla de la solución pulsa la opción “Simplex Iteration” y elige “Next Iteration”.

Habiendo comprobado nuestra información analizaremos los resultados:

En nuestro caso solo se recomienda los préstamos comerciales y para casa. Siendo los préstamos personales los menos atractivos por tener el coeficiente objetivo mínimo 0.026 (ver informe de sensibilidad solver), tenemos que aumentar la rentabilidad (retorno por intereses menos los préstamos impagables) de los préstamos personales en 0.0604 para que la transacción sea apenas rentable.

Para cualquier duda sobre el procedimiento puedes consultar comentando esta entrada.

Etiquetas:, , ,

"Trackback" Enlace desde tu web.

M. Vizcarra

Ing. Industrial, egresado de la Facultad de Ingeniería Industrial y de Sistemas de la Universidad Nacional Hermilio Valdizán de Huánuco, Perú.

Comentarios (3)

  • Ruben

    |

    por fafor necesito un modelo entidad relacion de sistema de asignacion de prestamos.

    Reply

  • M. Vizcarra

    |

    Tendría que considerar muchos factores y un colaborador para armar el modelo que necesitas pero no dejes de visitar el blog, posiblemente publicaremos tu solicitud muy pronto.

    Reply

  • Alberto

    |

    Un excursionista va a llenar su mochila antes del viaje, con N objetos, cada objeto pesa ai kg. (sub i), cafa objeto tiene un valor ci (c sub i). ¿Cual es la solucion optima a llevar en la mochila?

    Considere n=5 objetos; b=178 limite maximo a llevar en la mochila; a=(ai)=(78,53,53,45,45) y c=(ci)=(2,5,5,1,8)

    El problema es que al llenar los datos y pulsar SOLVE, sale un mensaje “INVALID ROW VALUE” y el programa se cancela, sin embargo con LINGO si se obtiene la respuesta, B=C=E=1.

    Reply

Deja un comentario