This feature will be available from 17 September 2020
The purpose of the holiday pay calculations workbook is to provide complete transparency with how the system calculates an employee's 8% (or the percentage recorded in the pay rates screen for the employee) holiday pay entitlement via the Leave Liability report. This article will provide clarification on each section of the workbook to ensure a thorough understanding of all calculations.
How do I access the workbook?
Firstly, you need to generate the Leave Liability report as at the desired date. From the screen, you will see a download icon next to the $ value of the employee's "Holiday Pay 8%" leave record, as follows:
When you click on the download icon, the workbook will be exported as an excel file.
The workbook explained
The holiday pay workbook contains several worksheets:
- Working: this contains the complete calculation to derive the employee's 8% (or the percentage recorded in the pay rates screen for the employee) holiday pay entitlement;
- Pay Runs: this lists all the relevant pay runs, if any, that encompass the employee's pay run gross earnings component of the workings;
- Historic Gross Earnings: this lists all the relevant historic gross earnings per pay period, if any, that encompass the employee's historic gross earnings component of the workings;
- Projection Pay Run Data: this lists all the relevant pay run earnings and historic gross earnings, if any, that are used to determine projected gross earnings.
A few notables on each worksheet is provided below.
Working
- Report Date = 'As at date' entered when generating the Leave Liability report;
- Employee Leave Year Start = the date configured in the employee's Leave Allowances screen. If 12 months have passed from that date, the date will then be displayed as the next start date of the employee's 12 month entitlement period;
- Leave Entitlement Earnings and Alternative Holiday Earnings = this is the $ value of the employee's entitlements that will be paid out on termination;
- Historic gross earnings: take note here that there may be instances where the historic gross earnings start date is not the actual start date for that pay period, as entered in the historic gross earnings export. This will occur when the employee's leave year start date falls after the actual historic gross earnings pay period start date. Additionally, in this instance, we will pro rata the earnings of that first historic pay period. Further information on how pro rata earnings are calculated for historic gross earnings can be found here.
- Pay run gross earnings: similarly to historic gross earnings, there may be instances where the first pay run start date is not the actual pay run start date for that pay period, as processed in the system. This will occur when there are no historic gross earnings being applied and the employee's leave year start date falls after the pay run start date. This flows on to the next section which calculates an employee's average weekly gross earnings.
- Average weekly gross earnings: to determine average weekly earnings based on actual gross earnings paid, the number of weeks spanning the period of actual earnings needs to be determined. This is where applying the later of the employee's leave year start date or historic gross earnings start date/first pay run start date is important, as it will ensure the number of weeks calculated is correct. What do I mean? Referring to the example image below, an employee's leave year start date is 14/8/19. The actual historic gross earnings start date is 12/8/19. Because the start is prior to the employee's leave year start date, the leave year start date is applied instead and used as the 'earliest included historic gross earnings' when determining the weeks of entered earnings.
If the actual historic gross earnings start date of 12/8/19 was referenced instead, it would result in an inflated weeks of entered earnings result. This would be incorrect because any earnings associated with the 12th and 13th of August relate to the employee's previous entitlement period. This also results in a reduced average weekly gross amount.
- Projection Data: this section will only contain data if the report date is greater than the last pay run end date, thereby needing projection data to determine average weekly earnings for that period where there are no actual pay run earnings. Similar to the historic gross earnings start date and/or first pay run start date, the system will apply the later of the employee leave year start date and historic gross earnings start date (or first pay run start date if there are no historic gross earnings). The reason for this is exactly the same as that explained in the "average weekly gross earnings section".
Pay Runs
This worksheet lists all the pay runs processed in the system with a period end date that is greater than or equal to employee's leave year start date. Take note of the two columns "TotalGrossEarnings" and "ApplicableGrossEarnings". The earnings contained in the "TotalGrossEarnings" column include all earnings processed for the employee in the pay runs. On the other hand, the earnings contained in the "ApplicableGrossEarnings" column exclude any earnings paid to the employee that are associated to pay categories configured to be excluded from average earnings. You can check which pay category has been excluded from AWE by running the Pay Categories Report. Additionally, if the first pay run earnings were pro rata'd (because of the leave year start date being later then the first pay run start date) the pro rata amount will be displayed in the "ApplicableGrossEarnings" column. You will know this is the case as there will be a note to the side of the pro rata earnings stating as much. Further information on how pro rata earnings are calculated for pay run earnings can be found here.
The value contained in cell C14 of the 'Working' worksheet - Pay run gross earnings - is the sum of all "ApplicableGrossEarnings" earnings contained in the 'PayRuns' worksheet.
Historic Gross Earnings
This worksheet lists all the historic gross earnings imported in the system for pay runs with a period end date that is greater than or equal to the employee's leave year start date. Take note of the two columns "TotalGrossEarnings" and "ApplicableGrossEarnings". The difference between the earnings in each column is the "ApplicableGrossEarnings" column factors any pro rating of the first historic gross earnings pay run (if the leave year start date is later then the first historic gross earnings start date). You will know this is the case as there will be a note to the side of the pro rata earnings stating as much. Further information on how pro rata earnings are calculated for historic gross earnings can be found here.
The value contained in cell C10 of the 'Working' worksheet - Historic gross earnings - is the sum of all "ApplicableGrossEarnings" earnings contained in the 'HistoricGrossEarnings' worksheet.
Projection Pay Run Data
This worksheet combines the data from the 'HistoricGrossEarnings' and/or 'PayRuns' worksheets and is used when needing to project earnings between the historic gross earnings end date or last pay run end date and the report date.
The value contained in cell C24 of the 'Working' worksheet - Projection data gross earnings - is the sum of all "ApplicableGrossEarnings" earnings contained in the 'ProjectionPayRunData' worksheet.