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