Weighted Average Cost of Inventory on FIFO basis

Hi,

Need a bit of excel help.......

I have two input data. The first sheet is ‘Receipt’ which has the details of inventory purchases made. The second sheet ‘StkOH’ displays of the stock on hand of the items. The third sheet ‘Wt Avg’ is the Output sheet which displays the stock on hand and respective weighted average price calculations. The method of calculation is mentioned beside, which need not be displayed.

What these sheets do is basically as follows (for eg):
Current StkOH for RM X: 25,000 kgs (This data is in one sheet)

Purchases Made for RM X: (This data is in another sheet)
01.11.12 – 15,000 kgs @ 100/-
10.11.12 – 20,000 Kgs @110/-
15.11.12 – 10,000 Kgs @ 120/-

In the above case, for calculating wt average FIFO stock on hand (in the third sheet), I would want to consider the purchases backwards. i.e 10,000 from 15.11.12 and 15,000 from 10.11.12, assuming the stock on 01.11.12 and part of the 10.11.12 stock were already consumed on FIFO basis.

I tried various threads in this forum, but couldn't find a similar case. Can anyone help me with a VBA code or an excel function to solve this? I have attached herewith a template describing my requirements. Any help would be great help!

Srigane

Hi Muzza68!

Thanks for your quick reply. If you take Item A, the weighted average fifo cost is actually 96.64 (refer cell C4 of 'WtAvg' sheet). According to your calculation in cell I2 in your new sheet, it is 96.02, the reason being you have taken all the 3 purchases of item A. Actually what is to be considered is only the last purchase on 20-10-12 of 7,600 qty and part of the 2nd purchase on 11-10-12 of 2,400 (out of total 2,850 qty) totaling 10,000 qty of stock on hand.

Can you think over it and let me know, how we could tweak this a little.

Thanks
Srigane

you haven't mentioned the % portion of the second last transaction to be considered, or if the weighted cost averaging is for a set reporting period?