I have a problem I am trying to solve in google sheets.
I have a roll of fabric that is 120 yards long of that I need to cut into 1 yard, 2 yard, 3 yard, 4 yard, and 5 yard pieces. We have varying demand for each size.
Our historic sales ratio is thus: 1 yard 53% 2 yard 24% 3 yard 15% 4 yard 4% 5 yard 5%
I am trying to make a calculator that will take an input of quantity of rolls of fabric and break it up into a piece count that will not exceed 120 Linear yards.
With tweaking numbers around I found an equation that makes it work, but I don't know why it works. Could someone help me understand why my work around works and also perhaps help me design an equation that automatically optimizes the product qty with a maximum constraint?
Example:
For each product (which is the 1, 2, 3, 4, and 5 yard pieces) I made the follow equation
1 yard: .53120.54 = 34 2 yard: .24120.54 = 16 3 yard: .15120.54 = 9 4 yard: .4120.54 = 2 5 yard: .5120.54 = 3
then
(1yard * 34 pieces)+(216)+(39)+(42)+(53)= 118
for some reason .54 works on keeping the sum of fabric piece length under 120yard
Is there a function of equation I can use to optimize the piece count while using my historic sales ratios?
Thanks so much!
It's very hard to read what you have typed here. Please use MathJax if you make any future posts.
One problem is that you sales figures add up to $101\%$ You'd get better results if you carry another decimal place or two. Anyway, what you're trying to do is to figure out how much of the fabric is sold in each size.
Using $100$ sales as a base, we sell $53$ yards in one-yard pieces, $48$ yards in two-yard pieces, $45$ yards in $3$-yard pieces, $16$ yards in $4$ yard pieces, and $25$ yards in $5$-yard pieces, for a total of $187$ yards. Now, we only have $120$ yards, so we want to adjust the figures by multiplying by $\frac{120}{187}\approx.6417$.
We have two problems. The first is that this doesn't give whole numbers, but this is easily dealt with by rounding. The second is that we don't get numbers that are easily divided into pieces of the right size. For example, we get that $28.877$ yards of fabric should be cut into $3$-yard pieces. Should we make $9$ or $10$ pieces?
In short, there isn't a way to solve the problem exactly. I suppose you could do better if you have a great many rolls, and you're willing to divide them in a number of different ways, but that may be more trouble than it's worth.