create manufacturing demand based on customer delivery
 

I'm looking to make some type of spreadsheet to help with loading my
manufacturing line.

Lets say in rows 2 thru 10 i have information in each column starting
at column d.

D1 = 5/1/12
E1 = 5/8/12
F1 = 5/15/12
the dates increment by 1 week, this could go out 2 to 3 fiscal
quarters

In column A, starting at a2 i have part numbers, lets say i have 10
different parts numbers, all unique in A2 thru A11
Lets say in cell L2 (which would be 6/26/12) i have entered a
quantity of 20, this would represent a customer demand (delivery of 20
parts on 6/26/12). Now i want to figure out what my actual build
schedule would be

In row 15 thru 24 (A15 thru A24) I repeat the part numbers from a2 to
a11
In b15 i enter what the yield % is for the associated product, lets
say it 80%, in cell C15 i enter 4, which indicates a 4 week lead time.

Now i need to populate my demand, by taking (for example) the 20 parts
from cell L2 and apply the yield factor of 80% from b15 then place the
calculated value (this would be 25 parts required based on 80% yield)
in row 15 but offset to the left by the value (lead time) in column C
of row 15, the result would show up in H15., which indicates i must
start production during week 6/5/12 and produce 25 parts to meet the
customer requirement of 20 parts on 6/26/12

Many thanks
burl_h