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

Problema de Corte Ensamblado y Producción de Sillas resuelto con Solver

Una empresa de Rústicos “El Viejo Baúl” fabrica entre muchos otros productos tres tipos de sillas A, B y C, las cuales se venden a precio de 11, 13 y 12 dólares cada una y respectivamente. Las sillas pasan por tres procesos, Corte, Ensamblado y Pintado, para lo cual se dispone máximo de 17, 13 y 15 horas respectivamente a la semana para dedicar a estas operaciones a estos productos. La silla tipo A requiere 3 horas para corte, 1 hora para ensamblado y 3 horas para pintura. La silla tipo B requiere 1 hora para corte, 4 horas para ensamblado y 3 horas para pintura. Y finalmente la silla tipo C, requiere de 5 horas para corte, 2 para ensamblado y 2 horas para pintura. De acuerdo a la anterior información:

a. Resuelva el problema con variables continuas y señale los resultados para cada variable.

Variables de Decisión: Se estable el nivel de producción semanal para cada una de las variedades de silla según se detalla a continuación:

Función Objetivo: Maximizar los ingresos semanales asociados a la producción y venta de las sillas.

Restricciones: En los procesos de corte, ensamblado y pintura se debe respetar la disponibilidad de horas semanales. Adicionalmente se deben satisfacer las condiciones de no negatividad.

La implementación computacional del problema anterior con Solver de Excel permite alcanzar los siguientes resultados:

Donde la solución óptima es A=1,914286, B=1,828571 y C=1,885714 con valor óptimo V(P)=67,45714.

b. Modifique las condiciones de las variables y elíjalas enteras (integer) y observe el cambio entre la respuesta del punto a y esta nueva hallada.

Al definir las variables de decisión enteras estamos frente a un modelo de Programación Entera (siendo el escenario inicial un problema de Programación Lineal). Los resultados son:

La solución óptima es A=1, B=2 y C=2 con valor óptimo V(PE)=61.

c. Concluya qué sucedió entre variables continuas y variables enteras.

Es importante observar que el dominio de soluciones factibles del problema entero (parte b) es un subconjunto del dominio de soluciones factibles del problema lineal (parte a). Por tanto es natural que al no obtener una solución con valores enteros para las variables de decisión en el problema inicial, el valor óptimo necesariamente disminuirá en la variante entera de dicho problema de maximización (V(PE)<V(P)). También se puede destacar que la solución entera no necesariamente se alcanza al aproximar los resultados fraccionarios de una solución de un problema lineal al entero inferior o superior más cercano. En consecuencia, para abordar de forma eficiente la resolución de un modelo que considere valores enteros para las variables de decisión requiere de una alternativa algorítmica específica como por ejemplo el Método Branch and Bound.

A continuación encontrarás un enlace de descarga del archivo Excel utilizado para la resolución del problema de corte, ensamblado y producción de sillas. En el archivo se incluyen 2 hojas que corresponden a la parte a) y b) del problema propuesto. Produccion de Sillas.

Problema de Producción y Transporte resuelto con Solver

El siguiente problema de producción y transporte fue enviado por uno de nuestros usuarios de Colombia de la ciudad de Santa Cruz de Lorica: “Una compañía que fabrica Cereal de Maíz tiene dos campos de siembra, el Campo I y el Campo II, y dos molinos, A y B. Las capacidades de suministro mensual de maíz de los Campos I y II son 125 y 245 toneladas, respectivamente. El molino A requiere por lo menos 190 toneladas de Maíz al mes y el B por lo menos 158 toneladas mensuales. Los costos de transporte en unidades monetarias por tonelada de cada Campo a cada molino son los siguientes: 2 del Campo I al molino A, 3 desde el Campo I al molino B, 4 desde el Campo II al molino A, y 5 desde el Campo II al molino B”.

¿Qué cantidad de Maíz debe transportarse desde cada Campo I y II a cada molino A y B de forma que se logre minimizar el costo total de transporte? ¿Cuál es ese costo mínimo? ¿Hay algún envío que no debe realizarse para conseguir dicho costo mínimo?.

