Plan: Not Available: Intermediate, Pro Available: Elite
Users: Not Available: All users Available:FYI Admins or User Group Permissions
Practice Management Source: Not Available: APS, Iris, MYOB AE & AO, CCH, MYOB Practice Management (formerly GreatSoft) Available: FYI or Xero Practice Manager
The WIP Control Report can be used to view the movements of Work In Progress (WIP) over a selected period, based on the opening and closing balance.
To support practices with large volumes of time data, the report can be generated using a filtered Time view and an optional Opening Balance setting to reduce data volume and improve report performance.
Depending on the size of your practice, the WIP Control Report can contain large amounts of data. Report generation time may vary, with reports containing up to approximately 245,000 rows and 4 million cells typically taking up to six minutes to process successfully.
If the report is unable to be generated due to excessive data, you may need to adjust the report settings when creating the report.
Report Data
Opening Balance
When creating the WIP Control Report an Opening Balance option is available in the Create Report drawer. It is recommended to enable this so that users can define a date range using standard fixed or relative date filters. For example, selecting Last Month as a relative filter.
Tip: Practices must ensure they are using the WIP Control 3.4 Master Template to ensure compatibility with Opening Balance feature.
If an Opening Balance is selected, the following time entries are included in the Data - Time tab of the report:
- Time entries within the selected period.
- Non-invoiced entries before the start date selected.
- Time entries before the start date that have been invoiced in the current period selected.
- Time entries before the start date that are invoiced after the selected end date.
- All time entries where the billable amount is not equal to 0.
Important Note: When an Opening Balance period is defined, the report uses a fixed date range and cannot be refreshed. To report on a different opening balance period, a new report must be created without an Opening Balance selected.
Report Tab
The Report tab leverages the Microsoft Excel Pivot Table functionality.
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.
The report displays the WIP and Invoice details for a practice's Clients and Jobs, based on either:
- The period selected when an Opening Balance is selected, or
- The period defined in the Report settings, when Opening Balance has not been selected.
The selected period is displayed in Row 3 of the Report tab.
Practices can customise the Pivot Table as required depending on their practice requirements. For more information, refer to Microsoft Support.
Data - Time Tab
The Data - Time 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 - Time worksheet will be the following columns:
| Calculation | Details |
| Opening Balance (Calc) |
First, checks the following conditions: Status, Type, Invoiced Date and the opening period start date, to decide whether to return the Billable Amount or 0. The Billable Amount is returned only if the entry is not in a Draft Status, and either:
In all other scenarios, the Billable Amount is returned as 0. The formula is as follows: =LET( |
| Billable Amount (Calc) |
First, checks the Type and Date of the entry agains the opening and closing period dates to decide whether to return the Billable Amount or 0. The Billable Amount is returned only if the time entry is a Time or Disbusement type and its date falls within the defined period. If the date is outside the range, this will return a 0. The formula is as follows: =LET( |
| Invoiced Amount (Calc) |
Checks whether the Invoiced Date is between the opening and closing period dates. If the Invoice Date falls between these dates, the "Invoiced Amount" will display. If note, the Invoiced Amount is returned as 0. The formula is as follows:
=LET( |
| Interim Amount (Calc) |
Checks the Type and Date of the time entry against the opening and closing period dates to decide whether to return a value or 0. The Billable Rate is displayed, as a positive value, only if the entry is an Interim type and its date falls within the defined period. If either condition is not met, the value returned is 0. The formula is as follows: =LET( |
| Write On/Off (Calc) |
Checks whether the Invoiced Date is between the defined opening and closing balance periods. If the Invoice Date is available and falls between these dates, the "Write On/Off" amount will display. If these conditions are not met, the value returned is 0. The formula is as follows: =LET( |
| Closing Balance WIP Formula (Calc) |
Calculates the closing balance by adding the Opening Balance (Calc) and Billable Amount (Calc), then subtracts the Invoiced Amount (Calc) and Interim Amount (Calc), finally adding Write On/Off (Calc). The formula is as follows: =[@[Opening Balance (Calc)]] + |
| Actual Closing Balance (Calc) |
Checks the Status, Type, Date, and Invoiced Date against the opening and closing period dates to decide whether to return the Billable Amount or 0. The Billable Amount is returned only if the entry is not in a Draft status and either falls on or before the period end date with no Invoiced Date or an Invoiced Date on or after the period end date. The Billable Amount is also returned if the entry is an Interim type, falls on or before the period end date, and has a status of Submitted or Locked. If the entry has a status of Invoiced with an Invoiced Date within the period, or none of these conditions are met, the Billable Amount is returned as 0. The formula is as follows: =LET( |
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.
| Variable | Details |
| 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. Tip: The Report Date has been defined as 'fyi_createdDate' displayed in the Name Box, for identifying in formulas. |
| Start Date |
Opening Balance Enabled Opening Balance Disabled Tip: The Start Date has been defined as fyi_StartDate displayed in the Name Box, for identifying in formulas. |
| End Date |
Opening Balance Enabled Opening Balance Disabled Tip: The End Date has been defined as 'fyi_EndDate' displayed in the Name Box, for identifying in formulas. |
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 a view 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.
Practices should consider setting a Period Lock Date in FYI and a Lock date in Xero to prevent changes to finalised data, safeguarding the accuracy of reports.
The views selected can be found by looking at the Filing section of the Report Template in FYI.
For the WIP Control Report, a Time - All Chargeable view is selected by default, and will create the Data - Time worksheet. Practices can add more views by selecting a View Type, and then selecting a View.
Note: 'Include All Columns' is disabled by default. Only columns included in the view selected will display in the Data worksheet. By ticking this option, all available columns in the report, not just those currently displayed in the View will be included when the report is generated.