Inventory Ageing Formula in Excel

[takhlaq]

I need to create an Ageing report for the inventory at hand. The issuing of inventory uses First In First Out (FIFO) principle.Hence If I receive 10 qty in period 3 (say 60 - 90 days) and issue 2 the answer would be 8 remaining. Now if I receive 5 in Period 2 (30 - 60 days) and issue 3, the excel should first check in Period 3 and issue from there hence Period 3 will now have 5 (8-3) remaining. Now in period 1 (0-30 days) if I need to issue 8, it should first check period 3 take 5 from there and remaining 3 from Period 2. Hence Final tally . Period 3 -0,Period 2 -2,Period 1-0. II can send a sample excel for you to play around. Thanks for any help at all

 

[yearnintolearn]

If you could post example that would be helpful.