Amir Melamed for submitting
the best solution to the August 2014 Reader Challenge. Like many other
readers, Amir used the Excel
Solver approach, but Amir's solution bettered them all to derive a
maximum portfolio of $70,184, as follows:
You can download Amir’s
solution at www.CarltonCollins.com/amir.xlsx.
Many readers, including myself, had trouble using Excel’s Solver because
this tool is limited, as follows: In Excel Solver, the number of variables is
limited to 200, and the number of constraints you can apply to
non-decision variables is limited to approximately 100 (depending on
whether you use a linear or nonlinear model) and the specific
configuration of the constraints. I got around these
limitations by dividing my solution up into 4 solver calculations, each
solving one quarter of the entire problem, and then combining the
results.
Amir ingeniously got around this
problem by creating calculations that simplified the problem into a 52
week numbers problem, expressed as integers.
Thereafter, he used
Solver to identify the specific 10 combinations of the 52 weeks that
would derive the top solution. The simplicity of this “week number”
approach can be seen in the following screenshot of Amir’s
Amir’s
Amir’s
Amir’s
I learned from this challenge that Excel’s solver
is limited, I was not aware of these specific limitations, although I
did understand that billions of calculations can overwhelm Solver.
Further, Amir’s solution helped me understand that Solver works best
when you can reduce your problem down to a simple series of integers. I
also learned that if you reached the limit of 100 constraints, you may
be able to overcome this obstacle by using the following steps:
1.
If you have
constraint cells containing simple formulas, such as =A1 (where A1 is a
decision variable), then these cells count against the limit of 100; but
if you apply the constraint directly to the decision variable cells
themselves, it won't.
2.
If a constraint
involves two or more decision variables, it might be helpful to place
individual limits on these variables instead.
3.
If you have
formula based constraints calculated into your worksheet, whose values
depend on the decision variables, then Solver treats them against the
constraint limit, even if they are decision variables. In this case, you
should replace these formula based constraints with individual cell
limits, if possible.
4.
If all else
fails, an excellent option may be to upgrade to Frontline's Premium
Solver Pro which has a limit of 1,000 constraints for linear problems,
and 250 constraints for nonlinear problems (apart from bounds on the
variables and integer constraints), and it can solve problems faster
than Excel Solver. In addition, Frontline’s Large-Scale GRG Solver
handles problems with thousands of constraints, in addition to bounds on
the variables. You can download a free 15 day trial of these Excel
add-in solutions at www.solver.com,
as pictured below.
I downloaded Frontline’s Premium Solver Pro and added it in to Excel and then used this solution, here is the solution’s ribbon toolbar.
I was particularly pleased that Frontline’s
Premium Solver Pro automatically detected and picked up the parameter
settings from Excel Solver, so I did not have to re-create them from
scratch.
I had always visualized
this challenge as a problem for Excel Solver, but some readers took
different approaches to solving this problem, a few of which are briefly
mentioned below:
1.
2.
3.
4.
5.
6.
Many readers indicated that they struggled with
this challenge and ultimately, many of them simply gave up. Kudos to
those readers who accepted the challenge, and put forth the effort to
submit a solution – you represent the cream of the crop.
Congratulations. |