Introducción a la optimización con la herramienta Solver de Excel

Se aplica a
Microsoft Office Excel 2003
Microsoft Excel 2000 y 2002

Cubierta del libro Este artículo es una adaptación del artículo Microsoft Excel Data Analysis and Business Modeling (Modelos empresariales y análisis de datos de Microsoft Excel) escrito por Wayne L. Winston.

Este libro de texto se desarrolló a partir de una serie de presentaciones realizadas por Wayne Winston, un conocido estadístico y profesor de administración de empresas especializado en aplicaciones prácticas y creativas de Excel. Así que prepárese: va a tener que estrujar su cerebro.

Visite Microsoft Learning para adquirir este libro.

En este artículo

Qué es la optimización

  • ¿Cómo puede una gran empresa farmacéutica determinar la mezcla de productos mensuales en su planta de Indianápolis necesaria para aumentar la rentabilidad empresarial?
  • Si Microsoft produce consolas Xbox en tres ubicaciones, ¿cómo puede reducir el costo necesario para satisfacer la demanda de consolas Xbox?
  • ¿Qué precio deben tener las consolas y los juegos Xbox para aumentar las ganancias de las ventas de Xbox?
  • Microsoft desearía poner en marcha 20 iniciativas estratégicas que compaginen dinero y programadores con experiencia para los próximos cinco años. No dispone de recursos suficientes para realizar los 20 proyectos. ¿Qué proyectos debería realizar?
  • ¿Cómo pueden los corredores de apuestas encontrar el mejor grupo de "clasificaciones" de los equipos de la Liga Nacional de Fútbol para establecer puntuaciones exactas?
  • ¿Cómo debo repartir mi cartera de acciones entre valores tecnológicos, acciones infravaloradas, bonos, efectivo y participaciones en oro?

En todas estas situaciones, queremos encontrar el mejor modo de hacer algo. O dicho de un modo más formal: queremos encontrar los valores de determinadas celdas de una hoja de cálculo que optimicen (aumenten o disminuyan) un determinado objetivo. La herramienta Solver de Excel le ayudará a resolver los problemas de optimización.

Definir un modelo de optimización

Un modelo de optimización consta de tres partes: la celda objetivo, las celdas cambiantes y las restricciones.

Celda objetivo

La celda objetivo representa el objetivo. Queremos reducir o aumentar la celda objetivo. En el ejemplo de una mezcla de productos de una empresa farmacéutica, el director de planta querrá probablemente aumentar la rentabilidad de la planta cada mes. La celda que mide la rentabilidad será la celda objetivo. En la tabla siguiente, se incluyen las celdas objetivo de cada una de las situaciones descritas al principio del artículo.

Modelo Aumentar o reducir Celda objetivo
Mezcla de productos de una empresa farmacéutica Aumentar Ganancias mensuales
Envío de Xbox Reducir Costos de distribución
Precio de Xbox Aumentar Ganancias de las consolas y juegos Xbox
Iniciativas de proyectos de Microsoft Aumentar Valor neto actual de los proyectos seleccionados
Clasificaciones de la Liga Nacional de Fútbol Reducir Diferencia entre los resultados previstos y los resultados reales de los partidos
Cartera de acciones Reducir Riesgo de la cartera

Tenga en cuenta que en algunas situaciones es posible que haya varias celdas objetivo. Por ejemplo, Microsoft podría tener un objetivo secundario para aumentar la cuota de mercado de Xbox.

Celdas cambiantes

Las celdas cambiantes son las celdas de la hoja de cálculo que podemos cambiar o ajustar para optimizar la celda objetivo. En el ejemplo de la empresa farmacéutica, el director de la planta puede ajustar la cantidad producida de cada producto durante un mes. Las celdas en las que se registran estas cantidades son las celdas cambiantes de este modelo. En la tabla siguiente se muestran las definiciones de las celdas cambiantes adecuadas para los modelos descritos al principio del artículo.

Modelo Celdas cambiantes
Mezcla de productos de una empresa farmacéutica Cantidad de cada producto fabricada durante un mes
Envío de Xbox Cantidad producida en cada planta cada mes enviada a cada cliente
Precio de Xbox Precios de la consola y los juegos
Iniciativas de programas de Microsoft Qué proyectos se seleccionan
Clasificaciones de la Liga Nacional de Fútbol Clasificación de los equipos
Cartera de acciones Fracción del dinero invertido en cada clase de activo

Restricciones

