Results 1 to 2 of 2

Thread: To Fin out Purchases against Closing stock

  1. #1
    Member
    Join Date
    Aug 2011
    Posts
    54
    Rep Power
    14

    To Fin out Purchases against Closing stock

    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.
    Attached Files Attached Files

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. Replies: 22
    Last Post: 03-19-2013, 07:57 AM
  2. Replies: 2
    Last Post: 12-04-2012, 02:05 PM
  3. Closing Stock Report With Parameters
    By Prabhu in forum Excel Help
    Replies: 8
    Last Post: 05-08-2012, 02:15 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •