Abrar
Aging Analysis is usually done in companies to find out the financial situation of the company by sorting out the status of the invoices and how much these invoices are past due. In this article, we will discuss a step-by-step procedure to do an aging analysis in Excel.
Here is a brief overview of the aging analysis and its outputs in Excel that we will be discussing in this article.
📁 Download Excel File
Download the practice workbook below.
What is Aging Analysis?
Aging analysis is a technique that is employed by accountants and investors to assess and detect any abnormalities in a company’s accounts receivables. It gives an idea about a company’s financial health by sorting the duration within which an invoice has been past due.
Learn to Do Aging Analysis in Excel with These 4 Steps
To demonstrate Aging Analysis in Excel properly, we have to prepare a dataset involving clients and their invoice numbers, the expiry date of those invoices, and list a category of invoices based on the number of days. Using these values, we will at first calculate the overdue days and thereby the status of the invoices. The dataset is shown as follows.
Step 1: Merging IF and TODAY Functions to Calculate Overdue Days
The first step in doing aging analysis is to calculate the number of overdue days. Overdue days means the number of days that the invoice is past due from its expiration date. We will use the combination of IF and TODAY functions to find the number of overdue days. To do so, we need to follow these steps.
⬇️⬇️ STEPS ⬇️⬇️
- At first, we click on cell E6 and write down the following formula.
=IF(TODAY()>D6,TODAY()-D6,0)🔨 Formula Breakdown
IF(TODAY()>D6,TODAY()-D6,0)
👉 In this formula, the TODAY() function gives the current date as output.
👉 The IF function has three arguments. The first argument TODAY()>D6 checks the condition of whether today’s date is greater than the date in cell D6.
👉 The second argument in IF function is the value to give output if the condition TODAY()>D6 is satisfied. The value of if condition is true results TODAY()-D6. If the condition is not satisfied, the output will be zero (0) as written in the third argument.
- Then, we drag the Fill Handle at the bottom of cell E6 and drag it up to cell E15.
- The overdue days will be calculated as shown in the following image.
Step 2: Finding Status of Invoice Using VLOOKUP Function
The next step in doing aging analysis is to find the Status of Invoice by comparing it to an existing category present in our dataset. We will be using the VLOOKUP function to find the values of Status of Invoice. So, to classify the Status of Invoice based on those categories, we go through the following steps.
⬇️⬇️ STEPS ⬇️⬇️
- At first, we need to create a Named Range based on the Days and the Category of Invoice to the present dataset.
- We select the cell range H6:I13.
- Then, we go to Formulas tab and click on Define Name option under the Defined Names section.
- A New Name box appears. We give the name ‘Categories’ to the selected range of cells, then press OK.
- Thus, a named range with the name Categories will be created.
- Next, we move to cell F6 and click on it.
- We go to formula bar and write down the following formula.
=VLOOKUP(E6,Categories,2,TRUE)🔨 Formula Breakdown
VLOOKUP(E6,Categories,2,TRUE)
👉 In this formula, the first argument E6 in the VLOOKUP function is the lookup value, that we want to look for.
👉 The second argument Categories is the table array where we will look for the lookup value. The table array has been specified as a Named Range consisting of cell range H6:I13.
👉 The third argument 2 represents the column number from the selected range from which we will extract the value. In this case, it is the I column.
👉 The last argument TRUE tells us to give output if there is an approximate match.
- Now, we drag the Fill Handle at the bottom right of cell F6 and drag it up to cell F15.
- We will get all the output values for Status of Invoice as shown in the following image.
Step 3: Creating Pivot Table
Now, we will be creating Pivot Table to organize and view the data table. It will help us to see comparisons and trends in the data. To create Pivot Table, we have to go through these steps.
⬇️⬇️ STEPS ⬇️⬇️
- In the beginning, we select the entire dataset within the cell range B5:F15.
- After that, we go to Insert tab and select Pivot Table option.
- PivotTable from table or range box appears.
- Table/Range will be selected automatically. We select New Worksheet, then press OK.
- A PivotTable Fields menu will be visible on the right. We check the Client, Overdue Days, and Status of Invoice options.
- We drag the Client option to the Rows segment, Status of Invoice to the Columns segment, and Sum of Overdue Days to the Values segment.
- Thus, we will get the following Pivot Table as an outcome.
Step 4: Generating a Dynamic Summary
In this final approach, we will generate a dynamic summary of the analysis. To represent this kind of summary, we will be using a 2D column chart. We can do this by the following steps.
⬇️⬇️ STEPS ⬇️⬇️
- At first, we select the entire pivot table.
- Then, we move to the Insert tab and from the Charts section.
- Then, select the Insert Column option.
- From the drop-down menu, we select the 2D Clustered Column Chart.
- As a result, we will get the following chart. This chart is a dynamic summary of the available data.
Aging Formula for Less than 30, 60, or 90 Overdue Days in Excel
We might need to calculate a certain invoice status that has less than 30, 60, or 90 overdue days. In this approach, we will be using conditional formatting using AND function to filter out the cells that have an overdue days number within 30. To do so, we need to follow these steps.
⬇️⬇️ STEPS ⬇️⬇️
- At first, we select the column that has the invoice expiry dates. We select the cell range D6:D15.
- Then, we go to Conditional Formatting option from the Home tab and click on the New Rule option.
- We select a Rule Type as Use a formula to determine which cells to format.
- Then, on the Format values where this formula is true box, we write down the following formula.
=AND(D6<= TODAY(), TODAY() <=( D6+30))🔨 Formula Breakdown
AND(D6<= TODAY(), TODAY() <=( D6+30))
👉 The AND function gives an output if both the first argument D6<= TODAY() and the second argument TODAY() <=( D6+30) are satisfied.
👉 The first argument checks if the value of cell D6 is smaller than today’s date.
👉 The second argument checks whether today’s date is less than the value of D6+30.
- Now, we click on the Format option.
- In the newly opened Format Cells window, we go to Fill section and select a color as shown in the image.
- Then, we press OK.
- We press OK again on the Edit Formatting Rule box.
- Now, all the dates that have Overdue Days with less than 30 days will be highlighted.
Similarly, we can do the same for 60 or 90 days by changing the value in the formula to 60 or 90 in place of 30.
📄 Important Notes
🖊️ The Named Range scope has to be selected as Workbook to use it all throughout our Excel workbook.
🖊️ We will use IF function along with TODAY function to eliminate the possibility of Overdue Days being negative. If Overdue Days value is zero or less than zero, which means there are no Overdue Days.
📝 Takeaways from This Article
You have taken the following summed-up inputs from the article:
📌 We can do the aging analysis by finding out the number of Overdue Days in the first step.
📌 Then, we can use the number of Overdue Days to look up the categories of invoices based on the VLOOKUP function.
📌 Finally, we can generate pivot table and a dynamic chart to represent the data of our analysis.
Conclusion
To conclude, I hope that this article has helped you to understand how to do aging analysis in Excel. For any queries, reach out to us by leaving a comment below. Follow our website ExcelDen for more informative articles related to Excel.