Las restricciones son delimitaciones que se aplican a las celdas cambiantes. En nuestro ejemplo de mezcla de productos, en la mezcla de productos no se puede utilizar más cantidad de cualquiera de los recursos disponibles (por ejemplo, materia prima y mano de obra) que la cantidad del recurso disponible. Además, no deberíamos producir más cantidad de un producto que la que los compradores estarían dispuestos a adquirir. En la mayoría de los modelos de Solver, hay una restricción implícita que establece que ninguna de las celdas cambiantes debe tener un valor negativo. Explicaremos con más detalle las restricciones de no negatividad en los siguientes capítulos. Recuerde que no es necesario que un modelo de Solver tenga restricciones. En la tabla siguiente se muestran las restricciones de los problemas presentados al comienzo del capítulo.

Modelo Restricciones
Mezcla de productos de una empresa farmacéutica

En la mezcla de productos no se utilizan más recursos que los disponibles

No producir más cantidad de un producto que la que se pueda vender

Envío de Xbox

No enviar desde una planta más unidades que la capacidad de la planta

Asegurarse de que cada cliente recibe el número de Xbox que necesita

Precio de Xbox Los precios no pueden apartarse demasiado de los precios de la competencia
Iniciativas de proyectos de Microsoft Los proyectos seleccionados no pueden utilizar más dinero o programadores con experiencia que los disponibles
Clasificaciones de la Liga Nacional de Fútbol Ninguno
Cartera de acciones

Invertir todo nuestro dinero en algún sitio (sin descartar los movimientos en efectivo)

Obtener un rendimiento previsto de al menos el 10 por ciento de nuestras inversiones

Instalar y ejecutar Solver

Para instalar Solver, haga clic en Complementos en el menú Herramientas y, a continuación, active la casilla de verificación Solver. Haga clic en Aceptar y Excel instalará Solver. Una vez instalado el complemento, puede ejecutar Solver haciendo clic en Solver en el menú Herramientas.

En la figura siguiente se muestra el cuadro de diálogo Parámetros de Solver, en el que se especifica la celda objetivo, las celdas cambiantes y las restricciones que se aplican al modelo de optimización.

 Nota   obtendrá más información sobre cómo realizar esta operación en cada uno de los artículos de modelos de Solver que se describen en la sección Vea también de este artículo.

El cuadro de diálogo Parámetros de Solver.

Una vez especificada la celda objetivo, las celdas cambiantes y las restricciones, ¿qué hace Solver? Para responder a esta pregunta, necesita conocer la terminología de Solver. Cualquier especificación de las celdas cambiantes que satisfaga las restricciones del modelo recibe el nombre de solución viable. Por ejemplo, en el caso de la mezcla de productos, cualquier mezcla de productos que satisfaga las tres condiciones siguientes será una solución viable:

  • En la mezcla no se utiliza más materia prima y mano de obra que la disponible.
  • La mezcla no produce más cantidad de cada producto que la demandada.
  • La cantidad producida de cada producto no es negativa.

Básicamente, Solver busca todas las soluciones viables y encuentra aquélla que tiene el "mejor" valor en la celda objetivo (el valor mayor para la optimización máxima y el menor para la optimización mínima). Esta solución se denomina una solución óptima. Algunos modelos de Solver no tienen ninguna solución óptima y otros tienen una solución única. Otros modelos de Solver tienen varias (e incluso infinitas) soluciones óptimas.

La mejor manera de comprender cómo se utiliza Solver consiste en examinar los ejemplos detallados. En la sección Vea también de este artículo, encontrará vínculos a artículos adicionales en los que se describe cómo se utiliza Solver para solucionar varios problemas importantes empresariales y de otro tipo.

Ponga a prueba sus conocimientos

En cada una de las situaciones descritas a continuación, identifique la celda objetivo, las celdas cambiantes y las restricciones.

  • Pido un préstamo de 100.000 dólares para una hipoteca a 15 años. El tipo de interés anual es del ocho por ciento. Realizo pagos mensuales. ¿Cómo puedo determinar la cuota mensual de la hipoteca?
  • ¿Cómo debería una empresa automovilística repartir su presupuesto en publicidad entre los distintos formatos publicitarios?
  • ¿Dónde debería una ciudad construir un único hospital?
  • ¿Cómo debería una empresa farmacéutica repartir el trabajo de sus vendedores entre sus productos?
  • Una empresa farmacéutica dispone de 2.000 millones de dólares para repartir en la adquisición de empresas biotecnológicas. ¿Qué empresas debe comprar?
  • El tipo impositivo imputado a una empresa farmacéutica depende del país en el que se crea el producto. ¿Cómo puede una empresa farmacéutica determinar dónde debe fabricarse cada fármaco?
 
 
Corresponde a:
Excel 2003