Inventory/Cost From Prior Periods Question
Current business model: Have monthly beginning Inventory + beginning inventory $ value (+ production/-usage) calculating ending inventory lbs. & $.
Ex: July beginning Inventory 100K lbs. & $100K value. (-usage of 50K lbs/$) leaves ending inventory for July and Beginning Inventory for August at $50K/ 50K lbs.
Business asking for the capability to take aged inventory & the associated cost out of Current Month values.
May Production - 100K Lbs @ $1 per lb.
June Production- 200K lbs @$2 per lb.
July Production - 200 K lbs @$4 per lb.
July Orders (usage) at 350K lbs.
Ideally, we want to fulfill in FIFO- using the oldest material until consumed, then next oldest.
We can return individual values (ex: May Production & May cost) using Lag values. Any ideas on formulae to return (First 100K July Sales Cost at $1, 2nd $200K at $2, Last 50K @$4) ?