Countback valuation

Fri, 18 Sep 2015 10:41:21 GMT

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

topic Countback valuation in QlikView

Countback valuation