Monitoring Batchwise Expire Stock, Need Formula in Excel

[rajendradk9]

Hi,

Need formula to monitor/track expire stock batchwise, Having data from Column A to F, based on this need to know in G column Expire qty from respective batch

A	B	C	D	E	F	G
Product	BATCH	Expiry	Consumption Start Date	Monthly Consumption	Batchwise Stock	Expire STK From Perticular Batch
Chemical-A	BATCHA1	31-Jan-23​	01-Dec-22​	10	30	10
Chemical-A	BATCHA2	28-Feb-23​	01-Dec-22​	10	5	0
Chemical-A	BATCHA3	31-Mar-23​	01-Dec-22​	10	5	0
Chemical-A	BATCHA4	30-Apr-23​	01-Dec-22​	10	60	40
Chemical-B	BATCHB1	31-Jan-23​	01-Dec-22​	30	20	0
Chemical-B	BATCHB2	28-Feb-23​	01-Dec-22​	30	100	30
Chemical-B	BATCHB3	31-Mar-23​	01-Dec-22​	30	100	40
Chemical-B	BATCHB4	30-Apr-23​	01-Dec-22​	30	50	50

 

[Automatrix]

I get that you end up with 10 stock expire BATCHA1 (30 stock, use 10 in December and January, end up with 10 expired), but how do you end up with 40 expired in BATCHA4? Shouldn't that be 10 as well?

 

[fjns]

Hi, see the linked file (with 15 auxiliary columns) for a possible solution...

The formulas used in the table:
H2: =F2-Q2-S2-V2
I2: =ROUND((DAY(D2)-1)/DAY(EOMONTH(D2,0)),6)
J2: =(YEAR(C2)-YEAR(D2))*12+MONTH(C2)-MONTH(D2)-1
K2: =ROUND((DAY(EOMONTH(C2,0))-DAY(C2))/DAY(EOMONTH(C2,0)),6)
L2: =J2+2-I2-K2
M2: =YEAR(D2)&"#"&RIGHT("0"&MONTH(D2),2)&"#"&I2
N2: =YEAR(C2)&"#"&RIGHT("0"&MONTH(C2),2)&"#"&(1-K2)
O2: =IF(A2=A1,IF(AND(W1>=M2,W1<=N2),1-T1,IF(W1<M2,I2,"")),I2)
P2: =IF(A2=A1,IF(AND(W1>=M2,W1<=N2),W1,IF(W1<M2,M2,"")),M2)
Q2: =IF(P2="",,IF(LEFT(P2,7)=LEFT(N2,7),MIN(ROUND((RIGHT(P2,LEN(N2)-8)-RIGHT(P2,LEN(P2)-8))*E2,3),F2),MIN(ROUND((1-RIGHT(P2,LEN(P2)-8))*E2,3),F2)))
R2: =MIN(MAX((LEFT(N2,4)-LEFT(P2,4))*12+MID(N2,6,2)-MID(P2,6,2)-1,0),ROUNDDOWN((F2-Q2)/E2,0))
S2: =R2*E2
T2: =IF(P2="",1,IF(LEFT(P2,7)=LEFT(N2,7),1-RIGHT(P2,LEN(P2)-8)-ROUND(Q2/E2,6),IF(Q2<(1-RIGHT(P2,LEN(P2)-8))*E2,1-RIGHT(P2,LEN(P2)-8)-ROUND(Q2/E2,6),IF((LEFT(P2,4)-LEFT(M2,4))*12+MID(P2,6,2)-MID(M2,6,2)+R2<J2,1-ROUND((F2-Q2-S2)/E2,6),MAX(K2,1-ROUND((F2-Q2-S2)/E2,6))))))
U2: =IF(P2="","",IF(LEFT(P2,7)=LEFT(N2,7),LEFT(P2,7)&"#"&(1-T2),IF(Q2<(1-RIGHT(P2,LEN(P2)-8))*E2,LEFT(P2,7)&"#"&(1-T2),IF((LEFT(P2,4)-LEFT(M2,4))*12+MID(P2,6,2)-MID(M2,6,2)+R2<J2,LEFT(P2,4)+INT((MID(P2,6,2)+R2)/12)&"#"&RIGHT("0"&MOD(MID(P2,6,2)+R2,12)+1,2)&"#"&(1-T2),LEFT(N2,7)&"#"&(1-T2)))))
V2: =IF(P2="",0,IF(LEFT(P2,7)=LEFT(N2,7),ROUND((1-T2)*E2,3),IF(Q2<(1-RIGHT(P2,LEN(P2)-8))*E2,0,ROUND((1-T2)*E2,3))))
W2: =IF(P2="",W1,U2)

BatchwiseExpireStock.xlsx