Countback valuation

[#1]

I need a way of valuing my stock using a count back method based on cost of latest receipts until I exhaust the stock count.

Stock records hold the following fields

     Stock code     Stock qty     Goods received qty     Goods received unit cost

     "p-code"         "curr-stock"   "grn-orig-qty"              "grn-price"

     A123              50                20;25;20;30;40;0;0;0   1.00;1.25;1.20;1.22;1.20;0;0;0

     B456              10                1;1;1;1;1;1;1;1            1.00;1.00;1.00;1.00;1.00;1.00;1.00;1.10

The goods received information is stored in a single field and separated by a ;

I need to value the stock on a LIFO basis so work from the newest values in each field.

e.g.

A123 working on the latest receipts first and working to through to the older ones

     20 x 1.00 = 20.00   (50-20 = 30 remaining stock units to value)

     25 x 1.25 = 31.25   (30-25 = 5 remaining stock units to value)

     5 x 1.20   = 6.00     (5-5 = 0 remaining units to value)

     Stock value is 20.00 + 31.25 + 6.00 = 57.25

B456 same as A123 but there are not enough receipt history transactions to exhaust my current stock so I need to use the oldest receipt to calculate the remaining

     1 x 1.00 = 1.00     (10-1 = 9 remaining stock units to value)

     1 x 1.00 = 1.00     (9-1 = 8 remaining stock units to value) etc etc

     1 x 1.10 = 1.10     (3-1 = 2 remaining stock units to value and need to be valued at 1.10)

     Stock value is = 10.30