Calculation of Stock Execution on FIFO basis

[RAMU]

Hi,
Pls go thru the mini sheet:
Yellow highlighted cells are my opening stock as on 01-04-2021. Green Highlighted cells are purchase history, blue highlighted certain area are my sale detail (A11 TO G19). From I12 to L12 heads are predefined. My desired results are in Red font against those Heads. See intentionally I have mentioned a wrong figure in date column of sale which is navy highlighted & font in white, there results are showing error as if you see, you can get that on that day that qty banana couldn't be sold as that much stock was not available on that day since all calculations are on FIFO based. I need the results like concatenate but if needed you may give me helper columns for merging those figures, I don't have any problem for that.
It would be highly appreciable, thanks in advance.

Calculation of stock execution on FIFO basis.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	PURCHASE								OPENING STOCK	DATE	QTY
2	FRUIT	SUPPLIER	INV NO	DATE	QTY	RATE			GUAVA	01-04-2021	43
3	GUAVA	GK TRADER	10	10-04-2021	96	8			APPLE	01-04-2021	12
4	APPLE	SM	102	09-04-2021	50	12			BANANA	01-04-2021	15
5	BANANA	SM	134	12-05-2021	144	3
6	APPLE	GK TRADER	15	19-05-2021	252	10
7	BANANA	SOM	35/58	05-05-2021	180	3.25
8	APPLE	SOM	35/59	17-05-2021	192	11.5
9	GUAVA	SM	149	22-05-2021	120	7.3
10
11				SALE					FIFO BASIS STOCK EXECUTE DETAIL
12	CUSTOMER	FRUIT	INV NO	INV DATE	QTY	RATE	VALUE		SUPPLIER	INV NO	INV DATE	QTY
13	S.M. TRADER	BANANA	4	01-04-2021	10	4	40		OPENING STOCK	NA	01-04-2021	10
14	DAD KITCHEN	GUAVA	5	01-04-2021	30	9	270		OPENING STOCK	NA	01-04-2021	30
15	NIRMAL	APPLE	6	01-04-2021	8	15	120		OPENING STOCK	NA	01-04-2021	8
16	ZAZU	APPLE	110	10-04-2021	50	15	750		OPENING STOCK, SM	NA, 102	01-04-2021, 09-04-2021	4, 46
17	KIDDY	GUAVA	114	11-04-2021	103	9	927		OPENING STOCK, GK TRADER	NA, 10	01-04-21, 10-04-2021	13, 90
18	S.M. TRADER	BANANA	124	12-04-2021	10	4	40		ERROR	ERROR	ERROR	ERROR
19	S.M. TRADER	BANANA	124	12-05-2021	10	4	40		OPENING STOCK, SM	NA, 134	01-04-2021, 12-05-2021	5, 5
Sheet1

Cell Formulas
Range	Formula
E6	E6	=21*12
E7	E7	=15*12
E8	E8	=16*12
G13:G19	G13	=+E13*F13

 

[Fluff]

Duplicate to: Calculation of stock valuation (FIFO Basis)

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.