Math Is Fun Forum

I have a manufacturing planning job. I have come across a problem, where in I need to allocate the available raw material, optimally between the finished goods, to maximize the production. The problem is explained as below : Say there are different types of finished goods FG1, FG2, FG3, FG4 etc., and raw materials RM1, RM2, RM3, RM4, RM5 etc,. Now raw material composition for these finished goods will be different, say
FG1 = RM1 + RM2 + RM3 FG2 = 2RM2 + 6RM4 + 2RM5 FG3 = 0.5RM1 + 3RM3 + 7RM5 FG4 = 1.5RM3 + 2RM4
Available quantity in stock for raw materials (RM) is partial, i.e. not all quantity is available for producing all the finished goods FG1, FG2, FG3 & FG4 etc. Each RM is available in some partial quantity (or some may not be totally available).
How do i optimize the use of available raw material quantity to deliver maximum amount of full Finished goods. I have to avoid a situation where in the raw material gets allocated in such a way that partial material gets available for all finished good, but very few can be completed.
This is just an example for the problem I am facing. In reality, the number of finished goods and the multiple combinations are huge (in terms of thousands).
Please suggest a solution(s) or a program (an excel program/macro) or any other program, which can be used to maximize the finished good output for such cases. Thanks in advance.