An inventory aging report is a list of the items on hand grouped by the length of time in inventory. It’s used to identify slow moving inventory plus the additional costs to store and maintain these products until they are sold. If you use accounting software, you’ve got a few options for creating the report.
QuickBooks Enterprise with Advanced Inventory allows you to create a customized inventory aging report to meet your needs. In addition to the inventory aging report, QuickBooks Enterprise helps you manage all of your business income and expenses, create budgets, and generate detailed financial statements. Download QuickBooks Enterprise and try it out risk free for 60 days.
What the Inventory Aging Report Is
Inventory aging reports, also called aged inventory reports or aged stock reports, provide key metrics into how quickly your inventory moves. Similar to accounts receivable or accounts payable aging reports, inventory aging reports tell you the number of days an item has been sitting in inventory based on the receipt date.
The inventory aging report provides businesses with insights such as:
- Identifying slow-moving items
- Highlighting non-moving items
- Understanding the length of time your products sit in inventory
- Quantifying the cost of maintaining inventory for long periods of time
Having access to this kind of information allows you to make informed decisions when it comes to what and how many products to purchase.
How to Calculate the Average Age of Inventory
To calculate the average age of inventory, you need to take the average cost of inventory and divide it by the cost of goods sold for the period. Then you take that result and multiply it by 365 to get the average age of inventory.
The steps for calculating the average age of inventory are:
1. Calculate the Average Cost of Inventory
The first step is to calculate the average cost of inventory. For this, you need to take the cost of inventory on hand at the beginning of the period and add it to the cost of inventory on hand at the end. Next, divide that sum by two and you have the average cost of inventory. If you use accounting software, you can obtain the beginning and ending inventory figures by running a balance sheet report.
The formula to calculate the average cost of inventory is:
(Beginning inventory + Ending inventory) / 2 = Average cost of inventory
Average Cost of Inventory Example:
Let’s assume Company A has $100,000 of inventory on hand as of Jan. 1 and $50,000 Dec. 31. The average cost of inventory for Company A is calculated as follows:
($100,000 + $50,000) / 2 = $75,000 (Average cost of inventory)
2. Calculate the Cost of Goods Sold
Next, you need to determine the cost of goods sold. This number includes all labor and materials that were used to make the products you sell. To calculate the cost of goods sold, you take the beginning inventory, add purchases, and deduct ending inventory for the period. Most accounting software lets you run a profit and loss report that includes your cost of goods sold for the period.
The formula to calculate the cost of goods sold is:
Beginning inventory + Purchases – Ending inventory = Cost of Goods Sold
Cost of Goods Sold Example:
Going back to Company A, let’s assume it still has a beginning inventory as of Jan. 1 of $100,000 and an ending inventory as of Dec. 31 of $50,000. But now let’s add purchases of $60,000 for the year. The cost of goods sold for Company A is calculated as follows:
$100,000 + $60,000 – $50,000 = $110,000 (Cost of goods sold)
3. Calculate the Inventory Turnover Ratio
The next step is to take the average cost of inventory calculated in step one and divide it by the cost of goods sold computed in step two. In our example, the average cost of inventory is $75,000 and the cost of goods sold is $110,000.
The average inventory turnover ratio for Company A is:
$110,000 / $75,000 = 1.47 (Average inventory turnover ratio)
4. Divide 365 by the Average Inventory Turnover Ratio
To get the average number of days it takes to sell an item, divide the total number of days in a year (365) by the average inventory turnover ratio. In our example, the average turnover ratio for Company A is 1.47, so we divide that by 365 to determine the average age of an inventory item.
The average age of inventory for Company A is:
365 / 1.47 = 248.29 Days
This means it takes 248 days for Company A to sell a piece of inventory. Without taking into consideration any other factors, this number seems very high, indicating inventory could become obsolete before it leaves the shelf. Plus, the additional costs to maintain the quality of inventory held for such a long period of time could be significant.
How to Generate an Inventory Aging Report
There are a couple ways to generate an inventory aging report. If you use QuickBooks Enterprise with Advanced Inventory, you can generate a report that has most of the data you need, export it to Excel, and create a pivot table to design a customized inventory aging report.
The other option is to purchase accounting software like IBM or Odoo. Both of these accounting software products allow you to generate an inventory aging report. However, these products are generally for mid- to large-size companies.
How to Generate Report in QuickBooks
Unfortunately, you cannot generate an inventory aging report in QuickBooks Desktop or QuickBooks Online. However, QuickBooks Enterprise with Advanced Inventory has a report that you can run, export to Excel and create an inventory aging report using a pivot table. We will walk through the steps of how to generate this report in this section.
Follow the steps below to create an inventory aging report:
1. Navigate to Company Preferences
Within QuickBooks Enterprise, the preferences section has the FIFO feature. You can navigate to that section via the top menu bar.
Click on Edit and select Preferences as indicated below:
Navigate to Company Preferences in QuickBooks
2. Navigate to Items and Inventory Preferences
First, select Items and Inventory from the menu. Then click on the Company Preferences tab on the right and select the Advanced Inventory Settings button as indicated below:
Navigate to Items and Inventory Preferences in QuickBooks
3. Turn on FIFO Inventory Tracking
Select the checkbox to the left of Use FIFO starting on to turn on the FIFO inventory tracking feature as indicated below:
Turn on FIFO Inventory Tracking in QuickBooks
4. Run the FIFO Cost Lot History by Item Report
From the Reports menu, select the FIFO Cost Lot History by Item report as indicated below:
Run the FIFO Cost Lot History by Item Report in QuickBooks
5. Display the FIFO Cost Lot History by Item Report
Below is a snapshot of the FIFO cost lot history by item report:
FIFO Cost Lot History by Item Report in QuickBooks
6. Export Report to Excel
Export this report to excel by clicking the Excel button to the right of Email as indicated below:
Export Report to Excel
7. Create Pivot Table
The next step is to create a pivot table in order to get the inventory aging report. In this step-by-step video tutorial, QuickBooks ProAdvisor Hector Garcia shows you how to create a pivot table to get your inventory aging report. A pivot table is a table that summarizes and groups data in a meaningful way. The table is generated using one or more Excel spreadsheets.
Alternative Ways to Generate an Inventory Aging Report
While the inventory aging report is not common in accounting software for small businesses, it is often found in accounting software that is ideal for mid- to large-sized companies. For example, Odoo and IBM allow you to generate an inventory aging report. We have included examples of what the inventory aging report looks like in both of these programs.
Below is an example of an inventory aging report generated in Odoo:
Sample Inventory Aging Report Generated in Odoo
The above report generated in Odoo includes each product listed on the left and the number of units in inventory grouped in 30 day increments. According to this report, all inventory items have been sitting in inventory for less than 30 days as of 12/3/2019 (indicated by the very first column).
Below is a sample inventory aging report generated in IBM:
Sample Inventory Aging Report Generated in IBM
The above report generated using IBM software includes the product line, product description, and product class on the left. In contrast to the report generated in Odoo, this report categorizes inventory items into four main groups: age less than one month, age more than one month, age more than two months, and inventory age unknown. “Unknown” generally means that the receipt date was never recorded so the age of the inventory cannot be calculated for those items. To learn more about other key financial reports read our small business bookkeeping guide.
Why Inventory Aging Matters
Staying on top of the age of products sitting in inventory is important. Inventory is one of the largest if not the largest asset you will have on your books. Therefore, it’s important to keep tabs on how much inventory is sitting on the shelf and for how long. This will give you insight into what products customers are buying versus products they are not purchasing so that you don’t invest a lot of money into products that will never sell.
Ways to Improve Your Inventory Aging Report
There are a number of ways you can improve your inventory aging report, but they all start with improving your inventory turnover ratio. If you can reduce the amount of time it takes for an inventory item to turn into a sale, your inventory aging report will improve. For this to happen, your inventory and purchasing departments must work together.
The inventory department needs to review the inventory aging report to determine which items aren’t moving. After these items have been identified, you need to further investigate to see why the products have not sold. It could be that you are ordering too much inventory and that is why you have excess on the shelf. If so, reduce your order for those products and communicate to sales to make sure you order products that move quickly so you can meet customer demand.
Key Takeaways of the Inventory Aging Report
There are several key takeaways of the inventory aging report. First, it identifies slow moving products and products that do not move at all. Second, it gives the purchasing department the data they need to make better decisions when ordering products. Third, it gives you key insight into the additional inventory costs you pay for products that do not sell quickly.
Some key takeaways of the inventory aging report are:
- Identifies slow moving and non-moving products: The inventory aging report highlights products that are either slow to move or aren’t moving out of inventory altogether, and helps you determine why certain products sit in inventory longer than others. This data helps determine what products you order in the future.
- Improves the decision making on inventory purchases: Having access to what products are not moving out of inventory allows the purchasing department to place orders for products that do sell quickly and reduces or eliminates slow moving products.
- Gives insight into additional costs: The longer products sit in inventory, the more it costs to keep them in a “saleable” condition. Storing products for a long time can become costly, plus non-moving products limit the space available for fast-selling products.
Frequently Asked Questions (FAQs) About the Inventory Aging Report
We have included the most frequently asked questions about the inventory aging report in this section.
The most frequently asked questions about the inventory aging report are:
How do you calculate age in inventory?
To calculate age in inventory, you need to first determine the inventory turnover ratio. Inventory turnover ratio is calculated by dividing the cost of goods sold by average inventory for the period. Divide the number of days in a year 365 by your inventory turnover ratio to get the average age of inventory.
How do I reduce inventory days?
Five ways you can reduce inventory days include:
- Work with the sales department to ensure orders match customer demand
- Reduce order sizes for products that are not selling
- Address issues with capacity
- Reduce lead time with suppliers
- Improve forecasts
Bottom Line
Now that you are more familiar with what an inventory aging report is and how it works, you should take the necessary steps to generate your inventory aging report for your business. If you don’t use an accounting software it may be difficult for you to generate an inventory aging report.
QuickBooks Enterprise with Advanced Inventory allows you to create a customized inventory aging report. You can start with an existing FIFO inventory report and export it to Excel to create a pivot table. The best news is when you purchase QuickBooks Enterprise you get a 60 day money back guarantee. Purchase your copy of QuickBooks Enterprise and you can qualify for a discount.
Submit Your Comment
You must be logged in to comment. Click a "Log in" button below to connect instantly and comment.