VBA or solver? Model for allocation of pipe-lengths with more constraints.

[DonKampfello]

Hi Experts

I run a small business where I produce and sell pipes in different widths, made out of a soft glas material which I purchase in the form of "Rolls". I want to automate my purchasing process so I dont waste material. Here is my worksheet: Excel solver exampel MKSO.xlsx

1. In yellow I have the dimensions
2. In Blue i have my forcasted Rolls demand amount I need to purchase. This is the amount of "rolls" of each dimension I have to make.
3. We have the big material rolls in bulk, purchased from my supplier, that I make cuts from to the right widht. And waste from cutting the roll.
4. In the lightgreen Array, is where I want to create my model. I am not sure where to start since im new to Data Science, so I am looking for the easiest tool that Excel can offer. VBA/Solver/functions etc.

Basically, Im trying to find a way to fill out all the cells and figure out which kind of combination I should cut. This would give me an idea of how many bulk roolls of 4 m I should buy from the supplier, so I end up with an equal sum of "Purchasing rolls" to my demand.

Any help would be highly appreciated. Even a pointing finger to what I should research.

I hope this makes sense.


Best from a small business owner.

 

[DonKampfello]

I will try that one. Yes the range is from another sheet. Thanks