• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4014
  • Last Modified:

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
1
jpldpd
Asked:
jpldpd
  • 4
  • 3
  • 3
  • +1
1 Solution
 
BusyMamaCommented:
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
 
telyni19Commented:
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
 
jpldpdAuthor Commented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
BusyMamaCommented:
0
 
lynx20Commented:
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
 
jpldpdAuthor Commented:
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
 
telyni19Commented:
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
 
jpldpdAuthor Commented:
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
 
telyni19Commented:
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
 
BusyMamaCommented:
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
 
jpldpdAuthor Commented:
Thank you. This is excellent!
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now