The Aged Receivables Report can be used to highlight overdue invoices based on the age of time entries, to ensure a practice can easily identify debtors that need to be contacted.
Report Data
Report Tab
The Report tab is created leveraging the Microsoft Excel Pivot Table functionality.
The Status of the Invoice will be filtered to Awaiting Payment only, and display Invoice Amounts by Client, Job, and Date. The total Invoice Amount will be displayed in the appropriate Ageing period.
Data is grouped by Client, which can be expanded and collapsed as required by clicking the + or - icon. Columns can be filtered by clicking the Down arrows next to column headings, for example, to filter to a specific job only.
Practices can customise the Pivot Table as required depending on their practice requirements. For more information, refer to Microsoft Support.
Data - Invoices Tab
The Data - Invoices tab will display columns as per the View configured in FYI. Refer to Default View below.
If Include All Columns was selected when generating the report, all columns available to the view will be included in the tab.
The last columns of the Data - Invoices worksheet will be the following columns, highlighted in yellow:
-
Invoice Date Ageing (Calc): The formula in this column looks at the Settings page to obtain the Ageing periods, then calculates the appropriate age of each invoice based on the Invoice Date. This formula will be repeated for each row in the worksheet.
The Ageing (Calc) formula is as follows:
- =IF([@[Invoice Date]]="", "",
INDEX(Age[[#All],[Display]],MATCH(TRUE, ([@[Invoice Date]]>=Age[[#All],[Day Range]]), 0)))
- =IF([@[Invoice Date]]="", "",
-
Due Date Ageing (Calc): The formula in this column looks at the Settings page to obtain the Ageing periods, then calculates the appropriate age of each invoice based on the Invoice Due Date. This formula will be repeated for each row in the worksheet.
The Due Date Ageing (Calc) formula is as follows:
- =IF([@[Due Date]]="", "",
INDEX(Age[[#All],[Display]],MATCH(TRUE, ([@[Due Date]]>=Age[[#All],[Day Range]]), 0)))
- =IF([@[Due Date]]="", "",
Based on the calculated Ageing, the value of the Invoice will be included in the total for the matching client and Ageing period on the Report tab.
Report Settings
The Settings worksheet in Excel is used to manage the configuration options for the report, and a Change Log to track updates made to the report.
Variables
The Variables contain data that is used in the header section of the report. Making changes to the Variables can affect the accuracy and layout of your report, and should only be modified by experienced users.
Report Name
The Report Name is displayed on the Report tab, in the top left corner.
Practice Name
The practice name configured on the Settings tab will be displayed on the Report tab under the Report Name. By default, the practice's name from FYI will be inserted, but can be updated if required.
Report Date
Displayed on the Report tab under the Report Name and Practice Name, and is the date the report was run. If the report data is refreshed on a later date, this can be updated to show the newer date.
Ageing
The Ageing settings contain formulas that automatically calculate the age of items based on the current date, categorising them into predefined periods (e.g., 1 month, 2 months). These formulas can be adjusted to accommodate different ageing breakdowns if required.
- The .Current field uses the =TODAY() formula to calculate the current date.
- For all months prior to the current date, the fields are calculated using the =EDATE () Excel formula, which calculates a date that is a specified number of months before or after a given start date.
For example, to find WIP entries created less than a month ago, the formula is =EDATE($B$12,-1) (where -1 is 1 month before the current date). $B$12 refers to the current date located in the cell B12. The formula for ageing of WIP entries older than one month, but less than 2 months, would be =EDATE($B$12,-2).
To add an additional ageing period, for example, 4 months, add a row before "Older" and insert the formula =EDATE($B$12,-5).
Note: After making changes to the Ageing section, the Report Pivot Table will need to be refreshed to see the updates.
When making changes to the Ageing periods, the changes will apply to this report only. To include new Ageing periods for all new Aged WIP reports, the changes must be made to the Template file itself, and not individual reports. Refer to Reporting Overview.
Change Log
The Change Log displays a record of all modifications to the template. When FYI releases new versions of reports, a summary of the changes will be added.
Practices can also use the Change Log to document any custom changes they make to their own reports. For example, if a practice added an extra ageing period of 4 months to a Template, the Change Log should be updated to reflect the new change.
Note: Updating the Change Log on an individual report will apply to this report only. To display an updated Change Log for each new report, the changes should be made to the Template file itself.
Default View
The Report Template configuration in FYI allows users to select the Views to be included in the report. Each selected view in FYI will generate a corresponding "Data" tab in the Excel report.
The views selected can be found by looking at the Filing section of the Report Template in FYI.
For the Aged Receivables Report, an Invoices view is selected by default, and will create the Data - Invoices worksheet. Practices can add more views if required, by selecting a View Type, then selecting a View.