Herramienta Solver

EXCEL: Herramienta Solver

Beatriz Loubet

Titular Investigación Operativa I

Facultad de Ciencias Económicas

Universidad Nacional de Cuyo

Introducción

Las planillas de cálculo se han convertido en herramientas obligadas de análisis de datos. Sin embargo, no siempre se aprovechan todas sus potencialidades.

La planilla más difundida en el mercado es Excel, que viene incluida en el paquete Office de Microsoft. En este trabajo se procura dar una sencilla explicación de su uso como herramienta de optimización. Incluye las versiones 5.0 y 97.

Optimización Restringida

Un problema de optimización consiste en encontrar aquellos valores de ciertas va-riables que optimizan (es decir, hacen máxima o mínima, según el caso), una fun-ción de estas variables. A las variables las llamaremos variables controlables o va-riables de decisión.

Matemáticamente, significa encontrar los valores de x1, x2,..., xn, tales que hacen máxima (o mínima) a la función f (x1, x2,..., xn).

El método más conocido para encontrar el óptimo de una función es a través del análisis de sus derivadas. Este método tiene dos limitaciones: no siempre la función es derivable, y, además, no siempre el óptimo nos da una solución que tenga senti-do en la práctica.

Debido a la primera limitación, surgieron los métodos numéricos, que parten de una solución inicial, y mediante algún algoritmo iterativo, mejoran sucesivamente la solución.

Debido a la segunda limitación, surgieron los métodos de optimización restringida. El nombre se debe a que podemos ponerle restricciones a las variables, de modo que cumplan una o más condiciones.

La restricción más común que se da en la práctica es que las variables deben ser no negativas. No tiene ningún sentido una "solución" que implique producir cantidades negativas, o sembrar un número negativo de hectáreas, o llevar un número negati-vo de paquetes, por ejemplo.

Pero, además, surgen naturalmente otras restricciones en el mundo real, debido a limitaciones de horas de trabajo, capital, tiempo, insumos, o a que quizás deseamos imponer ciertos mínimos o máximos de calidad, riesgo, etc.. Estas restricciones pueden ser funciones de las variables controlables.

Podríamos resumir diciendo que en un problema de optimización restringida bus-camos los valores de ciertas variables que optimizan una función objetivo, sujetas a restricciones, dadas también en términos de funciones.

Matemáticamente, significa encontrar los valores de x1, x2, ..., xn, tales que hacen máxima (o mínima) a f (x1, x2, ..., xn), sujeto a restricciones de tipo gj (x1, x2, ..., xn) , = ó  cj , donde cj es una constante.

Los modelos más sencillos de optimización restringida corresponden a modelos de Programación Lineal, donde tanto la función objetivo como las restricciones son funciones lineales, las variables deben ser no negativas, y pueden tomar cualquier valor real, no necesariamente entero.

Herramienta Solver

Solver es una herramienta para resolver y optimizar ecuaciones mediante el uso de métodos numéricos.

Con Solver, se puede buscar el valor óptimo para una celda, denominada celda ob-jetivo, en donde se escribe la fórmula de la función objetivo f (x1, x2, ..., xn).

Solver cambia los valores de un grupo de celdas, denominadas celdas cambiantes, y que estén relacionadas, directa o indirectamente, con la fórmula de la celda objeti-vo. En estas celdas se encuentran los valores de las variables controlables x1, x2, ..., xn.

Puede agregar restricciones a Solver, escribiendo una fórmula gj (x1, x2, ..., xn) en una celda, y especificando que la celda deberá ser mayor o igual, igual, o menor o igual que otra celda que contiene la constante cj.

También puede especificar que los valores sean enteros, para evitar dar resultados absurdos de algunos problemas, tales como que se necesitan 3,5 empleados.

Solver ajustará los valores de las celdas cambiantes, para generar el resultado es-pecificado en la fórmula de la celda objetivo.

Instalar Solver

En el menú Herramientas, fíjese si aparece el comando Solver. Si no aparece, deberá instalar el complemento o macro automática Solver.

• Si tiene Excel 97

1. En el menú Herramientas, elija Complementos.

Si Solver no aparece en la lista del cuadro de diálogo Complementos, haga clic en Examinar y localice la unidad, la carpeta y el nombre de archivo Solver.xla que, normalmente, está ubicado en la carpeta Macros/Solver, o ejecute el programa de instalación si no puede localizar el archivo.

2. En el cuadro de diálogo Complementos, seleccione la casilla de verificación Solver.

• Si tiene Excel 5.0

1. Elija el Programa de Instalación de Microsoft Excel.

