Margin Reporting

May Release: National Minimum Wage - If you are running the default margin report, or have included the "Min Wage(A)" column on the columns tab, you have a column in your results called NMW. This details the national minimum wage for that worker based on their date of birth. This is only displayed when outputting by Shift or Rate.

Margin reporting is an important part of the RSM InTime system, because it allows you to view the difference between your costs and charges (and hence see how profitable your agency is).

Because agencies may differ from one another in the way that they operate, RSM InTime provides you with two ways of running the margin report:

• By period end date
  This method allows you to retrieve the margin or invoiced margin information that relates to timesheets that have an end date that falls within a specified period. For more information, see Reporting by period end date.
• By invoicing date
  This method allows you to retrieve the invoiced margin information that relates to invoices that are dated within the specified date range. For more information, see Reporting by invoicing date.

How do I view and interpret a Margin Report?

1. On the menu bar, select Reports > Margin Report.
   The Margin Report page is displayed:
   
   
   
   
2. On the Main Filters tab, set the filters as required.
   Note:
   1. Advice notes and supplier invoices both contain an invoice amount.  Although the amount should be the same on both, it can vary as a result of rounding errors and the fact that advice notes don’t contain VAT (whilst supplier invoices can).
      It is also possible that an advice note might have been generated for a timesheet, although you have not yet received the supplier invoice (or the supplier invoice might not have been accepted yet).
      For these reasons, you can select either Show Invoiced Pay from Supplier Invoices or Show Invoiced Pay from Advice Notes to specify whether to use the invoice value or the advice note value.
   2. You can select Include Invoice Data for Reverted Timesheets to specify that you want the margin report to include invoice information that relates to reverted timesheets.

   3. By default, the margin report contains information about actuals (revenue that is on its way as a result of timesheets that have actually been approved). In addition to the actuals, you can also choose to take into account timesheets that are missing or are yet to be approved ('missing' timesheets). To do this, select Missing from the Status drop-down list.

3. Use the Date Ranges tab to specify whether you want to run the margin report by period end date or invoicing date.
4. The Grouping tab allows you to specify how you would like the information that’s in the report to be arranged. For example, you could opt to group the data by the Commission Consultant column, so that all the rows that relate to a particular consultant appear together in the report.
5. A margin report contains a lot of columns, so you can use the Columns tab to specify the columns that you want to include in your report. You can then use the Saved Reports tab to save the configuration so that you can access the report again later.
6. Click Gross Margin.
   The margin report is displayed:
   
   
   
   
7. Click Print Report or Print Detail Report if you want to generate and print a hard copy of the report, or click CSV if you want to export the report to a file that contains comma-separated values that you could then import into a spreadsheet.
   
   

A margin report looks similar to the results that are returned when you use RSM InTime to search for timesheets, but there are extra columns that list the sales invoices and purchase invoices that relate to the timesheets. If you click the link in one of these columns, the relevant invoice will be displayed in a separate window.

The figures in the Invoiced Margin column tell you how much profit has actually been made against each timesheet. The report's rows are colour-coded so that you can easily tell which timesheets have the most and least margin associated with them. Our support team can configure, at agency level, the default percentage colour-coding. Standard system defaults is as follows:

Margin reports also include items such as PAYE charges, pension deductions and Employer's National Insurance (on the purchase side, margin reports therefore effectively include PAYE costs).

Notes:

• Employer's National Insurance (ERNI)
  Employer's NI is not shown until a timesheet has been exported. If you are linked to InPay, then the Employer's NI values will be accurate. If you are not linked to InPay, then the Employer's NI is estimated. Please note that the estimate is based on each timesheet in isolation. Therefore, if a worker has two timesheets on different placements in a single week the estimate that is shown will be made by using the secondary earnings threshold on both timesheets.
  
  
• Holiday
  For PAYE workers the margin report will show the estimated cost of holiday that has been accrued for all shifts where the corresponding rate is marked as "Accrues Holiday" on the Pay Elements page. Holiday is accrued at the rate that has been configured for your agency or at the rate set on the worker or placement. The current accrual rate is stored on the Timesheet at the time of submission so that if the rate is changed it remains accurate for previous periods. Please note that this figure is an estimation of holiday cost rather than an actual accounting figure which would need to be derived from your payroll system. It does not take account of adjustments made in your payroll system.
  Standard practice is to set the Holiday Pay Element to be excluded from the margin report (and not to accrue holiday), since accrued holiday will have been accounted for in previous periods.
  
  
