Optomize raw material

Hello,

I am looking for a spreadsheet which will divide and ultilise 3 different raw material lengths in to several pallet wood requirements based on monthly production.

Currently doing this manually which is an absolute nightmare and very time consuming.

For example my raw materials (all in mm) are 4800, 3600 and 3000. From these lengths I will produce around 10 different raw material per length. 16x75,16x95,16x120,18x75 and so on.

There are 27 different products on order (as it stands) I am looking for a formula/spreadsheet which will optomize the best cutting plan to divide up the raw materials available.

Any help would be greatly appreciated.

I suggest you amend the linked names in K6 etc as they use inappropriate language re file names.

You mis-understood my post: please look at the formula in K6 [and many others] and see the file name "**** YOU PALLET.xlsx" !

Since pieces cut has both length and width i.e. they are two dimensional but to maximize use of cutting stock you will cut these parallel with stock length and perhaps also parallel to the stock width.

On the other hand since you are cutting wood you probably would cut along the fiber in the wood for maximum strength so this problem may never arise,

rotated.jpg

Alf

I've set up a solver model for one case based on the raw data in cell S2. Since T, W are the same for this case I've only modeled for length and used numbers from orders column as the number of pieces that shall
be produced.

The Excel solver had some problem with the model saying it was not linear so I had to run the GRG nonlinear engine. I also tested the OpenSolver model a freebie made by the University of Auckland NZ. Their model had no
problem with linearity.

Included excess production as wastage. the only thing I have not modeled is thickness of cut. Could be a problem as in some cases thee are no wastage.

The bad about this method are it requires a rebuild of the model when you change cut size as one tries to set up all possible combinations of cuts.

As you uploaded file is not that easy to understand I think an explanation of the different values the how and why would be of help.

Alf

Just a short intro on how I made the model.

First I tested a number of combinations of the different lengths to find combinations that added up to 4800. This is the time consuming part. Having got this setup I then build the solver model. Solver the excel add-in is not installed by default when Excel is in stalled.

https://support.microsoft.com/en-us/...c-e24772f078ca

To see the different combination just add 1 to every cell in range M13:M28. So a 1 in M13 will produce 2 pieces with a length of 2400 with a 0 waste, M14 will produce 1 of length 2400 and 2 of length 1200 also with 0 waste.

To run the model add the demand in cells F31:K31, clear range M13:M28, click on the "Data" tab and click "Solver" -> "Solve" button. Solver will chew on the number for some time and the result window will pop up and then click OK. That's all.

The solver settings are: "Set Objective" This is what one wish to achieve. In this case set to minimum as I wish to minimize total wastage.

Then "By changing cells" : the cells solver can change to reach the goal minimum wastage.

"Subject to the Constraint" : this tells solver how it can do to reach minimum wastage

1 The solver production must be equal to or greater than demand, range F31:K31. 2 The values in range M13:M28 must be integers

I'll have a go of your file again with your last info and see if I can improve my solver model or decide if solver is the tool you need.

Alf

Originally Posted by Alf

The Excel solver had some problem with the model saying it was not linear so I had to run the GRG nonlinear engine. I also tested the OpenSolver model a freebie made by the University of Auckland NZ. Their model had no
problem with linearity.

Hi Alf,

Solver is right, and OpenSolver is wrong (we know that the linearity check cannot be trusted).

The model is non linear because of the IF in column L. However, we don't need it, because we can calculate the waste per bar for a given configuration, and then it will be added to cell N29 only when used.

The best result for me is 4780. Excel does find this minimum easily, but then spends a huge amount of time looking for other combinations.

HTH,

Francesco

Do I need to set up a new solver for each size or can I change the sizes in the solver you sent?

This solver model is build for minimizing the waste when producing 6 different cuts 2400, 1200, 1140, 1000, 945 and 845. You can change the number of different cuts or remove some by changing the numbers in the range F31:K31 or remove the production of a cut by entering 0 in the appropriate cell.

What you can't do is adding a new cut say 1800, then you have to rebuild the model to the new yield structure you will get by adding a new cut. This is the downside of this kind of model.

Hi Fancesco:

I do like to keep my models as tidy as possible that's why I use the If statement but this is not the reason Excel solver tells the model is not linear. If you choose the excess production as target cell Solver has no problem and accept the model as linear or you could chose the total waste from bars again excel has no problem with linearity but if I combine the sum of "total waste from bars + excess production" then solver complains about linearity.

This is a setup I've used a number of times in excel 2010 (the If part and the sum of waste from bars and excess production) and excel2010 did not have any problem with it but excel 2019 seems to have it.

In this case it was faulty formulas in range L14:L28, this has now been corrected in the uploaded file post 11. Strange I removed image in this post but it still shows????

Alf

Originally Posted by Alf

If you choose the excess production as target cell Solver has no problem and accept the model as linear or you could chose the total waste from bars again excel has no problem with linearity but if I combine the sum of "total waste from bars + excess production" then solver complains about linearity.

Hi Alf,

I cannot replicate this behavior on my PC. When I remove the IFs, Solver has no problem using Simplex LP (the problem is indeed linear) on total waste + overproduction. However, I can spot some problematic cells in your workbook: see for instance L18, which should be 255. I assume that the value in M18 has been changed in the last iteration of OpenSolver, and not updated because of the IF. This may (or may not) return a true optimal result.

HTH,

Francesco

Digging through older posts I finally found this old post.
https://www.excelforum.com/excel-for...ator-help.html
Check out the file uploaded by Hydraulics post 9 where he uses the MMULT function entered as array function to find the optimal cutting pattern with different stock lengths to minimize losses.

This seems a much easier method instead of manually setting up a cutting pattern as I did. So ask him if you could have a guided tour through his setup. Myself I would not mine a more thoroughly explanation on the how and the way.

Alf

Strange check that the range M14:M28 is formatted as numbers. In my uploaded file they are but if you copy the values from sheet "Pallet Dump" I column the numbers there are formatted as "Text"

numbers_as_text.jpg

Another possible error is defining this range as "Integer" before you define them but then you get a different error message telling you can only set cells to change as Integer,

Alf

There is another way of setting up solver. In this case solver is run in a loop using VBA.

The good: The format is free, that is you just enter the lengths and lets solver figure out the optimal cutting.

The bad: This technique may produce a suboptimal solution because every time solver finds an optimal solution these pieces are removed and the that gives solver a lesser chance to find the next good/optimal solution equal to the previous one in wastage.

To use this model a reference to solver must set in Visual Basic. Developers tab -> Visual Basic icon -> Tools -> References and tick box "Solver". Click OK and close Visual Basic Window.

Then run macro "SolverLoop"

Macro clears old data except "Demand" and "Stock Length" upgrade these when needed. Model is build for 10 different cuts but can easily be expanded.

The result shows the suboptimal effect still the total wastage is the same as I got in the previous uploaded file. Also the last piece of wastage is 3660 so big that it can be used later on.

The theoretical need for stock lengths are 31.0047 i.e. one needs 32 stock boards but from the last one only 2 cm so the loop result is not to far off.

As I said before the input of lengths is free, smallest to biggest or mixed small, big, small, small, big.

Just for comparison reasons I also tested with "OpenSolver" and got the same result, the macro for "OpenSolver" will need an installation of "OpenSolver" as well as a reference set to it in Visual Basic, but you do not really need it so you can delete the "OpenSolver" sheet and the "OpenSolver" macro.

Alf

@Alf,
i give you 150 possible combinations with the 3 raw lengths 4800, 3600 and 3000.
I suppose, if you use the Top-50 in your model instead of just your length of 4800, you can achieve a better result ???
And work with integer or that'll take to much time ?

I have a loss of 580 mm (cut off after 180 seconds)

Schermafbeelding 2022-08-19 005412.jpg

The solution to find all combinations with VBA.
The way solver should work with VBA isn't finished yet

thanks,

that part, i have no experience with it, it keeps giving errors, so if you know better ...
There were 150 combinations, but i think all combinations with a waste of for example >400 can also be deleted or 'll that give no advantage in calculating ?

I was also thinking to allow the model to produce extra items and keep them in stock for next week.
That was that extra orange line "valided waste" where that waste had a weight of 10% of the effective waste. (then the equation 'd be ">=" instead of "=")
But it didn't use that method in this example,
Perhaps if there were only 2-3 worse combinations in the initial setup then Solver 'll use this ????

Which one is better ? Solver or OpenSolver ?

i hope this one is Okay.
It's important for the combinations that the smallest length is in C2 to fill a certain cut with the max possible of that smallest length.
For the others lengths the sequence isn't important, but adviceable best ascending (not obliged).
I have no experience with the SolverOptions ....

Please Login or Register  to view this content.

in the example in the attachment, just a small problem with at waste of 20 mm and 1 length of 1000 mm that can be used next week, so it gets a penalty of 10% = 100 mm. Together the waste is 120 mm.

I also worked further with #27, added a loop to calculate ad random 10 possible demands of lengths.
5 of 20 questions were solved within a fraction of a second, the other 15 were until the limit of 30 seconds, but if you watch the statusbar, there was no progress made in those last 29 seconds, bizarre, perhaps you have to give him 3 minutes, but is it worth it ? The losses vary between 1,000-6,000 (column AC)
Is OpenSolver better ?
When those 30 seconds are finished, you get a popup, i can't let disappear. Is it possible ?

Formula:

Please Login or Register  to view this content.

those 1-10 is the range of numbers you can choose to fill the green range J5:U5, with those numbers, solver find very easy a solution.Change that range to 10-100, meaning that you want for every length at least 10 to 100 units, then solver can't find it anymore in 1-2-3. That's a little bit logic, the that almost infinite loop triggers me.

Where can o download OpenSolver, because he isn't in my references.

Most of the macro was done with the recorder, so that one normally adds a lot of rubbish, that are the parts you deleted !

INSTALLED and ready to go : amazing !!! what a speed.
In contrary with what i said a few hours ago, the random question with lots of lengths (100-10.000) are easier to solve then when the choice is between 1-10), even twice the time !!! (2 seconds instead of <1s)
And i also suggested to reduce the number of possible combinations (now approx. 150) to the one's with a waste < 400 (=approx 80 = half) but 15% of the solutions use at least 1 cut with a waste >400, surprising !!!
Average Waste = 0,75%
i'm convinced of this tool !!!
attachment with the OpenSolver in the macro

OP's last activity here on the site was #24, so send him a PM as reminder ???

latest version, in the cooperation with Alf, you can now choose between the Solver and the OpenSolver in I1.

Problem now, after some changings, Solver doesn't solve anymore and the solution is 0 waste with input. Great Solution !??
If i modified to avoid that, it wasn't a liniair question anymore OpenSolver had no problems

isn't the waste columns G*H - I4 and now it doesn't work anymore, but i have to leave now ...

now optimizing the used length instead of the waste (but that's almost the same)
A little variant 'd be if you know the price per unit to find the cheapest solution, which is perhaps different of the solution with the smallest waste.

Hi bsalv

Testes file in post 41 and tried a run of excel solver on it's own. Problem is just the artificial imitation of excel solver max 200 constraint

toomany.jpg

So excel solver quits and this result is interpreted as zero value.

Alf

in post #43, i reduced the number of cuts to 150 with

Please Login or Register  to view this content.

without checking of in every column there was at least 1 number, that's for later.
So that number can be 200 ? And for OpenSolver, also 200 ?

In the last file post 43 the numbers of combinations are 159 so solver as a stand alone has no problem solving it aside from time. But I have problem with the i so I had to define it as integer but the macro stops at line Set dict = CreateObject("scripting.dictionary")
Saying it can't find project or library. Rather strange as I'm sure you have used it before and then it was no problem this macro.

It seems to me now that OpenSolver is the best choice on account of the artificial set limit of 200 constraints or there must be a check of rows and if more than say 185 a recommendation could pop up.

I'm really impressed with all the time and effort you have spent so far in solving this problem.

Regards

Alf

i think you have "option explicit" as first line in that module.
It's now a couple of years, i don't use that anymore, and i'm satisfied to do so. So if necessary you can add those variables or delete option explicit
You can add your changes if you want to, or if you want to change the layout, do it.
It's a common project.

Back to excel after dog having chased two rabbits and seriously frighten 10 doves. The last upload works without any problems on my PC both with the excel solver linear engine and with the "COIN-OR CBC" OpenSolver linear engine.

I think the cutoff on 200 lines make sense because the waste for line 200 is 570 and these high or higher waste numbers will probably never be used.

In cell E74 there is a formula "=COUNTA(Wanted_Quantities)". This I assume is to count the number of cuts required. In an earlier upload there was a macro "Loop10" this I have not tested as you said it was only for excel 2021 - 365 and I only have excel 2019.

Conclusion a most impressive building with all the bells and whistles one can wish for

Thanks again for time spent and all your efforts to buil this model. Let's hope the OP also will appreciate it.

Regards

Alf

E74 is deleted, it had no use.
Line 8, frequency of occurence is important, because if you ask a quantity in row 6 and Excel deleted all the cuts with that length because they all ended after the 200th combination, then there 'll be no solution. See column R now, but there is no problem because there is no actual length associated with that column.

The macro to make all the combinations runs now for 30 seconds creating the first half within 5 seconds and the remaing in the last 5 seconds, perhaps that macro can use a speedup. (look at the statusbar at the bottom left while the macro is running. May be i do that one later, because now, i'm a little bit fed up with this topic

then you find it, if you don't look anymore, a quicker method to make all the combinations

@Alf, i added comments to the macro, so it'll bee easier for you to understand.
While doing so, i also so a way to eliminate half of the useless permutations.
Now, there are still 17.000 loops madein 2.4 sec to find only 270 matches, so progress is still possible ... .

the final solution for all possible permutations

If you look in the references (Extra>References) of the VBA-editor, do you see "MISSING" items there ?
Which solver did you select the Solver or the OpenSolver ?
If you have choosen 1 of them, then you need that reference in Extra>References selected !
Is that okay ?

Administrative Note:

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #1 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread