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. By default, the WIP Control Report will include Locked entries i.e. time already added to an invoice.
Report Data
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 the Period defined on the Report Tab.
The period is filtered to the specified dates calculated on Row 3 of the Report tab.
Users can customise the period by:
- Update the Manual Start Date, where required.
- Update the Manual End Date, where required.
- Right-click any cell in the Pivot Table and click Refresh.
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 Period (Calc) |
Translates the Manual Start Date from the Report Tab. Refer to Report Tab. The formula is as follows: =Report!$B$5 |
Closing Period (Calc) |
Translates the Manual End Date from the Report Tab. Refer to Report Tab. The formula is as follows: =Report!$B$6 |
Opening Balance (Calc) |
First, checks the following conditions: Status, Type, Invoiced Date and Date, to decided whether to return the Billable Amount or 0. Billable Amount will return a value if certain conditions are met such as, if the Status is "Submitted" or the Invoiced Date is blank. The Billable Amount will return 0 if other conditions are met. For example, if the Status is "Draft" or the Invoiced Date is before the Opening Period. The formula is as follows: =IF([@Status]="Draft", |
Billable Amount (Calc) |
First, checks if the Invoiced Date is empty and if the Date falls within the specified period: Opening Period (Calc) to Closing Period (Calc). If the Date falls between this period, the Billable Amount is displayed. If the Invoiced date is not empty, it then checks if the Date still falls within the period mentioned above, returning the Billable Amount value. If the date is outside the range, this will return a 0. The formula is as follows: =IF([@Status]="Draft",0,IF(AND([@[Invoiced Date]]="",[@Date]>=[@[Opening Period (Calc)]],[@Date]<=[@[Closing Period (Calc)]],[@Type]<>"Interim"),[@[Billable Amount]],IF(AND([@Date]>=[@[Opening Period (Calc)]],[@Date]<=[@[Closing Period (Calc)]],[@Type]<>"Interim"),[@[Billable Amount]],0))) |
Invoiced Amount (Calc) |
Checks whether the Invoiced Date is between the defined Opening Period (Calc) date and Closing Period (Calc) date. If the Invoice Date falls between these dates, the "Invoiced Amount" will display. The formula is as follows: =IF([@[Invoiced Date]]="",0,IF(AND([@[Invoiced Date]]>[@[Opening Period (Calc)]],[@[Invoiced Date]]<=[@[Closing Period (Calc)]]),[@[Invoiced Amount]],0)) |
Interim Amount (Calc) |
Checks first if the Type is "Interim", and then if the Date is between the defined Opening Period (Calc) date and Closing Period (Calc) date. If the Type is "Interim" and the Date falls between these dates, the "Billable Rate" will display. The formula is as follows: =IF(AND([@Type]="Interim",[@Date]>[@[Opening Period (Calc)]],[@Date]<=[@[Closing Period (Calc)]]),ABS([@[Billable Rate]]),0) |
Write On/Off (Calc) |
Checks whether the Invoiced Date is between the defined Opening Period (Calc) date and Closing Period (Calc) date. If the Invoice Date falls between these dates, the "Write On/Off" amount will display. The formula is as follows: =IF([@[Invoiced Date]]="",0,IF(AND([@[Invoiced Date]]>[@[Opening Period (Calc)]],[@[Invoiced Date]]<=[@[Closing Period (Calc)]]),[@[Write On/Off]],0)) |
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)]]+[@[Billable Amount (Calc)]]-[@[Invoiced Amount (Calc)]]-[@[Interim Amount (Calc)]]+[@[Write On/Off (Calc) ]] |
Actual Closing Balance (Calc) |
Checks whether to return a 0 or a Billable amount based on a series of conditions. The Status is first checked, and if in "Draft" will display 0. If the Status is "Invoiced" with an Invoiced Date between the Opening Period (Calc) and Closing Period (Calc), and Date is within the Closing Period (Calc), 0 is displayed. If the Invoiced Date is blank or beyond the Closing Period (Calc) and the Date is within the Closing Period (Calc), the Billable Amount is displayed. Finally, if the Type is "Interim," the Date is within the Closing Period (Calc), and the Status is "Submitted" or "Locked," the Billable Amount is displayed, otherwise, it returns 0. The formula is as follows: =IF([@Status] = "Draft",0, |
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.
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.
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.