The Job Profitability Report evaluates each Job's performance by comparing the Job Budget values with Actual Values to determine their profitability. This is tracked against your estimated Job Completion Percentage for each Job state.
Report Data
Report Tab
The Report tab is created leveraging the Microsoft Excel Pivot Table functionality. The report will be grouped by State, and display Client, Name, Budget, Actual, Variance, Expected % and Actual %.
Users will be able to customise the Pivot Table as required depending on their practice requirements. For more information, refer to Microsoft Support.
Data - Jobs Tab
The Data - Jobs 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 two columns of the Data - Jobs worksheet, highlighted in yellow, are the following columns:
-
Expected % (Calc): this formula looks at the State of the relevant Job, and returns the expected completion percentage based on the Report Settings. Note that if a match cannot be found, it will return a 0 in the column.
The Expected % (Calc) formula is as follows:
=IFERROR(VLOOKUP([@State],JobState,2,FALSE),0)
-
Actual % (Calc): this column contains a formula that will divide the Actual Amount (the total Time and Disbursements, irrespective of interims) by the Budget Amount to produce a percentage value. Note that if the values cannot be found, it will return a 0 in the column.
The Actual % (Calc) formula is as follows:
=IFERROR([@[Actual Amount]]/[@[Budget Amount]],0)
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.
Budget Completion by Job State
The Settings contain both Job State and Percentage Completed information, which is used to calculate the expected Job Completion percentage in the Report tab.
The Report template will have the following default Job State and % Completed information:
| Job State | % Completed |
| Planned | 0% |
| Pending Client Info | 15% |
| In Progress | 50% |
| On Hold | 50% |
| At Review | 70% |
| Ready To Send | 95% |
| Completed | 100% |
A practice should update this table by including the relevant Job States and their associated expected completion percentages.
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.
The views selected can be found by looking at the Filing section of the Report Template in FYI.
For the Job Profitability Report, a Job view is selected by default, and will create the Data - Jobs worksheet. Practices can add more views if required, by selecting a View Type, then selecting a View.