MegaSack DRAW - 6pm Christmas Eve - LIVE on our YouTube Channel
I've been working on and off this for a couple of days now and still cannot come up with a solution without resorting to trail and error.
Problem:
We make 36 different paper coil widths.
Each paper coil must be wound onto to a cardboard core.
Each paper coil width made must be flush with the cardboard core it is wound on to we can trim down the cardboard to suit the paper width off line.
Instead of purchasing 36 different cardboard cores to suit each paper width we produce, we'd only to like to purchase 5.
For every millimetre of cardboard core trimmed off there is a cost involved of 4p per millimetre wastage.
The year to date usage is in column C.
I want to work out which 5 externally purchased core sizes minimise the sum of the total cost.
In Summary:
Col A: Paper Width
Col B: Core to Purchase
Col C: Usage year to date
Col D: Core Price per mm
Col E: Waste in mm (Col B minus Col A)
Col F: Total Waste Price = Col E * Col D * Col C
I have set excel solver to minimise the sum of column F, given that column B must be >= column A.
I want to restrict the number of answers excel gives me. When I run this it gives me 36 answers, but I want to limit this to 5 to cover all 36 widths.
I have tried putting a unique count formula at the bottom of column B in the name of:Â =SUM(IF(FREQUENCY(B2:B37,B2:B37)>0,1)) and then adding this as a constraint by setting it equal to 5 but the solver just cannot deal with it.
Any help would be greatly appreciated.
If you don't get anywhere with this, email me your file and I'll take a crack at it tomorrow
Whilst I am sure you can ultimately solve this in Excel it seems like the wrong tool for the job.
The only way that jumps to mind to solve this in Excel is to perform the wastage calculation for each of the possible combinations of 5 core sizes. Â Personally I'd write some fairly simple code to assess all the options and report the outcome.
Out of interest how did you determine that your optimal solution would come from 5 cores?
Excellent problem. I would think that you should be able to do it with solver but you might have to change the way you are doing the solve
I would look at having a table of the 5 different core sizes. You then set solver to use those 5 different sizes as the parameters to adjust
Then column B, the core size to use, has to be looked up to be the next largest value from the table of available core sizes. You need a combination of MATCH and INDEX for this (there are various solutions on the web). You know that the next available size gives you the minimum waste in each case
Finally you can calculate a total wastage for all 36 cores and you set solver to minimise that by adjusting the 5 core sizes
I think the trick is to have a table of the 5 core sizes and let Excel work through that.
I haven't used solver in ages but as far as I can see you are going to need to take care with the 'method' that you use. The problem is non-linear because you are picking the next largest core size each time. I think you probably have to use 'evolutionary' rather than any of the methods that try to predict where the minimum will be as they just won't be able to do it with the selecting of the next core size up
Note that this could take a long time. With 36 paper sizes and lets say cores are available in 1mm increments from 1 to 100mm then that is 72millions combinations to go through (I think). Computers are good at that stuff though đŸ™‚
Here you go, solver in Excel 2016
I've only solved for 11 different paper widths but you just insert the other ones that you want and wait a bit longer for it to run đŸ™‚
Note that we only run solver on 4 core widths. We just set the largest core width by hand as you already know it
I'm assuming that core widths are available in 1mm increments and that the minimum core is 1mm. You can change it to what you like under the constraints in solver or do it using a cell, whichever you fancy
Shout if it doesn't make sense. It seems to work and give a sensible answer but who knows
edit:oops, forgot to include Qty in the waste formula. done now
Forgot to say, you will need to download it rather than look at it online as solver doesn't run in a browser
</stalk>
Fantastic neat solution - thank you leffeboy!
Pleasure. It’s almost a perfect use for solver really and a nice little distraction
