Aging of Accounts Receivable [Excel model]

In order to calculate the Allowance for Doubtful Accounts and the monthly Bad Debt Expense, companies should regularly analyze the Aging of Accounts Receivable.

A detailed article about this accounting topic can be read at this Accounting Coach post.

In order to ease the analysis process, i created a simple Excel model with some formulas and a few lines of VBA code which can be applied/modified as required. The spreadsheet contains three sheets:

1) Collecting_probability: here we input the percentages of estimated collectable amounts. These are calculated based on the past experience.

2) Data: The list with unpaid invoices and some calculated columns.

• In the columns with blue header we input the data from the financial system (Client, Invoice no., Amount, Issue Date). In my example, the columns with yellow header are calculated with formulas, but it might not be applied in all cases. (for example, if the Due Date is different for different clients and we have it in the system, we will not have the formula “Issue Date + no. of days”.
• In the Category column we have the following formula, which writes the category of the invoice based on the number of overdue days: =IF((TODAY()-F3)<=0,”Current”,IF(AND((TODAY()-F3)>0,(TODAY()-F3)<=30),”1-30 days”,IF(AND((TODAY()-F3)>30,(TODAY()-F3)<=60),”30-60 days”,”+ 60 days”)))
• The Expected Uncolectable Amount and Expected Collectable Amouts are calculated based on the percentages from Collecting_probability worksheet.

3) Report: here we have a “special” pivot table which summarizes the data from “Data_base” and presents it in a friendly way. Why it is a special Pivot Table ? Because it has a Custom VBA filter, a Dynamic Data range and it is automatilcally refreshed when the sheet is activated.

• The formula for the Dynamic Named Range is the following: =OFFSET(Data!$B$2,0,0,COUNTA(Data!$B:$B)-1,8) and it is set in “Formulas” –> “Name Manager” and the input as Pivot Table Source Range.
• The VBA code for Pivot Table refresh and Custom VBA filter:

Public OldVal

Private Sub Worksheet_Activate()

Set ws = ActiveWorkbook.Sheets("Report")

For Each pt In ws.PivotTables

pt.RefreshTable

Next pt

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
OldVal = Target
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Target.Worksheet.Range("J3")) Is Nothing Then

ActiveSheet.Range("L3") = OldVal


ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Sum of " & OldVal).Orientation = xlHidden

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields(ActiveSheet.Range("J3").Text), "Sum of " & ActiveSheet.Range("J3").Text, xlSum

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of " & ActiveSheet.Range("J3").Text)
.NumberFormat = "#,##0"
End With


ActiveSheet.Range("I3").Select
End If


End Sub

Using an Excel model similar to the one presented above can streamline the Aging of Accounts Receivable analysis process.

Download the Sample file:

Aging of Accounts Receivable model