Para una mejor comprensión del problema anterior representaremos gráficamente la información anterior donde se puede apreciar los distintos oferentes (Campos) y demandantes (Molinos), además de la capacidad de producción y demanda (en toneladas mensuales) junto a los costos de transporte para cada combinación origen destino.

Problema de Producción y Transporte

1. Variables de Decisión: (con i=I,II y j=A,B)

2. Función Objetivo: Minimizar los costos que se asumen mensualmente por el transporte de cereal desde los campos a los molinos.

3. Restricciones:

Capacidad de Producción de los Campos: La cantidad de toneladas que se transporte desde cada campo a cada uno de los molinos no puede superar su capacidad de producción.

Demanda de los Molinos: Cada molino debe recibir un mínimo de toneladas mensuales de cereal desde los campos.

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

A continuación se detalla la implementación computacional del modelo de optimización haciendo uso de Solver de Excel:

Notar que la celda F9 es una fórmula asociada a la función objetivo que pondera los costos unitarios de transporte por las toneladas transportas en cada combinación de origen (campos) destino (molinos). La celda E3 es la suma de C3 y D3 (análogamente E4=C4+D4) representando las restricciones de capacidad. De similar forma la celda C5 es una fórmula que considera la suma de las celdas C3 y C4 (por supuesto D5=D3+D4). Una vez generada la estructura del modelo de Programación Lineal se carga éste en la interfaz de Solver:

La solución óptima (celdas color amarillo) consiste en transportar 125 toneladas del Campo I al Molino B y el Campo II envía 190 y 33 toneladas a los Molinos A y B, respectivamente. El valor óptimo es de 1.300 unidades monetarias.

¿Quieres tener el archivo Excel con la resolución en Solver de este problema?. Recomiéndanos en Facebook o Google+ utilizando la herramienta de redes sociales a continuación y accede de forma gratuita e inmediata a la descarga del archivo.

[sociallocker]Problema de Producción y Transporte[/sociallocker]

Como resolver un modelo de Programación Lineal con OpenSolver

OpenSolver es una excelente complemento de Excel que permite resolver modelos de optimización. En el siguiente artículo se describe cómo resolver un modelo de Programación Lineal con esta herramienta (previa descarga e instalación de OpenSolver en Excel 2010). Para fines académicos consideraremos un modelo lineal con 2 variables de decisión, no obstante se puede extender su aplicación a problemas de mayor tamaño sin inconvenientes.

A continuación necesitamos preparar una planilla Excel que considere los parámetros y variables del modelo (este paso es similar a la carga de un modelo en Solver de Frontline). Se puede apreciar que las celdas B2 y C2 (color amarillo) han sido asignadas a las variables de decisión y la función objetivo (celda azul) corresponde a la celda E2 que es una fórmula que vincula las variables de decisión y los respectivos parámetros que ponderan a éstas. Finalmente las celdas D5 y D6 son fórmulas que representan el “lado izquierdo” de las restricciones del problema (por ejemplo la celda D5 corresponde a B2*B5+C2*C5 o equivalentemente SUMAPRODUCTO(B2:C2;B5:C5)).

Una vez completado el paso anterior se debe ejecutar OpenSolver cuyo menú esta disponible en la pestaña de “Datos” de Excel. Luego se selecciona “Model…” según se muestra a continuación:

La interfaz para implementar el modelo es bastante similar a la versión tradicional de Solver (Frontline). Se define la celda objetivo (E2) en maximización; a continuación se selecciona el rango de variables de decisión (según se muestra en la siguiente imagen) y las restricciones. Si intentas replicar la estructura del ejemplo que desarrollamos en este artículo se debería ver así:

Luego seleccionamos “Save Model” (cambiará la estructura de la planilla la cual adoptará colores lo cual es una de las características de OpenSolver que hacen de este complemento una herramienta intuitiva para el usuario).

