Help Required: Stock lists - Calculating combinations of products

Hi,

I am after a bit of help.

Here is what i have so far: stock.xlsx

Basically, i have 2x base products but we can interchange the parts between the sets so we have 1 variant per base product.

But what i want to calculate is how many sets i can make out of the available units that are in stock.

Any ideas?

Regards,

Martin

i forgot to mention, in the first spreadsheet, the green cells are order numbers, so when someone orders 1 of the combinations i put it at the end so it will then calculate the remaining stock.

so, if you have a look at this spreadsheet (stock-v2.xlsx) i have removed the order numbers as if we have just received the amounts and i have manually put in how many sets can be made in each combination (in the "Sets Available" column).

so.....if we receive more stock of the base product, it will recalculate the sets available in each combination and then people who place orders will deduct 1 from that combination.

Does that make a bit more sense?

How come 1 sofa is worth 2 chairs in Base Product 1 but worth 3 chairs in Base Product 2?

Also, that last link you posted doesn't seem to work...

I know, but Base Product 1 goes from 2 sofas and 0 chairs to 0 sofas and 4 chairs in its Variant; Base Product 2 goes from 0 sofas and 6 chairs to 2 sofas and 0 chairs in its Variant.

It's like, if I decided to exchange my Base Product 1 for the Variant version, I'd be getting 4 chairs in exchange for my old 2 sofas, but if I'd bought, say, Base Product 2 Variant and then decided to exchange it for the normal Base Product 2, I'd actually be getting 6 chairs in exchange for my 2 sofas.

So the 'exchange value' of a sofa is actually quite different in both cases, which seems kind of strange, though I'm sure I'm missing something obvious...

Ok. I think I understand. But from your Total Stock (12 x Sofa, 6 x Table Small, 64 x Footstool, 10 x Table Large, 60 x Chair in your example) do you want to know:

a) Simply how many of each of your four Products you could make, assuming you were using the entire Total Stock to make just that product? (So, for example, I could make a total of 6 Base Product 1 Variants out of this stock (as there are only 6 x Table Small), even though I'd perhaps then be left with surplus stock for other items.)

b) All possible combinations of your four Products that you could make from this Total Stock and, I assume, trying to minimise the amount of 'waste' stock as a result of each combination?

a) is a lot easier than b) - which is actually an optimisation problem with several variables - but let me know anyway and I'll see if I can help.

Well, this array formula (confirm with CTRL+SHIFT+ENTER, not just ENTER) in cell A6 of your sheet and copied down will replicate your current 'sets available' entries and automatically adjust when the entries in the Total Units Available table are amended:

=ROUNDDOWN(MIN(IF(C6:G6<>0,($K$2:$O$2)/C6:G6)),0)

As for tying this in with updating your stock levels with goods in, sales out, returns, etc., etc., not sure if you're already running that in some different software (SAP, for example) but you're not just looking at a few Excel tweaks but a whole logistics set-up which could take weeks/months to devise and implement. Anyway, hope this at least helps/gets you off to a start.

Cheers

Glad I could help. Good luck with the business.