This template calculates the outstanding annual leave days of employees and keeps record of all leave taken or paid. Annual leave is sometimes also referred to as holiday pay or vacation days and we have designed this template with flexibility in mind to be able to keep record of any annual leave system whether leave accrues based on hourly, daily, weekly, bi-weekly (fortnightly) or monthly pay periods. The template can basically be used to calculate leave balances for any annual period with easy roll forwards to the next annual periods and you can save different copies of the template to accommodate different types of leave.
The template includes the following sheets:
Setup - set up the template for your business by changing the user input settings in the cells with yellow cell backgrounds. All other cells contain formulas and are automatically updated based on your settings. You can set the business name which is used as a sheet heading, enter a cycle date which represents the first day of the annual period included in the template, select a pay basis (monthly, bi-weekly, weekly, daily or hourly), enter the number of work hours per day, elect to accrue leave on total or worked hours or days and enter the number of holidays included in the specified annual period. This template can be used for an unlimited number of years by simply saving a new file for every year and copying over the opening balances from the previous year's file.
Leave - when an employee takes leave, the number of leave units taken should be recorded on this sheet. Leave units are usually expressed as days but the template also accommodates entering leave units in hours if the hourly pay period option is selected on the Setup sheet.
Summary - the outstanding leave days of each employee is calculated on this sheet. You need to create an employee code for each employee, enter the employment date and termination date (if applicable), enter the salary and leave entitlement per pay period and enter the opening leave units. The annual leave entitlement, leave accrual, leave taken or paid, outstanding leave days and provision for outstanding leave values are then calculated automatically. The sheet also contains a monthly analysis of leave taken or paid over the specified 12-month period.
Template Set-up
The template can be customized for your business by setting the appropriate user input values on the Setup sheet. Start by entering your business name which is used as a heading on all of the sheets. You can then set your leave cycle start date - the template covers an annual period and the date entered in this cell should therefore be the first day of the annual period which should be included in the template calculations. The cycle end date is then automatically determined based on the start date.
Note: The template can be used for multiple annual periods by saving a new version for each annual period and copying the opening leave balances from the previous period's file.
You can then select a pay period basis. The template accommodates monthly, bi-weekly (fortnightly), weekly, daily and hourly pay periods. When you select the pay period, the dates in the pay period section on the Setup sheet will be updated automatically and these dates are also included in the list box in cell L2 on the Summary sheet.
You also need to enter the work hours per day which only has a calculation effect if the hourly pay period basis is selected and you need to specify if leave should be accrued based on total hours or days or only based on worked hours or days. The last user defined setting is for the number of public holidays included in the selected annual period. The holidays only affect the business days calculation which in turn only affects the calculation of the provision for outstanding leave on the Summary sheet.
The pay basis and annual factor section and the pay period section are automatically set based on your period selections and used in the template formulas to calculate the accrual of leave and the leave days due.
Recording Leave Taken Or Paid
When an employee takes leave, the number of leave units taken should be entered on the Leave sheet. The following columns are included on this sheet:
Annual Leave Date - enter the date on which the employee's leave commences. All dates should be entered in accordance with the system date settings.
Form Number - enter the leave form number. If leave forms are not being used, enter a transaction number instead. We strongly recommend using leave forms which are numbered sequentially in order to be able to ensure that all leave forms are accounted for. For leave payments, the payment reference number can be used in this column.
Employee Code - select the appropriate employee code from the list box in column C. All employee codes which have been added to the Summary sheet will be included in the list box. For new employees, you will have to add the appropriate employee code to the Summary sheet before it will be available for selection from this list box.
Number of Units - enter the number of units of leave taken by or paid to the employee. If the hourly payment basis has been selected on the Setup sheet, the leave units taken by or paid to the employee should be entered in hours. If any other payment basis has been specified, leave units should be entered in days.
Payment Indicator - enter a "P" in this column if a payment in lieu of outstanding leave has been made. The "P" in this column distinguishes payments from leave taken.
Employee Name - this column contains a formula to display the employee name of the employee selected in column C. We have included this formula to make it easier for users to check whether the correct employee code has been selected.
Note: All the columns on the Leave sheet have been included in an Excel table. This feature is extremely useful when entering data in a table format because the formula in the calculated column (the column with a light blue column heading) is automatically copied when new rows are inserted into the table or when data is entered into the first blank row below the table. You can therefore add a new entry to the Leave sheet by simply entering a new leave entry date in the first blank cell in column A - the table will then automatically extend to include the new leave entry.
The leave data entered on the Leave sheet is used to update the calculations in the Annual Leave Taken and Annual Leave Paid columns and the 12-month leave analysis on the Summary sheet.
Outstanding Leave Days
The Summary sheet contains all the calculations of outstanding leave, annual leave entitlements, annual leave accruals, leave taken, leave paid and a provision for outstanding leave. All of these calculations are on a per employee basis and users therefore need to add a unique employee code for each employee to the Excel table on the sheet. Only the columns with yellow column headings require user input and all the columns with light blue column headings contain formulas and are automatically calculated when adding new employees to the table.
The following user input columns are included on the Summary sheet:
Employee Code - a unique employee code should be entered for each employee. We recommend that you use the employee number as per your payroll system for this purpose. If your payroll system does not include unique employee numbers, you can use any unique numbering system.
Employee Name - enter the name of the employee.
Department Code - enter a department or group code for each employee. The code entered in this column will enable you to use the Filter feature in order to filter employees by group or department and to thereby calculate a leave provision for each group or department.
Employment Date - enter the date on which the appropriate employee starts their employment. Leave will be accrued from this date. No adjustment is made for a part of a month - the leave accrual will start in the first month of an employee's employment. If you only want the accrual of leave to start in the subsequent month, enter the first day of the subsequent month as the employment date. You do not need to amend employment dates when rolling the template forward for the next annual period. Employment dates will therefore always remain the same.
Termination Date - if an employee is terminated, resigns or leaves employment for any other reason, the date on which the employee is terminated should be entered in this column. The accrual of leave will end on this date. No provision is made for portions of months which means that leave will still be accrued for the month in which the termination date falls. If you want the accrual of leave to end at the previous month, enter the last day of the previous month as the termination date. You do not need to remove terminated employees from the template when rolling forward as the leave accruals will automatically return zero values for pay periods after the specified termination date.
Salary Per Pay Period - enter the gross remuneration of each employee on the same basis as the pay period which has been selected on the Setup sheet. For example, if the monthly pay period has been selected, enter the monthly pay or if the hourly pay period has been selected, enter the hourly pay. This amounts entered in this column are only used for the leave provision calculations. If you only want to calculate outstanding leave days or hours and no provision, you do not need to enter any salary amounts in this column.
Annual Leave Opening - enter the opening balance of leave days or hours as at the leave cycle start date which has been set on the Setup sheet. When you roll the template forward from a previous annual period, the leave days or hours can be copied from the Annual Leave Due column in the file for the previous annual period.
Leave Pay Per Period - enter the number of hours or days of leave which need to be accrued for every paid hour or day worked. This annual leave accrual rate should be entered on the same basis and the pay basis which has been selected on the Setup sheet.
Here are a few examples of leave pay per period and the accrual of leave:
Example 1: If the annual leave entitlement of an employee is 15 days and the employee is paid on a monthly basis, the leave pay per period is 1.25 being 15 days divided by 12 months in an annual period. Leave will therefore be accrued at a rate of 1.25 leave days for every pay month worked.
Example 2: If the annual leave entitlement of an employee is 20 days and the employee is paid on a bi-weekly (fortnightly) basis, the leave pay per period is 0.769 being 20 days divided by 26 fortnightly periods in an annual period. Leave will therefore be accrued at a rate of 0.769 leave days for every fortnightly period worked.
Example 3: If the annual leave entitlement of an employee is 15 days and the employee is paid on a weekly basis, the leave pay per period is 0.288 being 15 days divided by 52 weekly periods in an annual period. Leave will therefore be accrued at a rate of 0.288 leave days for every weekly period worked.
Example 4: If the annual leave entitlement of an employee is 18 days and the employee is paid on a daily basis, the leave pay per period is 0.049 being 18 days divided by 365 days in an annual period. Leave will therefore be accrued at a rate of 0.049 leave days for every day worked. Note that the template calculations automatically adjust to 366 days if the specified annual period includes a leap year.
Example 5: If the leave entitlement of an employee is 1 hour for every 17 hours worked and the employee is paid on a hourly basis, the leave pay per period is 0.0588 being 1 hour divided by 17 hours. As it is based on worked hours, you should make sure that you select the Worked option from the list box in cell C13 on the Setup sheet so that the leave accrual is based on worked hours instead of total hours. In an annual period of 365 days, the employee would therefore earn 14.76 days or 118.1 hours of leave being 251 work days (as per our work days calculation) divided by 17 hours worked and multiplied by 8 for the accrued leave hours calculation.
Note: If you prefer to enter the annual leave entitlements instead of the leave entitlement per pay period, you can leave the leave pay per period column blank and override the formulas in the annual leave entitlement column by entering the appropriate values. You may then want to change the colour of the column heading cell to yellow as a reminder that the annual entitlement column is now a user input column.
We'll now cover the columns with light blue column headings which all contain formulas and are therefore automatically calculated and extended when adding new employees to the sheet:
Annual Leave Entitlement - this calculation is based on the leave per pay period entered in the previous column and adjusted based on the number of pay periods in an annual period. If you elect to calculate leave based on worked hours instead of total hours, this adjustment is also made. The calculated value reflects the total leave entitlement of the employee for a full annual period.
Annual Leave Accrued - the number of leave days accrued by the employee from the cycle start date (or employment date if after the cycle start date) to the end of the pay period which is selected from the list box in cell L2 (or the termination date if earlier). This calculation is based on the employee's leave entitlement as calculated in the previous column and the number of elapsed periods based on the period selected in cell L2. The number of elapsed payment periods is also reflected in cell L3.
Annual Leave Taken - the number of leave days or hours which have been taken by the employee based on the leave days or hours entered on the Leave sheet. This calculation only includes the leave days or hours taken between the template start date and the end of the period selected from the list box in cell L2.
Annual Leave Paid -the number of leave days or hours which have been paid to the employee based on the paid leave days or hours entered on the Leave sheet. This calculation only includes the paid leave days or hours between the template start date and the end of the period selected from the list box in cell L2.
Annual Leave Due - the leave days due to the employee is calculated in this column. The balance as at the end of the selected pay period (based period selected in cell L2) is reflected in this column and is calculated by adding the leave accrued to the opening leave balance and deducting the leave taken or paid from this calculation.
Annual Leave Provision - the leave provision for each employee is calculated in this column. This amount is calculated by applying a daily or hourly salary rate to the leave days or hours due which has been calculated in the previous column. The daily or hourly salary rate is calculated based on the salary per pay period which is converted to an annual equivalent which is then divided by the number of work days in the annual period as per the calculation on the Setup sheet. The leave provision amounts represent the amounts which would need to be paid to the employee upon termination of employment and the total leave provision represents the extent of the possible liability for the business.
Leave 1 to 12 - the calculations in these columns reflect the total days or hours leave taken by or paid to each employee. There is a column for each of the monthly periods included in the template with the appropriate monthly dates reflected above the column headings.
Note: If the calculation of the leave days due for any employee results in a negative value, the value is highlighted in orange and the leave provision amount is set to zero. The highlighting indicates that the employee has taken more leave than entitled to (up to the end of the selected pay period as per cell L2) - the leave of these employees should therefore be closely monitored bearing in mind that some employees may take most of their annual leave entitlement early on in a financial year and the accrual of leave in subsequent periods may reinstate a positive leave due balance. Negative leave due balances result in a no provision because it is assumed that excessive leave which has been taken will not be deducted from the employee's salary.
The total leave provision for all employees is included in cell N3. If a filter is applied to the Summary sheet by using the Filter feature, the total provision amount calculated will only include the filtered records.
Note: All the columns on the Summary sheet have been included in an Excel table. This feature is extremely useful when entering data in a table format because the formulas in calculated columns (the columns with light blue column headings) are automatically copied when new rows are inserted into the table or when data is entered into the first blank row below the table. You can therefore add a new employee to the Summary sheet by simply entering a new employee code in the first blank cell in column A - the table will then automatically extend to include the new employee.
This template has been designed to incorporate a full annual period. We do not recommend using the template for shorter periods - if you want to start using the template during a financial year, we suggest capturing your opening leave balances at the start of the financial year in the opening balance column on the Summary sheet and capturing all the leave taken by or paid to employees since the start of the financial year on the Leave sheet.
Template Roll Forward
This template can be used for any 12-month reporting period by simply entering the appropriate leave cycle start date on the Setup sheet. When you come to the end of the initial 12-month period, you will need to roll the template forward for the next 12-month period. The following steps should be completed in order to roll the template forward:
- Make sure that you complete all the input of leave taken & paid before rolling the template forward for the next 12-month period otherwise the necessary input may need to be repeated in the new version of the template and you may need to adjust your opening balances.
- Save the template under a different file name in order to create a copy of the current version of the template.
- Copy all the annual leave due calculations in column M on the Summary sheet and paste the calculations as values in column G on the Summary sheet. The closing leave balances of the previous period are thereby included as the opening balances of the new 12-month period.
- Enter a new leave cycle start date in cell B7 on the Setup sheet. The 12-month reporting period which is included in the template will be amended automatically.
- Update the salary per pay period amounts in column F on the Summary sheet and the leave per pay period in column H on the Summary sheet (if necessary).
- Note that the employment and termination dates of employees do not ever need to be changed regardless of how many times the template is rolled forward for subsequent annual periods. These dates will always remain the same aside from adding new termination dates as and when they occur. It is also not necessary to ever delete employees once they have been terminated as no leave calculations are performed on terminated employees.
- The leave data which has been captured on the Leave sheet can be left unchanged and will not affect the calculations for the new annual period. If you prefer to delete the previous 12 month's entries, you can do so but make sure that you retain the formula in the first row in column F.
