Recommendations for Solving Optimization Problems with the Solver Add-in

Building a Mathematical Model of the Problem

Work on solving any optimization problem always begins with building a mathematical model, which requires answering the following questions:

• What are the variables of the model (i.e., which quantities is the model built to determine)?
• What is the goal, for which the optimal values are chosen from among all admissible values of the variables?
• What constraints must the unknowns satisfy?

It should be noted that when constructing a model, formulating the constraints is the most critical part. In some cases, constraints are obvious (e.g., a raw material limitation). Other constraints may be less obvious and could be defined incorrectly. For example:

• In a multi-period model, the value of a material resource at the beginning of the next period must equal its value at the end of the previous period.
• In a supply model, the stock at the beginning of a period plus the quantity received must equal the stock at the end of the period plus the quantity shipped.
• Many quantities, by their physical meaning, cannot be negative (e.g., the number of units received).

At this stage, conclusions are drawn about:

• the source data (deterministic or random),
• the decision variables (continuous or discrete),
• the ranges within which the variables can lie,
• the dependencies between variables (linear or nonlinear),
• and the criteria by which the optimal solution should be found.

This also includes handling inconsistency and unboundedness of the objective function:

• For maximization, the feasible region must be bounded from above.
• For minimization, it must be bounded from below.

Preparing the Excel Worksheet for Optimization

It is recommended to:

• Correctly arrange all source data on the worksheet.
• Properly enter the formulas for the objective function and other relationships.
• Reserve a place for the variable values.

Solving the Problem with the Solver Add-in

All constraints, variables, objective function, and other values should be correctly entered into the Solver Parameters window.

Most optimization problems are linear programming problems, where both the objective function and the constraints are linear. In this case, you must enable the Assume Linear Model option in the Solver Parameters window. This ensures the use of the Simplex method. Otherwise, even for linear problems, Solver will use more general (and slower) methods.

Solver can also handle nonlinear dependencies and constraints, such as nonlinear programming problems or solving systems of nonlinear equations. For Solver to work effectively, dependencies should be smooth or at least continuous. Discontinuous dependencies most often arise when using the IF() function with variable arguments. Problems may also occur when using functions like ABS(), ROUND(), etc.

In the case of nonlinear dependencies, it is advisable to:

• Provide initial guesses for the decision variables (sometimes graphical analysis can give approximate solutions).
• In the Solver Options window, disable the Assume Linear Model option (if enabled).

For integer programming problems, remember the requirements of integrality and Boolean conditions.

Analyzing the Optimization Solution

If necessary, analysis of the solution can be carried out. Often, the solution is also presented in the form of charts or graphs. Solver can also generate a Solver Report.

There are three types of reports:

1. Answer Report – contains the final values of the objective function and constraints.
2. Sensitivity Report – shows the results of small changes in Solver parameters.
3. Limits Report – demonstrates the effect of sequentially maximizing and minimizing each variable while holding the others constant.

The type of report is selected at the end of the solution process in the Solver Results window . Multiple reports (two or all three) can be generated simultaneously.