CODEWITHSUNDEEP
excelvbaoptimization
Vedda
Vedda

Reputation: 7435

Excel Solver Not Providing Consistent Results

I'm trying to use solver to solve a non-linear problem, but the results are not consistent and change under certain conditions. This problem does not exist when using Risk Solver Platform, but does when using the regular Excel Solver. The optimization and constraints are not complex, so I don't understand what is going on.

Here is the file: temp.xlsm

Notice there are macros involved with buttons attached to run the solver.

To Replicate problem:

Note: Optimal Net Revenue is now lower and in the last month the last column is negative when it should be zero. It seems like solver needs to be reset everytime, or there is a bug, but I can't figure it out. Here's the macro:

Sub Monthly_Solver()

    Application.Run "SolverReset"
    Application.Run "SolverOk", "$T$18", 1, "0", "$F$6:$F$17,$N$6:$N$17", 1, "GRG Nonlinear"
    Application.Run "SolverAdd", "$F$6:$F$17", 5, "binary"
    Application.Run "SolverAdd", "$N$6:$N$17", 1, "$M$6:$M$17"
    Application.Run "SolverAdd", "$N$6:$N$17", 3, "0"
    Application.Run "SolverAdd", "$T$18", 3, "0"
    Application.Run "SolverSolve", True

End Sub

Upvotes: 0

Views: 4995

Answers (1)

user2285236
user2285236

Reputation:

GRG Nonlinear does not guarantee finding the global optimal solution. It finds a local optimal solution and the quality of that solution is affected by the initial values you supply to the algorithm. From Frontline's website:

When the message “Solver found a solution” appears, it means that the GRG method has found a locally optimal solution – there is no other set of values for the decision variables close to the current values that yields a better value for the objective function. Figuratively, this means that Solver has found a “peak” (if maximizing) or “valley” (if minimizing) – but if the model is non-convex, there may be other taller peaks or deeper valleys far away from the current solution. Mathematically, this message means that the Karush - Kuhn - Tucker (KKT) conditions for local optimality have been satisfied (to within a certain tolerance, related to the Precision setting in the Solver Options dialog).

Right now, your Harvest column has 3131 in N14:N16. If you change initial infestation to 1% and solve again, N14:N17 will have the value of 3131. So your initial solution has changed. If you change it back to 11% without changing the value in N17 the solver may found another local optimal solution. If you set N17=0 you will get the same solution as the first one.

On a different note, if the only reason for GRG Nonlinear is the binary Spray variable and the if/then conditions it represents, you can use linear constraints (so global optimality is more likely). Here's a quick reference.

Upvotes: 1

Related Questions