Solved

Calculate Age of Inventory

Posted on 2011-09-02
11
2,909 Views
1 Endorsement
Last Modified: 2012-05-12
Hello,

I have a file with received and shipped inventory. I want to know if there is a formula that will allow me to come product received with product shipped against the dates. With this information I want to determine the percentage of inventory older than 15 days.

I have attached a sample file.

Thank You,

John
Aged-Inventory-Sample.xlsx
1
Comment
Question by:jpldpd
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 7

Expert Comment

by:BusyMama
ID: 36475307
Hi, John-

This seems a little difficult, since looking at the example there is no way for us to know when a "shipped" item was "received".  You might send them out first in, first out, or last in, first out ....

To calculate age in days for each item you can use the formula
=TODAY()-D3 (replacing D3 with whatever cell it is).

So I can tell you that anything received on or before 8/18 is 15 days old.  But I can't tell you what percentage of your inventory that applies to.  Not sure if that's helpful or not, maybe you can give more info?

Thanks!

0
 
LVL 12

Expert Comment

by:telyni19
ID: 36475326
What about the initial inventory? It looks like in your sample that you start with 54 on hand and then receive a few, ship a few, and so on. How old do you want to consider those original pieces?
0
 

Author Comment

by:jpldpd
ID: 36475616
Hello,

For the sake of this project, please assume the original 54 are carried over from the previous month. That would make them more than 30 days old. Also, with those in mind, rather than 15 days, I want to change it to 30 days.

Can this be done with the current data?

Thanks,

John



0
 
LVL 7

Expert Comment

by:BusyMama
ID: 36475711
0
 
LVL 9

Expert Comment

by:lynx20
ID: 36475734
Hi:

I am going to assume FIFO [first in, first out]:

=SUMPRODUCT((D3:D74<TODAY()-15)*(G3:G74>0))/I75

This will choose any date 15 days before today and the amount received on that date.  Only works if you keep received positive and sent negative.

Bob
Aged-Inventory-Sample1.xls
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:jpldpd
ID: 36475814
Hello BusyMama,

I see in your formulas =IF(SUMIF(C3:C74,"Shipped",G3:G74)>54,0,(54-COUNTIF(C3:C74,"Shipped"))) the number 54 shows up twice. I know the 54-COUNTIF refers to the previous count. That I can change to reference a cell where I can place the original count which will allow that to be dynamic. What I do not understand is the 54 here Shipped",G3:G74)>54. It seems no matter what I do there it is not altering the result.

Would you please explain?

Thanks,

John
0
 
LVL 12

Expert Comment

by:telyni19
ID: 36475943
I recommend using pivot tables. See attached for an example of how you can display the data in a way that makes it easier to calculate what you need. The final result is calculated using GETPIVOTDATA functions, which pull the relevant sums out of the pivot table, so you could have the results displayed on a different sheet than the pivot table or even back on the original data sheet.
Aged-Inventory-Sample-2.xlsx
0
 

Author Comment

by:jpldpd
ID: 36475982
I like the pivot table and that works for me. Thank you! I do have a question though. Where is the calculation that determines what is less than and what is more than 31 days?

Thanks,

John
0
 
LVL 12

Accepted Solution

by:
telyni19 earned 500 total points
ID: 36476022
Great question. That part was a manual grouping of day counts. Here's another version that uses an additional calculated column to decide which items are in which category, so now the calculation is done in the right-most column of the data table on the original sheet.
Aged-Inventory-Sample-3.xlsx
0
 
LVL 7

Expert Comment

by:BusyMama
ID: 36476862
The SUMIF in my formula first looks for everything that was shipped.  If the number that was shipped is more than 54 (the starting inventory), then it makes that cell a 0.  If the number shipped is less than 54, it returns the number less than 54.  In your example you shipped 50, since that is less than 54 it returns there are 4 left from the original inventory.

So, if you changed some of your "Received" rows to "Shipped" and changed the counts to -1 for those as well, eventually you would run out of original inventory.

Hope that helps!
0
 

Author Closing Comment

by:jpldpd
ID: 36496538
Thank you. This is excellent!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel VBA - Returning results from batch file 9 67
Display the VBA Userform in Screen Center 4 31
Excel If Statement to return a value 5 21
Boolean help 6 27
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now