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

Por qué no aparece el Informe de Confidencialidad (o Informe de Sensibilidad) en Solver de Excel

Cuando resolvemos un modelo de Programación Lineal en Solver de Excel tenemos la posibilidad de generar una serie de informes de respuesta entre los cuales destaca el denominado «Confidencialidad» (o «Sensibilidad» en versiones de Excel anteriores) que resume los principales resultados relativos al análisis de sensibilidad. Lo anterior es fácilmente accesible en el módulo de Resultados de Solver en una interfaz similar como la que se presenta a continuación:

Sin embargo la opción de obtener el Informe de Confidencialidad no siempre está disponible. Para ello consideremos que nos interesa resolver el siguiente modelo de Programación Entera:

A continuación desarrollamos la implementación computacional según se muestra en la imagen a continuación:

Se puede apreciar que la celda E3 es la fórmula de la función objetivo que representa la ponderación de los parámetros de la misma (10 y 16) por los valores que adquirirán las celdas que definiremos como variables de decisión (B3 y C3). Adicionalmente las celdas D6 y D7 también son fórmulas que considera la ponderación de los parámetros del lado izquierdo de las restricciones por las variables de decisión. Luego, se carga el modelo en Solver de Excel en la interfaz «Parámetros de Solver»:

Para obtener los resultados sólo es necesario presionar «Resolver» donde se actualizará en la planilla los resultados con la solución óptima X=2 e Y=2 y valor óptimo V(PE)=52. Notar sin embargo que el informe de Confidencialidad ya no se encuentra disponible.

¿Por qué sucede ésto?. Básicamente por qué los informes de sensibilidad en Solver se pueden obtener si el problema que se implementa es de naturaleza continua (como lo que sucedería en este mismo problema si se omiten las condiciones de integralidad para las variables de decisión lo que daría lugar a un modelo de Programación Lineal). Por el contrario un modelo de Programación Entera como el que hemos utilizado en este artículo tiene un dominio de factibilidad discreto. Lo anterior queda en evidencia en la siguiente representación gráfica del problema realizada con Geogebra:

El dominio de factibilidad continuo del modelo de Programación Lineal (omitiendo las condiciones de enteros para las variables de decisión) corresponde al área achurada denotada por el polígono que tiene por los vértices A, B, C y D. En cuanto al modelo de Programación Entera el dominio de factibilidad es discreto y se puede enumerar, lo cual corresponde a las coordenadas que representan los puntos A, E, F, B, I, H, G, J, K, C, M, L y D. En este contexto se debe recordar que uno de los supuestos básicos de la Programación Lineal es la proporcionalidad el cual ya no es admisible cuando nos enfrentamos a un problema de Programación Entera.

En consecuencia, si en la utilización de Solver de Excel queremos analizar el impacto que tiene la modificación de los parámetros del modelo en la resolución de un modelo de Programación Entera (principalmente en lo que se refiere a la solución óptima y valor óptimo) se propone reoptimizar modificando la(s) celda(s) que sean necesarias y ejecutar el programa nuevamente.

Qué significa un Precio Sombra igual a Cero en Programación Lineal

Según hemos abordado en artículos anteriores el Precio Sombra de una restricción representa la tasa de cambio del valor óptimo ante una modificación marginal del lado derecho de una restricción. Se entiende por «marginal» aquella modificación que no cambia la geometría del problema, es decir, que la nueva solución óptima se puede encontrar a través de la resolución del sistema de ecuaciones al que da origen las restricciones activas originales (previa actualización del parámetro que estamos modificando). En este contexto el precio sombra puede ser un valor positivo, negativo o cero y en particular nos referiremos a este último caso en este artículo.

Consideremos el siguiente modelo de Programación Lineal en 2 variables:

El problema anterior lo podemos resolver gráficamente utilizando Geogebra, que da origen a un problema con infinitas soluciones óptimas en el tramo de recta que une los vértices B y C y que se puede denotar de forma general por: (X,Y)=λ(0,60)+(1-λ)(10,45) con λ en el intervalo entre [0,1]. El valor óptimo en consecuencia es V(P)=1.200.

¿Cuáles son las restricciones activas en el óptimo?. Por ejemplo si consideramos el vértice B (solución óptima) las restricciones activas son 3X+2Y<=120 y X>=0, sin embargo si seleccionamos el vértice C (también solución óptima) las restricciones activas son 5X+2Y<=140 y 3X+2Y<=120. Dado lo anterior ¿aumentará el valor óptimo si se dispone de unidades adicionales del recurso que representa el lado derecho de la restricción 5X+2Y<=140?.

Para ello buscaremos mantener activas las restricciones del vértice C identificando la máxima variación (aumentando el valor del lado derecho) que conserve las restricciones activas originales (esto genera un desplazamiento paralelo de la restricciones que hemos representado con la línea color punteada color rojo) lo cual se alcanza en la coordenada (X,Y)=(40,0). De forma análoga para determinar la mínima variación para el lado derecho desplazamos en un sentido de decrecimiento la restricción buscando conservar las restricciones activas originales hasta la coordenada (X,Y)=(0,60) (línea punteada color naranjo).

Evaluamos en la fórmula para el cálculo del precio sombra obteniendo lo siguiente:

Por tanto el precio sombra de la restricción 1 (5X+2Y<=140) es cero lo cual indica que si aumenta o disminuye el valor del parámetro que representa su lado derecho (actualmente b1=140) en el intervalo entre [120,200] no se verá afectado el valor óptimo del problema. Lo anterior es consistente con lo obtenido a través del informe de confidencialidad de restricciones de Solver de Excel:

En general un precio sombra igual a cero significa que la modificación del parámetro que representa el lado derecho de la respectiva restricción (en un intervalo que conserva la geometría del problema) no tiene un impacto en el valor óptimo del problema. Sin embargo, existen casos especiales como los problemas de Programación Lineal que admiten infinitas soluciones (como el descrito en este artículo) donde una restricción con precio sombra igual a cero puede ser activa en uno de los vértices óptimo (el caso más usual es que una restricción con precio sombra igual a cero no sea activa en el óptimo).

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]

Cómo secuenciar n Trabajos en una Máquina para minimizar Setup

El orden o secuencia en el cual se programan n trabajos en una máquina es significativo a la hora de estimar los tiempos de setup para pasar de un determinado trabajo a otro o incluso comenzar con cualquiera de ellos al inicio de la planificación.

En este contexto se reconoce formalmente como tiempo de setup la cantidad de tiempo necesario para preparar una máquina para realizar una operación diferente y cumplir con las especificaciones del cliente (estos tiempos de setup pueden representar por ejemplo calibración de la máquina, cambio de formatos, limpieza, mantención preventiva, etc).

En el siguiente artículo formularemos y resolveremos un modelo de Programación Entera que permita encontrar una secuenciación óptima de n trabajos en una máquina, lo cual será contrastado con la solución que se puede obtener por simple enumeración de secuencias, naturalmente para un número de trabajos «pequeño» (para fines académicos).

En general si se deben programar n trabajos en una máquina, la cantidad de secuencias posibles son n!.

Por ejemplo si tenemos 3 trabajos (n=3) que debemos asignar a una máquina la cantidad de secuencias posibles son 6 (3!=3*2*1). Lo anterior implica que aún para centros de trabajos pequeños es útil contar con un procedimiento que permita identificar aquella secuencia que minimice el makespan sin tener la necesidad de hacer una evaluación exhaustiva de cada una de ellas.

Consideremos un conjunto de trabajos que necesitan ser asignados a una cierta máquina, todos los cuales están disponibles al inicio de la programación y se conoce los tiempos de proceso y fechas de entrega como muestra la siguiente tabla:

Sin embargo, el tiempo de setup, correspondiente al tiempo requerido para preparar la máquina antes de cada trabajo, depende del trabajo que le precede. A continuación se indica los tiempos de setup aij cuando al trabajo j le precede el trabajo i:

Adicionalmente hay un tiempo de setup a0j asociado al primer trabajo a programar de acuerdo a los siguientes valores:

El objetivo entonces es programar las actividades de modo de minimizar el tiempo de utilización de la máquina (makespan) como resultado de la asignación propuesta. Notar que según lo descrito anteriormente el problema admite 6 secuencias posibles las cuales se enumeran a continuación para poder obtener el makespan de la programación.

Secuencia 1-2-3: 1+9+1+13+3+10=37[t]
Secuencia 1-3-2: 1+9+0+10+1+13=34[t]
Secuencia 2-1-3: 3+13+2+9+0+10=37[t]
Secuencia 2-3-1: 3+13+3+10+3+9=41[t]
Secuencia 3-1-2: 4+10+3+9+1+13=40[t]
Secuencia 3-2-1: 4+10+1+13+2+9=39[t]

Naturalmente la secuencia óptima es 1-3-2 con un makespan de 34[t]. Como el trabajo 1 es el que inicia la secuencia tiene un setup inicial de a01=1 y requiere de 9[t] para ser completado. A continuación sigue el trabajo 3 que dura 10[t] y como es el trabajo 1 el que le precede no se requiere tiempo de setup (a13=0). Finalmente se realiza el trabajo 2 con duración de 13[t], necesitándose 1[t] para pasar del trabajo 3 al trabajo 2 (a32=1).

Para abordar el problema anterior a través de un modelo de optimización definiremos el siguiente problema de Programación Entera que claramente permite extender su aplicación a problemas de mayor tamaño (número de trabajos):

Variables de Decisión:

Para i=1,2,3 y j=0,1,2,3 con i≠j. Por ejemplo si X10=1 esto indica que el trabajo 1 es el que se realiza inmediatamente después del trabajo 0, es decir, el trabajo 1 es el que inicia la secuencia.

Función Objetivo:

En la implementación computacional con Solver de Excel la función objetivo se representa a través de la siguiente fórmula:

Para mayor claridad a continuación un extracto de la pantalla del modelo computacional:

Se busca minimizar el makespan de la secuencia. Notar que se suman las constantes asociadas al tiempo de proceso de cada trabajo (las cuales son independientes de la secuencia y justifican que inicialmente el valor de la celda que representa la función objetivo sea igual a 32[t]) y que eventualmente se pueden omitir de la función objetivo (en dicho caso el valor óptimo representaría la sumatoria de los tiempos de setup de la secuencia y no el makespan de la programación).

Restricciones:

Se deben realizar los 3 trabajos:

A lo más una tarea sigue a la j-ésima al menos que sea la última de la secuencia:

Alternativas infactibles: (celdas color rojo en la planilla de cálculo)

Debe existir un trabajo inicial en la secuencia:

Luego de resolver con Solver el problema anterior se alcanza la siguiente solución óptima y valor óptimo:

Donde se corrobora que la secuencia óptima es 1-3-2 con un makespan de 34[t]. A continuación puedes descargar el archivo Excel con la resolución del problema anterior en el siguiente enlace: Minimizar Tiempos de Setup.