BILL OF MATERIAL EXTRACT THE SHORTAGE ITEM

[handri]

Good day

I m looking for the shortage item in my sample data to extract into information in details

A USE APPLE 1
B USE APPLE 5
C USE APPLE 6

PO EACH ONE IS 100

APPLE on hand is 500

meant PO*APPLE

TOTAL USE IS 1200

500 minus 1200=-700

How can i extract the shortage -700 of apple into information like my data sample below

DATA SAMPLE.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	BILL OF MATERIAL
2
3	MATERIAL	A	100	B	100	C	100	TOTAL USE	ON HAND	BAL
4	APPLE	1	100	5	500	6	600	1200	500	-700
5	ORANGE	2	200	3	300	1	100	600	200	-400
6	LEMON	3	300	2	200	1	100	600	100	-500
7
8
9				E.G
10					APPLE	A	100
11						B	500	SHORT -100
12						C	600	SHORT -600
13		PRIORITY OF SCHEDULE		PO
14		1	A	100
15		2	B	100
16		3	C	100
17
Sheet1

Cell Formulas
Range	Formula
G4:G6	G4	=F4*$G$3
H4:H6	H4	=C4+E4+G4
C4:C6	C4	=B4*$C$3
E4:E6	E4	=D4*$E$3
J4:J6	J4	=I4-H4

thanks