Plan: Not Available: Intermediate, Pro Available: Elite
Users: Not Available: All users Available: FYI Admins or User Group permissions
Practice Management Source: Available: All sources
The Aged WIP as at Date report can be used to review outstanding WIP and its ageing as at a selected historical date. This allows practices to report on WIP at a previous month end or another required date, rather than only viewing the current WIP position.
The report calculates the closing WIP balance as at the selected date and allocates the balance to the relevant ageing period.
Report data
Report tab
The Report tab uses Microsoft Excel PivotTable functionality to display the closing WIP balance as at the selected date.
The selected date is displayed at the top of the report as As at (date).
WIP is grouped by client and user, with the closing balance displayed in the appropriate ageing period. Data can be expanded, collapsed and filtered using the PivotTable options.
Practices can customise the PivotTable to suit their reporting requirements. For information on arranging fields in a PivotTable, refer to Microsoft Support.
Data - Time tab
The Data - Time tab displays the time-entry data used to calculate the report, based on the view selected. The columns available depend on the View configured for the Report Template in FYI.
If Include All Columns is selected when generating the report, all available columns are included in the worksheet.
The report includes additional calculated columns used to determine the WIP balance and ageing as at the selected date.
| Column | Formula | Details |
| Opening Period (Calc) | =Report!$B$5 | The start date selected when the report is generated. |
| Closing Period (Calc) | =Report!$B$6 | The as-at date selected when the report is generated. |
| Actual Closing Balance (Calc) | =IF([@Status]="Draft",0,IF(AND([@Status]="Invoiced",[@[Invoiced Date]]>=[@[Opening Period (Calc)]],[@[Invoiced Date]]<=[@[Closing Period (Calc)]],[@Date]<=[@[Closing Period (Calc)]]),0,IF(AND(OR([@[Invoiced Date]]="",[@[Invoiced Date]]>=[@[Closing Period (Calc)]]),[@Date]<=[@[Closing Period (Calc)]]),[@[Billable Amount]],IF(AND([@Type]="Interim",[@Date]<=[@[Closing Period (Calc)]],OR([@Status]="Submitted",[@Status]="Locked")),[@[Billable Amount]],0)))) | Calculates the WIP balance that applies at the selected closing date, based on the status and invoicing information for each time entry. |
| Aging (Calc) | =IF([@[Actual Closing Balance (Calc)]]=0,"",INDEX(Aging[[#All],[Display]],MATCH(TRUE,([@Date]>=Aging[[#All],[Day Range]]),0))) | Assigns the calculated closing balance to the appropriate ageing period. Entries with no calculated closing balance aren't assigned an ageing period. |
The calculated closing balance is then included in the PivotTable on the Report tab.
Report settings
The Settings worksheet contains the configuration used by the report and a change log for tracking updates to the report template.
Variables
The Variables section contains information used in the report header. Changing these values can affect the report display and should only be done when required.
Report Name
The report name displayed at the top of the Report tab.
Practice Name
The practice name displayed below the report name. By default, the practice name from FYI is inserted but can be updated if required.
Report Date
The date associated with the generated report.
Ageing
The Ageing settings determine how the closing WIP balance is grouped based on the selected as-at date.
The default ageing periods are:
- .Current
- < 1 Month
- 1 Month
- 2 Months
- 3 Months
- Older
The .Current period uses the selected closing date from the report. Earlier periods are calculated from the end of each preceding month using the Microsoft Excel EOMONTH function.
For example, if the report is generated as at 30 June, the preceding ageing boundaries are calculated from the end of May, April, March and February.
If the ageing periods are changed, refresh the PivotTable on the Report tab to display the updated results.
Changes made to the Ageing section of an individual report apply only to that report. To apply the changes to future reports, update the Report Template. For more information, refer to Reporting Overview.
Change log
The Change Log records updates made to the report template. When FYI releases an updated version of the report, the changes are recorded in this section.
Practices can also use the Change Log to record custom changes made to their own report template.
Changes to the Change Log in an individual report apply only to that report. To include the updated Change Log in future reports, update the Report Template.
Default View
The Report Template configuration in FYI determines which Views are included in the report. Each selected View creates a corresponding data worksheet in Excel.
For the Aged WIP as at Date report, a Time View is used to create the Data - Time worksheet.
The Include All Columns option is disabled by default. When disabled, only the columns included in the selected View are added to the data worksheet. Enable this option to include all available report columns.
Practices should consider setting a Period Lock Date in FYI and a lock date in Xero to help protect finalised historical data from subsequent changes.
Updating the Aged WIP as at Date Report Template
When a new version of the Aged WIP as at Date Report Template is available, download the latest version and upload it to your practice.