Creating an ageing report

In Finance various aged debtor and creditor reports are produced with different ageing buckets.

In Excel it is possible to create a report with custom ageing buckets.

Source data

In this example the source data needs to contain a date, an identifier and an amount.

The date is used to determine which bucket (column) to put the amount in.

Deciding on Ageing buckets

Enter headings into column D.
Cell D3 as Age from date.
Cell D4 as Days.

Enter a date to age from in cell E3.

In row 4, starting with column E enter a number of days that represent the maximum value for each bucket.

The last value should be a very high value to capture all items whose number of days outstanding is greater than the last ageing bucket. It is set so that the same formula can be used for all ageing buckets except the first.

In the example we are using 30 day buckets (0,30,60,90,120)

Add heading formulas

In cell D6 enter a heading of Days Outstanding.

The other column headings in Row 6 are going to be created by formula. This is so the headings automatically updated when the aging buckets are changed.

Cell E6 would have a formula of =CONCATENATE("<",E4+1)
Cell F6 would have a formula of =CONCATENATE(E4+1,"-",F4)
Copy this formula to all the other heading cells except the last.
Cell J6 would have a formula of =CONCATENATE(I4+1,"+")

Apply your preferred formatting to the headings.

Calculate Outstanding days

In cell D7 enter the following formula =$E$3-A7
This shows the number of days that an item has been outstanding for.

First ageing bucket

We could base the ageing on the outstanding days that we calculated in the previous step. In this example we are going to compare the dates each time instead.

In Cell E7 enter the following formula =IF($E$3-$A7<=E$4,$C7,0)

Second ageing bucket

In Cell F7 enter the following formula =IF(AND($E$3-$A7>E$4,$E$3-$A7<=F$4),$C7,0)

Copy this formula across the other ageing columns.

Results

Highlight the cells that contain formulas (D7:J7) and copy them down along side the data.

Hiding zeros

All the zero values are distracting and can be hidden by changing the formatting.

On the Home tab menu in the Number format section, use the drop down to select More number formats.

Select Custom in the Category box and then choose the -* #,##0-;-* #,##0_-;-* “-“-;-@- type.

This will display all the zero values as a dash and not show any decimals.

Highlighting numbers

The aged values can be highlighted to make it easier to see any patterns.

Highlight your data area E7:J13, making sure that cell E7 is active. On the Home tab menu click on the Conditional Formatting icon and the New Rule option.

Select a rule type of Use a formula to determine which cells to format
Type in a formula of E7<>0
Click on the Format button, select the Fill tab and choose Yellow as a background colour.

Click on the OK button twice.

Results

The ageing report is complete. Add any headings and totals that you require.

It is now easy to change the aging buckets by changing the values in row 4. See the screenshots showing a change of the buckets from (0,30,60,90,120) to (0,45,90,135,180).

New data can be copied into the report (Column A:C) and the formulas (Column D:J) copied down along side the data.