Gestión de Operaciones

Cómo descargar e instalar Premium Solver en Excel 2010

Solver es sin duda junto a What’sBest! la herramienta más popular para resolver modelos de optimización utilizando Microsoft Excel como plataforma. En efecto la versión gratuita que utilizamos de Solver es desarrollada por Frontline Systems Inc, empresa que ofrece un importante número de herramientas y motores de resolución que son de gran utilidad para enfrentar problemas de naturaleza real aplicados en la industria.

En el siguiente artículo nos referiremos a cómo descargar e instalar la versión de prueba de Premium Solver que está disponible en forma gratuita por un período de 15 días y la cual nos permite enfrentar la resolución de modelos de Investigación Operativa significativamente mayores tanto en el número de variables de decisión como restricciones, que aquellos posibles de abordar con la versión tradicional de Solver.

Adicionalmente, al contar con algoritmos de resolución mejorados se favorece la confiabilidad de las soluciones encontradas y los tiempos de procesamiento.

A continuación presentamos el procedimiento detallado para descargar e instalar la versión de prueba de Premium Solver en un computador con Excel versión 2010.

Paso 1: Ingresar a Frontline Systems Inc, completando la información requerida en el formulario de suscripción.

Paso 2: Descargar la versión de prueba compatible con la versión de Excel que dispongamos. En este tutorial se muestra la instalación de la versión de 64 bits. Una vez que estamos seguros de nuestra elección seleccionamos “Download Now”.

Si tienes Excel 2010 y deseas saber de cuántos bits es la versión que utilizas ejecuta Excel y en el menú Archivo selecciona la opción Ayuda. En la esquina inferior derecha se mostrará la cantidad de bits de tu versión bajo el titulo “Acerca de Microsoft Excel”.

Paso 3: Revisa tu cuenta de correo electrónico (la que proporcionaste en el formulario de suscripción del Paso 1) y anota las 2 contraseñas que te han sido asignadas (en la imagen a continuación éstas han sido protegidas con color azul y rojo).

Paso 4: Una vez completada la descarga ejecuta el archivo “SolverSetup64.exe” (o el nombre que corresponda según la versión de Excel que estés utilizando).

A continuación se desplegará el asistente para la instalación del programa. (Seleccionar “Next”).

Ahora debemos ingresar los password que hemos recibido por correo electrónico (Paso 3) para continuar con la activación del programa:

Luego debemos aceptar los acuerdos de la licencia del programa y seleccionar “Next”.

En estos momentos la licencia del programa ya ha sido activada y continuamos el proceso de la instalación seleccionando “OK”.

Se debe seleccionar la carpeta donde se instalará el programa y podemos en este punto seleccionar simplemente aquella ubicación que el programa selecciona por defecto.

Es el momento de seleccionar el producto que deseamos activar. En este caso hemos seleccionado “Premium Solver Pro” que corresponde a la versión mejorada de la versión de Solver que se utiliza generalmente para fines académicos.

El asistente de instalación nos solicitará confirmar la instalación que hemos seleccionado (“Install”).

Finalmente hemos completado la instalación del programa Premium Solver Pro y ya estamos en condiciones de poder utilizarlo para resolver modelos de optimización utilizando Microsoft Excel como plataforma.

Cambio de Variables en un Modelo de Programación Lineal

Cuando se desea resolver un modelo de Programación Lineal es importante tener en cuenta que generalmente se dispone de varias alternativas las cuales difieren en dificultad y por tanto es necesario evaluar cada una en su mérito para luego decidir qué estrategia algorítmica utilizar.

Este concepto es válido tanto para la resolución de pequeños modelos de optimización lineales como los que usualmente se consideran para fines académicos, como también para modelos de mayor tamaño, donde seleccionar una estrategia adecuada favorece los tiempos y confiabilidad de la resolución computacional.

A continuación presentaremos un problema de Programación Lineal en 2 variables que resulta de interés su resolución:

Notar que existen distintas estrategias que nos permiten abordar el problema anterior. Por ejemplo, lo podríamos resolver gráficamente (Método Gráfico en Programación Lineal) o utilizar el Método Simplex de 2 Fases, agregando variables de holgura para las restricciones 1, 2 y 3 y variables de exceso y auxiliares para las restricciones 4 y 5. También por cierto se podría definir el Problema Dual del ejemplo anterior y luego intentar su resolución, lo que a primera vista no sería de mayor ayuda.

