## Optimized cutting of steel angles using EXCEL

## Optimized cutting of steel angles using EXCEL

(OP)

Can someone suggest how to determine optimum cutting schedule from a give stocks of angle sections using excel?

Stock data

100*100*7 ---- 5000 mm 150 qty cost 1000 $/MT

100*100*7 ---- 6500 mm 25 qty cost 1050 $/MT

90*90*6 --- 6500 mm 100 qty cost 1020 $/MT

90*90*6 ---- 5500 mm 120 qty cost 1000 $/MT

Requirement

100*100*7 2000 mm 20 qty

100*100*7 2200 mm 25 qty

100*100*7 3000 mm 50 qty

90*90*6 2000 mm 75 qty

How optimize cutting schedule so that wastage is minimize.

There are ready made programs available but I want to develop it on Excel .

Thanks

Stock data

100*100*7 ---- 5000 mm 150 qty cost 1000 $/MT

100*100*7 ---- 6500 mm 25 qty cost 1050 $/MT

90*90*6 --- 6500 mm 100 qty cost 1020 $/MT

90*90*6 ---- 5500 mm 120 qty cost 1000 $/MT

Requirement

100*100*7 2000 mm 20 qty

100*100*7 2200 mm 25 qty

100*100*7 3000 mm 50 qty

90*90*6 2000 mm 75 qty

How optimize cutting schedule so that wastage is minimize.

There are ready made programs available but I want to develop it on Excel .

Thanks

## RE: Optimized cutting of steel angles using EXCEL

although I had to make some assumptions to fill the lack of definitions, I've tried to answer your question.

My solution is attached.

Please fill free to make comments.

Kind regards

## RE: Optimized cutting of steel angles using EXCEL

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/

## RE: Optimized cutting of steel angles using EXCEL

Regards

Nitin

## RE: Optimized cutting of steel angles using EXCEL

first of all, I need to know from you if the problem has been correctly understood.

Please note that the proposed solution is with excel only.

Regards

## RE: Optimized cutting of steel angles using EXCEL

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

## RE: Optimized cutting of steel angles using EXCEL

But, as too often it occurs, the "experts" seem more interested than the originators.

I've seen the comment of IRstuff but the Solver model in my spreadsheet is not able to reproduce his solution.

Anyway, on that base, I've found an error in the original model (ie one cut opton missed), corrected it and re-ran.

Now the solution is better (ie 71700 cost, 26500 waste) than before but not very stable.

So, I'm curious to see if IRstuff, IDS or others can improve the spreadsheet.

Thank you.

## RE: Optimized cutting of steel angles using EXCEL

http://files.engineering.com/getfile.aspx?folder=6...

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

## RE: Optimized cutting of steel angles using EXCEL

During cutting if remaining Pieces length is less then 500 mm then it is a wastage.

If it is more than 500 mm then we can consider that same pieces will be utilized for other project.So minimum wastage is the goal.

Also please note that cost is define as $/MT. Excel file is attached for better understanding.The data in Excel file is only a sample one.

Regards

Nitin

## RE: Optimized cutting of steel angles using EXCEL

as I suspected not all the basis were given....

Some questions for you:

1) what is MT? In the present scenario, it could be either meter (m) or metric ton (t).

2) what is the physical meaning of the three parameters characterizing the stocks (eg 100*100*7)?

3) the model results will be different by minimizing either the cost or the wastage. Logically thinking, the cost should prevail on the wastage.

4) would you be able to modify the spreadsheet to obtain your target?

5) would you give a star to the spreadsheet?

Regards

## RE: Optimized cutting of steel angles using EXCEL

http://en.wikipedia.org/wiki/Cutting_stock_problem

www.nxjournaling.com

## RE: Optimized cutting of steel angles using EXCEL

2-100*100*7 is steel angle section ( L shape )with both flange having 100mm width and 7mm thickness.

3-Minimizing wastage is main criteria.If we minimize wastage balanced material can be used for other project.

4- ???

5- YES

Regard

## RE: Optimized cutting of steel angles using EXCEL

If it is more than 500 mm then we can consider that same pieces will be utilized for other project.So minimum wastage is the goal."

This is another constraint, so a subgoal is to aggregate the leftovers so that they are larger than 500mm.

@Cowski thanks for the article link. The article and subsequent websearches suggest that this is a relatively non-trivial problem. This one site: http://www.delphiforfun.org/Programs/Cutting%20Sto... has a Pascal program that does an optimization for the cutting problem. You'll need to enter the different (6 or 7) stock separately, but its solution uses only 6500mm 100*100*7 for those orders and uses 41 pieces. My hand solution used two types with 42 pieces. The source code is also on the site. I've not figured out how to actually use the files as inputs; I typed the info manually. The output of the program is below; the program also has a very pretty graphical representation of the solution.

## CODE -->

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

## RE: Optimized cutting of steel angles using EXCEL

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

## RE: Optimized cutting of steel angles using EXCEL

http://files.engineering.com/getfile.aspx?folder=5...

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

## RE: Optimized cutting of steel angles using EXCEL

## RE: Optimized cutting of steel angles using EXCEL

http://en.wikipedia.org/wiki/Knapsack_problem

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

## RE: Optimized cutting of steel angles using EXCEL

IMHO the solution you proposed is not correct (see attached cut_solver_IRS.pdf)

@zelgar,

can't see your solution since the macro doesn't run on my pc (my fault, for the time being I've no time available for understanding & debugging)

@all,

after implementing all the basis given by nitin (and added others to bound the solution), my proposed solution is attached (cut_solver2.pdf).

I think it is the best achievable from excel 2007 solver, but I'm curious to see if there are better ones.

Thanks, regards and smiles

## RE: Optimized cutting of steel angles using EXCEL

## RE: Optimized cutting of steel angles using EXCEL

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

## RE: Optimized cutting of steel angles using EXCEL

you're right: the calculation reported in "cut_solver_IRS.pdf" reproduces the output of the Pascal program.

The problem is that the solution is not allowed because it violates the constrain relevant to the max number of "100*100*7 ---- 6500 mm" profile (S2 in my spreadsheet).

Only 25 are available but 41 are used.

Regards

## RE: Optimized cutting of steel angles using EXCEL

TTFN

FAQ731-376: Eng-Tips.com Forum Policies