Optimización con Excel Solver

Solver es un programa complementario de Microsoft Excel que puede utilizar para optimizar los análisis hipotéticos.

Según O'Brien y Marakas, optimization analysises una extensión más compleja del análisis de búsqueda de objetivos. En lugar de establecer un valor objetivo específico para una variable, el objetivo es encontrar el valor óptimo para una o más variables objetivo, bajo ciertas restricciones. Luego, una o más variables se cambian repetidamente, sujeto a las restricciones especificadas, hasta que descubra los mejores valores para las variables de destino.

En Excel, puede usar Solver para encontrar un optimal value (máximo o mínimo, o un cierto valor) para una fórmula en una celda llamada celda objetivo, sujeta a ciertas restricciones o límites, en los valores de otras celdas de fórmula en la hoja de trabajo.

Esto significa que Solver trabaja con un grupo de celdas llamadas variables de decisión que se utilizan para calcular las fórmulas en las celdas de objetivo y restricción. Solver ajusta los valores en las celdas de la variable de decisión para satisfacer los límites de las celdas de restricción y producir el resultado que desea para la celda objetivo.

Puede utilizar Solver para encontrar soluciones óptimas para diversos problemas, como:

• Determinación de la mezcla de productos mensual para una unidad de fabricación de medicamentos que maximiza la rentabilidad.

• Programación de la fuerza laboral en una organización.

• Resolviendo problemas de transporte.

• Planificación financiera y presupuestación.

Activación del complemento Solver

Antes de continuar con la búsqueda de una solución a un problema con Solver, asegúrese de que Solver Add-in se activa en Excel de la siguiente manera:

• Haga clic en la pestaña DATOS en la cinta. losSolver El comando debería aparecer en el grupo Análisis como se muestra a continuación.

En caso de que no encuentre el comando Solver, actívelo de la siguiente manera:

• Haga clic en la pestaña ARCHIVO.
• Haga clic en Opciones en el panel izquierdo. Aparece el cuadro de diálogo Opciones de Excel.
• Haga clic en Complementos en el panel izquierdo.
• Seleccione Complementos de Excel en el cuadro Administrar y haga clic en Ir.

Aparece el cuadro de diálogo Complementos. ChequeSolver Add-iny haga clic en Aceptar. Ahora, debería poder encontrar el comando Solver en la cinta de opciones en la pestaña DATOS.

Métodos de resolución utilizados por Solver

Puede elegir uno de los siguientes tres métodos de resolución que admite Excel Solver, según el tipo de problema:

LP Símplex

Utilizado para problemas lineales. UNASolver El modelo es lineal en las siguientes condiciones:

• La celda objetivo se calcula sumando los términos de la forma (celda cambiante) * (constante).

• Cada restricción satisface el requisito del modelo lineal. Esto significa que cada restricción se evalúa sumando los términos de la forma (celda cambiante) * (constante) y comparando las sumas con una constante.

Gradiente reducido generalizado (GRG) no lineal

Se utiliza para problemas suaves no lineales. Si su celda objetivo, cualquiera de sus restricciones, o ambas, contienen referencias a celdas cambiantes que no son de la forma (celda cambiante) * (constante), tiene un modelo no lineal.

Evolutivo

Se utiliza para problemas suaves no lineales. Si su celda objetivo, cualquiera de sus restricciones, o ambas, contienen referencias a celdas cambiantes que no son de la forma (celda cambiante) * (constante), tiene un modelo no lineal.

Comprensión de la evaluación de Solver

El Solver requiere los siguientes parámetros:

• Celdas de variable de decisión
• Celdas de restricción
• Células objetivas
• Método de resolución

La evaluación de Solver se basa en lo siguiente:

• Los valores de las celdas de la variable de decisión están restringidos por los valores de las celdas de restricción.

• El cálculo del valor en la celda objetivo incluye los valores en las celdas de variable de decisión.

• Solver utiliza el método de resolución elegido para obtener el valor óptimo en la celda objetivo.

Definición de un problema

Suponga que está analizando los beneficios obtenidos por una empresa que fabrica y vende un determinado producto. Se le pide que encuentre la cantidad que se puede gastar en publicidad en los próximos dos trimestres sujeto a un máximo de 20.000. El nivel de publicidad en cada trimestre afecta lo siguiente:

• El número de unidades vendidas, que determina indirectamente la cantidad de ingresos por ventas.
• Los gastos asociados y
• La ganancia.

Puede proceder a definir el problema como:

• Encuentre el costo unitario.
• Encuentre el costo publicitario por unidad.
• Encuentre el precio unitario.

A continuación, configure las celdas para los cálculos necesarios como se indica a continuación.

Como puede observar, los cálculos que se realizan para Quarter1 y Quarter2 que están en consideración son:

• El número de unidades disponibles para la venta en el Trimestre 1 es de 400 y en el Trimestre 2 es de 600 (celdas - C7 y D7).

• Los valores iniciales para el presupuesto publicitario se establecen en 10000 por trimestre (celdas: C8 y D8).

• El número de unidades vendidas depende del costo de publicidad por unidad y, por lo tanto, es el presupuesto para el trimestre / Adv. Costo por unidad. Tenga en cuenta que hemos utilizado la función Min para tener cuidado de ver que el no. de unidades vendidas en <= no. de unidades disponibles. (Celdas: C9 y D9).

• Los ingresos se calculan como Precio unitario * Número de unidades vendidas (Celdas: C10 y D10).

• Los gastos se calculan como Costo Unitario * No. de Unidades Disponibles + Adv. Costo de ese trimestre (celdas: C11 y D12).

• Las ganancias son ingresos - Gastos (celdas C12 y D12).

• La ganancia total es la ganancia en el trimestre 1 + la ganancia en el trimestre 2 (celda - D3).

A continuación, puede configurar los parámetros para Solver como se indica a continuación:

Como puede observar, los parámetros de Solver son:

• La celda del objetivo es D3 que contiene el beneficio total, que desea maximizar.

• Las celdas de Variable de decisión son C8 y D8 que contienen los presupuestos para los dos trimestres: Trimestre 1 y Trimestre 2.

• Hay tres celdas de restricción: C14, C15 y C16.

  • La celda C14 que contiene el presupuesto total es para establecer la restricción de 20000 (celda D14).

  • Celda C15 que contiene el no. de unidades vendidas en el Trimestre 1 es para establecer la restricción de <= no. de unidades disponibles en Quarter1 (celda D15).

  • Celda C16 que contiene el no. de unidades vendidas en el Trimestre 2 es establecer la restricción de <= no. de unidades disponibles en Quarter2 (celda D16).

Resolviendo el problema

El siguiente paso es usar Solver para encontrar la solución de la siguiente manera:

Step 1- Vaya a DATOS> Análisis> Solucionador en la cinta. Aparece el cuadro de diálogo Solver Parameters.

Step 2 - En el cuadro Establecer objetivo, seleccione la celda D3.

Step 3 - Seleccione Máx.

Step 4 - Seleccione el rango C8: D8 en el By Changing Variable Cells caja.

Step 5 - A continuación, haga clic en el botón Agregar para agregar las tres restricciones que ha identificado.

Step 6- Aparece el cuadro de diálogo Agregar restricción. Establezca la restricción para el presupuesto total como se indica a continuación y haga clic en Agregar.

Step 7- Establezca la restricción para el número total. de unidades vendidas en el Trimestre 1 como se indica a continuación y haga clic en Agregar.

Step 8- Establezca la restricción para el número total. de unidades vendidas en el Trimestre 2 como se indica a continuación y haga clic en Aceptar.

Aparece el cuadro de diálogo Solver Parameters con las tres restricciones agregadas en el cuadro: Sujeto a las restricciones.

Step 9 - En el Select a Solving Method cuadro, seleccione Simplex LP.

Step 10- Haga clic en el botón Resolver. Aparece el cuadro de diálogo Resultados de Solver. SeleccioneKeep Solver Solution y haga clic en Aceptar.

Los resultados aparecerán en su hoja de trabajo.

Como puede observar, la solución óptima que produce la máxima ganancia total, sujeta a las restricciones dadas, es la siguiente:

• Beneficio total - 30000.
• Adv. Presupuesto para el trimestre 1 - 8000.
• Adv. Presupuesto para el trimestre 2 - 12000.

Pasando por las soluciones de prueba de Solver

Puede recorrer las soluciones de prueba de Solver, mirando los resultados de la iteración.

Step 1 - Haga clic en el botón Opciones en el cuadro de diálogo Parámetros de Solver.

los Options aparece el cuadro de diálogo.

Step 2 - Seleccione el cuadro Mostrar resultados de iteración y haga clic en Aceptar.

Step 3 - el Solver Parametersaparece el cuadro de diálogo. Hacer clicSolve.

Step 4 - el Show Trial Solution Aparece un cuadro de diálogo que muestra el mensaje: Solver paused, current solution values displayed on worksheet.

Como puede observar, los valores de iteración actuales se muestran en sus celdas de trabajo. Puede evitar que Solver acepte los resultados actuales o continuar con Solver para que no encuentre la solución en pasos posteriores.

Step 5 - Haga clic en Continuar.

los Show Trial SolutionEl cuadro de diálogo aparece en cada paso y, finalmente, después de encontrar la solución óptima, aparece el cuadro de diálogo Resultados de Solver. Su hoja de trabajo se actualiza en cada paso, mostrando finalmente los valores de resultado.

Guardar selecciones de Solver

Tiene las siguientes opciones de guardado para los problemas que resuelve con Solver:

• Puede guardar las últimas selecciones en el cuadro de diálogo Parámetros de Solver con una hoja de trabajo guardando el libro de trabajo.

• Cada hoja de trabajo de un libro de trabajo puede tener sus propias selecciones de Solver, y todas se guardarán cuando guarde el libro de trabajo.

• También puede definir más de un problema en una hoja de trabajo, cada uno con sus propias selecciones de Solver. En tal caso, puede cargar y guardar problemas individualmente con Cargar / Guardar en el cuadro de diálogo Parámetros de Solver.

  • Haga clic en el Load/Savebotón. Aparece el cuadro de diálogo Cargar / Guardar.

  • Para guardar un modelo de problema, ingrese la referencia para la primera celda de un rango vertical de celdas vacías en el que desea colocar el modelo de problema. Clic en Guardar.

• El modelo del problema (el conjunto de parámetros del solucionador) aparece comenzando en la celda que ha proporcionado como referencia.

• Para cargar un modelo de problema, ingrese la referencia para todo el rango de celdas que contiene el modelo de problema. Luego, haga clic en el botón Cargar.