The overview of the steps are:
1. Define the PTO Policy settings
2. Enter list of weekends and holidays
3. Enter Employee details with starting PTO balances, work hours
4. Capture changes in work hours
5. Enter PTOs used by employees
6. View the automated reports
Lets take a detailed look into each of these steps:
Step 1:
In the PTO Policy settings sheet,
STARTING DATE : Enter the date from which you would like to use this template to track PTO. There are two key purposes for this date. 1) The template will only calculate PTO from this date. 2) The Starting Balance (which we will discuss soon) will be the balance at the end of the day before this Starting Date.
There are several components of a PTO policy as described below:
PTO Accrual Period
This is to inform how we often accrue the PTO using this PTO accrual calculator excel template. We have 4 options here: Weekly, Every 2 Weeks, Twice a Month and Monthly.
REVIEW FIRST ACCRUAL (to the far right of this sheet): We need to check and ensure that the data provided in the inputs are correct and result in a valid accrual policy. This calculate pto accrual excel template shows the first set of accrual dates.
Now over to the Rollover Policy.
Annual PTO Rollover Policy
The template provides three options.
Rollover policy options
1. Zero Rollover: Employee loses all the PTO balance at the end of the year and starts from scratch in the next year.
2. Rollover Limit: We can set a limit on how many hours are carried over.
3. Unlimited Rollover: Here the employee does not lose any PTO and will carry over everything to next year.
With rollover policies, there is another variation. Some companies may apply rollover at calendar year change 1st Jan of every year or on work anniversary dates (which vary for each employee). You can easily change that setting.
Rollover Timing options.
Probationary Period
Enter the duration of the Probationary period in days.
For example, in the screenshot above, employee does not earn any PTO during the first 90 days of employment.
Maximum Allowed PTO Balance
You can set a limit on maximum balance at any time, to ensure that there is a limit to PTO accrual. You can set the amount (hours)in the Maximum Allowed PTO Balance field.
Rounding in PTO Balance
When the template calculates balances and display in the report, you can control the precision by just entering the number of decimals.
PTO Accrual Rates: This defines how much PTO hours are earned by the employee for hours worked.
Step 2: Enter list of weekends and holidays
In the Weekends and Holidays sheet,
Enter which days are day-offs and the company's holiday list.
Step 3: Enter Employee details with starting PTO balances, work hours
Enter list of employees for whom we will be tracking and calculating PTO balance.
HIRE DATE: Enter the date when employee joined the company. Even if you have been tracking PTO using some other tool and now want to switch to use this template, enter the actual hire date of the employee.
STARTING BALANCE: Here, we can enter balance we carry over for employees whom you have been tracking PTO from other tool before you switch to using this template.
For example, if the employee started working in 2017, but you started to switch to this template on July 3, 2019, then you can enter the starting balance as of end of July 2, 2019. Any PTO used from July 3, 2019 will have to be entered in the template.
This can be captured for all the four types of PTOs supported by the template.
REGULAR HOURS WORKED: This is to record how many hours this employee works every working day. This option is given to reduce data entry. Let’s say an employee works usually 8 hours a day but occasionally he/she may work less or more than 8 hours. You can enter 8 as default working hours. On all days except holidays and weekends, the template will assign the default hours worked by employee.
You can then enter adjustments for specific dates when the employee did not work 8 hours in the HOURS_WORKED sheet.
Step 4. Capture changes in work hours
As stated above, use this sheet to capture changes in work hours of any employee.
Step 5. Enter PTOs used by employees
When an employee takes PTO or plans to take PTO, you can enter that in the PTO USED sheet.
We can enter date ranges to enter multi-day vacation. Even if it is a single day vacation, please enter both start and date as the same date.
Step 6: View the automated reports
PTO REPORT
For a chosen, employee get a high level as well as a detailed look into their PTO balances, work hours with this report.
CALENDAR
While PTO Report is designed to print and share each employee’s balances to the employee, the Calendar sheet allows the manager or the small business owner to view all the employees’ PTO hours during a month in a calendar view.
BALANCE SHEET
Get the available PTO balances of all employees in the Balance sheet.
View all four of PTOs as a Current balance and balance as a user specified date, as shown below:
