The Average Hourly Rate Report calculates the average hourly rated based on the time invoiced to the client. It helps to understand a user's performance, and can be filtered to a job, team, and overall practice level.
The report displays a summary of a staff member's Average Billable Rate, the total amount of time worked, and the overall Average Hourly Rate per user.
Report Data
Report Tab
The Report tab leverages the Microsoft Excel Pivot Table functionality.
The User rows will be expanded to display details of the Clients, Jobs, Date, Average Billable Rate, Time Total and Average Hourly Rate.
The Average Billable Rate is calculated using pivot table functionality to determine the average hourly rate for each relevant entry and calculate the average of these rates.
Users will be able to customise the Pivot Table as required depending on their practice requirements. For more information, refer to Microsoft Support.
Data - Time Tab
The Data 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 column of the Data - Time worksheet will be the Average Hourly Rate (Calc) column, highlighted in yellow.
This column contains a formula to calculate the average hourly rate for each user. The formula will read the data sheet and check first if the Type is "Time" and if the Status is either "Locked" or "Invoiced". If both of these conditions are met, the formula will divide the "Invoiced Amount" by the "Time" to calculate the Average Hourly Rate.
Note: Cells that don't match the formula requirements will be left empty i.e. not a Time entry, with a Status other than Locked or Invoiced.
The Average Hourly Rate (Calc) formula is as follows:
- =IF(AND(OR([@Status]="Locked",[@Status]="Invoiced"),([@Type]="Time")), [@[Invoiced Amount]]/[@Time],"")
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, 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.
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 Average Hourly Rate report, a Time view will be selected by default, and will create the Data - Time worksheet in the Excel Report.
Practices can add more views if required, by selecting a View Type, then selecting a View.