Forum Discussion
Solver Feasibility troubleshooting
I have a Solver problem. The solver returns "Solver could not find a feasible solution" but I do not understand why.
Thanks
Brad
Can you explain the problem you're trying to solve?
- I am working on a scenario where I am ordering 3500 shirts and to bring the total on hand inventory to reflect the scale of sales experience.
bracurrie wrote: ``The solver returns "Solver could not find a feasible solution" but I do not understand why``
Because there is indeed no solution that meets all of your criteria:
You want to add 3500 units by allocating the new units in G4:G20 so that the new totals in H4:H20 have the same distribution (I4:I20) as the target distribution in D4:D20.
But by enabling "make unconstrained variables non-negative", you have the additional constraint that none of the values in G4:G20 is negative. That is what makes a solution in feasible.
By disabling that constraint, we see that the solution is:
IOW, we must reduce some items in order to achieve the target distribution -- if you add only 3500 units.
Alternatively, we could determine the minimum number of additional units (G21) that is necessary to achieve the target distribution with only non-negative amounts in G4:G20.
The result is:
Presumably, that is not an acceptable solution. I suspect that 3500 was chosen based on other requirements or limitations.
So, finally, you might want a solution where none of G4:G20 is negative, but the total is 3500.
In that case, we cannot achieve the same overall distribution that is in D4:D20.
But we can achieve the same relative distribution for the positive increases in G4:G20.
LMK if your are interested in that solution. It might be a bit more involved. (TBD)
-----
BTW, none of these solutions require the use of Solver.
For example, for the original Solver problem, but allowing negative values in G4:G20, we get the same results with the following formulas:
G4: =H4-E4
H4: =D4*$E$23
Copy G4:H4 into G5:H20
Thank you for your thoughtful reply.
I am very interested in pursuing: "But we can achieve the same relative distribution for the positive increases in G4:G20."
Also , I need integer results and even case quantities for each row which hadn't been defined yet.
Your suggestions and help are greatly appreciated.
Brad
bracurrie wrote: ``I am very interested in pursuing: "But we can achieve the same relative distribution for the positive increases in G4:G20."``
The following image demonstrates what I have in mind. See the attached Excel file for details.
However, first, I should say that I am no longer confident that this is a reliable method. I wonder if there are cases where the "w new" amounts (I4:I20) might overshoot the target allocations (column D) for the positive increases in column G. But I'm afraid I do not have time to pursue this further.
I explain this as a multi-step procedure. I believe it make things clearer. But especially with Excel 365, I suspect that we can consolidate some steps and formulas.
(Since I do not use Excel 365, any assistance with that consolidation must come from someone else.)
1. In G4:G20 ("trial new units"), we determine how to allocate the target total new units (E23) so that the distribution of the target total "w new" (E24) in I4:I20 would achieve the target "average scale" (D4:D20), allowing for negative values.
The formula in G4 is: =ROUND($E$24*D4 - E4, 0)
2. In E25, we count the number of negative values in G4:G20.
The formula is: =COUNTIF(G4:G20,"<0")
If E25 is zero, we can simply "copy" the values in G4:G20 into H4:H20. Skip to step #5.
3. But if E25 is non-zero, we must avoid increasing the corresponding units on hand in I4:I20.
To that end, in E26, we calculate the sum of the target "average scale" percentages that correspond to the positive values in G4:G20.
The formula is: =SUMIFS(D4:D20,G4:G20,">=0")
4. Then in H4:H20, we prorate the target new units (E24) by the target "average scale" percentages that correspond to the positive values in G4:G20 relative to their sum in E26.
Effectively, the formula in H4 would be: IF(G4<0, 0, ROUND($E$23*D4 / $E$26, 0))
5. Consolidating the formula in step #2 for E25=0 and the formula in step #4, the actual formula in I4 is:
=IF($E$25=0, G4, IF(G4<0, 0, ROUND($E$23*D4 / $E$26, 0)))
6. The "w new" formula in I4:I20 is: =E4+H4
7. Finally, in J4:J20, we calculate the new distribution.
The formula in J4 is: =I4/$I$21
If E25=0 (all positive increases in G4:G20), the percentages in J4:J20 should be the same as the target "average scale" in D4:D20, subject to integer rounding.
Otherwise if E25>0 (some negative changes in G4:G20), at the very least, the percentages in J4:J20 should be a step in that direction.
-----
A word about rounding....
I have rounded each calculation to integers, as you requested.
But note that the sum of the "trial new units" (G21) is 3499, not 3500.
And it is merely a coincidence that the sums in H21 and I21 are their intended targets.
Such rounding "error" should be expected because "the sum of the rounded parts is not necessarily the same as the rounded whole".
For that reason, I usually do not to round intermediate calculations.
Instead, when all is said and done, I will add one or more columns (as needed) to round the non-integer values. That allows us to implement a "clever" rounding algorithm that distributes the rounding "error" and results in exactly the target total.
