StockAge FIFO

[JW-ZS]

Hi all,

I am stuck on building a solution to weekly calculate the stock age by FIFO (first-in-first-out) method for all our products (amount, days per date).

Example input data (note Stock minus Sell could be different from stock amount in the next row, because of depreciation or theft) :

Article	Date	Purchase	Stock	Sell
94082	14-3-2020	300	300	0
94082	21-3-2020		300	0
94082	28-3-2020		293	7
94082	4-4-2020		285	8
94082	11-4-2020		286	-1
94082	18-4-2020		277	9
94082	25-4-2020		265	11
94082	2-5-2020		263	2
94082	9-5-2020		254	9
94082	16-5-2020		237	17
94082	23-5-2020		212	25
94082	30-5-2020		186	26
94082	6-6-2020		171	15
94082	13-6-2020		152	18
94082	20-6-2020	353	491	14
94082	27-6-2020		458	33
94082	4-7-2020		440	19
94082	11-7-2020		431	10
94082	18-7-2020		414	17
94082	25-7-2020		399	15
94082	1-8-2020		374	25
94082	8-8-2020		348	26
94082	15-8-2020		323	25
94082	22-8-2020		300	24
94082	29-8-2020		292	8
94082	5-9-2020		287	5
94082	12-9-2020		286	1
94082	19-9-2020		283	4
94082	26-9-2020		285	-2

To calculate the stock age amount and days, we have to take the Stock per certain date and look back at the last purchases (loop back) to define the stock age amount and days.

This could result in extra rows per date. So for example date 1-8-2020. Stock = 374. Look for last purchase (353 on 20-6-2020), so 353 have the age of 42 days (datediff between

Date And Last Purchase Date). For the remaining 21 we have to look back again for the next purchase (300 on 14-3-2020) and so on....

So the remaining 21 have the age of 140 days.

						RESULT
Article	Date	Purchase	Stock	Sell		StockAge Amount	StockAge Days	Date
94082	14-3-2020	300	300	0
94082	21-3-2020		300	0
94082	28-3-2020		293	7
94082	4-4-2020		285	8
94082	11-4-2020		286	-1
94082	18-4-2020		277	9
94082	25-4-2020		265	11
94082	2-5-2020		263	2
94082	9-5-2020		254	9	>>	254	56	9-5-2020
94082	16-5-2020		237	17
94082	23-5-2020		212	25
94082	30-5-2020		186	26
94082	6-6-2020		171	15
94082	13-6-2020		152	18
94082	20-6-2020	353	491	14	>>	353	0	20-6-2020
94082	27-6-2020		458	33		138	98	20-6-2020
94082	4-7-2020		440	19
94082	11-7-2020		431	10
94082	18-7-2020		414	17
94082	25-7-2020		399	15
94082	1-8-2020		374	25	>>	353	42	1-8-2020
94082	8-8-2020		348	26		21	140	1-8-2020
94082	15-8-2020		323	25
94082	22-8-2020		300	24
94082	29-8-2020		292	8	>>	292	70	29-8-2020
94082	5-9-2020		287	5
94082	12-9-2020		286	1
94082	19-9-2020		283	4
94082	26-9-2020		285	-2

I am not that experienced in using macro's (especially not iterative macro's), so I hope someone can help me how to start solving this.

Thanks in advance!

Kind regards,

Johann

[KaneG]

You could definitely do this using an iterative macro but you could also do it by just using the Multi-Row. I've used a dummy text here for the date that you want the audit on. I added some data to your dataset to cater for when the StockAge went over 4 deliveries or so.

Firstly trim the data to just what is needed and join it together to get the dataset needed for this problem

Then it's a matter of starting to allocate the stock on hand. Sort it descending by date, Work out the Total allocation using a Multi-Row, then tidy up and work out how many were allocated on each week. Finally work out the Age. You can probably shorten this part... there are many ways to do it (Running Total and then work out what was needed out of that total for instance).

[paulfound]

Hi @JW-ZS 

I have created a non-macro way of creating a Stock Age monitor for you, messed around a little, so this might be good to refine further.

Nice challenge this. 

Hopefully this will get you going in the right direction.

Paul

[JW-ZS]

Thanks Paul and Kane! I think both flows will be helpful to start. 

One question though, this will work for looking back last 2 purchases, but how to solve if we have to look back for 3 or more purchases?

Kind regards.

Johann

[KaneG · Accepted solution]

That's why I edited your data in order to cater for more than 2 purchases. Here it is attached with the edited data. In the example used here, it only needs to go back 3 purchases, but would go back further if needed.