Raw Material Length selection to optimize wastage

Hi,

I am just new here, appreciate if you can help me solve my problem in excel.
I know macro can be a great help but I am also new to it.
Please help.

PROBLEM:
1. I have a list of sets of lengths bars required for the project (Please see attached file)
2. lengths are very long, so I need to use several number of raw material (available in the market are 6m, 7m, 8m==> max 11.8m) to complete the full length per set
3. I need to select the best available length of raw material in order to have a least wastage
4. it's easy to do it if have only 1 set of length (just divide the total length with number you want that will have least wastage) but problem is if have say 5 items and they require different total lengths, so it's tricky on how to arrange/select the raw material to have the best combinations of cutting.
5. much more difficult also if some items are different in diameters, so difficult to group them
6. also sometimes you need to use 2 different raw material length in one set
7. with above problems, difficult also to calculate automatically the wastage for each item
8. say after having all calculated, final step is I need to spread the wastage to all items so that the unit price in each items are consistent

right now, I am doing it all manually (club them and do several trials) which is a headache and taking to much time and sometimes if the required total lengths are changed then I have to start all over again.

Thanks for taking time reading and hope someone can help me to solve this.

Many Thanks in advance.

God bless.

Hi sourabhg98,

Thanks for your time to help.
If you look into my table:
On column M, you see that my wastage are high and not consistent.
- my goal is to select the best raw material length (in column E) based on available length in the market the will have the least wastage as possible
- normally, my first option is to select the longest raw material so that I have only few cuts (or less number of raw material per set) but I have to check if this length that I selected will have good wastage if I have to use it also for other items because depending on the total number of quantity per set I can't just select any length to use in each set because of MOQ (minimum order quantity for raw materials), so as much as possible I have to club them to have least wastage and reasonable quantity to order for each raw material. when clubbing them, I just manually add the end cuts to other items that has sufficient remaining length then I will manually do the wastage calculation by making new formula (old formula in the template will be modified).
- then wastage need to spread in each item after optimization (each item / row is linked to other sheet for some specific purpose, pricing breakdown). Modification on the formula as well.
- another challenge is when other items are different diameter, hard to group them also.

Let me know if still not clear.
thanks in advance for your help.

Hi sourabhg98,

thanks for taking it seriously to help.
On your illustration, yes that is right. And if need to optimize further the raw material length can also be changed (selection from 6000 to 11800, 100mm increment) and option to use different length of raw material in other items if it gives better wastage and if MOQ allows.

You may take a look on the file attached I am currently working to explain above (but this is optimizing by selecting raw materials only, not combined to other items yet).
But don't the new tables (still working on it), to give you idea, I am actually trying to spread the wastage but my formula will not work if they have different diameters.

Thanks

Hi sourabhg98,

I've attached new file, you may have a look at it.
I've revised the wastage spreading procedure, I considered weight of raw materials because after all this what will matter (the weight - we consider price per kilo of raw material) no matter what will be the diameter so I think it can be mixed when spreading the wastage.

thanks.

Hi sourabhg98,

thank you so much for your help.
It's helpful because I got new idea for better way to calculate it.

your solution can help but I see some problems:
1. this will work only if quantity per set in each item are many
2. this way means that it will dictate the raw material length and I have no choice to make them uniform
3. this way also means that no need to club the end cuts to other items to best utilize the remaining cuts
4. this way means that each item will have different raw materials lengths and so problem if the quantity of sets are only few (MOQ problem)
5. problem especially when they have different diameters


May be what i need is:
1. let column E (raw material) as an inpute - this will be the first step to best select the raw material
2. and then i need macro to club the end cuts to the remaining cuts of other items with the same diameter to optimize wastage and to make sure that i have no problem with my tonnage (MOQ)


thanks for your full support.

Hello,

I am looking for something similar to divide 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.

Screenshot 2022-08-11 112719.png

Excel file attached.

Started a new thread, thank you.