Inventory/Cost From Prior Periods Question

Jojo88

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. 

Ex: 

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

 

Amaya

Hi @Jojo88 ,

 

It was a good challenge for me, and thank you for the question.
I could make a solution for FIFO calculation, so please consider this.

 

First of all, we should prepare an intermediate module for match productions and orders. It has a Time dimension and a list of purchase orders (Orders #).

 

Intermediate Module

If cumulate order quantity exceeds cumulate production quantity, we can say the production in the month is all sold. When the production items in the month are partly sold, we have to separate the cost of sold items from the remaining items.

Then we could match the remaining items to the second order.

 

I could hardly describe the idea well it is faster to look at screenshots or grid view excel.

 

This solution works under the condition that production items in the month sold up to 2 orders. If the purchase order would be smaller and production separates into more than three orders, we have to improve this module.

 

FIFO Module

 

Outcome

 

Order Module

 

I would be happy this helps you, and any questions are welcomed

 

Taichi

FIFO_Model_117298.xlsx