Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Calculate Age of Inventory

Posted on 2011-09-02
11
Medium Priority
?
3,809 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 

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 2000 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

926 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