Ahora bien, si una variable de decisión x debe cumplir la restricción x≥a, para una constante a positiva, se puede imponer un cambio de variables y=x-a, que define una nueva variable y que tiene solo una restricción de no negatividad y simplifica imponer la primera como una restricción general en la aplicación del Método Simplex.

El concepto anterior nos permite hacer lo siguiente: Y1=X1-20>=0; Y2=X2-20>=0. Es decir X1=Y1+20 y X2=Y2+20. En consecuencia podemos reescribir el modelo original de la siguiente forma:

Reduciendo términos semejantes se obtiene:

Notar que este nuevo modelo de optimización se puede resolver de forma directa a través del Método Simplex, incorporando las variables no negativas Y3, Y4 e Y5 como holguras de las restricciones 1, 2 y 3, respectivamente. De esta forma el problema en su forma estándar proporciona la siguiente tabla inicial: (Notar que el valor de la función objetivo inicialmente es 1.100 debido a que dicha constante se obtiene luego de realizar el cambio de variables)

La variable Y1 entra a la base al ser la variable no básica con el costo reducido más negativo. Luego para determinar la variable básica que deja la base calculamos el mínimo cuociente: Min {420/6; 140/1; 160/2} = 70 ==>Y3 sale de la base. Se realiza una iteración del Método Simplex:

Ahora Y2 entra a la base al ser la única variable no básica con costo reducido negativo. A continuación la variable que deja la base se determina a través del criterio del mínimo cuociente: Min {70/1/2; 70/3/2; 20/1} = 20 ==>Y5 sale de la base. Se realiza una nueva iteración:

Se ha alcanzado la tabla óptima donde Y1=60 e Y2=20 con valor óptimo V(P)=3.400. Reemplazando en las variables originales se obtiene la solución óptima del problema original: X1=60+20=80 y X2=20+20=40. Se puede corroborar que esta solución óptima al ser evaluada en la función objetivo del problema inicial proporciona el valor óptimo del modelo de programación lineal: Max 30*(80)+25*(40)=3.400, lo que permite garantizar la equivalencia del procedimiento utilizado.

Cómo hacer un Histograma con Geogebra

En un artículo anterior nos referimos a Cómo hacer un Histograma con Excel y EasyFit y a continuación mostraremos cómo poder desarrollar el mismo procedimiento utilizando el software de distribución gratuita Geogebra el cuál ya hemos utilizado previamente para la Resolución Gráfica de un modelo de Programación Lineal y como resulta evidente su aplicación no se ve limitada a lo anterior.

Los pasos a seguir son muy sencillos y los detallamos a continuación:

Paso 1: Abrir el programa Geogebra y en el Menú “Vista” seleccionar “Hoja de Cálculo”.

Paso 2: Copiar y Pegar los datos a granel en la planilla (Columna A) que desplegara el programa en la esquina superior derecha. En el ejemplo utilizaremos los mismos datos (40) del artículo anterior.

Paso 3: En el Menú seleccionar el icono con barras azules (con forma de histograma) y en las opciones que se desplegaran seleccionar “Análisis Una Variable”.

Paso 4: Se desplegara la ventana “Fuente de Datos” donde se podrán observar los valores ingresados en la Columna A. Luego seleccionar “Analiza”. Importante: Si los datos de la Columna A no aparecen en la ventana de “Fuente de Datos” debes posicionarte sobre la letra A de la planilla de cálculo y repetir el Paso 2 y 3. La imagen a continuación muestra cómo se deberían visualizar los datos de la Columna A antes de proceder con el Paso 4.

Paso 5: Listo!. Ya hemos generado un histograma con Geogebra. Se puede observar que existe una barra que se puede desplazar para ajustar la cantidad de clases que tiene el histograma según lo que nos parezca razonable. En la imagen a continuación hemos seleccionado 6 clases para mostrar la consistencia de los resultados con lo obtenido previamente con Excel y Easyfit. Notar adicionalmente que en el eje vertical se considera por defecto la frecuencia absoluta (“n”).

Finalmente se puede obtener de forma muy sencilla un resumen de las estadísticas de los datos proporcionados a granel seleccionando el icono “Muestra Estadísticas” (símbolo de sumatoria). Adicionalmente existen otras opciones interesantes que permiten generar un Diagrama de Caja o Diagrama de Tallo y Hojas. Te proponemos el desafío para que lo puedas revisar directamente!

