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.

Download Workbook


4 Steps to Make Inventory Aging Report in Excel

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 versions according to 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.

how to make inventory aging report in Excel

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.

basic outline

Read More: How to Generate Reports Using Macros in Excel (with Easy Steps)


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

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

=C4*D4

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

how to make inventory aging report in Excel

➤ Press ENTER and drag down the Fill Handle tool.

using formulas

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

how to make inventory aging report in Excel

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

=IF((F4-TODAY())<0,0,F4-TODAY())

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.

using formulas

➤ Press ENTER and drag down the Fill Handle tool.

using formulas

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

using formulas

➤ 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.

=VLOOKUP(G4, limit,2, TRUE)

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.

how to make inventory aging report in Excel

➤ Press ENTER and drag down the Fill Handle tool.

using formulas

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

using formulas

Read More: Aging Formula in Excel Using IF (4 Suitable Examples)


Similar Readings


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.

how to make inventory aging report in Excel

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.

creating Pivot Table

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

creating Pivot Table

➤ 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.

how to make inventory aging report in Excel

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

creating Pivot Table

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.

creating Pivot Table

➤ 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.

creating Pivot Table

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

how to make inventory aging report in Excel

Read More: How to Create a Summary Report in Excel (2 Easy Methods)


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 and so we can eliminate them easily.
➤ Go to the PivotTable Analyze Tab >> Options Dropdown >> Options Option.

how to make inventory aging report in Excel

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.

Decorating Pivot Table

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

Decorating Pivot Table

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

Decorating Pivot Table

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

Decorating Pivot Table

Read More: How to Make Monthly Report in Excel (with Quick Steps)


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

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo