CHALLENGE! How to keep track of stock per lot without lot :-)

[#1]

Dear all,

I have a very cool challenge which is easy to work out in Excel but (for me) really difficult in QV. Please find attached Excel file for Input & requested Output (= Stock). The Stock column is the expected outcome per day based on the records from the Input.

The challenge is to keep track of a LotNr once there are multiple LotNrs on an item location. Based on First/Last In First Out (FIFO/LIFO) the Stock is tracked.

Description may be a bit vague but once you see the Excel it will make sense (or please ask me and I will explain in further detail).

Hope one of you can help me on the way...

Best regards,

Marcel

[MK_QSL]

The Result provided for 03-Jan looks wrong. Please check and let me know.

[#3]

Hi Manish,

No it's okay:

Transaction:

Jan 1: +900 IN

STOCK: +900 IN

Jan 2: -400 IN

Jan 2: +400 A

STOCK: 500 IN, 400 A

Jan 3: -500 IN (therefore Stock IN = 0)

Jan 3: +500 B

STOCK: 0 IN (no show), 400 A, 500 B -- all with LotNr 20150101

[MK_QSL]

Check LotNr  I mentioned below... Are they Ok?

How come LotNr for A on 02-Jan is 20150101? It should be 20150102

How come LotNr for B on 03-Jan is 20150101? It should be 20150103

Let me know if I am wrong...

02-Jan	ES	IN	VLA1863.001.06	20150101	500
	ES	A	VLA1863.001.06	20150102	400
03-Jan	ES	A	VLA1863.001.06	20150102	400
	ES	B	VLA1863.001.06	20150103	500

[#5]

Hi Manish,

No, that is the trick: once an item is received for the first time in the warehouse, then I would like to add the LotNr (which officially does not exist). This LotNr should taggle along all item movements. The ultimate goal is to measure stock age per item location (based on assumption of FIFO and LIFO).

Hope this clarifies my question?

Thank you for your interest in this challenge!

Regards,

Marcel

[#6]

I have more solution areas, each having its own difficulties. I find it difficult to determine best approach.

Solution 1: add the correct "LotNr" to every record. Then the only thing I need to do is to sum the Qty to get the stock per item location lot.

Tricky part:

- How to split up such a record when a movement comprises more than 1 lot? Then FIFO/LIFO assumption also arises (which Lot to pick first).

Solution 2: determine LotNr after calculating stock.

Here I mean that you calculate all transactions per Item & Date (Inflow), e.g. on Jan 1 and 10 there was a new inflow. Than subtract all outflow and determine (again from FIFO and LIFO perspective) which LotNrs are left with what Qty.

It is relatively easy to explain when using the idea of buckets where you decide for each record what to do:

1. get it from stock (scenario: sufficient stock)

2. split rule to match LotNr Qty (obviously you cannot have negative stock per lot)

Quite a challenge (at least in my opinion)...

[#7]

One more thing to add: it is possible to alter the input file for easing scripting. But this is how I now receive the data from the item transaction table..