Grado en Administracion de EmpresasDepartamento de EstadsticaAsignatura: Optimizacion y Simulacion para la EmpresaCurso: 2011/2012

PRACTICA 1: Optimizacion con Excel 2010

1. Modelizacion mediante hojas de calculo

Tarde o temprano, el analista debe informar al cliente acerca del modelo y dar susrecomendaciones sobre el mismo. Debe tenerse en cuenta la diferencia de lenguaje y cono-cimientos matematicos que existe entre el tecnico analista y el gestor de la organizacion:los directivos conocen su negocio, pero, a menudo, no entienden demasiado de modelosmatematicos o de como estos se implementan en hojas de calculo. Es tarea de quien harealizado el analisis presentar el modelo en unos terminos que la gente sin educacionmatematica pueda entender.

En general, al trabajar con un modelo matematico en una hoja de calculo, distingui-remos tres tipos de valores:

datos de entrada (inputs): valores dados (fijos);

variables de decision: valores sobre los que se tiene control;

datos de salida (outputs): valores finales de interes, determinados por los datos deentrada y las variables de decision.

La modelizacion con hojas de calculo consiste en introducir datos de entrada y variablesde decision que, mediante formulas adecuadas, den lugar a los datos de salida.

Puesto que es muy frecuente que varias personas tengan que trabajar sobre una mismahoja de calculo, es conveniente adquirir buenos habitos de modelizacion que faciliten sulegibilidad:

Estructura el modelo de forma clara y logica.

Si te es necesario, separa las diferentes partes del modelo mediante varias hojas decalculo.

1

Pon encabezamientos a las diferentes secciones del modelo. En especial, a los datosde entrada, variables de decision y datos de salida.

Usa formatos que permitan distinguir los datos y las secciones con claridad (negrita,italica, fuentes de mayor tamano, colores. . . ).

2. Primer ejemplo y primeros consejos

Usaremos la herramienta Solver del programa Microsoft Excel 2010. Para ello, hayque seleccionar Archivo Opciones. A continuacion, en Complementos, elegimosAdministrar Complementos de Excel, seleccionaremos Solver y haremos clic enAceptar.

Figura 1: Menu Solver.

Una vez instalado, el comando Solver estara disponible en el grupo Analisis de laficha Datos.

Para aprender como trabajar con esta herramienta y como modelizar un problema,resolveremos un ejemplo paso a paso.

Un fabricante de bebidas produce semanalmente 955, 1412 y 205 litros de las bebidasAghwa, Zerbessa y Visky, respectivamente. Estos lmites son consecuencia de los recursosde los que dispone y de que, fruto de sus largos anos en el negocio, sabe que es imposiblevender semanalmente mas de 1000, 1500 y 300 litros de estas bebidas, respectivamente. En

2

la elaboracion de las bebidas, intervienen cuatro ingredientes que, por razones de espionajeindustrial, denominaremos A, B, C y D. Los dos primeros son solidos y los dos segundosson lquidos. Para obtener un litro de cada una de las bebidas, se usan los siguientesrecursos, respectivamente:

300 gramos de producto A, 500 gramos de producto B, 250 mililitros de producto Cy 350 mililitros de producto D;

200 gramos de producto A, 700 gramos de producto B, 450 mililitros de producto Cy 250 mililitros de producto D;

400 gramos de producto A, 600 gramos de producto B, 350 mililitros de producto Cy 550 mililitros de producto D.

La venta de un litro de Aghwa, Zerbessa y Visky, reporta un beneficio de 1.2, 1.7 y2.6 euros, respectivamente. Los recursos semanales disponibles son 750 kilos de producto A,1800 kilos de producto B, 1050 litros de producto C y 800 litros de producto D.

Un buen da, el hijo de nuestro fabricante, que se encuentra cursando la asignaturade Optimizacion, le comenta a su padre que esa poltica de produccion esta lejos de seroptima. El padre, sin dejarse amilanar por la impertinencia de su hijo, ignorante de losnegocios, le reta a que, si encuentra una solucion mejor, le dara el incremento neto debeneficios de una semana. Consigue algun beneficio el muchacho? En caso afirmativo, acuanto asciende?

En primer lugar, debemos encontrar un modelo matematico que nos permita represen-tar nuestro problema.

Comenzaremos con las variables de decision, que en este caso son bastante intuitivas:

x1 = numero de litros de Aghwa,x2 = numero de litros de Zerbessa,x3 = numero de litros de Visky.

