How to Calculate Inventory Age: Combine with Turns for a Holistic View

How to Calculate Inventory Age: Degradable and Perishable Inventory

Inventory age is not something that a lot of inventory planners consider but depending on the types of items you hold in inventory, this can be an important topic. In a previous video , I explained how you can calculate inventory turns to determine which items or components move quickly through your inventory storage and which ones may linger.

The items that linger in your inventory need to be addressed because that means they are either finished goods that aren’t selling fast enough or they are components of a finished good that aren’t being used up in production. Inventory turns alone may not provide you with enough information to know which items are the most concerning. Inventory age can be a crucial calculation in special circumstances.

Consider the following examples of aging items:

• Your company carries perishable foods that have an expiration date.

• Your company carries items made of a degradable substance that has a specific lifespan.

• Your company carries items that will become obsolete within a specific time frame.

Let’s go through a specific example.

Suppose you carry rubber gaskets in inventory. Unless these gaskets are highly specialized, they are fairly cheap, so it might be reasonably harmless to carry a lot of them in your inventory. When we take a look at the recommended shelf life of rubber though, we see that the shelf life of a rubber gasket depends on the type of rubber used.

If your gaskets are made of natural rubber and have been sitting on the shelf for two years, your goal should be to use them up before they become three years old. Remember too, that it’s not just that you want to use them up before they are three years old, but you also don’t want to send a finished good to a customer using a rubber gasket that is close to its shelf life.

For example, suppose you offer a 5-year warranty on your product but by the time you produced this product for your customer, the gasket used was 2 years old. With a shelf life of 3-5 years for the rubber, the product may fail for your customer within 1-3 years because of the gasket’s age.

Example Calculation of Inventory Age 

Let’s see how we can calculate the age of our inventory. I will assume here that we are using a FIFO system which is the most popular choice for inventory management. If you’re not familiar with the term FIFO, it stands for first-in-first-out. This means that the inventory that is used first for any item you carry is the inventory that first arrived in storage, i.e., your oldest inventory.

What I’m showing in the table is for one gasket item we have in stock. As of today’s date, which is May 10, 2021, we have 116,000 of this particular gasket in our inventory. The table of data shows the history of purchases we made from our vendor for this part. For example, on March 5, 2021, we received 25,000 gaskets and on February 11, 2021, we received 25,080 gaskets. The remainder of the table shows our purchase history for this part number back to 2018.

Since we are using a FIFO system, we know that what we have in stock is our youngest inventory, but we don’t really know how young or old our existing inventory is. To calculate the age of what we have in stock, we start with the most recent purchase, shown on Line 6. If we have 116,000 in stock and our most recent purchase was 25,000, then we know we have 25,000 gaskets that have been in our storage for 66 days. This is a simple subtraction in Excel of the March 5th purchase date from today’s date of May 10.

This leaves 91,000 gaskets in inventory where we have not yet assigned an age, so let’s move down to the next purchase date.

On February 11, 2021, we received 25080 gaskets, so we have 25080 gaskets that are 88 days old. This leaves 65,920 gaskets where we have not yet assigned an age. We continue down the table until all 116,000 gaskets have been assigned an age.

In this example, we finish our age assignments with the October 5, 2019 purchase. While we purchased 15,120 gaskets in the purchase, we only have 5729 left to account for, so we have 5729 gaskets that are 583 days old.

We can now examine the overall picture of the age of this gasket. We certainly see that we have gaskets that are more than a year old. Internally, we need to decide how old these gaskets can get before we no longer wish to use them in production.

I’ve done manual calculations here so you can see how to calculate inventory age but depending on where you store your inventory and purchasing records, these calculations can be automated. As an example, what’s shown here is an inventory age dashboard which refreshes its calculations multiple times per day providing the company’s leadership not only the detailed calculations I just demonstrated but also an overall picture of whether older product is a finished good or raw material, and how much cash is tied up in older inventory.

What Action Can You Take to Reduce Old Inventory?

I’ll conclude this topic with a few ideas on how you can reduce the amount of old inventory you have in stock.

Some purchasing contracts allow for returning a negotiated percentage of product to the vendor each year. If you don’t currently have this arrangement, you may wish to negotiate that into future contracts.

If that’s not an option, you can attempt to sell this inventory directly or hire a third-party service to do so.

You can donate the inventory to charity if the item can be of use. Excess rubber gaskets are probably not useful to most charities. If you had older inventory of furniture and household supplies though, that would be more appropriate for a donation.

Failing all of the above, you may have to consider your older inventory to be scrap and have your accountant write it off in the financial records.

 In Summary

We have seen how to calculate inventory age for each item carried and how it can enhance your knowledge of the true state of inventory. Before I conclude, I will offer one practical rule of thumb. In most cases, you can consider inventory dead if it’s been on your shelf for more than 1 year. If it’s that old and the inventory turn metric shows it’s slow moving, it may be time to invoke some of the actions I just mentioned.