Cómo hacer un Histograma con Excel y EasyFit

En el siguiente artículo mostraremos cómo hacer un histograma aplicado a una serie de datos a granel. Para ello utilizaremos 2 programas computacionales frecuentemente utilizados para estos propósitos: Excel y EasyFit.

Recordemos que un histograma consiste en una representación gráfica a través de un diagrama de barras, donde cada barra es proporcional a la frecuencia de los valores representados. El histograma como herramienta de análisis gráfica que resume información nos ayuda para tener una primera visión de si, por ejemplo, la distribución de los datos se asemeja al comportamiento de una función de probabilidad conocida.

Consideremos los siguientes 40 datos a granel que consideran la medición de un cierto fenómeno de interés:

A continuación generaremos una tabulación de la información utilizando algunos conceptos estadísticos básicos. Primero determinaremos la cantidad de clases “k” para lo cual se pueden utilizar múltiples criterios y donde se selecciona aquel que otorga una cantidad de clases “razonable”. En nuestro ejemplo consideraremos k=6 clases.

Luego determinamos el Rango “R” que consiste en la diferencia entre la mayor y menor observación de los datos a granel. R=Máximo(Xi)-Mínimo(Xi)=2,8-0,5=2,3.

Ahora determinamos la amplitud de cada clase “a”. Notar que el concepto de “Unidad” esta relacionado con los datos que se disponen que en nuestro ejemplo consideran un decimal, en consecuencia se define como unidad a 0,1.

Finalmente calculamos el Límite Inferior (LI) y Límite Superior (LS) utilizando las siguientes fórmulas:

Límite Inferior (LI) = Mínimo Dato (Xi) – 0,5 «Unidad»
Límite Superior (LS) = Limite Inferior de la clase + amplitud

De acuerdo a lo anterior estamos en condiciones de construir una tabla que resume la información de los datos proporcionados a granel:

Notar, por ejemplo, que para la primera clase el Límite Inferior (LI=0,45) se obtiene restando al Mínimo Dato (en el ejemplo el dato 9 con valor 0,5) menos 0,5*(0,1).

El Límite Superior de la primera clase (LS=0,85) se obtiene sumando al Límite Inferior (LI=0,45) la amplitud obtenida previamente (a=0,4).

Adicionalmente los valores en la columna etiquetada con “Mi” representa la marca de la clase (por ejemplo en M1 es igual a (0,45+0,85)/2=0,65).

En la columna n se contabilizan las observaciones que corresponden a la clase lo que se denomina como frecuencia absoluta (por ejemplo en la clase 1 se observan 3 datos que están en el intervalo entre 0,45 y 0,85).

En f se considera la frecuencia relativa, es decir, la proporción de datos sobre el total de la muestra que pertenecen a la clase (por ejemplo, para la clase 1 es f=3/40).

Finalmente en N y F se representa la frecuencia absoluta acumulada y frecuencia relativa acumulada, respectivamente.

Si generamos un gráfico de columna en Excel con los valores de la frecuencia relativa de cada clase y como etiqueta de datos (línea horizontal) la marca de clase, se obtiene lo siguiente:

Cabe destacar que existe una serie de software estadístico que permite procesar este tipo de análisis de forma rápida e intuitiva. A continuación mostraremos cómo generar un histograma utilizando EasyFit el cual esta disponible en una versión de evaluación de 30 días y en una licencia académica de 69 Euros. Para ello copiamos y pegamos los 40 datos en una columna de la interfaz del programa y luego seleccionamos el icono con forma de rayo.

A continuación se desplegara el menú a continuación donde sólo será necesario seleccionar OK.

El programa ejecutará una rápida rutina donde ajustará un importante número de distintas funciones de probabilidad teóricas a los datos proporcionados. Por ejemplo, en el siguiente gráfico mostramos el histograma de los datos (que por cierto es consistente con lo que hemos obtenido previamente en Excel) y donde se ha ajustado una distribución normal a los datos (línea color rojo).

Claramente la función de densidad de probabilidad ajustada es una aproximación a la distribución de los datos y resulta de interés decidir si una distribución particular es representativa de la naturaleza de los datos. Para esto es necesario realizar un Test de Bondad de ajuste sobre lo cual nos referiremos en un próximo artículo.

Política de Lotificación de Costo Unitario Mínimo aplicada al MRP

