Optimizing a cutting list for least waste

Hello everybody.,

This is my second post after the introduction so hopefully i can learn a lot with you guys.

So, i have a very small manufacturing company making aluminium Doors, Windows and other openings and i'm trying to build an excel workbook to make life easier for me ( There are a lot of professional Doors & windows calculating software available on the market but they are very expensive and i cant afford it for now )

My first issue is how i can use excel to do a linear calculation and optimizing a cutting list for least waste, below is an example :

- Aluminium Profile Ref is " A "
- Length of Profile "A" is 5800mm
- Stock of Profile "A" is 50bars

below, cut list for ref "A" for production
- 10 x 3500
- 3 x 2560
- 1 x 1500
- 2 x 1200
- 4 x 2000
- 5 x 4000
- etc....

Question is how many bars of ref "A" will i need with the least waste possible ? Just need a formula for it, hope you guys can help, cheers..

Hi Ali

Don't think the OP is crossposting, he just added the link to a page at MrExcel where he found a possibel solution to the problem.

Alf

You could perhaps use the Excel Add-In "Solver". But you have to set up a "table" with possible cutting pattern for "Solver" to work with and that could take a bit of time.

If you have a number of "standard" cut lengths "table" building will be a one time job but if cut lengths vary all the time you may have to rebuild your table every time cut length changes.

Based on your information in post #1 I've set up a solver model you can study to see if "Solver" may be the tool you are looking for.

Alf

I don't use the canned cross-post message unless I have actually found the same query posted elsewhere.

Sorry, my bad Ali as only saw the old MrExcel thread.

Alf

Thanks for the "solver" Alf, will try it and will let you know if i can manage with it and sorry guys for any inconvenience as i'm a newbie and don't know much about forum rules, will be more careful in futur post.

Nadim.

Hi nadim

Thank for feedback.

Solver is not installed by default when you install Excel so you need to check under the "Data" tab if Solver is installed.

If so you clear range K6:K22, then click Solver -> Solve -> OK

If Solver not found in "Data" tab install Solver. See link

https://support.ivey.ca/entries/2224...for-Excel-2013

I've added another file where Solver is run in a loop using a macro. This requires

1) Solver must be installed

2) A reference to Solver must be set in "Visual Basic".

So select "Developers" tab, click "Visual Basic" icon, click "Tools", then "References" and tick box marked Solver. Close "Visual Basic" window.

In range C4 to J4 you specify the number of cut list you wish to make.

The master rod length can be changed by changing F12 value.

The cut length can be changed by changing the values in range C2:J2. You specify up to 8 different cut length at the moment, more is possible but then the macro has to be rewritten a bit.

To test run macro "SolverLoop"

This macro is build using Excel 2010 so I would recommend you to use it in an Excel 2013 environment (can be modefied to Excel 2007)

The good: this is an easy model to work with as you don't need to set up a table with all the different combinations of list lengths.

The bad: the total Solver solution will be sub optimized i.e. as Solver finds the optimal solution for one set of lists based on lists needed and length of master rod the next loop of solver has less lists to work with and the number of available lists will decrease with every loop. So every Solver solution will be optimal but based on the decreasing number of lists to cut the "total" solution will not be as good as when Solver can "weight" all possible combinations in one go in order to produce minimum wastage.

Alf