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

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.

Comparación de un Servicio General y Específico para la Atención de Clientes (Teoría de Colas)

En los sistemas de atención de público se suelen encontrar distintos esquemas o configuraciones en las que se organiza la espera de los clientes antes de ser atendidos. Se pueden observar casos donde los clientes se ordenan en una fila para ser atendidos por un servidor, otros donde los clientes se ordenan en una fila común y luego son atendidos por un servidor en la medida que este disponible (esquema frecuente en las cajas rápidas en los supermercados). En este contexto el siguiente artículo evaluaremos un sistema de atención general y uno específico, comparando desde un punto de vista cuantitativo el desempeño de cada caso a través de la simulación del comportamiento de la Línea de Espera.

Un banco pequeño en un centro comercial tiene dos cajeros. Uno maneja al público general y uno maneja a los clientes regulares. Cada tipo de clientes llega con una media de 20 por hora (para una proporción de la llegada total de 40 clientes por hora). El tiempo de servicio para ambos cajeros promedia 2 minutos (sigue una distribución exponencial, es decir, se verifica el cumplimiento de la Propiedad de Falta de Memoria o Amnesia de la Distribución Exponencial). El gerente del banco está considerando cambiar el orden de atención para permitir que cada cajero pueda manejar ambos tipos de clientes. Debido a que los cajeros tendrían que manejar ambos tipos de trabajos, sus eficiencias disminuirían a un tiempo de servicio de 2,2 minutos por cliente. ¿Se debe cambiar al nuevo esquema de atención?.

Servicio General y Específico para la Atención de Clientes

El servicio específico implica que cada cajero atiende de forma exclusiva un tipo de clientes sin existir colaboración entre los mismos. Una representación esquemática de dicho escenario se muestra a continuación:

En el artículo Simulación de una Línea de Espera M/M/1 (Teoría de Colas) en Excel se detalla el procedimiento para obtener los indicadores de desempeño de una línea de espera con un servidor, donde el tiempo entre llegadas de los clientes se distribuye exponencial, al igual que los tiempos de servicios. En el ejemplo la atención para cada tipo de clientes muestra los siguientes resultados:

El número esperado de clientes en el sistema Ls es de 2 clientes (en este caso la fila y atención para cada tipo de cliente constituye un sistema), el número esperado de clientes en la fila Lq es de 1,333, el tiempo promedio que un cliente esta en el sistema Ws es 0,1 horas, es decir, 6 minutos. Finalmente el tiempo que un cliente esta en la fila Wq es de 0,06667 horas (4 minutos).

En el caso de un servicio general, en donde existe colaboración entre los servidores, la capacidad de cada uno de ellos baja a µ=60/2,2[clientes/hora].

Los indicadores de desempeño son: Ls=3,17307 (considerando los 2 tipos de clientes, es decir, en promedio se espera tener menos clientes en el sistema que el caso del servicio específico donde en total se esperan, en promedio, 4 clientes en el sistema); el tiempo promedio que un cliente esta en el sistema Ws es 0,07933 horas (aprox 4,76 minutos); el número esperado de clientes en la fila Lq es de 1,7064 y el tiempo que en promedio un cliente esta en la fila Wq es de 0,04266 horas (2,56 minutos).

Se concluye que si bien en nuestro ejemplo la capacidad de cada uno de los servidores baja al atender los 2 tipos de clientes, esto se ve compensado por el efecto de colaboración que se genera entre los mismos, lo que permite bajar el tiempo que en promedio un cliente esta en el sistema y en la fila. Estos aspectos claramente son valorados desde la perspectiva de los clientes y deberían ser tomados en cuenta al momento de decidir si se cambia el esquema original de atención de clientes.

¿Quieres tener el archivo Excel con la planilla de Simulación de una Línea de Espera utilizada en este ejemplo?

[sociallocker]

MUCHAS GRACIAS!. DESCARGA AQUÍ EL ARCHIVO

[/sociallocker]