• Missing timesheets
  If you filtered on Missing status, the report shows expected timesheets for the date range selected. Any existing timesheets that have not been approved are also included in the results. The Units, Pay, Charge and Margin columns for missing timesheets show predicted values.
  
  
• National Minimum Wage - If you are running the default margin report, or have included the "Min Wage(A)" column on the columns tab, you have a column in your results called NMW. This details the national minimum wage for that worker based on their date of birth. This is only displayed when outputting by Shift or Rate.

Example

Suppose that a worker who is subject to PAYE submits the following weekly timesheet:

The margin report for this timesheet would show:

Reporting by period end date

To produce a margin report by period end date

1. On the menu bar, select Timesheets>Margin.
   The Margin Report page is displayed.
2. On the Main Filters tab, set the filters as required.
3. On the Date Ranges tab, enter a date range into the fields labelled Timesheet End / Expense Receipt...Date Range.
   RSM InTime will retrieve the margin information for any timesheet that falls within that period.

On the report you will see two different margin figures:

• Margin
  This is the amount of margin that can be realised if the corresponding timesheet is both billed and paid. This would typically be the case as ultimately you will bill and charge for all work done unless, for example, your client refuses to pay for the work but you still have to pay the contractor.
• Invoiced Margin
  This is the margin that has actually been realised by the process of raising invoices or paying workers. If you have paid the worker but not billed the client, then the invoiced margin will be negative as you will have incurred the cost but not received any payment against that cost. Similarly, if you have billed the client but not paid the worker, then the invoiced margin will be very high as you will have billed for the work but not yet paid for it.

Here is an example of the figures that might be shown when a margin report that is based on period end date is generated, and two date ranges are used as the filtering criteria:

Here are the figures that would be shown if the margin report were generated using a filter that consists of the same dates expressed as a single date range:

This shows that when a margin report that is based on period end date is generated, the totals for both invoiced margin and margin match whether one whole date range or two sub-ranges are specified in the filtering criteria.

The difference between the margin and invoiced margin figures in either case will be due to the fact that some items haven’t been fully invoiced on both sides.

Reporting by invoicing date

Margin reporting can be driven from the invoicing side, rather than timesheets. This allows you to produce a margin report that is based upon the date range when items were invoiced.

To produce a margin report by invoicing date

1. On the menu bar, select Timesheets>Margin.
   The Margin Report page is displayed.
2. On the Main Filters tab, set the filters as required.
3. On the Date Ranges tab, select the check boxes that correspond to the types of invoice that you want to report on, and then select the date range in the Invoice Date Range / PAYE Export Date Range fields below.
   RSM InTime will retrieve the list of items that have had one or more invoices of any sort produced during the specified period, or that have been exported during that period.

Producing margin reports that are based on invoicing date allows you to focus on financial activity during the period. When looking at these reports, you would normally pay attention to the invoiced margin rather than the standard margin figure. This is because you are looking at financial movements.

Example 1

Suppose that you produce a sales invoice for a timesheet for April, and then you credit it in May. The charge for the timesheet is £300, and the pay is £100. The timesheet is paid in April. You then run margin reports using the invoiced date range. The results would be:

• April: The timesheet will appear in the margin report since there is one or more financial transactions in that period. The margin will be £200 and the invoiced margin will be £200.
• May: The timesheet will also appear in this period as there is a financial transaction associated with it. The margin will be £200. The invoiced margin will be -£300 since it has been credited in this period.

This example shows that if you produce a margin report based upon invoice date range, you must use the invoiced margin figures.

Example 2

Here is an example of the figures that might be shown when a margin report that is based on invoicing date is generated, and two date ranges are used as the filtering criteria:

Here are the figures that would be shown if the margin report were generated using a filter that consists of the same dates expressed as a single date range:

