Simple FIFO Inventory with Lot expiry date

[Bluhawk]

Hello members of the Mr. Excel crew. Coming to you for some pointers on how to create a simple indicator on Inventory management of product codes (FIFO - First in, First out Method) taking into account the product lot expiry date. I have the feeling it should be simple, but I am having a hard time coming up with the formulae.

So I have these 2 data sets (below simplified):

1 - Current stock by product code/lot and the corresponding expiry date.

2 - Monthly consumptions.

The indicator I want to create is wether, with the information we currently have, each lot will be consumed before the expiry date or not (which would be presented in conditional formatting to the user).


1st Data set

Product code	Lot	Exp. Date	Stock (kg)
1004-14000063	31768	05/10/2020​	15,626
1004-14000063	32055	20/11/2020​	81,000
1004-14000063	32035	10/12/2020​	270,000
1005-14000031	31590	15/09/2020​	7,554
1006-14000070	31074	02/07/2020​	5,811
1011-26000028	31917	15/10/2020​	58,987
1011-26000028	32407	15/04/2021​	60,807

2nd Data set

Product Code	May	June	July	August	September	October
1004-14000063	8.27	29.26	16.81	20.61	24.81	18.81
1005-14000031	2.50	5.56	2.91	4.63	6.60	4.70
1006-14000070	0.90	0.98	1.67	0.93	0.39	1.26
1011-26000028	16.59	38.91	22.00	26.86	38.61	32.15

Any ideas?

Thanks!

 

[yky]

You have stock in tens of thousands while consumption is in the tens. You stock is at least, very very conservatively, 100 folds more than monthly consumption. That means it'll take a least 8 years to consume the stock. Without doing any rigorous calculation, I can tell your stock will all expire before being consumed. Take 1004-14000063 as an example. There are three batches, the first one being 15,626 kg, and there are only four months left to consume it. Even if the monthly consumption is 3,000 kg, you know you can't consume all of it in time.

Would you give a more meaningful example, one that would show some will expire and some won't? With current numbers, which only give one result, errors in formulae may not be detected.

 

[Bluhawk]

You have stock in tens of thousands while consumption is in the tens. You stock is at least, very very conservatively, 100 folds more than monthly consumption. That means it'll take a least 8 years to consume the stock. Without doing any rigorous calculation, I can tell your stock will all expire before being consumed. Take 1004-14000063 as an example. There are three batches, the first one being 15,626 kg, and there are only four months left to consume it. Even if the monthly consumption is 3,000 kg, you know you can't consume all of it in time.

Would you give a more meaningful example, one that would show some will expire and some won't? With current numbers, which only give one result, errors in formulae may not be detected.

Thanks for your reply. You are correct, the data set 2 should be in the thousands. It is a mistake by me when transcribing the data here.

Below the correct tables. I have included some more data to cover more cases-

Data set 1

Product code	Lot	Exp. Date	Stock (kg)
1004-14000063	31768	05/10/2020​	15,626
1004-14000063	32055	20/11/2020​	81,000
1004-14000063	32035	10/12/2020​	270,000
1005-14000031	31590	15/09/2020​	7,554
1006-14000070	31074	02/07/2020​	5,811
1011-26000028	31917	15/10/2020​	58,987
1011-26000028	32407	15/04/2021​	60,807
1057-14000071	32037	01/12/2020​	872
1057-14000071	32744	15/04/2021​	20,000
1071-14000067	31806	20/11/2020​	221
1072-26000029	31494	20/09/2020​	33,705
1072-26000029	31860	20/12/2020​	21,125
2014-14000069	31989	01/11/2020​	643
2014-14000069	32012	15/11/2020​	11,831
2014-14000069	32130	20/11/2020​	118,183
2014-14000069	32096	10/12/2020​	405,000
2014-14000069	32755	15/04/2021​	135,000

Data set 2 - Consumption

Product Code	May	June	July	August	September	October
1071-14000067	25,391	23,110	21,609	19,282	25,649	24,299
2014-14000069	131,038	235,253	172,738	233,474	287,848	247,974
2014-14000072	131,038	235,253	172,738	233,474	287,848	247,974
1057-14000071	29,729	79,810	52,720	62,851	61,608	62,510
1004-14000063	8,272	29,260	16,813	20,607	24,812	18,811
1005-14000031	2,504	5,561	2,905	4,632	6,597	4,696
1006-14000070	899	984	1,675	931	391	1,263
1011-26000028	16,593	38,907	21,997	26,856	38,609	32,151
1072-26000029	3,550	8,324	4,706	5,746	8,261	6,879

Using logical criteria for negative values (which represents a consumed lot) I was able to replicate the "FIFO" consumption in a table. I am having trouble on the next step, integrating the expiry date onto the equation.

 

[yky]

Please read this article to learn how to use XL2BB. I can't use your date format.

 

[Bluhawk]

Please read this article to learn how to use XL2BB. I can't use your date format.

Thanks for referring me to the tool! It is really practical.

Below both Data sets in xl2bb code. I changed the format to MM/DD/YYYY for your reference.

