Calculating Stock Value using LIFO / FIFO

[udi]

Hi

I'm Designing and Inventory Database and want to know how do I
calculate the present value of my Inventory based on LIFO (Last In
First Out) , FIFO (First In Firts Out) or Weighted Cost Method.

Thanks
udi

 

[Nikos Yannacopoulos]

Udi,

You are not giving any details on your table structure; I suppose there is a
table with stock movements, including fields like:
ProductID
MvtType (in or out)
MvtDate
UnitCost

If this is correct, then for the first two you need:
* A DMax expression to pick up the date of the last in movement, or a DMin
for the date of the oldest in movement
* A DLookup expression to pickup the unit cost in that movement (the
previous one nested in the where clause of this one, unless done in code)
* A DSum expression to sum the current inventory
Then it's just a multiplication. This would work nicely in a piece of VB
code.
Alternatively, you could use Totals queries. In any case, you need to have a
mechanism of tracking the oldest "batch" still in stock for the FIFO.

The weighted average is best done with a query (or two consecutiveones),
multiplying units by unit cost and summing.

HTH,
Nikos