Finalmente seleccionamos “Solve”:

El programa se ejecutará y proporcionará (de existir) la solución óptima (X=0 e Y=60) y valor óptimo (V(P)=1.200) del problema de optimización:

Los resultados alcanzados son coincidentes con los alcanzados en la resolución gráfica del problema que hemos abordado en el artículo “Qué significa un Precio Sombra igual a Cero en Programación Lineal“ según muestra la imagen a continuación:

A continuación puedes descargar el archivo con la resolución en OpenSolver de este problema de modo de que puedas familiarizarte con este complemento de Excel: Modelo de Programación Lineal resuelto con OpenSolver

Intervalo de Confianza para un Pronóstico de Demanda

En el siguiente artículo abordaremos cómo calcular un Intervalo de Confianza para un Pronóstico de Demanda, lo cual permite incorporar de forma explícita el impacto que tiene la incertidumbre en la planificación de las actividades comerciales y operacionales de una empresa.

Para ello utilizaremos el Método de Alisado Exponencial o Suavizamiento Exponencial el cual hemos descrito previamente en nuestro sitio. (Ver también: Suavizamiento Exponencial Doble Ejercicios Resueltos).

Consideremos una serie histórica con la demanda de un producto para un periodo de 12 semanas. Se requiere desarrollar un intervalo de confianza del 95% para el Pronóstico de Demanda de la semana 13 utilizando el Método de Suavizamiento Exponencial Simple con α=0,3.

Para ello adoptaremos el supuesto que los errores del pronóstico se distribuyen normalmente lo cual es algo que por supuesto se puede verificar con una dedicación mayor de trabajo y para lo cual se puede utilizar un software de análisis estadístico como Easyfit.

En este contexto la tabla a continuación se muestra el pronóstico comenzando a contar de la semana 4 (esta es una decisión arbitraria dado que podría haber comenzado antes).

Notar que el primer pronóstico corresponde simplemente a la Media Móvil Simple de las primeras 3 semanas.

Luego el pronóstico de la semana 5 se obtiene de la aplicación de la siguiente fórmula: F5=F4+α(A4-F4) que al reemplazar se obtiene F5=1.775+0,3*(1.860-1.775)=1.800,5~1.801 (hemos aproximado éste y los otros pronósticos al entero más cercano según se puede apreciar en la fórmula de Excel utilizada):

Ahora necesitamos calcular la desviación estándar del error del pronóstico la cual se obtiene simplemente evaluando en los datos de la tabla anterior según se muestra a continuación:

Finalmente el intervalo de confianza de un 95% para el pronóstico de la semana 13 se obtiene: (notar que F13=1.766+0,3*(1.780-1.766)=1.770,2~1.770)

El resultado anterior es consistente con el proporcionado por la herramienta de Cálculos de Probabilidad de Geogebra donde para una distribución de probabilidad normal (recordar el supuesto de normalidad del error adoptado anteriormente) con media μ=1.770 (F13) y desviación estándar SF=71, el área achurada en color azul representa los valores contenidos en el intervalo de confianza de un 95% (% del área bajo la curva achurada).

Cómo utilizar una Regresión Lineal para realizar un Pronóstico de Demanda

El Método de Mínimos Cuadrados o Regresión Lineal se utiliza tanto para pronósticos de series de tiempo como para pronósticos de relaciones causales. En particular cuando la variable dependiente cambia como resultado del tiempo se trata de un análisis de serie temporal.

En el siguiente artículo desarrollaremos un Pronóstico de Demanda haciendo uso de la información histórica de venta de un producto determinado durante los últimos 12 trimestres (3 años) cuyos datos se observan en la siguiente tabla resumen:

La ecuación de mínimos cuadrados para la regresión lineal es la que se muestra a continuación donde β0 y β1 son los parámetros de intercepto y pendiente, respectivamente:

Estimar los valores de dichos parámetros es sencillo haciendo uso de una planilla Excel tal como muestra la tabla a continuación:

