Cálculos de optimización con hoja de cálculo

Sabiendo distinguir las variables de un problema de optimización, es hora de que encuentres la solución óptima de este con la ayuda del Solver en una hoja de cálculo.

Ordenamiento de los datos.

En el siguiente ejemplo se definen los datos del problema: Una empresa productora de balones requiere saber cuántos balones de fútbol y básquetbol tiene que hacer con una cantidad de caucho determinada para maximizar sus ganancias.

• Las ganancias que se generan por las ventas de cada uno de los tipos de balones. 50 monedas por cada balón de fútbol y 40 por los de básquetbol.
• La cantidad de materia usada para cada uno de los balones, se pueden crear máximo 500 balones de fútbol o 400 de básquetbol.
• El tiempo que tarda en hacer cada tipo de balón, sólo se pueden hacer 400 balones de fútbol y 300 de básquetbol al mes.

 Para esto, se deben ordenar los daros del problema en la hoja de cálculo en tres secciones

 

Unidades a Producir

Balones de Fútbol

Balones de Básquetbol

 

Unidades a Producir	Caucho por balón	Caucho Total
Balón Fútbol
Balón Básquetbol
Caucho Total Utilizado
Caucho Disponible	50,000

 

Ganancia por utilidad
Balones de Fútbol	$50
Balones de Básquetbol	$40
Ganancia Total	$ -

• Total de Unidades: En este caso se tiene la cantidad de balones de cada tipo a producir. Estos datos cambiarán automáticamente cuando se genere la solución.
• Material utilizado por unidad: Se señala el total del material utilizado por tipo de balón y el material disponible. En este ejemplo es el caucho.
• Ganancias que se obtienen por unidad y totales: En esta última casilla es donde colocas tu función objetivo de la siguiente forma: ganancias por cada balón de fútbol por la cantidad de balones de fútbol producidos más la ganancia por unidad de balones de básquetbol por la cantidad de balones de básquetbol producidos. 

Aplicación de Solver.

Una vez que tengas organizados tus datos, sigue estos pasos para utilizar el Solver:

• Mantén seleccionada la casilla de las ganancias totales.
• Dirígete a la pestaña de datos y en el apartado de análisis da clic en Solver.
• Selecciona la opción Max para Maximizar.
• Selecciona tus variables de decisión ubicadas en la casilla llamada cambiando las celdas de variables.
• Agrega las siguientes restricciones:

o   El material total usado debe ser menor o igual al material disponible.

o   El total de pelotas de fútbol producidas debe ser menor o igual a 400, ya que solo pueden producir esa cantidad durante el tiempo disponible.

o   El total de pelotas de básquetbol producidas debe ser menor o igual a 300, ya que por tiempo solo pueden producir esa cantidad.

• Selecciona como método de resolución Simplex LP y marca la opción Convertir variables sin restricciones en no negativas.
• Haz clic en Resolver y Aceptar.

Si realizas este proceso de forma correcta, el número de balones producidos habrá cambiado a la solución óptima para el problema.

 

Fuente:
Fundación Carlos Slim – Curso Analista de Datos.