Hi,
At year end, we need to determine age of Items left in Inventory. For example, we are left with 2 pieces of Material Part No.357000 at year end, and need to determine age of these two quantities of material based on purchase. We go to purchase data and find most recent purchase. Recent Purchase (1) : 1 qty On 14.02.2014 so, Age of 1 qty is* 289 days.* Then we look again on purchase and find recent purchase (2) before Recent Purchase (1) Recent Purchase (2) is of qty 1 on 26.06.2013 so age of balance qty is *522 days. * *This process continues till age of quantities left in stock are calculated.* *After calculation of age , it is plotted in Ageing Table. ( Colm H to Q)* *In given example, age of two qty is * *Qty 1 Age is 289, so, "1" is placed in col N of part number 357000 row 3* *Another Qty 1 is 522 days old, so "1" is placed in col O **of part number 357000 row 3* *Please see attached excel sample file.* *Let me know if further details are required.* *Many Thanks,* *C.G.kumar* On Mon, Dec 22, 2014 at 3:15 PM, Mandeep Baluja <rockerna...@gmail.com> wrote: > Please explain your Query Again I an unable to understand it . Aeging can > be count but on what criteria whether on particular order no, material no , > document no. where is availble qty column. > > > On Monday, December 22, 2014 9:44:49 AM UTC+5:30, kumar wrote: >> >> Hi, >> >> I have purchase data in a spreadsheet and List of Inventory Items on >> another worksheet of excel. >> >> May someone help me with a macro or advance formula which computes Ageing >> of Inventory. >> >> Age of Inventory is calculated in Purchase Sheet but do not know how to >> compute for available qty only. >> >> Sample Sheet attached. >> >> >> -- > Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s > =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ > https://www.facebook.com/discussexcel > > FORUM RULES > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > 2) Don't post a question in the thread of another member. > 3) Don't post questions regarding breaking or bypassing any security > measure. > 4) Acknowledge the responses you receive, good or bad. > 5) Jobs posting is not allowed. > 6) Sharing copyrighted material and their links is not allowed. > > NOTE : Don't ever post confidential data in a workbook. Forum owners and > members are not responsible for any loss. > --- > You received this message because you are subscribed to the Google Groups > "MS EXCEL AND VBA MACROS" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to excel-macros+unsubscr...@googlegroups.com. > To post to this group, send email to excel-macros@googlegroups.com. > Visit this group at http://groups.google.com/group/excel-macros. > For more options, visit https://groups.google.com/d/optout. > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Ageing Inventory_Kumar.xlsx
Description: MS-Excel 2007 spreadsheet
