Link to home
Start Free TrialLog in
Avatar of John
JohnFlag for United States of America

asked on

Calculate Age of Inventory

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
Avatar of BusyMama
BusyMama
Flag of United States of America image

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!

Avatar of telyni19
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?
Avatar of John

ASKER

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



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
Avatar of John

ASKER

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
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
Avatar of John

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of telyni19
telyni19
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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!
Avatar of John

ASKER

Thank you. This is excellent!