Bar Stock Calculator

[BrettW]

I am trying to develop a calculator to help determine how much and what lengths of bar stock needs to be purchased based on orders. We receive orders of several different part lengths and varying quantities. We buy bar stock then cut it to specified length, bar stock can be one of two lengths. I can do some of the easier math, but I keep running into trouble with so many different possible outcomes. For example if we receive orders for 10 of part1, 15 of part2, 13 of part3, etc, how many of each bar do I need to order to meet the demand but not have excessive leftover stock? I have attached what I came up with so far, please let me know if further explanation is needed. Thanks.

[MrShorty]

I don't have much experience with "cutting stock" problems. I know they are "NP-hard", which means that there is not really good, simpler-than-brute-force algorithms for them.

However, there is much written about the cutting stock problem, including sample spreadsheets and algorithms. I would suggest you start with
Wikipedia: https://en.wikipedia.org/wiki/Cutting_stock_problem
the people behind Solver: https://www.solver.com/cutting-stock
maybe this: http://blog.excelmasterseries.com/20...aste-with.html

and/or put something like "cutting stock problem Excel" into your favorite internet search engine.

From there, let us know where you get stuck and we'll help where we can.

[BrettW]

Thanks MrShorty. I'll look into the resources you suggested.