Con estas variables, es muy facil ver que el beneficio que nosotros queremos maximizar(nuestra funcion objetivo) es

z = 1.2x1 + 1.7x2 + 2.6x3.

Observa que en Optimizacion es muy frecuente usar la letra z para representar la funcionobjetivo.

Finalmente, ahora queda representar las condiciones que rigen nuestro problema, esdecir, definir el conjunto de restricciones.

En primer lugar, tenemos cantidades de recurso disponible limitadas. Por ejemplo, nopodemos usar semanalmente mas de 750 kilos de producto A. Como los kilos de producto Ausados se representan como 0.3x1 + 0.2x2 + 0.4x3, entonces una restriccion es

0.3x1 + 0.2x2 + 0.4x3 750.

3

Del mismo modo, deducimos que las restricciones sobre los lmites disponibles de pro-ducto B, C y D son

0.5x1 + 0.7x2 + 0.6x3 1800,0.25x1 + 0.45x2 + 0.35x3 1050,

y0.35x1 + 0.25x2 + 0.55x3 800,

respectivamente.

Por otra parte, sabemos que hay unos lmites de produccion que no vamos a sobrepasarporque no vamos a poder vender los productos que fabriquemos de mas. Por lo tanto,establecemos las restricciones

x1 1000,x2 1500

yx3 300.

Finalmente, no debemos olvidar indicar algo que puede parecer trivial pero que hayque indicar siempre: que tipo de variables tenemos. En este caso, los litros de bebida esuna magnitud positiva y continua. Por lo tanto, escribiremos

x1, x2, x3 0.

El ultimo paso es escribir el modelo entero:

Max. 1.2x1 + 1.7x2 + 2.6x3s.a 0.3x1 + 0.2x2 + 0.4x3 750,

0.5x1 + 0.7x2 + 0.6x3 1800,0.25x1 + 0.45x2 + 0.35x3 1050,0.35x1 + 0.25x2 + 0.55x3 800,x1 1000,x2 1500,x3 300,x1, x2, x3 0.

Podemos distinguir las siguientes partes en la hoja de calculo que vamos a crear:

1. Datos de entrada. Todos los datos de entrada numericos (es decir, los valores nece-sarios para calcular la funcion objetivo y las restricciones) deben aparecer en la hojade calculo. Usaremos la convencion de enmarcar estos valores con borde azul y fondosombreado. Trataremos de situarlos en la seccion superior izquierda de la hoja decalculo.

2. Celdas cambiantes. En lugar de usar nombres de variables (por ejemplo, x1), losmodelos en hojas de calculo emplean un conjunto de celdas que desempena el papel delas variables de decision. Los valores de estas celdas pueden cambiarse para optimizarel objetivo. En Excel estas celdas se denominan celdas cambiantes. Enmarcaremosestas celdas con borde rojo.

4

3. Celda (funcion) objetivo. Una celda, denominada celda objetivo, contiene la funcionobjetivo. La herramienta Solver vara los valores de las celdas cambiantes para opti-mizar el valor de la funcion objetivo. Enmarcaremos la celda objetivo con un bordedoble negro.

4. Restricciones. En Excel, las restricciones no se muestran directamente en la hojade calculo. En su lugar, especificamos las restricciones en el menu de Solver. Porejemplo, podramos establecer un conjunto de restricciones que fuese

B15 : D15 B16 : D16.

Esta declaracion implica tres restricciones separadas: el valor de la celda B15 debeser menor o igual que el valor de B16, el valor de la celda C15 debe ser menor o igualque el valor de C16 y el valor de la celda D15 debe ser menor o igual que el valorde D16. Otra manera de trabajar es mediante rangos, etiquetando conjuntos de celdas(podemos ver una lista de los rangos definidos en Formulas Administradorde nombres). De este modo, una restriccion podra ser

Usado Disponible.

5. No negatividad. Normalmente, queremos que las variables de decision sean positi-vas. Esta restriccion se incluye seleccionando el submenu Opciones de Solver ymarcando Convertir variables sin restricciones en no negativas.

En la hoja Practica1 Ejemplo solver2010.xlsx (disponible en Aula Global) apa-rece cada paso en una hoja distinta del fichero para que puedas ver la evolucion de laconstruccion del modelo.

Inicialmente, como resultara obvio, la hoja esta vaca (Hoja 1).