La política de lotificación de Costo Unitario Mínimo en el Plan de Requerimientos de Materiales (MRP) al igual que la alternativa de Costo Total Mínimo consiste en una técnica iterativa que compara los costos de realizar (emitir) pedidos con los costos de almacenamiento de inventario (ver clasificación de los Costos de Inventario) para distintos tamaños de pedido, seleccionando aquel donde al prorratear el costo total en el tamaño del lote solicitado se alcanza el menor costo unitario.

En este contexto, a continuación mostraremos el desarrollo del Costo Unitario Mínimo (CUM) aplicada a la siguiente Planificación de Requerimientos de Materiales:

Ejemplo Costo Unitario Mínimo (MRP)

Consideremos las Necesidades Brutas del Producto A para un periodo de 8 Semanas:

El Lead Time logístico del Producto A es de 1 semana. Adicionalmente existe un inventario inicial de 350 unidades y una recepción de pedido programado para la semana 2 de 500 unidades. El costo de emitir un pedido es de $1.000 y el costo unitario de almacenar una unidad en inventario es de $2 por semana. Con esta información desarrolle un MRP aplicando como política de lotificación el Costo Unitario Mínimo.

Notar que la información de las necesidades brutas corresponde a la primera fila de la tabla color celeste. Adicionalmente se ha considerado el ingreso del pedido de 500 unidades en la semana 2. Por tanto al existir un inventario inicial de 350 unidades y descontando los requerimientos de 200 unidades en la semana 1 queda 150 unidades como inventario o saldo disponible proyectado al final de la semana 1. Ahora el saldo disponible proyectado al final de la semana 2 es de 450 unidades, debido a que se comienza dicha semana con 150 unidades y se reciben 500 adicionales, pero fue necesario entregar 200 unidades esa semana.

El saldo disponible proyectado que queda al final de la semana 2 permite cubrir las necesidades brutas de la semana 3 y 4, por tanto los requerimientos comienzan a contar de la semana 5. En este momento se aplica Costo Unitario Mínimo según se muestra a continuación:

Se evalúa la posibilidad de hacer un pedido por 200 unidades para satisfacer la necesidad bruta exacta de la semana 5. Dicha alternativa por cierto no genera costos de almacenar inventario al final del periodo pero si un costo de emisión de $1.000. En resumen se genera un costo total de $1.000 que al ser prorrateado en el tamaño del pedido (Q=200) genera un costo unitario de $5 ($1.000/200).

Luego se analiza la alternativa de un pedido por 350 unidades para cubrir las necesidades brutas de la semana 5 y 6. Esto genera un costo de inventario de $300 (debido a que al final de la semana 5 quedarían 150 unidades en inventario) y un costo de emisión de $1.000. El costo total para este caso sería de $1.300 que al ser dividido por el tamaño del pedido (Q=350) nos da un costo unitario de $3.714.

El procedimiento sigue de la misma forma agrupando ahora las necesidades de la semana 5 a la semana 7, sin embargo, el costo unitario ahora es de $3,818.

Por tanto se corrobora que el primer costo unitario mínimo se alcanza agrupando las necesidades brutas de la semana 5 a la semana 6.

Continuando con la metodología del Costo Unitario Mínimo, se observa que queda por programar las necesidades de las semanas 7 y 8. Un pedido de 200 unidades para satisfacer las necesidades brutas de la semana 7 no genera costos de almacenamiento pero si de emisión. Finalmente un pedido por 400 unidades para enfrentar los requerimientos de la semanas 7 y 8 genera un costo de $400 (por el almacenamiento de 200 unidades al final de la semana 7) y un costo de emisión de $1.000. Luego al prorratear el costo total de $1.400 en un pedido de Q=400 unidades se genera un costo unitario de $3,5 que resulta ser el segundo costo total mínimo.

En consecuencia el MRP para el Producto A aplicando la política de lotificación de Costo Unitario Mínimo es el siguiente:

Notar que como se agrupan necesidades de la semana 5 a la semana 6 y de la semana 7 a la semana 8 se requiere que en la semana 5 y 7 se reciban pedidos planeados por 350 y 400 unidades, respectivamente. Sin embargo, la expedición (emisión) de pedidos planeados se realiza en cada caso con una semana de antelación teniendo en cuenta el Lead Time del producto. Te recomendamos revisar a continuación otros ejemplos de MRP en los “Artículos Relacionados” al final de este artículo.