Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Calculate Age of Inventory

Posted on 2011-09-02
11
Medium Priority
?
3,612 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: 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!

 
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

688 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