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
Bookmarks