Stock aging calculation method

[levanduyet]

Stock aging calculation method
Hi everyone,

I have a Access Table to keep all the movements IN/OUT for warehouse.

It has the following fields:
_ MaterialCode
_ MaterialName
_ DocumentDate
_ UoM (Unit of Measurement)
_ InOutQty (it depend on In or Out, so this qty can be +/-)

How can I write the query to calculate the stock aging?

Thanks,

LVD

[OBP]

Do you know what the calculation is for stock aging?

[levanduyet]

Hi,
Suppose that I apply FIFO for my material A. Our record is as following:

13/03/2009 In/Out +50
15/03/2009 In/Out -10
12/12/2009 In/Out -20
05/01/2010 In/Out +20
07/02/2010 In/Out -10

So, I want to calculate the stock age days. As the above example, there are 10 items left from 13/03/2009 and 20 items left from 05/01/2010.

I need to write the query to calculate the stock age days.

LVD

[orange]

How do you know that "there are 10 items left from 13/03/2009" and
"20 items left from 05/01/2010"?

Why couldn't 1 or more items of the last -10 transaction come from the batch from 05/01/2010?

I found and read your conversation at
http://www.vbforums.com/showthread.php?p=3906758

I am unfamiliar with a Stock Aging Report. Can you describe what the logic is to calculate "stock age"?

Many people on this forum understand database/ queries/tables etc, but we don't know what exactly you are looking for.

Help us help you by clarifying what it is you are trying to do.

I tried looking up a definition of a stock aging report, but was unsuccessful. I could only find parts of info from Oracle/SAP and a few other packages.

Good luck.

PS:
I did do some more searching and found some info that might be useful to you.
see http://www.investopedia.com/terms/a/average-age-of-inventory.asp

Further info: I found a "quasi explanation" of Stock Aging from SAP. You may get some info on approach and what is required (as far as types of variables), but it is wrapped in SAP terms and screens.
see
http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/30f15839-0cf1-2b10-c6a7-ebe68cc87cdc?overridelayout=true

[levanduyet]

How do you know that "there are 10 items left from 13/03/2009" and
"20 items left from 05/01/2010"?
Hi Orange,

I have shown on the above example.
You can see that on the 13/03/2009, I received 50 (batch 1). Then I have issued out 10 on 15/03/2009, 20 on 12/12/2009 and 10 on 07/02/2010. So there are 10 left of 13/03/2009.

LVD

[OBP]

Orange, FIFO stands for First In First Out.

I don't think a query can handle the complexity of this calculation, but a form with a VBA Recordset can do so.

[orange]

Yes, I understand FIFO. The issue I had with the calculation as given by LVD in his other post is that he does not use a Batch number. However, in his latest post here, he referenced Batch1.. The other references I found all seemed to deal with batches/lots.
I'm really asking him, and others, to define a Stock Aging process/calculation in plain English, and then we can possibly assist with SQL or vba or whatever solution evolves.

OBP, I agree that it seems too complex for a simple query. I'd still like to see the logic stated clearly in plain English, so we could understand what is required.

Orange

[levanduyet]

Yes, I understand FIFO. The issue I had with the calculation as given by LVD in his other post is that he does not use a Batch number. However, in his latest post here, he referenced Batch1.. The other references I found all seemed to deal with batches/lots.

Hi orange,
Thanks for your help.
I have added in the batch number for easy to explain.



OBP, I agree that it seems too complex for a simple query. I'd still like to see the logic stated clearly in plain English, so we could understand what is required.
The logic that I have explained in thread#3. We will issue the item of the first batch until it finish then we issue the item of the next batch.

Tks,

LVD

[OBP]

One way that you could do this is to have an Age field in the table and use VBA to update it each time that a quantity is added to the inventory.
Thta way the Age would always be avaialable in a simple query.

[levanduyet]

Anyone got any suggestion or any solutions?

Tks,

LVD

[OBP]

I suggested one in post #9.
Do you want to try it?

[orange]

LVD,

You may find some info at this site useful.

http://allenbrowne.com/AppInventory.html

Not specifically aging of stock, but stock related info that may be useful to you. Have you tried OBP's idea?