The Staff Leave Planner is a tool written in Microsoft Excel to allow you to record and manage the leave of your employees.
The planner typically contains a set 12 month period which should align to your annual leave reporting year. Some of the main features and customisations you can make to the planner include:
|Employee Data||The employee data sheet is where you enter your employee details and their entitlement.
The settings on this sheet allow you to:
|Calendar Worksheets||The calendar worksheets serve two main purposes:
|Individual Reports||Individual reports allow you to see a full breakdown of an employees' leave across specific dates and specific leave codes. These reports can be exported to pdf or excel workbooks, and the export can be run as an automatic batch job for multiple employees.|
|Group Reports||Group reports allow you to see summarised totals of groups of employees across specific dates and specific leave codes. These reports can be exported to pdf or excel workbooks.|
|Monthly Reports||Monthly reports allow you to reproduce the views from the calendar across a more specific group of employees, leave codes or time frames. These reports can be exported to pdf or excel workbooks.|
|Main Menu||The main menu allows you to quickly navigate to parts of the planner as well as assign the following global settings:
When you purchase and download the planner it will be contained in a zip file. You must extract the planner from the zip file before using it otherwise you will not be able to save it. If you are unsure of how to unzip the planner, typically right click the file and choose "extract all". The planner can then be found in the sub folders that were extracted.
The planner can be saved to a network drive or a shared folder so that other users can access it.
Double click the file to open it in Excel. You will likely see some security prompts at the top of the document warning you about macro content. This is ok, the planner uses macros to performs it's calculations and provide you with additional features. You will need to enable macros for the planner. For further details on enabling Macros please see the following Microsoft help page: Enable or disable macros in Office files
The setup wizard will guide you through configuring the planner for your first time use. Any settings you choose here can be configured at a later point in time. To run the wizard click on the red "Click here to setup the planner" button.
If you have previously used the planner you can choose an option during setup to import your data from a previous planner. The importer will allow you to transfer important information from one planner to another such as:
You can only import data from anotehr planner during the initial setup of the planner. To import data follow the steps below:
The main purpose of the employee data sheet is to provide a place where you can add, edit and delete employee information. All of your employee information needs to be edited from this worksheet (and not the calendars). The changes that you make here will be cascaded to other sheets. The columns that show you leave entitlement, taken and remaining are linked to how you have configured your leave categories and keys, specifically:
The main functionality of the Employee Data worksheet is as follows:
Leave categories can be setup to simply count the amount of leave taken or they can be setup to have an entitlement. When leave is setup to have an entitlement you will typically see the following columns (in this example let's assume the leave category is called "maternity leave"):
When you change the leave amounts the planner will also update any leave amounts that are shown on the calendar sheet.
You can sort the data on the employee data sheet by using different criteria. You can also optionally apply this sort to the calendar worksheets.
It's possible to rename the columns on the Employee Data sheet:
Columns that are not a Built In type can be deleted from the planner. Built-in columns can be hidden. To make these changes:
It's possible to change the order in which columns are shown on the Employee Data sheet:
You can extra columns to the planner which can be:
Adding columns to the planner is all achieved via the employee data settings. In this example we will add a column that is linked to annual leave, and when data is entered into the column it will update the total entitlement:
The main purpose of the Calendar worksheets are to provide you with a place to record when employees are taking leave. Your leave year will be split over 2 worksheets with 6 months typically shown on each sheet. It is possible to setup a read only view of the calendar so that employees who are not administrators can view the leave booked. For more information please see the [password and security] section.
The calendar sheet has the following functionality:
Leave can be recorded onto the planner by selecting the leave code from the right-click menu or by typing in the leave code (followed by the number of hours if tracking in hours). It is also possible to enter multiple leave codes into a single day.
To enter leave via the right-click menu:
To enter leave directly into the cell:
To enter multiple leave codes into the same day we recommend using the right-click menu method outlined above. Simply repeat this for each code you need to enter into the same day.
To enter the same leave code across multiple days:
To delete leave codes that have been entered:
You can add comments to leave entries which will then appear on the reports that are generated.
To add or edit comments on a leave entry:
To delete an existing comment:
You can customise how the days of the week are shown via three different options:
To make these customisations:
You can add pre-existing or your own national holidays / significant dates to the calendar:
The calendar sheets allow you to show up to 5 columns of data relating to leave which appear next to the names of your employees. The default settings are to show the amount of annual leave taken and the amount of annual leave reamining. If you need to see more leave details than the 5 columns allow we suggest looking at the data stored on the employee data sheet or look at running a group report which will give you this information in greater depth. To customise the columns shown:
The planner operates on the principle that there are leave categories and leave keys. A Leave category is the overall category for the type of leave you are tracking, such as "Annual Leave", "Sick Leave", or "Maternity Leave". Leave keys are the individual keycodes that are associated with the category, e.g "S" for a day of sickness, "SH" for half a day of sickness etc.
Each leave category can be set up in one of two ways:
To "track leave as entitlement" which means:
To "track leave taken only" which means:
Before adding a code to the planner consider the following table:
|What do you wish to achieve||Which process should you follow.|
|I want to add a code for a category that does not already exist|
|I want to add a code to an existing category, the code should remove 1 day from the total leave taken||
|I want to add a code to an existing category, the code should remove half a day from the total leave taken||
|I want to add a code to an existing category, I'm tracking leave in hours||
|I want to add a code to an existing category, the code should accrue leave and increase the total leave entitlement.||
The name that you give the leave category will be the name that it is referred to throughout the planner. Therefore we recommend keeping it short and descriptive. For example, if you call the category "Unpaid Leave" then this is the name that will be used to create the column titles on the Employee Data sheet and it will be the name used to refer to the category when creating reports.
To add a new category follow the steps below:
|Category name||The overall name of the category, e.g. "Annual Leave".|
|Default Colour Code||Click to change the default colour that keycodes will use for this category. Please note that when you add keycodes it is possible to use different colours to this one.|
|How do you want to track leave||Choose either Count or Entitlement:
|Enable Leave Accrual (Time off in lieu)||If the leave for this category is to be tracked as entitlement then this option becomes enabled. Checking this option will place a column on the employee data sheet to show you TOIL accrued, and it will also enable options on the key codes to let you create TOIL keys.|
You can add multiple keycodes to a category. The default behaviour of a keycode is that when a key is logged onto the calendar it will increase the leave amount taken and reduce the overall amount of leave remaining. However it is also possible to setup a leave code that will just increase the amount of entitlement i.e accrue leave. There is a slightly different process to follow for this which is listed below [link].
To add a key follow the steps below:
Choose the title to describe the leave code.
|Colour||This is the colour that the calendar cell which change to when you enter the code. Click on it to change the colour.|
|Code||Choose the code that you will enter on the calendar. If the leave is for a full day or will be used for tracking in hours we recommend using just one letter e.g. "U". If it's a half day it's worth putting an extra letter on the end to distinguish this, e.g. "Uh".|
|Weighting||Weighting applies to the keycode when you enter it onto the calendar for an employee who is tracking leave in days. If you are tracking leave in hours then this is ignored as you have to supply the weighting (hours) when entering the code e.g. "U7.5".
You can setup an Accrual code providing that the category for the keycode is set to track leave as entitlement, and that TOIL is enabled on the category. To check that the category has been setup correctly:
The Staff Leave Planner allows you to generate reports for individual employees. This report contains detailed information about the type of leave the employee has taken.
With an individual report you can:
An example of an individual report is shown below:
The report can be exported to a seperate workbook, which is ideal for sharing with your colleagues. To use this feature follow the steps below:
You can use the "Batch Leave Reports" feature to automatically generate and save a series of Individual Leave Reports, which will be saved as seperate Excel files on your computer.
These files could be kept for record keeping or sharing with employees and can be password protected to prevent editing.
To generate the reports as a batch: