Length Efficiency Calculator (1 Viewer)

[MattBaldry]

Hi forum people,

I have an idea someone has asked about at work, and wondered if anyone would have any tips or if it would even be possible.

We use a type of cable that comes in straight 2m lengths. Sadly it is not always cut into multiples of 2m so we get a lot of waste.

What has been asked about, if possible, is a way to input the number of lengths we need to cut, and then loop through the lengths we have to work out which best to cut them from.

However the bells and whistles here would be to use up the shortest available lengths first, before cutting into a complete length again.

I am working on some examples and will add these to the post soon, just wanted to get some brains warmed up first

~Matt

 

[MajP]

There is common family of problems in mathematics called the 1 dimensional cutting stock problem. Normally the problem is the other way around where you have a common stock length and varying lengths to cut. There are 2d and 3d problems too.

Cutting stock problem - Wikipedia

en.wikipedia.org

This problem is mathematically hard to solve depending on the various amount of different lengths. To truly get an optimize solution you will likely need a solver which you will not be able to code yourself. You can find free solvers on line and formulate in Excel using the solver add-in. Formulate cutting stock problems in Excel is a pain.

If the amounts of different stock sizes and lengths are relatively simple then you can formulate this in Access using some heuristic. This may not give you an optimal solution but a good solutions. I have many examples of doing optimizations and using heuristics in Access. I really would need to see some data and example problems to see how involved this is.

Here is an example heuristic of a dosing problem which is similar to the cutting stock that I coded in Access.

Best approach to calculate if required dose possible from multiple different stock sizes

We have a manual process where if a 250mg dose of Drug A is required, we give 120mg + 75mg + 55mg as 3 separate doses, or (2 x 75mg) + 100mg, or (2 x 80mg) + 90mg What I'm after is the best way to calculate if the available dose is divisible by any combination of 120mg, 100mg, 90mg, 80mg, 75mg...

www.access-programmers.co.uk

 

[MattBaldry]

As an example, and this one will be basic just for an idea we have the below.

CableSerial	CableLength
001	0.330
002	0.660
003	0.660
004	0.800
005	0.850
006	1.650
007	2.000

Now we need to cut 10 x 0.165 lengths. Basic math would tell us that 10 x 0.165 = 1.650.

So on here there would be 3 options. Take the 1.650 length, easy. Use the 0.800 + 0.850, almost as easy. Or, the most efficient way, use the 0.330, 0.660 and 0.660.

If possible, I would like to find the most efficient way first. Is this even possible in Access?

~Matt

 

[MajP]

The problem is you have to give real amounts of data and not just a toy problem. Most people would look at this and think you can reasonably calculate this with some loops. You probably could with this toy example. However if not representative of your real problem it may not. It does not take much for these types of problems to grow exponentially difficult. If the amount of cable serials goes up a little, if the sizes stop being easily divided, if the lengths are variable, the amount of potential solutions becomes exponential. Even a very small problem can soon have billions or trillions of possible solutions. There comes a point that even small problems are unsolvable with modern super computers by trying a brute force approach of iterating all possibilities.

Heuristics and optimizing solvers come up with optimal solutions or very good solutions without trying to iterate all potential.
1. If the data and problems are really about this simple then you can code a reasonable solution in Access. I have shown that for multiple complex optimizations using heuristics. These problems such as shortest path, traveling salesman get close to optimal and could not be reasonably solved by brute force.

2. If it is more complex and your data resides in Access. You can use access to help formulate a problem and solve in Excel using the Optimization solver or import into a true cutting stock solver.

One thing about optimization you need a clear objective function.
You want to minimize waste while using the shortest stock first. This could get real complicated. Assume it is not as clean as you show and all solutions could lead to some waste on each cable cut. You can get the least waste with a solution that does not use a lot of the short stock, the next solution has more waste but uses more short stock. etc.
Which one do you pick?

 

[MattBaldry]

The problem is you have to give real amounts of data and not just a toy problem. Most people would look at this and think you can reasonably calculate this with some loops. You probably could with this toy example. However if not representative of your real problem it may not. It does not take much for these types of problems to grow exponentially difficult. If the amount of cable serials goes up a little, if the sizes stop being easily divided, if the lengths are variable, the amount of potential solutions becomes exponential. Even a very small problem can soon have billions or trillions of possible solutions. There comes a point that even small problems are unsolvable with modern super computers by trying a brute force approach of iterating all possibilities.

Heuristics and optimizing solvers come up with optimal solutions or very good solutions without trying to iterate all potential.
1. If the data and problems are really about this simple then you can code a reasonable solution in Access. I have shown that for multiple complex optimizations using heuristics. These problems such as shortest path, traveling salesman get close to optimal and could not be reasonably solved by brute force.

2. If it is more complex and your data resides in Access. You can use access to help formulate a problem and solve in Excel using the Optimization solver or import into a true cutting stock solver.

One thing about optimization you need a clear objective function.
You want to minimize waste while using the shortest stock first. This could get real complicated. Assume it is not as clean as you show and all solutions could lead to some waste on each cable cut. You can get the least waste with a solution that does not use a lot of the short stock, the next solution has more waste but uses more short stock. etc.
Which one do you pick?

Hi Majp,

Yes, I did make this one easy, and it will not always be this way. What we are going to put in process as that the BOM will always have the required quantity in a multiple of 0.100, so there will never be any weird 0.056 lengths like we currently have. 0.056 will be rounded up to 0.100 so the 0.044 waste is costed into the job, and our system will be easier to manage.

Myself, I would like the common sense approach and people use their brain to use the most efficient length. In reality, we currently have 250m of stock, all was bought in 2m lengths, and yet we do not have a single length over 1.5m. As you can see, common sense if seriously lacking in my workplace.

This cable is used for a quantum computer setup like below, so the lengths are critical with almost no tolerance. As you can see, we need a more efficient way, other than relying on the brains of others to do the right thing.

~Matt

 

[MajP]

Any chance you can give me the real data? You can PM me if needed. Just need the absolute minimum fields and tables. You can provide fake serials or anything proprietary.
Like to get the real available stock quantities and sizes and a close to real problem. There are several heuristics to the 1d cutting stock (which I assume this is). They all depend on the complexity of the inputs, constraints, and objective.
I have developed some pretty complicated optimizations in Access. Here is one of the most complicated ones I ever seen. The UI and algorithm I have never seen anything similar. So, I probably can get something for you.

Optimising Routes

i was going to ask about the piggyback status, and how it worked. You can pick a trip that has 0 vehicles to ship. You will see if another driver has previously driven that trip. Then it will assign the current driver as a piggyback and not increment the number of cars shipped on that trip.

www.access-programmers.co.uk

But you will see in that problem, until I got real data and a real problem my solution was not sufficient.

 

[MattBaldry]

Any chance you can give me the real data? You can PM me if needed. Just need the absolute minimum fields and tables. You can provide fake serials or anything proprietary.
Like to get the real available stock quantities and sizes and a close to real problem. There are several heuristics to the 1d cutting stock (which I assume this is). They all depend on the complexity of the inputs, constraints, and objective.
I have developed some pretty complicated optimizations in Access. Here is one of the most complicated ones I ever seen. The UI and algorithm I have never seen anything similar. So, I probably can get something for you.

Optimising Routes

i was going to ask about the piggyback status, and how it worked. You can pick a trip that has 0 vehicles to ship. You will see if another driver has previously driven that trip. Then it will assign the current driver as a piggyback and not increment the number of cars shipped on that trip.

www.access-programmers.co.uk

But you will see in that problem, until I got real data and a real problem my solution was not sufficient.

Thanks MajP,

I will get a list and PM it over. There is nothing hidden here so I will give you real lengths and cable details, and then same sample lengths we would cut it in to.

~Matt

 

[Gasman]

Hi Majp,

Yes, I did make this one easy, and it will not always be this way. What we are going to put in process as that the BOM will always have the required quantity in a multiple of 0.100, so there will never be any weird 0.056 lengths like we currently have. 0.056 will be rounded up to 0.100 so the 0.044 waste is costed into the job, and our system will be easier to manage.

Myself, I would like the common sense approach and people use their brain to use the most efficient length. In reality, we currently have 250m of stock, all was bought in 2m lengths, and yet we do not have a single length over 1.5m. As you can see, common sense if seriously lacking in my workplace.

This cable is used for a quantum computer setup like below, so the lengths are critical with almost no tolerance. As you can see, we need a more efficient way, other than relying on the brains of others to do the right thing.

~Matt

View attachment 114153

Surely you should not accept the shipment if it is not as requested?
How hard is it to check when the items come in?

I realise that is not solving your problem right now, but it could eradicate it altogether if you have a couple of process checks for shipments received?

This is sort of closing the barn door after the horses have bolted.

 

[MattBaldry]

Surely you should not accept the shipment if it is not as requested?
How hard is it to check when the items come in?

I realise that is not solving your problem right now, but it could eradicate it altogether if you have a couple of process checks for shipments received?

This is sort of closing the barn door after the horses have bolted.

Hi Gasman,

No, the fault here is not the supplier. It is our workers. The cable was all received in 2m lengths. But they always just grab a new length when doing a job, rather than find the most efficient lengths. You have no idea how frustrating this is, so I am trying to find a solution to help our stores team to take the cable away from the assembly team, and only give them the most efficient lengths.

Sadly we have a large team of people who use zero common sense, have no care on the amount they waste, and never, ever, think outside of the box.

~Matt

 

[Gasman]

OK, I understand.
I worked for JCB as a contract computer programmer for 6 months or so.
I had to write a program that worked out what cable ties were required for a particular build of machine.

The reason was the workers would just grab 2ft cable ties or the like and cut off most of it, instead of using a 6inch cable tie, and it was costing the company a lot of money.
Not as difficult as this as I just had to interrogate an IDSII database to produce a report for the build.

 

[gemma-the-husky]

As an example, and this one will be basic just for an idea we have the below.

CableSerial	CableLength
001	0.330
002	0.660
003	0.660
004	0.800
005	0.850
006	1.650
007	2.000

Now we need to cut 10 x 0.165 lengths. Basic math would tell us that 10 x 0.165 = 1.650.

So on here there would be 3 options. Take the 1.650 length, easy. Use the 0.800 + 0.850, almost as easy. Or, the most efficient way, use the 0.330, 0.660 and 0.660.

If possible, I would like to find the most efficient way first. Is this even possible in Access?

~Matt

Option 2 wouldn't work would it, unless you can splice lengths.

Also can you cut without waste, so that you can cut 1.65 into 10 x 0.165

With this problem I think you are better trying to use the smallest lengths first.

 

[Uncle Gizmo]

I've only tested it on the data you've supplied but it seems to work! Will need more tests to be sure!

 

[The_Doc_Man]

After thinking about this for a while, I believe I have something to offer, but it requires a little redirection.

For discussion purposes only, call the 2m lengths your stock wire and call the desired shorter lengths your cuts.

Part of the problem is that your wire comes in 2m lengths from wherever you get it. Take one wire out of the box; it is no different than other wires still in the box. But suppose that the individual stock wires WERE identifiable? That is not an idle suggestion because doing so makes this problem similar to a queuing theory problem involving multiple identical but separately identifiable processors and multiple unequal tasks. In the queuing problem, the goal is the shortest amount of time to finish a work load, but here it is the least amount of waste.

Part one of the solution implementation starts by having labels - maybe some Avery or other brand of file folder labels and you put numbers on them. Then, when you need to draw one of the 2m lengths of stock wire, you stick this numbered label on one end of that wire. This wire is no longer indistinguishable from the other stock wires. You make a table to hold the number and the length of the wire. This table CAN start empty because the method I'm going to use is self-loading. You have this via a query that is sorted by shortest remaining length first. (Ascending order of remaining length.) This is part one of the solution.

Part two is that you have your list of today's required INDIVIDUAL lengths. Sort them, longest length first (descending order of length). ALWAYS take the next longest remaining required cut length from the list. NEVER "group" a set of 10 x some length cuts to be subdivided later. Cuts are ALWAYS for single pieces. So you have a table of cuts that might or might not be separately identifiable. That would be up to you.

Here is where it gets fairly simple. When you need to make a cut, you look for the shortest labeled stock wire long enough to supply the length you need. That is, you look for the first labeled wire longer than the cut you desire, which should be easy if you have a query sorted in ascending order. Since the stock table is sorted, it is inherently ordered as well. You can probably do a SELECT FIRST stock_label WHERE stock_length >= cut_length. Or something similar, =DFirst( "[StockLabel]", "stocklist", "[StockLength] >= [CutLength]" )

You will get one of two results.

A) You CAN identify a satisfactory stock wire that provides the cut.
A.1) Issue the order to make the cut from that labeled wire to get the desired length.
A.2) Remove that cut's requirement from the cuts list.
A.3) Adjust the recorded remaining length of the selected stock wire.
A.4) Test the remaining length of the selected stock wire for being shorter than the minimum length you will ever cut.
A.5) IF the remaining stock is now too short, issue an instruction to retire that labeled "stock" wire and its record from the stock list.

B) No remaining labeled stock wire is long enough.
B.1) You issue an order to pull another stock wire and label it. Add that new label to the table of stock wire with 2m length.
B.2) Identify the new wire as meeting requirements. Return to step A.

Lather, rinse, repeat until no more required cuts remain. Yes, it's a loop and it will involve traversing a couple of recordsets so this won't be blazing fast - but because you are searching a relatively short table and the cut table is being visited in overall sort-order, not necessarily being re-sorted for each cut, it should be mechanically efficient.

This method's behavior will be that the first (and therefore longest) cuts will require several "case B" actions to add more wires to the stock table. As the list of cuts gets down to the shorter cut requirements, this method will start reaching back to the remaining stock fragments to take from stock wires that still have usable length. This even would work if you just left the stock wires in place for cutting for the next day's batch. And you can start with the stock table being empty. The way to"clean up" the stock table is just that "minimum usable length" test to allow you to purge useless fragments as you go.

This discussion should be specific enough to let you implement something if you think it makes sense.

EDITED 5/18/24 by The_Doc_Man to correct the expression suggested from > to >= and to amplify the method of finding the match.

 

[MajP]

I've only tested it on the data you've supplied but it seems to work!

Not really, it just fits the limited data. That is why I would wait until you got a real data set.

In optimization modeling you have Feasible Solutions and Optimal Solutions. A good model has to ensure it can at least get a feasible solution (meet the constraints), if a feasible solution exists. Heuristic usually guarantee a feasible solution, provides a good solution but does not guarantee the solution is optimal. Your approach cannot guarantee a feasible solution. A feasible solution will meet the total required length requirement.

You are using a greedy algorithm. As it loops if the value can be added it is added. So in your case it adds 1 then adds 14 and 15. These should not be added. It also cannot add solutions that cause waste. So if you added 30 at least you would have a feasible solution.

The solution returns a total length of 30 which is not feasible.
There are feasible solutions, and an the optimal solution 1,30 has no waste.

In this case you get waste with the optimal solution of 30 and 14 with 10 left over. Your solution again is not feasible.

 

[MattBaldry]

Hi All,

Thank you for taking an interest in this task. I did want to have a real time list of data for today, but sadly the guy tasked with the stock list decided to be ill today. In the meantime, I have found the below link, which seems to do something like the endgame here.

Linear 1D Cutting List Optimiser Calculator - free Version 2

1D Linear cutting list optimiser app - calculator. The free optimizer is used to calculate the best way to cut lengths of timber, metal, steel or wood or other material that needs to be cut to length.

www.cutlistpro.com

The issue here is, our shopfloor do not have internet access. I can easily make a table with our different cable and cuts. And given that we are going to change our methodology here to always issue the jobs in 100mm multiples, it may help make this a little easier. 100mm goes into 2000mm much easier than stupid 74mm cuts. And anything left over should still be in 100mm multiples, so as such still usable.

Once we have a working cut table, and 100mm multiples, it may even just be easier for them to manually select a length from the table based on how much they need, rather than how many cuts. 12 x 100mm is easy to see you need the shortest length 1200mm or over (or it should be for those who use common sense).

Where common sense will fail is when the WO single cut is a multiple that doesn't go well into 2000 (300, 600, 700, 800, 900).

I will ponder this over the weekend and see what I can get my head around. And hopefully on Monday I will have some a real dataset to tinker with.

~Matt

 

[MajP]

You definitely need to address leftover and the precise rules. There is a whole subset of models dealing just with this topic.
Often there is a length of leftover that is a partial penalty because you can use it in future cuts. It is a partial penalty because you want to use full sheets first. Then there is a length that is to small and is in fact waste.

I did not look at the model, but be aware that there are many versions of the cutting stock problem where you minimize or maximize different things. Yours is a special case of "Irregular Stock".
In the most traditional model you have a common stock, but the order contains cuts of different sizes. In the wikipedia link you see an order with 13 different size cuts and each size has many items. Here is where the complexity can grow quickly depending on the number of different sizes to cut.
Your example had only one size cut per order, which greatly simplifies the model. My assumption it is not always your case.

The problem with some of the free calculators is getting and saving your data in there. You may have to type your stock data line by line. If you can do an download from your db and an import / export that is what you may need if you have lots of stock data.

 

[gemma-the-husky]

Hi All,

Thank you for taking an interest in this task. I did want to have a real time list of data for today, but sadly the guy tasked with the stock list decided to be ill today. In the meantime, I have found the below link, which seems to do something like the endgame here.

Linear 1D Cutting List Optimiser Calculator - free Version 2

1D Linear cutting list optimiser app - calculator. The free optimizer is used to calculate the best way to cut lengths of timber, metal, steel or wood or other material that needs to be cut to length.

www.cutlistpro.com

The issue here is, our shopfloor do not have internet access. I can easily make a table with our different cable and cuts. And given that we are going to change our methodology here to always issue the jobs in 100mm multiples, it may help make this a little easier. 100mm goes into 2000mm much easier than stupid 74mm cuts. And anything left over should still be in 100mm multiples, so as such still usable.

Once we have a working cut table, and 100mm multiples, it may even just be easier for them to manually select a length from the table based on how much they need, rather than how many cuts. 12 x 100mm is easy to see you need the shortest length 1200mm or over (or it should be for those who use common sense).

Where common sense will fail is when the WO single cut is a multiple that doesn't go well into 2000 (300, 600, 700, 800, 900).

I will ponder this over the weekend and see what I can get my head around. And hopefully on Monday I will have some a real dataset to tinker with.

~Matt

I think where you say "I can easily prepare"... Is that in practice it's probably not going to be that easy. In order to automate the process you will need a way to extract both the lengths you have available, and to set up the cutting/selection requirement, and then to have a way to optimise the process.

So you need to understand how to present the data to the calculation engine.

I imagine generally speaking you would match any precise lengths first. You don't want to cut a 400mm length to produce 3*100, only to find you then have a required for a single length 390. Equally you don't want to cut a 600 length to provide the 390 if there was a 400 or maybe 500.

Many of these problems are too hard to solve precisely. Often the best you can do is come up with a working solution and try to improve it somewhat, as there can be too many combinations to test every alternative.

 

[MajP]

Many of these problems are too hard to solve precisely. Often the best you can do is come up with a working solution and try to improve it somewhat, as there can be too many combinations to test every alternative.

Yes, but no one does it that way. No model will test all alternatives. As I said this is an extremely well known problem with proven methods to get an Optimal solution. It can be solved as a traditional Linear Program or many solutions use Column Generation so as not to have to do exhaustive feasible pattern generation prior to model execution.

 

[MajP]

This one has the ability to import all properties from excel. Handles irregular stock too.

Linear Cutting List Calculator - optiCutter

Online linear cut calculator designed to find optimal solution for cutting linear material with maximal material yield. Try the pipe or lumber cut calculator!

www.opticutter.com

 

[MajP]

The issue here is, our shopfloor do not have internet access

If you have not noticed this is a well known problem and many people selling solutions. If you cannot build it yourself or get online when needed to one of the free versions, many of these companies offer stand alone options that import and export to excel (also have APIs) for a very cheap price. 10-20 bucks a month or a couple hundred bucks out the door even for more advanced problems.
This problem can be solved optimally in Excel using Solver. From doing it, it is kind of a pain to set up for each problem.