This article explains the formulas used by the system to calculate the pro rata amount of an employee's gross earnings. A time saving new column in the Pay Categories Report shows you if the Pay Category has been excluded from AWE or not. This is applicable/relevant in the following scenarios:
- calculating the 8% holiday pay amount in the Leave Liability report;
- calculating an employee's payroll history earnings or annual earnings component when determining average daily pay;
- calculating an employee's payroll history earnings or annual earnings component when determining average weekly earnings;
- calculating an employee's historic gross earnings or gross earnings component when determining the 8% holiday pay on termination.
There will only be one pay period where an employee's earnings will need to be pro rata'd. This is the first pay period that incorporates the first of the 52 weeks and will be referred to as the "affected pay period" herein.
Important note: any reference to "gross earnings" in this article excludes any earnings that are not included for the purposes of determining average wage earnings (AWE). Any earnings associated to pay categories that have the "Exclude From Average Weekly Earnings" checkbox ticked are excluded from gross earnings.
The following topics are explain in this article:
- Determining pro rata days worked in affected pay period
- Pro rata historic gross earnings
- Pro rata pay run gross earnings
Determining pro rata days worked in affected pay period
The number of pro rata days worked in the affected pay period is a crucial component of determining pro rata earnings. How this is determined depends on whether the employee has advanced work hours or basic work hours recorded in their Pay Run Defaults record.
If advanced work hours are recorded, it allows the system to know exactly how many days per week and the actual days of the week the employee is working. If basic work hours are recorded then the system uses the assumption that the employees works 5 days per week, Monday to Friday.
How do we pro rata historic gross earnings?
The historic gross earnings function should be used where:
- an existing business has transitioned over to this payroll system; and
- existing employee gross earnings (up to 52 week's worth) need to be added to this payroll system to correctly calculate AWP and/or ADP.
The historic gross earnings export includes columns where the number of days worked by each employee should be entered for each applicable pay period.
The pro rata formula for historic gross earnings varies depending on what data the system has for days worked. The different scenarios are explained below.
Scenario 1: Number of days worked per pay period has been entered in the employee's historic gross earnings
The basis of the pro-rata formula here is to apply a percentage of days worked vs days in the affected pay period. This is because the system doesn't know what specific days were worked and so matching the entered number of days worked with advanced or basic work hours isn't feasible.
So, the formula applied for the affected pay period in this scenario is as follows:
- number of days worked in affected pay period / number of total days in affected pay period = % of days worked in affected pay period, then
- total gross earnings for the affected pay period / number of days worked in affected pay period = gross earnings per day worked, then
- total the number of days between pro rata start date (inclusive) and end date of affected pay period (inclusive) = number of pro rata days, then
- number of pro rata days x % of days worked in affected pay period = pro rata days in affected pay period, then
- pro rata days in affected pay period x gross earnings per day worked = pro rata earnings.
Let's apply the formula using an example. An employee is taking bereavement leave and in order to determine ADP, we need to take into account the employee's historic gross earnings to make up the 52 week's earnings. The employee took bereavement leave in the fortnight pay period commencing 6 January 2020, so the pro rata commencement date is 6 January 2019. This date falls in Period 3 of the employee's historic gross earnings:
- number of days worked in affected pay period = 5 days (as displayed in historic gross earnings export)
- number of total days in affected pay period = 14 days (fortnightly pay period)
- total gross earnings for the affected pay period =1451.03 (as displayed in historic gross earnings export)
- total the number of days between pro rata start date (inclusive) and end date of affected pay period (inclusive) = 1 day. This is derived from the fact the pro rata start date is 6/1/19 and so that is counted as 1 day. (If, for eg, the pro rata start date was 5/1/19 then the total number of days worked between pro rata start date and end date of affected pay period will be 2 days.
So:
- 5 days / 14 days = 0.357 = 35.7% of days worked in affected pay period
- $1451.03 / 5 days = $290.21 gross earnings per day worked
- 1 day x 35.7% = 0.357 pro rata days in affected pay period
- 0.357 days x $290.21 = $103.60 pro rata earnings.
The $103.60 is then added to the remaining gross earnings (calculated from adding all gross earnings from Period 4 onwards from the historic gross earnings export and then any pay run gross earnings up to pay period ending 5 January 2020).
Scenario 2: Number of days worked per pay period has NOT been entered in the employee's historic gross earnings
Where no work days have been entered, indicated by the value '0' in the "Period x Days" column of the affected pay period, the system will look at the employee's standard work week (advanced or basic) to determine the number of pro rata days worked in the affected pay period.
The formula applied for the affected pay period in this scenario is as follows:
- total gross earnings for the affected pay period / number of days worked in affected pay period = gross earnings per day worked, then
- total the number of days worked between pro rata start date (inclusive) and end date of affected pay period (inclusive) = number of pro rata days, then
- number of pro rata days x gross earnings per day worked = pro rata earnings.
Using the same example as above whereby an employee takes bereavement leave in the fortnight pay period commencing 6 January 2020. The pro rata commencement date is 6 January 2019, which falls in Period 3 of the employee's historic gross earnings:
Where an employee has basic work hours:
Using the above formula:
- total gross earnings for the affected pay period = $1451.03 (as displayed in historic gross earnings export)
- number of days worked in affected pay period = 10 days (the system calculates 5 weekdays worked per week for an employee with basic work hours and so a fortnight is 10 days)
- total the number of days worked between pro rata start date (inclusive) and end date of affected pay period (inclusive) = 0 days. This is derived from the fact the pro rata start date of 6/1/19 falls on a Sunday and because this is not a weekday, it is not counted as a day worked.
So:
- $1451.03 / 10 days = $145.10 gross earnings per day worked
- 0 days x $145.10 = $0 pro rata earnings.
Therefore, no pro rata earnings are added to the total gross earnings from Period 4 onwards from the historic gross earnings export and any pay run earnings up to period ending 5 January 2020.
Where an employee has advanced work hours:
Using the above formula and an example where an employee works 8 hour days from Thursday through Sunday:
- total gross earnings for the affected pay period = $1451.03 (as displayed in historic gross earnings export)
- number of days worked in affected pay period = 8 days (the affected pay period runs Monday to Sunday for 2 weeks. On that basis and the fact the employee works every Thursday to Sunday, this equates to 8 days worked in the fortnight)
- total the number of days worked between pro rata start date (inclusive) and end date of affected pay period (inclusive) = 1 day. This is derived from the fact the pro rata start date of 6/1/19 falls on a Sunday and this is a work day for the employee, as indicated via their advanced work hours.
So:
- $1451.03 / 10 days = $145.10 gross earnings per day worked
- 1 day x $145.10 = $145.10 pro rata earnings.
The $145.10 is then added to the remaining gross earnings (calculated from adding all gross earnings from Period 4 onwards from the historic gross earnings export and then any pay run gross earnings up to pay period ending 5 January 2020).
N.B.: With this specific example, an employee with advanced work hours that does not include weekend work would produce the same pro rata result as the scenario provided for an employee with basic work hours.
How do we pro rata actual pay run gross earnings?
Thankfully, this one isn't as complicated as historic gross earnings! This formula follows the same principle as Scenario 2 above and uses the employee's standard work hours (basic or advanced) to determine the final result.
The formula applied for the affected pay period in this scenario is as follows:
- total gross earnings for the affected pay period / number of days worked in affected pay period = gross earnings per day worked, then
- total the number of days worked between pro rata start date (inclusive) and end date of affected pay period (inclusive) = number of pro rata days, then
- number of pro rata days x gross earnings per day worked = pro rata earnings.
Let's apply the formula using an example. A termination pay is processed for an employee who terminated their employment effective on 18 March 2020. The employee's last leave entitlement date was on 10 December 2019, therefore the employee's gross earnings will be calculated from 11 December 2019 until the last pay run. The employee is paid monthly - from the start of the month to the end of the month - so the system will need to pro rata the December pay run. To do this, first ensure the employee's earnings exclude anything that is not eligible for AWE. When looking at the Pay Categories report for the month of December, the employee's earnings are broken down as follows:
Using the above formula and an example where an employee works 8 hour days from Thursday through Sunday:
- total gross earnings for the affected pay period = $2680 (as per the Pay Categories report displayed above. Take note that the report also includes earnings for the pay category "Extra Pay (Discretionary Payment - excl. AWE)". As this pay category is excluded from AWE you must exclude the amount attached to this pay category when determining the employee's gross earnings)
- number of days worked in affected pay period = 17 days (this is determined by calculating the number of Thursdays, Fridays and Saturdays occur in the month of December 2019).
- total the number of days worked between pro rata start date (inclusive) and end date of affected pay period (inclusive) = 12 days. This is derived by calculating how many Thursdays, Fridays and Saturdays occur from 11 December 2019 to 31 December 2019). Refer below for other examples of how this will be calculated based on the employee's scenario.
So:
- $2680 / 17 days = $157.65 gross earnings per day worked
- 12 days x $157.65 = $1891.76 pro rata earnings.
The $1891.76 is then added to the remaining gross earnings (calculated by adding all gross earnings from January 2020 up to the employee's termination pay). When determining the gross earnings from a termination pay the following pay categories are excluded:
- Termination Holiday Pay (8%);
- Redundancy Payment;
- Retirement Allowance.
Other scenarios to determine the number of pro rata days
The above example uses the scenario where an employee has advanced work hours to determine the number of pro rata days. The other scenarios the system caters for includes timesheet employees and employees with basic work hours recorded.
Timesheet employees
Because timesheets are date based, the system will review all timesheets imported into the December pay run and their corresponding date to identify the number of pro rata days worked.
Similarly, when determining the number of days worked in the affected pay period, ie the whole of December, it will count the number of timesheets dated in December for that pay run. To clarify, where there are multiple timesheets for the same day, this will still be counted as 1 day worked.
Employees with basic work hours
As previously stated, the system treats an employee with basic work hours as working 5 days a week, Monday to Friday.
So, when determining the number of pro rata days worked, ie the days worked from 11 December 2019 to 31 December 2019, the system will look at how many Mondays, Tuesdays, Wednesdays, Thursdays and Fridays occurred during that date range, being 15 days.
Similarly, when determining the number of days worked in the affected pay period, the system will look at how many Mondays, Tuesdays, Wednesdays, Thursdays and Fridays occurred in December 2019, being 22 days.