This shows that when a margin report that is based on invoice date range is generated, the invoice margin matches whether one whole date range or two sub-ranges are specified in the filtering criteria. The margin is however different.

This is because some items may have invoices in both periods, and hence the timesheet appears in both of the sub ranges (whereas it would appear once in the overall report). Once again, this demonstrates that if you produce a margin report based upon invoice date range, you must use the invoiced margin figures.

Can commission be shown in the report?

If split commission has been set up for the relevant placement (you can set this on the Financials tab of the Edit Placement page), you can generate a report that shows commission:

1. On the menu bar, select Reports > Margin Report.
2. On the Main Filters tab, set the filters as required.
3. Click Split Commission.
   A report that includes commission information is displayed. For each timesheet that is included in the report, there are now rows for each consultant who will receive a proportion of the commission:
   
   

The split commission report is configured with four colour bands. Our support team can configure, at agency level, the default percentage colour-coding. Standard system defaults is as follows::

Can I use the margin reporting for forecasting?

Yes, the report can provide you with a prediction for your margin over the next 12 months.

For information about predicted margin to become available, you must ensure that contracted hours or rotations have been set up for your placements. Where applicable, exchange rates must also exist between the charge and pay currencies and the base currency. To set up contracted hours for your placements, you can use the /wiki/spaces/ds/pages/51871937 screen.

Note:

• If a worker is on a fixed or daily rate, then you must ensure that the figure that's in the Contracted Hours Per Week field (on the Edit Placement screen) is consistent with the figure that's in the Duration (minutes) field for the rate. For example, if the Duration field contains the value 480 (indicating that the worker is contracted to work 8 hours in a day), then, for a 5-day week, the value in the  Contracted Hours Per Week field should be 40. Note that the forecast cannot take into account bank holidays and sickness. It assumes that workers are working their contracted hours for the whole period that you select.
• If a placement's End Date is the same as the Start Date it is treated as a one-off charge placement, and the rate is multiplied by one.

To view predicted margin

1. On the menu bar, select Reports > Margin Report.
   The Margin Report page is displayed.

2. On the Grouping tab:

   1. Select one more groupings in the Group By list.

   2. Select Show Charge By Month. This means that the data will be grouped into a monthly block, according to the groupings that you selected in the Group By list.

   3. Select Include Forecast.

3. Click Gross Margin.
   The margin report appears, showing predicted margin:
   
   
   
   The report is a simplified margin report; all you get is the total invoice margin figure.
   

All the information up to the current time is real, actual invoice data. Information for future months is predicted data. Predicted margin indicates what you would expect to pay and charge for a timesheet, based on the default rate for a placement, and the contracted hours for that placement.

Margin is calculated on the last day of each timesheet period and this date determines which month the margin is included in. For example: The full margin for a timesheet ending on the 2nd January will be included in January even though there are 5 days in December. This applies whether a rotation is present or not.

How is predicted margin calculated?

Predicted margins are calculated as follows:

• ActiveWeeks = the number of days the placement overlaps the timesheet period / 7
• Units = (ContractedHours x ActiveWeeks) / (Duration of default rate / 60)
• Predicted Pay = Units x Pay in Base (from default rate)
• Predicted Charge = Units x Charge in Base (from default rate)
• Predicted Margin = Predicted Charge - Predicted Pay

Note:

If a placement has a rotation the Units figure in this calculation will be the number of selected working days in the period.

Example 1

• Weekly timesheet
• Hourly rate
• Pay: £20
• Charge: £30
• Contracted hours: 40

The calculation:

Active weeks = 7 / 7 = 1

Units = (40 x 1) / (60 / 60) = 40

Predicted pay = 40 x 20 = £800

Predicted charge = 40 x 30 = £1200

Predicted margin = 1200 - 800 = £400

Example 2

• Weekly timesheet
• Hourly rate
• Pay: £20
• Charge: £30
• Contracted hours: 40
• Placement ends 3 days into the week.

The calculation:

Active weeks = 3 / 7 = 0.428571429

Units = (40 x 0.428571429) / (60 / 60) = 17.142857143

Predicted pay = 17.142857143 x 20 = £342.86

Predicted charge = 17.142857143 x 30 = £514.29

Predicted margin = 514.29 - 342.86 = £171.43

Example 3

• 31-day monthly timesheet
• Daily rate
• Duration: 480
• Pay: £100
• Charge: £150
• Contracted hours: 40

The calculation:

Active weeks = 31 / 7 = 4.428571429

Units = (40 x 4.428571429) / (480 / 60) = 22.142857143

Predicted pay = 22.142857143 x 100 = £2214.29

Predicted charge = 22.142857143 x 150 = £3321.43

Predicted margin = 3321.43 - 2214.29 = £1107.14

How Do I Export Timesheets and Expenses From The Margin Report?

If you set the Status filter to Approved, the Export button will be displayed. Clicking the export button will export all timesheets. If you wish to export a subset of timesheets/expenses, you will need to include the "Select" column on the columns tab and select the appropriate sheets. If you are using the default margin report, this will already be included.

Giving timesheets/expenses a payroll reference

It is possible to group a range of timesheets/expenses into a batch by assigning them a payroll reference.

For an admin to enter a Payroll Reference onto the system they will need the Timesheet Write All role. 

Invoiced Filters

It is possible to invoice or write off individual shifts within a timesheet, so they are not all necessarily in the same invoiced state. Also, if an adjustment shift is added that only affects the purchase or sales side, the other is automatically written off so that is not invoiced.

When selecting output by timesheet along with invoice filters, if a timesheet includes shifts that are invoiced it will show, even if there are shifts that are not invoiced. Because the state of the shifts can vary within a timesheet when outputting by timesheet the results must be a generalisation of the shifts in each sheet.

The following rules are applied:

- Timesheets are shown as Written off if any shifts are written off
- Timesheets are shown as Invoiced if any shifts are invoiced or not payable/chargeable
- Timesheets are shown as Not invoiced if any shifts are not invoiced and not written off

When filtering timesheets and outputting by Shift the individual shifts in the selected status are returned.

When filtering timesheets and outputting by Rate the results are a generalisation of the shifts on each rate.
The following rules are applied:
- Rows are shown as Written off if any shifts are written off
- Rows are shown as Invoiced if any shifts are invoiced or not payable/chargeable
- Rows are shown as Not invoiced if any shifts are not invoiced and not written off

Expenses are filtered based on the Invoiced status of each expense item.

Items for PAYE workers cannot be written off on the purchase side and are normally exported rather than invoiced.
Selecting a Purchase Invoiced status filter will exclude all items that are for PAYE workers from the results unless they were a LTD worker at the time the item was processed.

Items that have been added to an Advice Note but a Supplier Invoice has not been uploaded will be shown for the 'Not Invoiced' option of the Pay Invoiced filter.
When a Supplier Invoice has been uploaded to the Advice Note items will be shown for the Invoiced option even if the supplier invoice has not yet been accepted.

Saved reports 

Saved reports allow you the flexibility to build a suite of reports with specific fields that you require. Go to reports in the top menu header, and then margin report. Go to the columns tab, in the left-hand box click on the fields you want to see in your report, and using the arrow put them in the right-hand box. You can highlight multiple headers at the same time and move them over. If you want to change the order they appear in the margin report, use the up and down arrows below the righthand box.

Once you have selected all the fields you require, go to the saved reports tab, name your report, select if you wish other users to see your report, and click save report. 

How to use a saved report

Select the date ranges and filters you wish to report on, go to saved reports click on name of report you wish to run, then hit gross margin or split commission report depending on what you want. It will run the report for you.

If you wish to delete a report at any time click on the red cross. Please note: you can only delete a report you have created. 

You can also group your reports. It’s exactly the same principle as the columns just pick the group that you want and you can save this as part of the report. Alternatively if you wish to use the report for various groupings, simply select your group each time you run the report. 

Frequently Asked Questions

In margin reports, if we have credited and re-invoiced a sales invoice or self-bill invoice, can the original invoice and credit note appear on the report? 

This will depend on the criteria on the margin report.  If you are raising credits/re-invoicing via the invoice screens using the ‘Credit’ option, we would advise to run a margin report by invoice date range and include invoices and credits.

Related topics

• Maintaining Placements