The Excel Solver a tool in solving particular problem has its build in limitations when it comes to the size of the problem.
One way to overcome the size problem is to break down the big problem in several small parts and solve these as stand alone. Then add all these bits and hope that the sum of these reflects the true solution of the big problem.
But how sure is it that the sum of a number of small solutions where a few factors are decided at a time as opposed to the solution where all factors are taken in account at the same time should give the same answer?
Looking at an example where there is a production of a number of lists of different length to be made from a standard list of given length and the wastage should be minimized I’ve set up two models. Sheet “Solver_suboptimal” uses a macro to loop solver until the demand of lists is met.
The other sheet “All_in_one_go” I’ve tried to model all the possible combinations of lists that adds up to standard length or less where solver selects what combinations that makes up the total demand with a minimum wastage.
To run the macro “SolverLoop” the sheet “Solver_suboptimal” must be the active sheet and a reference to solver must be set in “Visual Basic”
Developers “Tab” -> Click “Visual Basic” icon -> “Tools” -> “References” and tick box “Solver”. Click “OK” and close “Visual Basic” Window.
To run solver on sheet “All_in_one_go” just click “Data” tab, select solver and click button “Solve”. After 15 seconds a message will pop up saying “The maximum time was …..” click stop to get the result as solver is not sure it has found it but in the bottom left corner you see the text “Incumbent” and a number and this number stabilizes after 4 – 5 seconds so solver has found a solution but not absolutely sure.
Comparing the solutions the suboptimal one needs 378 stock rod to meet demand with a waste of 260920 as compared to all_in_one_go needing 364 stock rods producing a waste of 175264.
If the number of lists to produce becomes less then the difference between these two setups becomes less but this is difficult to predict unless bot setups are tested.
To set up all combinations is quite a tedious process and if a list length is changed the the whole setup must be rewritten. To see how the formulas are made write 1 for each cell in range L4 to L54.
The “Solver_suboptimal” is very easy to set up and a change in list length will not matter but for a big number of lists it will be as accurate as the “All_in_one_go”.
Finally the is a small walk-through of the macro used.
Alf
Bookmarks