2. Elija Agregar / Eliminar componentes.

3. Seleccione Macros Automáticas, y haga clic en el botón Modificar Opción.

4. Seleccione la casilla Solver, y luego haga clic en Aceptar.

5. Haga clic en Aceptar.

Algoritmos y Métodos Utilizados por Solver

Microsoft Excel Solver utiliza diversos métodos de solución, dependiendo de las op-ciones que seleccione.

• Para los problemas de Programación Lineal utiliza el método Simplex.

• Para problemas lineales enteros utiliza el método de ramificación y límite, im-plantado por John Watson y Dan Fylstra de Frontline Systems, Inc.

• Para problemas no lineales utiliza el código de optimización no lineal (GRG2) desarrollado por la Universidad Leon Lasdon de Austin (Texas) y la Universidad Allan Waren (Cleveland).

Para obtener más información acerca del proceso de solución interno que utiliza Solver, póngase en contacto -en inglés- con:

Frontline Systems, Inc.

Página Web: http://www.frontsys.com

Correo electrónico: info@frontsys.com

Solver y Optimización No Lineal

Veremos algunos casos de optimización a partir de un modelo tomado del archivo muestras.xls de Solver.

Éste es un modelo típico de mercadotecnia que muestra las ventas en función de los gastos en publicidad y de un factor de temporada. Esta función es no lineal y se ex-presa:

Unidades vendidas = 35*factor de temporada*(publicidad+3000) ^ 0.5

Para un factor de temporada 1 (neutro), el gráfico es:

Observe que aumentan las ventas a partir de una cifra base (quizás debido al per-sonal de ventas) al incrementar la publicidad, pero con una caída constante en el flujo de caja.

Por ejemplo, los primeros 5.000 $ de publicidad producen aproximadamente un incremento de 1.200 unidades vendidas, pero los 5.000 $ siguientes producen cerca de 800 unidades adicionales.

Puede utilizar Solver para averiguar si el presupuesto publicitario es escaso y si la publicidad debe orientarse de otra manera durante algún tiempo para aprovechar mejor el factor de temporada.

El Modelo

Para cada trimestre, se tiene:

• Los factores de temporada:

Trimestre 1 Trimestre 2 Trimestre 3 Trimestre 4

Factor de temporada 0.9 1.1 0.8 1.2

• Unidades vendidas = 35*factor de temporada*(publicidad+3000) ^ 0.5

• Ingresos por ventas = precio del producto * unidades vendidas.

• Costo de las ventas = costo del producto * unidades vendidas.

• Margen bruto = Ingreso por ventas - Costo de las ventas

• Costo personal = 8000 los dos primeros trimestres y 9000 los dos últimos.

• Publicidad = 10000 {éstas serán las variables controlables}

• Costos fijos = 0.15 * Ingresos por ventas

• Costo total = Costo personal + Publicidad + Costos fijos

• Beneficio = Margen bruto - Costo total.

• Margen de beneficio = Beneficio / Ingresos por ventas.

El precio de venta y el costo son constantes a lo largo del año:

• Precio del producto = 40

• Costo del producto = 25

Introducción de Datos

Abra una nueva planilla de cálculo, e ingrese los datos y las fórmulas del modelo anterior. La planilla debe quedar como se muestra en la próxima página. Se sugiere ingresar los rótulos de la columna A, y luego aumentar el ancho de la columna.

Para ayudarle en la confección de la planilla, la fuente en negrita indica las celdas con valores fijos, y en normal las celdas donde debe ingresar una fórmula.

En las columnas B, C, D y E, se escriben las fórmulas de cada trimestre: T1, T2, T3 y T4. Ingrese las fórmulas de T1 y luego cópielas a los otros trimestres. La columna F (Total), es la suma de los 4 trimestres, excepto en el Margen de Beneficio.

Windows tiene definido un separador de decimales, que suele ser el punto o la co-ma. Para saber cuál de los dos es el que tiene establecido, ingrese en la celda B3 el número 0,9. Si se alinea a la derecha, el separador de decimales es la coma, y lo ha interpretado como número. Si se alinea a la izquierda, el separador seguramente es el punto, y ha interpretado que ingresó un rótulo o texto.

Para que aparezca el signo $, debe seleccionar del menú, Formato / Celda / Mone-da. Para expresar los Márgenes de Beneficio de la fila 16 en porcentajes, Formato / Celda / Porcentaje.

Optimizar una Función de Una Variable

Puede utilizar Solver para determinar el valor máximo de una celda cambiando el valor de otra. Las dos celdas deben estar relacionadas por medio de las

...