Availability of material

[KlaudiaJ]

Hi

I try to calculated availability of material and I'm not sure how to achieve that.

I have Bill of material for products and production plan, on hand balance for material.

Basically I want to show when material will not be on the stock.

Here are sample data:

Bill of material

Product	Material	quantity
A	M1	1
A	M2	1
A	M3	0,5
A	M4	2
A	M2	0,5
B	M8	1
B	M2	1
B	M5	0,5
B	M4	2
B	M3	0,5
C	M1	2
C	M2	0,5
C	M6	1
C	M3	1
C	M5	1

Plan

Product	Planned quantity	Plan
A	100	1
C	200	2
B	500	3

Stock of material

Material	On hand
M1	500
M2	200
M3	600
M4	1000
M5	200
M6	8000
M8	200

Output 

Plan	Product	Material	BOM Qty	Planned qty	total qty	Onhand-total
1	A	M1	1	100	100	400
1	A	M2	1	100	100	100
1	A	M3	0,5	100	50	550
1	A	M4	2	100	200	800
1	A	M2	0,5	100	50	50
2	C	M1	2	200	400	0
2	C	M2	0,5	200	100	-50
2	C	M6	1	200	200	7800
2	C	M3	1	200	200	350
2	C	M5	1	200	200	0
3	B	M8	1	500	500	-300
3	B	M2	1	500	500	-550
3	B	M5	0,5	500	250	-250
3	B	M4	2	500	1000	-200
3	B	M3	0,5	500	250	100

What formula could help me to see  negative balance  on stock?