This template enables users to control the sick leave entitlement of employees and thereby analyse the attendance of employees. The template can be used to calculate remaining sick leave days for any period based on the sick leave entitlement & sick leave cycle duration which is specified by the user. The sick leave that is taken by each employee can simply be recorded and the template automatically calculates the remaining sick days that an employee has left. The template does not need to be rolled forward and automatically adjusts the sick leave cycles based on the review period that is specified.
Note: The template can also be used for other leave types like family responsibility or compassionate leave where only the days leave taken needs to be recorded and measured based on a specified entitlement & cycle duration. Refer to our Annual Leave template for annual leave entitlement calculations which also includes the calculation of a provision based on monthly employee remuneration.
The template includes the following sheets:
Leave - when an employee takes sick leave, the number of days that is taken should be recorded on this sheet.
Summary - the employee code, name & employment date of each employee should be recorded on this sheet. The sick leave entitlement, leave cycle duration and leave cycle type should also be specified on this sheet. The remaining sick leave days of each employee are then automatically calculated. The leave cycle start & end dates of each employee are automatically determined based on the review period which is entered at the top of the sheet.
Monthly - this sheet includes a 12 month summary of the sick leave that is taken by each of the employees that are added to the Summary sheet. Note that this report is populated based on the reporting period selection on the Summary sheet.
Template Set-up
The first step in customizing the template for your business is to specify the sick leave entitlement in cell E2 and the sick leave cycle duration in cell E3 on the Summary sheet. You also need to select the leave cycle type in cell E4 - there are two options, namely Fixed and Rolling.
Select the Fixed option if you only want a new leave cycle to start when the previous leave cycle ends. Select the Rolling option if you want the oldest period to be dropped and a new period added when the cycle date is rolled forward for a new annual period.
Example: A leave cycle starts on the 1st of March 2013 and spans a 36 month period. If the Fixed option is selected, the first leave cycle would start on the 1st of March 2013 and end on the 29th of February 2016 for any review date on or before 29 February 2016. When a review date of on or after 1 March 2016 is entered, an entirely new leave cycle will be started and will include the leave cycle from 1 March 2016 to 28 February 2019. Any sick leave taken in the first leave cycle will fall away entirely.
Example: If the Rolling option is selected and the same example is used, the first leave cycle will also start on the 1st of March 2013 and end on the 28th of February 2016 for any review date on or before 28 February 2016. Only when a review date of on or after 1 March 2016 is entered, will any difference between the Fixed and Rolling bases be noted. If the Rolling basis is selected and the review date is on or after 1 March 2016, the leave cycle will be determined by dropping the oldest 12 month period in the 36 month period and adding a new 12 month period. An entirely new sick leave cycle is therefore not started and leave taken in the past 24 months will still be counted. With every 12 month period that passes, sick leave taken in the oldest 12 month period will be dropped and sick leave taken in the next 12 months added.
All employees should then be added to the Summary sheet. The following details are required for each employee:
Employee Code - a unique employee code should be entered for each employee in column A. 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 in column B.
Department Code - enter a department or group code for each employee in column C. The code that is entered in this column will enable you to use the Filter feature in order to filter employees by group or department.
Date Employed - enter the date on which the appropriate employee starts their employment. The first sick leave cycle will start from the first day of this month. No adjustment is made for a part of a month - the sick leave entitlement 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, simply enter the first day of the subsequent month as the employment date.
Note: Columns E to I contain formulas that calculate the sick leave cycle start & end dates, the sick leave entitlement, the sick leave days taken and the remaining sick leave days.
Cycle Start Date - the first sick leave cycle will start on the first day of the month in which the employee was employed. When the cycle duration in months is reached, the cycle start date will be amended automatically based on the leave cycle type which has been selected in cell E4.
Cycle End Date - the cycle end date is determined by adding the cycle duration in months to the cycle start date.
Days Entitlement - the sick leave entitlement is the same as the days that are entered in cell E2. The entitlement will be the same for all employees but the individual cycle start & end dates of employees will differ based on when the employee was appointed.
Leave Days Taken - all sick leave that is taken by employees need to be recorded on the Leave sheet. The total leave days taken that is calculated for each employee in this column includes all the days that fall between the cycle start & end dates and are before the review period which is specified in cell I2.
Leave Days Remaining - the remaining sick leave days are calculated in this column be deducting the sick leave days taken from the sick leave days entitlement.
Note: The template has been designed for any leave type where the same entitlement is applied to all employees and leave does not accrue on a monthly basis but are simply measured against the specified entitlement days. You can therefore use the same template for other similar leave types (like family responsibility or compassionate leave) by simply saving this file under a different file name, changing the days entitlement, cycle duration and cycle type settings and recording the leave taken for the new leave type on the Leave sheet.
Note: This template is not suitable for annual (or vacation) leave purposes - refer to our Annual Leave template for a solution which will be suitable in instances where the leave entitlement needs to be specified per employee & where leave needs to be accrued on an annual basis. This template also facilitates the calculation of an annual leave provision based on the outstanding leave days & the remuneration of each employee.
Note: The Sick Leave template is not designed for any particular period and does not need to be rolled forward at the end of each financial period. You can therefore simply continue to record the sick leave that is taken and the template will continue to automatically calculate the remaining leave as at the specified reporting period date.
Note: If the calculation of the remaining sick leave days in column I results in a negative value, the value is highlighted in orange. The highlighting indicates that the employee has taken more sick leave than entitled to (up to the end of the particular reporting period) - the leave of these employees should therefore be closely monitored and consideration can also be given to forcing the employee to take the excess sick leave days as part of their annual leave entitlement. In this case, the excess days should be removed from the Leave sheet and included on the Leave sheet in the Annual Leave template (which can be purchased as a separate template).
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 that are included in calculated columns (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 an employee by simply entering an employee code in column A - the table will then automatically extend to include the new employee.
Recording Leave Taken
When an employee takes sick leave, the number of days leave that is taken should be entered on the Leave sheet. The following columns are included on this sheet:
Date of Leave - enter the date on which the employee's leave commences. All dates should be entered in accordance with the regional date settings that are specified in the System Control Panel.
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 that are numbered sequentially in order to be able to ensure the completeness of leave input.
Employee Code - select the appropriate employee code from the list box in column C. All employee codes that have been included on the Summary sheet are 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 Days - enter the number of days leave that is taken by the employee.
Certificate - enter "Yes" if the employee has provided a certificate from a medical practitioner. Leave blank if no certificate has been provided.
Comments - use this column to enter any comments about the leave or absence from work by the employee.
Employee Name - this column contains a formula that displays the employee name that is associated with the employee code that has been selected in column C. The formula should be copied for all new rows that are entered on this sheet. The employee name can be used to check whether the correct employee code has been selected in column C.
Note: If you want to include opening sick leave days taken balances, these should be added to this sheet. We recommend recording the days as they were taken but if this information is not available, you can add opening balances on the first day that you want to include in the template.
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 formulas that are included in calculated columns (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 leave entry by simply entering a date in column A - the table will then automatically extend to include the entry.
The leave data that is entered on the Leave sheet is used to update the calculations in the Leave Days Taken column on the Summary sheet and the monthly analysis of sick leave by employee on the Monthly sheet.
Sick Leave Reporting
The remaining sick leave days are calculated in column I on the Summary sheet. These calculations are based on the reporting period that is specified in cell I2. The calculations on the Summary sheet are automatically updated when a new reporting period is entered in cell I2. You can therefore also display sick leave calculations of previous leave cycles for an employee by simply entering a new date in this cell.
The Monthly sheet includes a monthly analysis of the sick leave that is taken by each employee as well as the total sick leave days taken for the appropriate month (above the column headings). All calculations are displayed in days. The periods that are included on this sheet are determined by the reporting period that is specified in cell I2 on the Summary sheet.
Note: The Monthly sheet requires no user input. All the calculations on this sheet are automated but only the first 50 employees are included on this sheet by default. The number of employees that are included on this sheet can be extended by simply copying the formulas in the last row that contains data into the appropriate number of additional rows.
Note: The report on the Monthly sheet is based on the date that is selected on the Summary sheet and will only include sick leave days taken during the 12 month period which is included on this sheet. If you want to view the report for a different period, simply enter a new date in cell I2 on the Summary sheet in order to display the specified and 11 preceding months' data on the Monthly sheet.
Template Roll Forward
The Sick Leave template has been designed on a cumulative basis and therefore never needs to be rolled forward. Simply continue to record sick leave days taken on the Leave sheet and simply enter a new reporting period date in cell I2 on the Summary sheet in order to view all the remaining leave days balances.
