4 Steps to Do Aging Analysis in Excel

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.

Overview of aging analysis


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

Sample dataset

Step

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.

Insert combination of IF and TODAY functions for calculating overdue days

  • Then, we drag the Fill Handle at the bottom of cell E6 and drag it up to cell E15.

Dragging Fill Handle

  • The overdue days will be calculated as shown in the following image.

Get Overdue Days

Step

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.

Defining Named Range

  • A New Name box appears. We give the name ‘Categories’ to the selected range of cells, then press OK.

New Name window

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

VLOOKUP formula to find status of invoice based on overdue dates

  • Now, we drag the Fill Handle at the bottom right of cell F6 and drag it up to cell F15.

Dragging Fill Handle

  • We will get all the output values for Status of Invoice as shown in the following image.

Status of Invoice outputs

Step

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.

Inserting a pivot table

  • PivotTable from table or range box appears.
  • Table/Range will be selected automatically. We select New Worksheet, then press OK.

Customize Pivot Table

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

PivotTable fields

  • Thus, we will get the following Pivot Table as an outcome.

Pivot Table for aging analysis

Step

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.

Inserting a dynamic chart

  • From the drop-down menu, we select the 2D Clustered Column Chart.

Inserting a 2D column chart

  • As a result, we will get the following chart. This chart is a dynamic summary of the available data.

Dynamic chart for aging analysis


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.

Accessing New Rule option from Conditional Formatting

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

New Formatting Rule box

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

Fill Color

  • We press OK again on the Edit Formatting Rule box.

New Formatting Rule box

  • Now, all the dates that have Overdue Days with less than 30 days will be highlighted.

Excel Aging Formula for less than 30 Overdue Days

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.

(Visited 45 times, 1 visits today)
Abrar

Abrar

Hello there! I am Mohammad Abrar Uddin, an engineering graduate from BUET. I write articles and blogs related to Excel in this site and hope that the viewers can learn simple to complex solutions of Excel problems easily by reading such articles.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo