6 Replies Latest reply: Mar 13, 2015 8:25 AM by Marcel Artz

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

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

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

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

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

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

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

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
• ###### Re: CHALLENGE! How to keep track of stock per lot without lot :-)

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

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

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)...

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

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..