Simple FIFO Inventory with Lot expiry date

Bluhawk

New Member
Joined
Apr 10, 2013
Messages
8
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 codeLotExp. DateStock (kg)
1004-1400006331768
05/10/2020​
15,626
1004-1400006332055
20/11/2020​
81,000
1004-1400006332035
10/12/2020​
270,000
1005-1400003131590
15/09/2020​
7,554
1006-1400007031074
02/07/2020​
5,811
1011-2600002831917
15/10/2020​
58,987
1011-2600002832407
15/04/2021​
60,807


2nd Data set
Product CodeMayJuneJulyAugustSeptemberOctober
1004-140000638.2729.2616.8120.6124.8118.81
1005-140000312.505.562.914.636.604.70
1006-140000700.900.981.670.930.391.26
1011-2600002816.5938.9122.0026.8638.6132.15


Any ideas?

Thanks!
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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.
 
Upvote 0
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 codeLotExp. DateStock (kg)
1004-1400006331768
05/10/2020​
15,626
1004-1400006332055
20/11/2020​
81,000
1004-1400006332035
10/12/2020​
270,000
1005-1400003131590
15/09/2020​
7,554
1006-1400007031074
02/07/2020​
5,811
1011-2600002831917
15/10/2020​
58,987
1011-2600002832407
15/04/2021​
60,807
1057-1400007132037
01/12/2020​
872
1057-1400007132744
15/04/2021​
20,000
1071-1400006731806
20/11/2020​
221
1072-2600002931494
20/09/2020​
33,705
1072-2600002931860
20/12/2020​
21,125
2014-1400006931989
01/11/2020​
643
2014-1400006932012
15/11/2020​
11,831
2014-1400006932130
20/11/2020​
118,183
2014-1400006932096
10/12/2020​
405,000
2014-1400006932755
15/04/2021​
135,000


Data set 2 - Consumption

Product CodeMayJuneJulyAugustSeptemberOctober
1071-1400006725,39123,11021,60919,28225,64924,299
2014-14000069131,038235,253172,738233,474287,848247,974
2014-14000072131,038235,253172,738233,474287,848247,974
1057-1400007129,72979,81052,72062,85161,60862,510
1004-140000638,27229,26016,81320,60724,81218,811
1005-140000312,5045,5612,9054,6326,5974,696
1006-140000708999841,6759313911,263
1011-2600002816,59338,90721,99726,85638,60932,151
1072-260000293,5508,3244,7065,7468,2616,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.
 
Upvote 0
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
ABCDEFGHIJKL
1Product codeLotExp. DateStock (kg)Product CodeMayJuneJulyAugustSeptemberOctober
21004-140000633176805/10/2020 15,626 1071-14000067 25.39 23.11 21.61 19.28 25.65 24.30
31004-140000633205511/20/2020 81,000 2014-14000069 131.04 235.25 172.74 233.47 287.85 247.97
41004-140000633203510/12/2020 270,000 2014-14000072 131.04 235.25 172.74 233.47 287.85 247.97
51005-140000313159009/15/2020 7,554 1057-14000071 29.73 79.81 52.72 62.85 61.61 62.51
61006-140000703107402/07/2020 5,811 1004-14000063 8.27 29.26 16.81 20.61 24.81 18.81
71011-260000283191710/15/2020 58,987 1005-14000031 2.50 5.56 2.91 4.63 6.60 4.70
81011-260000283240704/15/2021 60,807 1006-14000070 0.90 0.98 1.67 0.93 0.39 1.26
91057-140000713203701/12/2020 872 1011-26000028 16.59 38.91 22.00 26.86 38.61 32.15
101057-140000713274404/15/2021 20,000 1072-26000029 3.55 8.32 4.71 5.75 8.26 6.88
111071-140000673180611/20/2020 221
121072-260000293149409/20/2020 33,705
131072-260000293186012/20/2020 21,125
142014-140000693198901/11/2020 643
152014-140000693201211/15/2020 11,831
162014-140000693213011/20/2020 118,183
172014-140000693209610/12/2020 405,000
182014-140000693275504/15/2021 135,000
Hoja1
 
Upvote 0
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
ABCD
1Product codeLotExp. DateStock (kg)
21004-140000633176810/5/202015,626,358,266
31004-140000633205520/11/202081000
41004-140000633203512/10/2020270000
51005-140000313159015/09/20207,553,889,396
61006-14000070310747/2/20205,810,595,017
71011-260000283191715/10/202058,987,378,068
81011-260000283240715/04/202160807
91057-140000713203712/1/2020871,551,393
101057-140000713274415/04/202120000
111071-140000673180620/11/2020220,670,878
121072-260000293149420/09/2020337,046,732
131072-260000293186020/12/202021,124,644,188
142014-140000693198911/1/2020643,343,175
152014-140000693201215/11/202011,830,966,178
162014-140000693213020/11/2020118,182,921,597
172014-140000693209612/10/2020405000
182014-140000693275515/04/2021135000
Sheet1
 
Upvote 0
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
ABCDEFGHIJKL
1Product codeLotExp. DateStock (kg)Product Code5/1/20206/1/20207/1/20208/1/20209/1/202010/1/2020
21004-140000633176810/5/2020801004-140000638.2729.2616.8120.6124.8118.81
31004-140000633205511/20/202081,0001005-140000312.505.562.914.636.604.70
41004-140000633203512/10/2020270,0001006-140000700.900.981.670.930.391.26
51005-14000031315909/15/20207,5541011-2600002816.5938.9122.0026.8638.6132.15
61006-14000070310747/2/20205,8111057-1400007129.7379.8152.7262.8561.6162.51
71011-260000283191710/15/202058,9871071-1400006725.3923.1121.6119.2825.6524.30
81011-26000028324074/15/202160,8071072-260000293.558.324.715.7582.616.88
91057-140000713203712/1/20208722014-14000069131.04235.25172.74233.47287.85247.97
101057-14000071327444/15/202120,0002014-14000072131.04235.25172.74233.47287.85247.97
111071-140000673180611/20/2020221
121072-26000029314949/20/202033,70531768FALSEFALSEFALSEFALSETRUETRUE
Sheet1
Cell Formulas
RangeFormula
G12:L12G12=IF(AND($C$2>G1,SUM($G$2:G2)>$D$2),TRUE,FALSE)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top