Consulta MR EXCEL.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Product code	Lot	Exp. Date	Stock (kg)		Product Code	May	June	July	August	September	October
2	1004-14000063	31768	05/10/2020	15,626		1071-14000067	25.39	23.11	21.61	19.28	25.65	24.30
3	1004-14000063	32055	11/20/2020	81,000		2014-14000069	131.04	235.25	172.74	233.47	287.85	247.97
4	1004-14000063	32035	10/12/2020	270,000		2014-14000072	131.04	235.25	172.74	233.47	287.85	247.97
5	1005-14000031	31590	09/15/2020	7,554		1057-14000071	29.73	79.81	52.72	62.85	61.61	62.51
6	1006-14000070	31074	02/07/2020	5,811		1004-14000063	8.27	29.26	16.81	20.61	24.81	18.81
7	1011-26000028	31917	10/15/2020	58,987		1005-14000031	2.50	5.56	2.91	4.63	6.60	4.70
8	1011-26000028	32407	04/15/2021	60,807		1006-14000070	0.90	0.98	1.67	0.93	0.39	1.26
9	1057-14000071	32037	01/12/2020	872		1011-26000028	16.59	38.91	22.00	26.86	38.61	32.15
10	1057-14000071	32744	04/15/2021	20,000		1072-26000029	3.55	8.32	4.71	5.75	8.26	6.88
11	1071-14000067	31806	11/20/2020	221
12	1072-26000029	31494	09/20/2020	33,705
13	1072-26000029	31860	12/20/2020	21,125
14	2014-14000069	31989	01/11/2020	643
15	2014-14000069	32012	11/15/2020	11,831
16	2014-14000069	32130	11/20/2020	118,183
17	2014-14000069	32096	10/12/2020	405,000
18	2014-14000069	32755	04/15/2021	135,000
Hoja1

 

[yky]

I don't know what's going on but after copying and pasting your data, this is what I get. Some of the weights now become a million times or so larger.

Book1
	A	B	C	D
1	Product code	Lot	Exp. Date	Stock (kg)
2	1004-14000063	31768	10/5/2020	15,626,358,266
3	1004-14000063	32055	20/11/2020	81000
4	1004-14000063	32035	12/10/2020	270000
5	1005-14000031	31590	15/09/2020	7,553,889,396
6	1006-14000070	31074	7/2/2020	5,810,595,017
7	1011-26000028	31917	15/10/2020	58,987,378,068
8	1011-26000028	32407	15/04/2021	60807
9	1057-14000071	32037	12/1/2020	871,551,393
10	1057-14000071	32744	15/04/2021	20000
11	1071-14000067	31806	20/11/2020	220,670,878
12	1072-26000029	31494	20/09/2020	337,046,732
13	1072-26000029	31860	20/12/2020	21,124,644,188
14	2014-14000069	31989	11/1/2020	643,343,175
15	2014-14000069	32012	15/11/2020	11,830,966,178
16	2014-14000069	32130	20/11/2020	118,182,921,597
17	2014-14000069	32096	12/10/2020	405000
18	2014-14000069	32755	15/04/2021	135000
Sheet1

 

[yky]

This is what I got so far. I have sorted the tables by product number. To get a quick result, I changed the weight of lot #31768 to 80 kg and the months from text to real dates. I believe your second sheet, which only lists product codes, is not enough. You need also list the lot numbers so you know the situation of each lot. The formula for lot #32055 is more complicated. I haven't started that one yet.

mrexcel1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Product code	Lot	Exp. Date	Stock (kg)		Product Code	5/1/2020	6/1/2020	7/1/2020	8/1/2020	9/1/2020	10/1/2020
2	1004-14000063	31768	10/5/2020	80		1004-14000063	8.27	29.26	16.81	20.61	24.81	18.81
3	1004-14000063	32055	11/20/2020	81,000		1005-14000031	2.50	5.56	2.91	4.63	6.60	4.70
4	1004-14000063	32035	12/10/2020	270,000		1006-14000070	0.90	0.98	1.67	0.93	0.39	1.26
5	1005-14000031	31590	9/15/2020	7,554		1011-26000028	16.59	38.91	22.00	26.86	38.61	32.15
6	1006-14000070	31074	7/2/2020	5,811		1057-14000071	29.73	79.81	52.72	62.85	61.61	62.51
7	1011-26000028	31917	10/15/2020	58,987		1071-14000067	25.39	23.11	21.61	19.28	25.65	24.30
8	1011-26000028	32407	4/15/2021	60,807		1072-26000029	3.55	8.32	4.71	5.75	82.61	6.88
9	1057-14000071	32037	12/1/2020	872		2014-14000069	131.04	235.25	172.74	233.47	287.85	247.97
10	1057-14000071	32744	4/15/2021	20,000		2014-14000072	131.04	235.25	172.74	233.47	287.85	247.97
11	1071-14000067	31806	11/20/2020	221
12	1072-26000029	31494	9/20/2020	33,705		31768	FALSE	FALSE	FALSE	FALSE	TRUE	TRUE
Sheet1

Cell Formulas
Range	Formula
G12:L12	G12	=IF(AND($C$2>G1,SUM($G$2:G2)>$D$2),TRUE,FALSE)