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 Job Cost Profitability Custom Report is designed to calculate the profitability of jobs by comparing revenue generated against the internal cost of delivering the job. The report will analyse time recorded by users, apply an internal cost rate to that time, and compare it with the billable and invoiced amounts, enabling practices to assess profitability at a Client, Job, or User level.
Note: This report uses Cost Rates to determine Job Cost Profitability. These rates are directly related to the salary of each user and considered sensitive information. Access to this report should be restricted to appropriate users.
This article explains how to upload the custom report template to your practice, outlines the structure of the report and provides additional information on how calculations work. A custom report with live data is also provided for reference only.
The report is a custom report and can be downloaded and imported into your practice. To download the report, refer to Managing Report Templates.
Before you Begin
Step 1 - Configure the Default View
A Practice Time must be created to populate data in the Data - Time worksheet of the report.
The view should:
- Be filtered to Submitted and Invoiced time entries.
- Include an appropriate date filter for the reporting period.
- Contain Time, Billable Amount, Invoiced Amount and Write On/Off columns.
For information on creating a Time view, refer to Practice Time.
Step 2 - Upload the Custom Report Template
The Custom Report template can be downloaded from the Download Files section of Managing Report Templates article.
Once the template is uploaded to FYI, make sure that the following is applied:
- Configure the View from Step 1.
- Add users' Cost Rates to the Cost Rate tab.
Template Layout and Data
Report tab
The Report tab is created leveraging the Microsoft Excel Pivot Table functionality. The report will be grouped by Client, Job and User, displaying the following totals:
- Time
- Billable Amount
- Invoiced Amount
- Cost Amount
- Profit Amount
- Write On/Off
- Net Profit Amount
Users will be able to customise the Pivot Table as required, depending on their practice requirements. For more information, refer to Microsoft Support.
Cost Rate tab
The cost Rate worksheet contains the internal Cost Rate for each user. These rates must be entered manually, and is recommended that they are added to the report template.
Important Note: Cost rates are based on each user's salary and should be treated as confidential information.
The Charge Rate will come from the Time view.
Complete the following fields on the Cost Rate worksheet:
| Column Name | Details |
| User Name | Enter the User name as it appears in FYI. |
| Start | The date the user began at your practice. |
| End | Enter the termination date of a user, where applicable. If the user is still employed, this can be left blank. |
| Cost rate | Enter the cost rate of the user. |
Data - Time tab
The Data - Time tab will display columns as per the View configured in FYI. Refer to Step 1 - Configure the Default View.
If Include All Columns was selected when generating the report, all columns available to the view will be included in the tab.
The last four columns of the Data - Time worksheet, highlighted in yellow, are the following calculation columns:
| Column | Formula | Details |
| Cost Rate (Calc) | =IFERROR( INDEX('Cost Rate'!D:D, MATCH(1, ('Cost Rate'!A:A=[@User]) * ('Cost Rate'!B:B<=[@Date]) * (('Cost Rate'!C:C>=[@Date]) + ('Cost Rate'!C:C="")), 0) ), "") |
This formula looks up the Cost Rate for the current row by finding the matching User and the rate period that covers the row’s Date.
The formula will return the corresponding value from the Cost Rate column. If no match is found, it will return a 0 in the column. |
| Cost Value (Calc) | =IFERROR([@Time]*[@[Cost Rate (Calc)]],0) | This formula calculates the Cost Value by multiplying the amount of Time by the calculated Cost Rate. If either value is missing or invalid, a 0 value is returned in the column. |
| Profit Value (Calc) | =IFERROR([@[Billable Amount]]-[@[Cost Value (Calc)]],0) | This formula calculates the Profit Value by subtracting the Cost Value from the Billable Amount. If either value is missing or invalid, a 0 value is returned in the column. |
| Net Profit (Calc) | =IFERROR([@[Profit Value (Calc)]]+[@[Write On/Off]],0) |
This formula calculates the Net Profit by adding any Write On/Off adjustment to the Profit Value. Note: A write-on increases profit, while a write-off reduces it. If either value is missing or invalid, a 0 value is returned in the column. |
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.
Tip: When changes are made to the report, users should enter details in the Change Log for historical reference.