Solver - Adding Constraint to maintain the proportion of variables

Hello Experts,

I have attached an excel sheet with objectives, variable and constraint defined. My Variable are from Cell C49 to C63. I want to add an constraint to make sure that the proportion of variables to the sum of variables in close to proportion of given forecast qty ( cell B27:B41). How can I add this constraint

Thanks,

After most of a day, I will venture to respond.

You have already added a couple of constraints to your solver model, so I'm assuming that you don't have any question about the mechanics of adding constraints. Can you help us understand exactly what part of adding this constraint you are having trouble with? If it helps, I would expect to:

1) Somewhere in the spreadsheet, calculate "closeness of match" between the two ranges. I did not take the time to fully explore your sheet, but this calculation did not immediately jump out at me.
2) Because Solver does not include a "constraint to minimum or maximum" option, I would expect to choose a threshold value that makes sense to me, then add a constraint that the "closeness of match" value does not exceed the threshold. Details, obviously, depend on what you are using to calculate "closeness of match."
3) Perhaps run the model a few different time with different values for the threshold until you understand how the main objective function and the constraint function interrelate.
4) Evaluate the result(s) to see if progress is being made, or if a different approach needs to be taken.

Help us understand exactly what you need help with, and we will try to help as best we can.

I suppose the yellows values need to be integer.
I added an extra contraint for the max % difference, now it's up to you to finetune A67

Max % is only one possible way to measure deviation from Forecast qty. As I asked before, how would you like to calculate deviation from forecast? If you can help us understand how you want to calculate devation from forecast, we should be able to help you program that into the sheet.

with VBA in a loop.
First a solution without that max%-constraint.
Then in a loop each time -0.5% and copy the results to line 75

What is the largest value in A65 that you would accept? I don't have Excel to test with, but What happens if you add a constraint to your Solver model that A65 must be smaller than the largest value you would accept?

then you use sum(A49:A63) instead of max(A49:A63).
When you try to shrink that constraint, perhaps you get unwanted sideaffects.

Here is an alternate take on your problem.

Instead of minimizing directly the deviation, we bracket our variables between an upper and lower bound (two constraints have been added), and then we seek to minimize this range, while maximizing production.

Solver is still based on Simplex, the main loop uses the bisection method.

'Initialization
Set wks = ThisWorkbook.Worksheets("Sheet1")
bracket_upp = wks.Range("C43").Value
bracket_low = 0
eps = 1 * 10 ^ -5
Set delta = wks.Range("A46")
delta.Value = (bracket_upp - bracket_low) / 2
'First run
result = SolverSolve(UserFinish:=True)
Set max_prod = wks.Range("D65")
best_prod = max_prod.Value
'Bisection
While Abs(bracket_upp - bracket_low) > eps
    result = SolverSolve(UserFinish:=True)
    If max_prod.Value <> 0 Then
' Save best_prod
        best_prod = max_prod.Value
' Save min delta
        best_delta = delta.Value
' We must decrease upper bound
        bracket_upp = bracket_upp - (bracket_upp - bracket_low) / 2
    Else
' We must increase lower bound
        bracket_low = bracket_low + (bracket_upp - bracket_low) / 2
    End If
    delta.Value = (bracket_upp + bracket_low) / 2
Wend
'Final iteration
delta.Value = best_delta
result = SolverSolve(UserFinish:=True)

HTH,

Francesco

You are asking both for maximizing production, and keeping it proportional to forecasting (minimizing deviation).
The point is, you have two objectives that cannot be compared to each other directly, and you must choose what is the "right" answer for you.
A production of 125 with a deviation of 9% is better or worse than a production of 119 and a deviation of 6%? (I'm assuming both solutions are feasible, that is, al the other constraints are satisfied.)

If we wanted only to maximize production, your original setup was fine.
When we also wish to keep our production proportional to forecasting, we can add this constraint forcing variables to be within a range.
We solve this maximization problem, find (hopefully) a solution, and then make this range smaller. However, this constraint limits the number of feasible solutions, therefore max production may change as well.
At each iteration, the values of variables are closer and closer to the ideal proportion given by the forecast. Some are bigger and some smaller, and sum of the deviation keeps shrinking. Sooner or later, we reach a point where one variable will be at the edge of the interval and we cannot go further.

In your workbook, this happens for the first variable in D49 at 0.3%. If we try to reduce its value from 4 to 3.9, the sum (81) will be smaller, but then second, third and fourth variables would be pushed out of their interval as well.
So we are forced to reduce them, the sum becomes even smaller, and then it's a race to the bottom that leaves us with the only solution where each variable (and production) is zero. Please note that keeping variables proportional to forecasting may not give you the smaller deviation.

Keeping your last workbook as reference, the first solution of 100 has a deviation of 43.9%. Is this better or worse then the final one?
If we could find a solution that has a production of 99 and a deviation of 0%, would it be better or worse?
And if the deviation of the final solution (81) was 0.8%, hence smaller than what was found, but due to only one variable (thus giving us a solution that is less "proportional"), would it be better or worse?

Answering these questions may help us find a solution closer to your request.

HTH,

Francesco

Originally Posted by piku9290dgp

I am hoping that the while loop is not infinite loop, or please let me know if there is any case when it can go infinite.

I don't think so. Even if you set a negative value in C43, the loop will end. Anyhow, I do hope you are not interested in negative (or imaginary) production.
If you want to be absolutely sure, add a new declaration
Dim i as Long
set it to zero at the first iteration (out of the loop), change the first line of the loop to
While Abs(bracket_upp - bracket_low) > eps And i < 1000
and then write
i = i + 1
just below the While instruction

I used your code for my actual dataset and my manager loves it :D

Glad to hear that!

I would like to add only one thing. If the workbooks you attached are representative of your real problem, there is a very slim chance you will find a solution with the same production value and a smaller deviation.

You may also want to check the solution offered by bsalv, maybe it's more suitable for your problem.

If this takes care of your question, please mark the thread as solved.

HTH,

Francesco

or you can do it my way.
You write the results of every loop just beneath that range, compare visual and choice the best option.