Luego evaluamos en las ecuaciones presentadas anteriormente para obtener los valores de β0 y β1:

Una vez obtenido los parámetros de la regresión lineal se puede desarrollar un pronóstico de demanda (columna color naranja) evaluando en la ecuación de la regresión para los distintos valores de la variable independiente (x).

Por ejemplo, para el primer trimestre el pronóstico es: Y(1)=441,71+359,61*1=801,3.

Observación: los valores de los pronósticos han sido redondeados arbitrariamente a un decimal para mayor comodidad.

Notar que con la información que hemos obtenido podemos calcular el MAD y la Señal de Rastreo y utilizar estos indicadores para validar la conveniencia de utilizar este procedimiento como dispositivo de pronóstico.

Adicionalmente puede resultar de interés consultar el artículo Ejemplo de una Regresión Lineal Múltiple para un Pronóstico con Excel y Minitab que muestra cómo abordar el caso de realizar una regresión lineal con más de una variable independiente (explicativa).

Siguiendo con nuestro análisis a continuación podemos desarrollar un pronóstico de demanda para los próximos 4 trimestres (un año) que corresponden a los trimestres 13, 14, 15 y 16:

Y(13)=441,71+359,61*13=5.116,64
Y(14)=441,71+359,61*14=5.476,25
Y(15)=441,71+359,61*15=5.835,86
Y(16)=441,71+359,61*16=6.195,47

Si bien el procedimiento anterior es válido puede ser resumido haciendo uso de las herramientas de análisis de datos de Excel o simplemente realizando un ajuste de una regresión lineal en un gráfico de dispersión de la misma forma que abordamos en el articulo sobre el Método de Descomposición. Para ello luego de realizar el gráfico nos posicionamos en una de las observaciones y luego botón derecho del mouse para seleccionar “Agregar línea de tendencia…”.

Luego en la interfaz de Excel activamos las opciones “Presentar ecuación en el gráfico” y “Presentar el valor R cuadrado en el gráfico” (este último indicador según se aborda en los cursos de estadística consiste en una medida de la bondad de ajuste de la regresión).

Notar que los valores obtenidos para los parámetros de la regresión son similares salvo menores diferencias por efecto de aproximación.

Otra opción disponible para ajustar una Regresión Lineal haciendo uso de Excel es a través del Complemento llamado Herramientas para análisis.

Su activación es simple: en el menú Archivo (esquina superior izquierda en Excel) ir a Opciones, luego Complementos, a continuación a la derecha de donde dice Complementos de Excel presionar Ir… y luego activar la Herramientas para análisis.

Una vez activada las Herramientas para análisis, se puede encontrar ésta abajo del complemento Solver en el menú de Datos.

Luego de las opciones disponibles que nos ofrece este complemento seleccionamos Regresión.

A continuación seleccionamos el Rango Y de entrada las celdas correspondientes a la variable dependiente (Ventas) y en Rango X de entrada las celdas correspondientes a la variable independiente (Trimestre).

Debemos activar adicionalmente la casilla Residuos si deseamos obtener un pronóstico para las ventas del Trimestre 1 al Trimestre 12 (junto al cálculo del error o residuo de la estimación).

Finalmente presionamos Aceptar lo que generará una nueva hoja en nuestra planilla de cálculo.

Un extracto de los resultados es el que se presenta a continuación, donde en color celeste se destaca los coeficientes asociados a los parámetros de la regresión lineal β0 y β1, respectivamente, y en color naranjo el pronóstico obtenido para cada uno de los doce trimestres al utilizar la ecuación de la regresión.

Por ejemplo: Y(1)=441,67+359,61*1=801,28. El residuo o error correspondiente para dicho período (Trimestre 1) es: $e_{1}=A_{t}-F_{t}=600-801,28=-201,28$ .

¿Quieres tener el archivo Excel con el ajuste de la Regresión Lineal de este problema?.

[sociallocker]MUCHAS GRACIAS!. DESCARGA AQUÍ EL ARCHIVO[/sociallocker]