The Revenue per Full-Time Employee (FTE) Report displays the revenue generated by Full-Time Equivalent employees. The current date is retrieved when the report is created, with practices able to specify a date range once the report has been generated.
A Full-Time employee is defined as a user who works 37.5 hours, according to the Working Hours set by an FYI Admin, but can be adjusted in the Report Settings tab if required. Refer to Managing Users and the section Report Settings below.
Report Data
Report Tab
The Report tab is created leveraging the Microsoft Excel Pivot Table functionality.
The report will display the Name of all Users, identify their hours compared to a Full-Time Equivalent (FTE) Employee, and their total Revenue for the period.
The period is filtered to the specified dates calculated on Row 3 of the Report tab. When generating the report, the dates will automatically be filtered from earliest to latest dates based on the Data - Time tab.
Users will be able to customise the period by:
- Unhide Rows 4, 5, 6 of the Report tab.
- 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.
Users will be able to customise the Pivot Table as required depending on their practice requirements. For more information, refer to Microsoft Support.
Data - Users Tab
The Data tabs will display columns as per the View configured in FYI. If Include All Columns was selected, all columns available to the view will be included in the tab.
The last two columns of the Data - Users worksheet will be the following columns:
-
FTE Equivalent (Calc): this formula divides the employee's Expected Working Hours by the Full-Time Total Hours, as set on the Settings tab.
For example, a part-time staff member working 20 hours a week will have an FTE Equivalent of 0.53.
The FTE Equivalent (Calc) formula is as follows:
=IFERROR([@[Expected Working Hours]]/Settings!$B$12,0)
-
Invoiced Amount (Calc): this column contains a formula that calculates the invoiced amount from the Data - Time tab within the date range specified in the Report tab. The result of the calculation will produce the total invoiced time for each user for that period.
The Invoiced Amount (Calc) formula is as follows:
=SUMIFS(Time[Invoiced Amount],Time[User],[@Name],Time[Date],">="&Report!$B$5,Time[Date],"<="&Report!$B$6)
Data - Time tab
The Time data does not contain calculation formulas, but rather provides the data utilised by the Data - Users tab to calculate the Invoiced Amount (Calc) formulas:
- Invoiced Amount
- User
- Date
Report Settings
The Settings worksheet 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. 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.
FTE Equivalent and Total Hours
The Total Hours are based on the standard 37.5 hours for a full-time employee. If required, practices can adjust the Total Hours to reflect their own full-time hours standard.
It is recommended the FTE Equivalent cell remains set to 1 to ensure accurate calculations in the report.
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.
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. For example, the default Time view selected for the Revenue per FTE report will create the Data - Time worksheet.
By default, the Revenue per FTE report will have the following views selected:
- Time
- Users
Users can add more views if required, by selecting the Type, and a View from a dropdown.