Despues, le damos un ttulo y anadimos los primeros datos (Hoja 2). En concreto,hemos introducido los beneficios unitarios y el bloque constituido por los tres beneficios(B7:B9) lo hemos definido como el rango Beneficios.

De modo analogo, vamos anadiendo los demas datos (Hoja 3). Observemos que hemosdefinido los nuevos rangos Disponibles y Lmites.

A continuacion (Hoja 4), establecemos las celdas cambiantes que seran las variables.Como valores iniciales, podemos establecer cualquier valor, incluso aunque no sea factible.Establecemos el rango Variables (C27:C29).

El siguiente paso es definir la funcion objetivo: en la celda C31, escribimos

= SUMAPRODUCTO(Beneficios; Variables).

Tambien podramos haber escrito

= SUMAPRODUCTO(B7 : B9; C27 : C29),

5

Figura 2: Hoja 2.

pero el usar rangos nos facilita tanto la escritura como la lectura del modelo.

Finalmente, antes de pasar a introducir las variables en el menu Solver, escribimos unaslneas que nos calculan el consumo de los distintos materiales. En realidad, este paso no esnecesario, pero nos permite visualizar mejor los datos y estar preparados para responder apreguntas como cuantos kilos del producto A se usan?. Para ello, calculamos la cantidadde recurso utilizado para cada uno de los cuatro productos.

Escribimos

= B19 C$27 + C19 C$28 + D19 C$29.

en la celda H27 y despues la copiamos en las tres inferiores (Hoja 5). Observa el usoque se hace de celdas relativas y celdas absolutas en la formula (el smbolo dolar antesdel numero de fila bloquea la fila, mientras que antes de la letra de columna bloqueabloquea la columna). Estas cuatro formulas, de hecho, van a ser luego las restricciones quenecesitamos declarar.

Ahora ya lo tenemos todo preparado para poder introducir el modelo en el menu Solver :

6

Figura 3: Hoja 3.

Establecer objetivo: la celda en donde hemos declarado la funcion objetivo.

Para: elegiremos Max. o Mn segun estemos maximizando o minimizando, respec-tivamente.

Cambiando las celdas de variables: seleccionamos las celdas cambiantes que actuancomo variables de decision.

Sujeto a las restricciones: aqu introducimos las restricciones. Podemos hacerlo me-diante el uso de rangos, si los hemos ido declarando, o mediante formulas concretas.En la imagen, vemos que se ha usado la primera opcion, pero, por ejemplo, la primerafamilia de restricciones,

Usados Disponibles,

equivale a

H27 : H30 C12 : C15.

7

Figura 4: Hoja 4.

Por supuesto, tambien podramos haber escrito las restricciones de esta familia unaa una:

H27 C12,H28 C13,H29 C14,H30 C15.

Metodo de resolucion: elegiremos Simplex LP.

Finalmente, y tras no olvidarnos de seleccionar la opcion de que los valores sean nonegativos, resolvemos el modelo.

Vemos que la solucion optima, producir 742.8571 litros de Aghwa, 1500 litros de Zer-bessa y 300 litros de Visky da un beneficio de 4221.29 euros.

Facilmente, comprobamos que la produccion propuesta inicialmente por el fabricantereporta un beneficio de 4079.4 euros. Es decir, gracias al analisis del problema, ha habidoun incremento del 3.48 %. El hijo gana, con escaso esfuerzo, 141.89 euros.

En las celdas que usamos para escribir el uso de las restricciones, podemos ver lasdistintas cantidades de recursos utilizados. En particular, podemos observar que no pro-ducimos mas porque hemos agotado un recurso (producto D). Si no hubiesemos agotado

8

Figura 5: Hoja 5.

completamente ninguno de los cuatro, la solucion no podra ser optima, pues seguiramosproduciendo hasta agotar alguno. Este breve analisis es la parte mas importante en laresolucion del modelo para un analista: la interpretacion del modelo y la extraccion deconclusiones.

3. Ejercicios:

1. Modifica la cantidad disponible de uno o mas productos y observa si vara la polticade produccion. Haz lo mismo pero modificando ahora los beneficios.

2. Resuelve el Ejemplo 1 de clase (problema de la dieta).

3. Resuelve el Ejemplo 2 de clase (planificacion de la produccion).

9

Figura 6: Declaracion del modelo.

Figura 7: Solucion optima.

10

Modelizacin mediante hojas de clculoPrimer ejemplo y primeros consejosEjercicios: