How to Make Inventory Aging Report in Excel (Step by Step Guidelines)

If you are trying to find the effective steps to make an Inventory aging report in Excel, then you will find this article useful. The Inventory aging report indicates the time it takes for each inventory before stocking out and by analyzing the time duration we can categorize the products as slow-moving, fast-moving, or stagnant inventories easily.
So, let’s dive into the main article.

How to Make Inventory Aging Report in Excel: 4 Steps

Making an Inventory aging report requires steps such as making the basic outline, calculating some values using formulas, and converting the dataset to a Pivot Table to make the report more understandable. In the following 4 steps, we tried to illustrate the steps thoroughly.
We have used Microsoft Office 365 version here, you can use any other version at your convenience.

Step-01: Creating Basic Outlines

Here, we have created the basic outlines of the Inventory Aging report and its related dataset.
➤ In the following figure, you can see the basic report for inventory aging and we have created this outline in the Inventory sheet.
Here, we have the Product IDs, Product names, Unit Prices, Quantities, and Expiry Dates for some of the products, you can give the inputs of your inventories in these places.
For further calculations, we have inserted the columns; Total Price, Due Time, and Condition.

Now, it’s time to create another outline for analyzing the states of the products in the Category sheet.
➤ Create a list showing the categories of the products according to their due times to state the condition or age of the inventories. We have also named the range as the limit.

Read More: How to Keep Track of Inventory in Excel

Step-02: Using Formulas to Make Inventory Aging Report in Excel

➤ To calculate the products’ total prices, apply the following formula in cell E4.

Here, C4 is the Unit Price and D4 is the Quantity of the product Apple.

➤ Press ENTER and drag down the Fill Handle tool.

In this way, you will get the total prices for the products in the Total Price column.

➤ Now we will calculate the times remaining for the expiration of the products after today’s date (19-05-22).

Here, F4 is the Expiry Date of the products, and TODAY() will return today’s date which is 19-05-22.
When the difference between these two values becomes negative IF will return 0 in that case, otherwise for a positive difference value we will get their differences as the Due Time.

➤ Press ENTER and drag down the Fill Handle tool.

After that, you will have the due times remaining for the products after today.

➤ By applying the following formula, we will determine the conditions of the products by looking up the values of the due times in the Category sheet.

Here, G4 is the look-up value which we are going to look up in the limit named range, 2 is the column index number and TRUE is for an approximate match.

➤ Press ENTER and drag down the Fill Handle tool.

Finally, we will have the conditions of the inventories in the Condition column.

Read More: How to Maintain Store Inventory in Excel

Step-03: Creating Pivot Table to Make Inventory Aging Report

In this step, we will create a Pivot Table to organize the data table for highlighting the age of the inventories properly.
➤ Go to the Insert Tab >> PivotTable option.

After that, the PivotTable from table or range dialog box will open up.
➤ Select the range of your table from the Inventory sheet and press OK.

After that, you will be taken to a new sheet with two portions; PivotTable, and PivotTable Fields.

➤ Drag down the Product ID and Product fields to the Rows area, Quantity and Total Price to the Values area, and Condition to the Columns area.

To shorten the name of the fields in the Values area we can customize them like the following.
➤ Click on the dropdown symbol of the Sum of Quantity field and select the option Value Field Settings from different options.

Afterward, the Value Field Settings dialog box will open up.
➤ Rename the field name as Q or whatever you want in the Custom Name box and press OK.

➤ Similarly, rename the Sum of Total Price field to P for brevity.
Finally, we are getting the two new field names in the Values area.

Here is the pivot table of our data range below with the status of the products as headers of their quantities and prices.

Read More: How to Create Inventory Database in Excel

Step-04: Decorating Pivot Table

The final step is to decorate the Pivot Table to make it more noticeable.
In this table, we don’t need the total values, so we can eliminate them easily.
➤ Go to the PivotTable Analyze Tab >> Options Dropdown >> Options Option.

After that, the PivotTable Options dialog box will open up.
➤ Select the Totals & Filters tab and then uncheck the options of the Grand Totals.
➤ Finally, press OK.

In this way, we have eliminated the total values from the rows and columns.

➤ We can change the design by going to the Design Tab and then selecting our desired theme.

So, this is the final outlook of our Inventory Aging Report.

Download Workbook

Conclusion

In this article, we tried to cover the steps to make an Inventory Aging report in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.

Related Articles

• Min Max Inventory Calculation in Excel
• How to Calculate Economic Order Quantity in Excel
• How to Calculate Stock to Sales Ratio Using Formula in Excel

<< Go Back to Inventory Management in Excel | Learn Excel