Bar length optimization to reduce scrap

[varun.chadha]

Hello everyone,

This request is a little complicated so I will try my best to explain.

A little background on the operation:
Operation consists of using metal bars (or strips) to bend into rings. Initial information to work with is the OD of the ring and the quantity required. This can be used to calculate the length of metal bar (which would be the circumference of the circular ring) per ring and the total number of these bars needed (based on the total quantity). This bar(s) can be welded together to make longer bars which would then be used to bend multiple rings (think of using this bar to bend into a spring coil and then cutting each section into rings).

Problem:
I have standard bar lengths 120", 60" and 48". These can be welded together to make longer bars. The constraint for this longer bar is that it cannot be more than 480" long (due to equipment limitation).
Say I need 5 rings of 31" OD each, which means each ring will have a circumference of 98" approx. (or the length of bar needed to make one ring). Since I need 5, it would be ideal to weld multiple bars together (end to end) and create a longer bar of 490" so that these 5 rings can be bent together (to achieve an OD of 98" each). Now, ideal situation is when we use the prime bar length 120" for the most part and use the appropriate subprime for the last section. I have a spreadsheet which can do this. The problem arises because of the constraint of 480" (max bar length for bending). Since this is longer than 480", I would have to create two long bars. Now the second bar cannot be 10" long as it will not be possible to bend a 98" ring out of a 10" bar.

How do I create a formula that would calculate the ideal length of the long bars (wherever applicable) and the combination of the shorter bars (120", 60" and 48") to make these long bars so that whole rings can be bent out of them and scrap is minimized for the operation.

I did find a similar question posted a few years ago on the forum but my problem seems more complicated
http://www.excelforum.com/excel-gene...t-lengths.html

Please feel free to ask for clarification or more information on this problem.

Thanks in advance.

[varun.chadha]

Any help will be greatly appreciated!

[Alf]

Just for fun I made a list of all the possible combinations to make bars from 480 to 48.

Enter the circumference of ring in C2 and you will see the number of rings you can make and length of scrap metal. Not very sophisticated by at least I used solver to calculate all the different combinations.

So not sure this will be of help to you but it was fun doing it.

Alf

[varun.chadha]

Thanks Alf. You had the right idea and this is a great start.

How do you narrow down to the best combination? Was it manually?

[Alf]

No I set up a small solver model and worked my way through stock lenght from 480 to 120.

Upploaded som images but not sure you can see them. If you are using Internet Explorer you can't. Old problem in this forum started August last year and tech team has still not managed to fix it if they ever manage to do so

Alf

[newdoverman]

Here is something that might work for you. Test it to see if it does what you want. There are several formulae involved so will not post them separately.

[newdoverman]

There are some errors produced by the worksheet....drat!

Correction.....There is a pile of errors.....disregard the workbook. It is crap.

[varun.chadha]

@Alf - I have not really used solver before but I see where we are going with this. The question that I have is how do we know the "Target val"?

Another variable is to reduce the number of welds as processing and smoothing the welds adds to lead time and is lower quality than not having to weld at all. Ex: Using 120" to get 108" (and scrapping 12") is better than using 60"+48" (and scrapping 0) because its a single piece construction. Its when the combining two is inevitable that we need to reduce scrap. Ex 122".

@newdoverman - I had created something similar too but I could not add the 480" bar constraint. Thanks for trying.

[newdoverman]

Here is something that I think is close. There are a couple of areas where the length is equal using different combinations. There are a couple of other instances where the indicated waste is equal to the shortest length of rod. However I believe that in these cases the amount of waste is likely very slightly less than that due to rounding making the waste "necessary".

With lengths greater than the max, would you not make several runs using what was left over from the run before thereby not wasting anything unnecessarily? That is why I have lengths that greatly exceed the max.

[newdoverman]

For the 480 and under, this is a representative sample from the workbook that I uploaded.

	B	C	D	E	F	G	H	I	J	K	L	M
2	Diameter	Circumference	Rings	Total Length	120	Remaining	60	Remaining2	48		Total	Waste
3	1	3.1416	2	6.2832	0		0		1		48	42
4	2	6.2832	2	12.5664	0		0		1		48	35
5	3	9.4248	2	18.8496	0		0		1		48	29
6	4	12.5664	2	25.1328	0		0		1		48	23
7	5	15.708	2	31.416	0		0		1		48	17
8	6	18.8496	2	37.6992	0		0		1		48	10
9	7	21.9912	2	43.9824	0		0		1		48	4
10	8	25.1328	2	50.2656	0		1		0		60	10
11	9	28.2744	2	56.5488	0		1		0		60	3
12	10	31.416	2	62.832	0		1		1		108	45
13	11	34.5576	2	69.1152	0		1		1		108	39
14	12	37.6992	2	75.3984	0		1		1		108	33
15	13	40.8408	2	81.6816	0		1		1		108	26
16	14	43.9824	2	87.9648	0		1		1		108	20
17	15	47.124	2	94.248	0		1		1		108	14
18	16	50.2656	2	100.5312	0		1		1		108	7
19	17	53.4072	2	106.8144	0		1		1		108	1
20	18	56.5488	2	113.0976	0		2		0		120	7
21	19	59.6904	2	119.3808	0		2		0		120	1
22	20	62.832	2	125.664	1		0		1		168	42
23	21	65.9736	2	131.9472	1		0		1		168	36
24	22	69.1152	2	138.2304	1		0		1		168	30
25	23	72.2568	2	144.5136	1		0		1		168	23

[Alf]

Re costs this is really where solver is really useful to decide what to do so what you need to define is the cost of scrap pr inc and the cost of welding for each combination and that can be in cooperated
in a rebuild solver model so solver can find the minimum cost solution.

I've uploaded I file where you can calculate different combination of rods to meet length going from 480 to 60.

Re you questions:

“Target val” is the value I select and what solver should meet by adding stock sizes of 120, 60 and 48.

What solver works with is “Object Function” this is the “Set Objective”

The “Set Objective” can be set to “Max”, “Min” and “Value Of:”

So why did I not set that value to 460? Well if solver find a solution that matches 460 then everything is fine, but if no solution is found then Solver stops saying “No solution found”.

So I give solver a degree of freedom telling I wish “Set Objective” to be as small as possible and set the function up to achieve this.

In my “Small_solver.xlsx” the function is defined as “ =SUMPRODUCT(C4:C6;D4:D6)-H2”

i.e. the nr of 120 rods * 120 + nr of 60 rods * 60 + the nr of 48 rods * 48 – “Target val “

Solver also need to be told what cells it can change in order to achieve “Set Objective” and these changes must have an impact on “set Objective”. The cells to change are defined as D4:D6.

And finally one must set “constraints” in order to tell solver how the problem should be solved.

Agan in file “Small_solver.xlsx” the range “D4:D6” is set as “Integer” so solver can only use numbers 1, 2, 3 ….. and not 1.25 for instance to solve this problem.

The other constraint is cell D8 where this cell can be 0 or greater than 0.

Alf

[varun.chadha]

Thanks for your time guys.

I will use this information and try to create something uncomplicated for the machine operators.

Thank again.