excel Archivos - Página 2 de 20 - Gestión de Operaciones

Modelo de Localización y Transporte con Preferencias

Los modelos de optimización que integran decisiones de localización y transporte han sido materia de análisis detallado en nuestro sitio como se aborda en los artículos Optimización de una Red Logística de Transporte y Localización de Centros de Distribución y el Problema de Transbordo en una Red Logística de Transporte Multiperíodo, entre otros. En esta ocasión incorporaremos un concepto adicional a la problemática anterior a través de la incorporación de las preferencias de los clientes por ser abastecidos por determinados centros de oferta. A este problema lo llamaremos Modelo de Localización y Transporte con Preferencias de Clientes y a continuación describiremos un caso particular que permita visualizar una alternativa de formulación sencilla con su correspondiente implementación computacional.

Asumamos que cada cliente (demandante) ha manifestado su preferencia por ser abastecido por determinados oferentes (potenciales). En este contexto cada cliente elegirá siempre su mejor (menor) prioridad de las alternativas ofertas disponibles, es decir, de aquellas que se decidan instalar (localizar).

Consideremos los siguientes parámetros para el modelo:

Sea $C_{ij}$ el costo de abastecer al cliente j (para el total de su demanda) desde el centro de oferta i. Como se puede apreciar asumiremos que cada cliente debe ser abastecido por solo un oferente. Adicionalmente y para efectos de ilustración consideraremos 4 oferentes (potenciales) y 6 clientes.

Sea $K_{i}$ el costo de instalar el centro de oferta i. Por ejemplo, habilitar (localizar) el oferente 1 tiene un costo fijo de 3.500 unidades monetarias.

Sea $p_{ij}$ la preferencia que manifiesta el cliente j por ser abastecido por el oferente i. Asumiremos que un menor valor representa una mayor preferencia. Por ejemplo, el cliente 1 prefiere ser abastecido por los oferentes 4,1,3,2, respectivamente. En este sentido si se llegará, por ejemplo, solo a instalar el oferente 1 y 3, el cliente 1 debe ser abastecido del oferente 1 dado que de las 2 alternativas este oferente representa una mayor preferencia.

Dada las definiciones anteriores, el Modelo de Localización y Transporte con Preferencias de Clientes es el siguiente:

Donde $x_{i}$ es una variable binaria que adopta un valor 1 si se instala el centro (oferente) i (cero en caso contrario. Por otra parte $y_{ij}$ es una variable binaria que indica si el cliente j se abastece (exclusivamente) desde el oferente i (cero en caso contrario). Luego, la función objetivo representa la minimización de los costos de instalación de los oferentes y el transporte que se origina entre éstos y los clientes.

En cuanto a las restricciones tenemos:

(1) Determina que cada cliente sea abastecido desde un único centro de oferta.
(2) Los clientes pueden optar a ser abastecidos desde aquellos oferentes que hayan sido seleccionados.
(3) La preferencia de cada cliente corresponderá al promedio ponderado de columna correspondiente en la matriz de preferencias (asociada a dicho cliente) por la decisión de abastecimiento desde un oferente dado.
(4) Se impone a través de la preferencia calculada en (3) que cada cliente sea atendido por aquel oferente que le reporta la mayor satisfacción (menor puntuación en el ejemplo según lo descrito previamente).
(5) Las variables de decisión son binarias.

Luego de implementar en Solver el modelo de Programación Entera anterior se alcanzan los siguientes resultados:

Se observa que se instalan los centros de oferta 1, 2 y 4, que representa un costo de localización (total) de $9.000. En cuanto a las decisiones de distribución, el oferente 1 abastece al cliente 4; el oferente 2 abastece a los clientes 2 y 5 y finalmente el oferente 4 abastece a los clientes 1, 3 y 6. El costo total de transporte es de $12.062, de modo que el costo total es $21.062 (valor óptimo). Notar que cada cliente recibe los pedidos de su mejor alternativa posible (marcado con color verde). A continuación se encuentra disponible el archivo Excel con la implementación computacional del Modelo de Localización y Transporte con Preferencias para ser descargado.

[sociallocker]Localización y Transporte con Preferencias[/sociallocker]

Optimización de la Mezcla de Combustibles en una Refinería

El siguiente problema representa la optimización de la mezcla de combustibles en una refinería con el propósito de maximizar los beneficios asociados a su explotación. En este contexto este caso constituye una variante o extensión del Ejemplo de un Problema de Mezcla de Productos en Programación Lineal y otros conceptualmente similares como el Problema de Producción de Mezcla de Café, entre otros. A continuación los antecedentes de nuestro caso de estudio:

Problema de Mezcla de Combustibles

Una refinería compra 4 tipos de gasolinas no refinadas con las cuales puede fabricar hasta 3 tipos de combustibles para venta al público. La información se resume en la siguiente tabla:

Por ejemplo, la Gasolina No Refinada Tipo 1 tiene 68 Octanos y se dispone de un máximo de 4.000 barriles diarios, donde cada uno de estos barriles se compra a 23 Euros. Así mismo, por ejemplo, el Combustible 1 requiere un mínimo de 95 Octanos y su precio de venta es de 45 Euros el barril. Para el Combustible 1 en particular se establece un máximo de producción de 10.000 barriles diarios.

La refinería puede vender adicionalmente la gasolina no refinada a un precio de 39 Euros el barril si ésta tiene un octanaje mayor o igual a 90 Octanos. Alternativamente el precio de venta se reduce a 37 Euros el barril si el octanaje es inferior a 90 Octanos.

Formule un modelo matemático de Programación Lineal para ayudar a la refinería a maximizar sus ganancias diarias.

Variables de Decisión: Cada tipo de gasolina sin refinar tiene 4 usos posibles: ser vendida directamente o ser utilizada como insumo para producir Combustible tipo 1, 2 y 3.

Función Objetivo: Se desea maximizar la ganancia asociada al proceso de mezcla y venta de combustibles. Con verde se destaca los ingresos asociados a la venta de los 3 tipos de combustibles, con celeste los ingresos que provienen de la venta de gasolinas sin refinar y con color amarillo se descuentan los costos asociados a la compra de las gasolinas sin refinar.

Restricciones:

Disponibilidad de barriles diarios: Cada gasolina no refinada tiene 4 usos posibles: venta directa o como mezcla para elaborar combustible 1, 2 o 3. En cualquier caso su uso no podrá superar el máximo de barriles disponibles.

Octanaje mínimo: El octanaje de la mezcla de cada uno de los 3 combustibles (obtenido como un promedio ponderado de los octanajes de las respectivas gasolinas) debe al menos igualar el requerimiento mínimo establecido en este aspecto.

Límites de venta: No se puede vender más de 10.000 barriles diarios de Combustible 1 y adicionalmente no se puede vender menos de 15.000 barriles diarios de Combustible 3.

No negatividad: Las variables de decisión deben adoptar valores no negativos.

Obtenga la solución óptima y valor óptimo para el modelo utilizando Solver de Excel. Comente brevemente las características de la solución obtenida.

La solución óptima se observa en las celdas con color amarillo de la imagen anterior. Notar que no se produce Combustible tipo 2 y que el Combustible 3 se produce a máxima capacidad. La utilidad o valor óptimo es de 285.509,26 euros.

Analice las siguientes variantes del problema, explicando los resultados:

Aumento de la disponibilidad de la Gasolina No Refinada Tipo 4 a 5.000 barriles por día.

En este caso el valor óptimo aumenta en 10.629,63 euros en relación al valor óptimo original. El Precio Sombra de la restricción de disponibilidad de barriles diarios para la gasolina 4 (disponible en el archivo para descarga al final de este artículo) es de aproximadamente 15,185 con un aumento permisible para el lado derecho de 3.662,5 barriles (diarios). Luego un incremento en la disponibilidad de gasolina 4 en 700 barriles diarios genera una utilidad adicional del 700*15,185=10.629,5 (la diferencia con los 10.629,63 euros es sólo por efecto de la aproximación de decimales).

Aumento de los costos de las gasolinas con un octanaje menor a 90 en un 10%.

En este caso no se observa un cambio en la solución óptima en comparación al escenario inicial, no obstante las utilidades se ven reducidas dado el aumento en el costo de las gasolinas 1 y 2 (aquellas que tienen un octanaje inferior a 90 puntos).

Aumento de la demanda del Combustible 3 en 2.000 barriles diarios.

Este caso representa una merma en cuanto a las utilidades al ser más restrictivo que el problema original. Notar que ahora no se asignan gasolinas para venta directa y que también disminuye la cantidad de barriles diarios a fabricar del Combustible 1, llegado a 3.450.

¿Quieres tener la resolución en Solver de Excel de este modelo de optimización?

[sociallocker]Problema Refinación de Combustibles[/sociallocker]

Planificación de la Producción Multiproducto

El siguiente problema consiste en la formulación de un modelo de Programación Entera y posterior resolución computacional haciendo uso del complemento OpenSolver de Excel, específicamente en lo que se refiere a un modelo que permita encontrar la estrategia óptima para la Planificación de la Producción Multiproducto (es decir, 2 o más productos) y multiperiodo (2 o más períodos en el horizonte de evaluación). Referencias adicionales sobre esta clase de problemáticas pueden ser consultadas en la categoría Plan Maestro de la Producción (PMP) donde se presentan un importante volumen de ejercicios resueltos de planificación agregada. Dicho lo anterior a continuación presentamos el ejemplo objeto de nuestro análisis:

Una empresa desea optimizar la planificación de la producción de sus cinco productos principales para los primeros 6 meses del año 2016. Para el desarrollo de la tarea encomendada la empresa recolecta los siguientes antecedentes:

El proceso de fabricación es intensivo en mano de obra donde cada trabajador percibe un salario bruto de US$1.200 por una jornada de 160 horas de trabajo al mes.
El costo unitario de materiales y gastos generales, excluyendo el trabajo es de US$12 para A, US$14 para B, US$9 para C, US$13 para D y US$8 para E.
El costo de mano de obra de producción en tiempo extra se paga con un recargo de un 50% respecto a la hora trabajada en horario normal. No obstante por política de la empresa se establece un máximo de 200 horas hombre en tiempo extraordinario para cada mes, exceptuando Enero y Febrero donde el límite corresponde a 100 horas (por acuerdos con el sindicato).
El costo mensual de almacenar una unidad de cualquier producto en inventario es de US$4 por unidad. La bodega tiene una capacidad de almacenamiento de 250 unidades.
El tiempo de producción por unidad es de 5 horas para A, 6 horas para B, 8 horas para C, 4 horas para D y 3 horas para E.
La contratación de personal de producción considera un costo único de US$1.500 (adicional al sueldo) por concepto de capacitación y entrenamiento.
Para la reducción de horas de trabajo o despido considere en promedio: un sueldo de US$1.200 y una antigüedad de 2 años. Por política de estabilidad laboral se establece un máximo de despido de 6 trabajadores durante el primer semestre.
El inventario inicial corresponde a 120 y 80 unidades para los productos B y C respectivamente. No se dispone de inventario inicial para el producto A, D y E.
La planilla de trabajadores al 31 de Diciembre de 2015 es de 55 trabajadores.
Es posible dejar demanda pendiente del producto A y D asumiendo un costo unitario de US$25 en cada caso, la cual no expira y sólo se posterga para un próximo mes. No obstante la empresa requiere que como máximo queden 500 unidades de demanda pendiente (en total para la suma de ambos productos) a fines de Junio de 2016.
En cuanto al producto B, éste se puede comprar adicionalmente a un proveedor a un costo unitario de US$75. Adicionalmente el costo fijo de gestionar un pedido al proveedor del producto B (independiente del tamaño del pedido) es de US$200.
En cuanto al producto E, éste se puede comprar adicionalmente a un proveedor a un costo unitario de US$35. Adicionalmente el costo fijo de gestionar un pedido al proveedor del producto E (independiente del tamaño del pedido) es de US$150.

Formule y resuelva un modelo de optimización matemática que permita determinar la política operacional que minimice los costos totales en el horizonte de planificación y cumpla con las condiciones expuestas.

Planificación de la Producción Multiproducto

Variables de Decisión:

Notar que se dispone de 5 productos y 6 períodos. En este contexto y con el objetivo de lograr una notación más compacta se utilizan los índices i y t para representar los productos y períodos (meses), respectivamente.

Parámetros:

La definición de parámetros no es estrictamente necesaria y se realiza de modo de establecer un caso más general para el problema que facilita (compacta) la notación requerida para definir el modelo. Se puede apreciar que no todos los datos factibles de poder representar con parámetros ha sido llevado a cabo, lo cual corresponde a una decisión arbitraria la que sin embargo no afecta los resultados.

Función Objetivo:

Se busca minimizar los costos totales de la planificación multiproducto y multiperiodo. Los costos involucrados son (en orden): producción, inventario, mano de obra en tiempo normal, mano de obra en sobretiempo, contratación, despido, demanda pendiente, compra del producto B y compra del producto E.

Restricciones:

Balance de Inventario: Para el caso del producto A y D se puede utilizar demanda pendiente y para los productos B y E se pueden realizar compras. En este caso sólo los requerimientos del producto C deben ser satisfechos de forma exclusiva a través de la producción e inventario.

Balance de Trabajadores: La cantidad de trabajadores disponibles en un mes para funciones de producción será igual a los disponibles en el mes anterior, más los contratados en el mes y menos los despedidos en dicho mes.

Capacidad de Producción: El lado izquierdo de la restricción representa la cantidad de horas requeridas en un mes para la producción de los 5 productos, lo cual no podrá superar las horas disponibles (siendo éstas las horas en tiempo normal más las horas que eventualmente se utilicen en sobretiempo).

Capacidad de la Bodega: Para cada mes del horizonte de planificación la cantidad de productos almacenados en inventario (suma de todos los productos) no podrá superar la capacidad de almacenamiento de la bodega de 250 unidades.

Máximo de Compras B y E: La cantidad máxima de compra para el producto B y E dependerá si se adopta la decisión de realizar una compra en el mes respectivo. En dicho caso la cantidad máxima a comprar corresponderá a los parámetros o constantes grandes $M_{B}$ y $M_{E}$ , respectivamente. Por ejemplo un valor para $M_{B}$ podría ser 3.152 que corresponde a la suma de la demanda del producto B del mes 1 al mes 6.

Máxima Cantidad de Despidos: Durante el horizonte de planificación no se pueden despedir más de 6 trabajadores.

Máximo Demanda Pendiente Mes 6: Al final del mes 6 no debe quedar más de 500 unidades de demanda pendiente para el producto A y D (en conjunto).

No Negatividad y Enteros: Las variables de decisión deben adoptar no negativos y enteros (exceptuando las variables binarias).

La implementación computacional con OpenSolver del modelo de optimización anterior entrega los siguientes resultados. Las celdas en color amarillo corresponden a las variables de decisión del problema definidas inicialmente que satisfacen las restricciones impuestas (solución factible).

El valor óptimo corresponde a US$599.770 que corresponde al costo mínimo asociado al plan de producción. A continuación se desglosa dicho costo total en los distintos ítems de costos según lo detallado anteriormente.

¿Quieres tener la planilla Excel con la resolución en OpenSolver de este problema?.

[sociallocker]

MUCHAS GRACIAS!. DESCARGA AQUÍ EL ARCHIVO

[/sociallocker]

Problema de Producción y Mezcla de Café en Programación Lineal

Como hemos abordado anteriormente en el Blog, los modelos de Programación Lineal constituyen una alternativa metodológica para enfrentar Problemas de Mezcla de Productos. En este contexto a continuación presentamos la formulación de un modelo de optimización lineal junto a su implementación computacional haciendo uso de Solver de Excel el cual fue enviado por uno de nuestros usuarios de Costa Rica.

Problema de Producción y Mezcla

Una firma de café produce dos tipos de mezclas: suave y suavísimo. En la planta se cuenta con:

Por ejemplo, el costo por libra del café colombiano es $52, el cual contiene 2,5% de cafeína y se dispone de 20.000 libras para la producción de mezclas. Adicionalmente los productos que se comercializan en el mercado son:

Es decir, la mezcla suave se vende a $72 la libra, con una demanda de 35.000 libras y puede contener como máximo un 2,2% de cafeína.

Variables de Decisión:

Donde i=1,2,3 representa los países de origen Colombia, Brasil y México, respectivamente y j=1,2 la mezcla Suave y Suavísimo, respectivamente.

Función Objetivo:

Se busca maximizar la ganancia (diferencia entre los ingresos menos los costos) asociada al plan de producción y venta de las mezclas de café. Con color amarillo se destaca los ingresos por venta correspondientes a las variedades Suave y Suavísimo y en color verde los costos asociados a la utilización de libras de café colombiano, brasileño y mexicano.

Restricciones:

Disponibilidad de Café: para cada país de origen la cantidad de libras utilizadas para el proceso de mezcla no debe superar la disponibilidad.

Demanda de Mezclas: se debe satisfacer la demanda de cada mezcla de café a través de la asignación de las variedades provenientes de los 3 países de origen.

Porcentaje Máximo de Cafeína: cada mezcla no debe superar un porcentaje máximo de cafeína admitido.

No Negatividad: naturalmente las variables de decisión deben satisfacer las condiciones de no negatividad y se permiten valores fraccionarios: $X_{ij}\geqslant 0$ .

Al implementar el modelo de Programación Lineal anterior haciendo uso de Solver de Excel se alcanza la siguiente solución óptima y valor óptimo:

La ganancia total (valor óptimo) es de $1.385.000, la cual se obtiene al asignar 20.000 libras de café Colombiano para la producción de la variedad Suave, 25.000 libras de café Brasileño para la producción de la mezcla Suavísimo y 15.000 libras de café Mexicano para la producción de la variedad Suave (solución óptima).

¿Quieres tener el archivo Excel con la resolución en Solver de este problema?.

[sociallocker]

MUCHAS GRACIAS!. DESCARGA AQUÍ EL ARCHIVO

[/sociallocker]

Método de Suavizamiento Exponencial Ajustado a la Tendencia (Suavización Exponencial Doble)

Los métodos de pronóstico de demanda de series de tiempo como Suavizamiento Exponencial (Alisamiento Exponencial) y Media Móvil Simple, tienen un mejor desempeño cuando el patrón histórico de la demanda no evidencia tendencia ni estacionalidad marcada como se observa en el gráfico a continuación. En particular en el caso del Suavizamiento Exponencial, si la serie de tiempo tiene una tendencia creciente se tenderá a subestimar la demanda real y de forma análoga cuando la demanda presenta una tendencia decreciente el alisamiento exponencial tenderá a sobrestimar el valor de la demanda real.

Para mejorar la calidad del pronóstico al observar una tendencia en la serie de tiempo se puede considerar el método de Suavizamiento Exponencial Doble, conocido también como Suavizamiento Exponencial Ajustado a la Tendencia o Método de Holt. Cabe recordar que una tendencia es un incremento o decremento sistemático en el promedio de la serie a través del tiempo. Luego, el método de Suavizamiento Exponencial Doble busca incorporar la tendencia en un pronóstico suavizado exponencialmente.

Para su cálculo se requieren dos constantes de suavizamiento: α y β, realizándose las siguientes estimaciones:

Donde $A_{t}$ es el promedio suavizado exponencialmente de la serie en el período t, $T_{t}$ el promedio suavizado exponencialmente de la tendencia en el período t, α el parámetro de suavizamiento para el promedio, con un valor entre 0 y 1, β el parámetro de suavizamiento para la tendencia, con un valor entre 0 y 1 y $F_{t+1}$ el pronóstico para el período t+1.

Ejemplo Suavizamiento Exponencial Doble

Un laboratorio clínico realiza exámenes de sangre cada semana. En promedio el laboratorio realizó 28 análisis de sangre cada semana durante las últimas cuatro semanas. Adicionalmente la tendencia en ese período fue de tres muestras adicionales por semana. La demanda en esta semana fue de 27 análisis de sangre. Si α=0,2 y β=0,2 se requiere calcular el pronóstico correspondiente a la semana próxima.

En el caso que el número real de exámenes en la semana 2 resultará ser 44, entonces el pronóstico actualizado para la semana 3 sería el siguiente:

A continuación se presenta un gráfico con el comportamiento de la demanda real y el pronóstico con Suavizamiento Exponencial Doble para los datos del ejemplo anterior.

Finalmente ponemos a disposición de nuestros usuarios una plantilla que permite editar los datos de la demanda real (celdas color amarillo claro) y los datos iniciales $A_{0}$ y $T_{0}$ (celdas celestes), junto con el valor de los parámetros α y β. Al modificar la información de dichas celdas se actualiza automáticamente los pronósticos, el error de cada período, el MAD (Desviación Media Absoluta) y el gráfico que contrasta el comportamiento de la demanda real con el pronóstico.

En caso de obtener un error del tipo #VALUE! ingrese los valores de α y β utilizando . (punto) como separador de decimal, por ejemplo, α=0.2.