***Purchases against Closing stock***

[[email protected]]

Hi Friends,
Plz help to have Macro for the below requirement

I have part number wise purchase data and part number wise closing stock.
Now I need to identify the purchases against the closing stock i.e if I have 500 Qty in my closing stock I need to identify the last purchases up to the extent of 500 Qty considering First in First out (FIFO)

Example 1
I have 100 Qty of part number ABCD and purchases against the part number as below. Form the below purchase data I need to identify the last 100 Qty purchase details
Purchase List
Received Date PO No Part Number details Qty Unit Pirce Value
02-02-2013 10 ABCD XXX 30 900 22500
03-03-2013 11 ABCD XXX 40 920 46000
06-03-2013 12 ABCD XXX 30 950 28500
06-04-2013 13 ABCD XXX 20 940 18800
19-05-2013 14 ABCD XXX 10 925 9250
10-05-2013 15 ABCD XXX 15 970 14550

Output need to be as below.
Received Date PO No Part Number details Qty Unit Pirce Value
03-03-2013 11 ABCD XXX 25 920 23000
06-03-2013 12 ABCD XXX 30 950 28500
06-04-2013 13 ABCD XXX 20 940 18800
19-05-2013 14 ABCD XXX 10 925 9250
10-05-2013 15 ABCD XXX 15 970 14550

In the output PO no. “11” reflecting only 25 instead of 40 since we are carrying only 100 Qty and we need to consider last 100 Qty purchase, hence it has considered that 15Qty has sold against Po No. “11”" and reflecting 25 as closing stock.

I have attached sample file for your reference Kindly help to get Macro to automate my routine activity.

[[email protected]]

Hi All,

Can any one help me to solve my Query?

[arlu1201]

What about PO 10? Why is it not showing in the output?

[[email protected]]

What about PO 10? Why is it not showing in the output?

Hi,

we have only 100 Qty in the closing stock. since we applied first in first out logic ie.out of 145 purchases we sold 45 Qt and the closing stock will be last 100 Qtys. Hence we considered PO 10 got sold and also against PO 11 we have only 25 Qty in the closing stock.