Saquib Ahmad Shuvo
If you are looking for some special tricks to do aging analysis in Excel, you’ve come to the right place. There is one way to do aging analysis in Excel. This article will discuss every step of this method to do aging analysis in Excel. Let’s follow the complete guide to learn all of this.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article. It contains all the datasets and graphs in different spreadsheets for a clear understanding.
What Is Aging Analysis?
Accountants use aging to determine any irregularities in a company’s accounts receivables. To figure out how long an outstanding bill has been unpaid, customer invoices are usually categorized by 30 days.
Step-by-Step Procedure to Do Aging Analysis in Excel
In the following section, we will use one effective and tricky method to do aging analysis in Excel. To create a more understandable aging report, it is necessary to make a basic outline and calculations with formulas, as well as convert the dataset to a pivot table. This section provides extensive details on this method. You should learn and apply all of these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.
Step 1: Create Dataset
Here, we have created the dataset of aging analysis.
- In the following image, we can see the basic dataset of the Aging Analysis report.
- Here, we have Customer names, Invoice numbers, Date, and Amount in the following dataset.
- For further calculations, we have inserted the columns Days Sales Outstanding and Status of Invoice.
Now, we are going to create another outline for analyzing the status of the invoice in the Category sheet.
- You have to create the categories of the invoice according to their day’s sales outstanding to dictate the condition. Here, we have also named the range of the cells as LIMITS.
- To name the range of the cells as LIMITS, you have to select the cells as shown below and go to the Formulas tab and select Name Manager.
- Next, when the New Name dialog box appears, enter the name as LIMITS in the Name box.
Step 2: Use Formulas for Aging Analysis
Now, we will do further calculations for aging analysis. Here we use TODAY and IF functions to calculate the day’s sales outstanding column values. We also use the VLOOKUP function to determine the status of the invoice.
- To calculate days sales outstanding, we will use the following formula in the cell F5:
=IF(TODAY()>D5,TODAY()-D5,0)
Here, D5 is the date of each invoice, and the TODAY function will return today’s date which is 13-06-22. The IF function will return 0 if the difference between two values is negative, otherwise the Days Sales Outstanding value will be equal to the difference between two positive values.
- Then, press Enter.
- Next, drag the Fill handle icon.
- As a consequence, you will get the following Days Sales Outstanding column.
- To determine status of invoice, we will use the following formula in the cell F5:
=VLOOKUP(F5,LIMITS,2,TRUE)
By applying the above formula, we will be able to identify the conditions of the invoice by looking up the values of days sales outstanding. Here, F5 is the look-up value which we are going to look up in the LIMITS named range. 2 is the column index number and TRUE is for an approximate match.
- Then, press Enter.
- Next, drag the Fill handle icon.
- As a consequence, you will get the following Status of Invoice column.
Read More: Aging Formula in Excel Using IF (4 Suitable Examples)
Step 3: Create Pivot Table for Aging Analysis Summary
Now, we are going to create a pivot table to organize the data table for dictating the status of the invoice.
- First of all, you have to select the range of the cells as shown below.
- Then, go to the Insert tab and select Pivot Table.
- When the PIVOT Table from table or range dialog box appears, select the range of the cells and choose New Worksheet.
- Next, click on OK.
- When the PivotTable Fields window appears, drag down the Customer to the Rows area, Amount to the Values area, and Status of Invoice to the Columns area.
- As a result, you will get the following pivot table. Through this pivot table, we can figure out how long an outstanding bill has been unpaid by categorizing customer invoices by 30 days.
Read More: How to Use Stock Ageing Analysis Formula in Excel (2 Easy Ways)
Step 4: Generate Dynamic Aging Analysis Report
To create a dynamic summary of the aging analysis, we are going to create a chart.
- To create a Clustered Column chart, select the range of data and go to the Insert tab. Next, select the Clustered Column chart.
- As a consequence, you will get the following Clustered Column chart.
- To modify the chart style, select Chart Design and then, select your desired Style 8 option from the Chart Styles group.
- As a consequence, you will get the following Clustered Column chart
- This is how we will get the final report of aging analysis. Through the following summary, we can figure out how long an outstanding bill has been unpaid.
Read More: How to Make Inventory Aging Report in Excel (Step by Step Guidelines)
💬 Things to Remember
✎ When you use the IF function carefully gives all the required parentheses. If you don’t use the if function to identify the day’s sales outstanding, we will get negative values. To avoid negative values we use the if function.
✎ You have to adjust row height after following each method.
Conclusion
That’s the end of today’s session. I strongly believe that from now you may be able to do aging analysis in Excel. l. If you have any queries or recommendations, please share them in the comments section below.
Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions. Keep learning new methods and keep growing!
Thank you. A first-timer doing the aging analysis and the tutorial is very helpful.
Glad to know it helped you!