***Please note there is a newer version of this template that is available here
Tracking employee vacation and time-off requests can be a bit of a headache. This template will help track time off as well as how much vacation has been used up and how much is remaining. It also helps to prepare your recurring journal entries to expense vacation.
Free version: Download Here
Full version: (no ads/workbook locks): see product page
About This Template
This template will help you track and reconcile vacation liability owed to employees. Both in hours accrued and taken, and also in dollar values if you need to as well.
Vacation Accrual – Employee Data
The first tab is the EmployeeInfo tab which is where you will need to enter your employee information. All the cells in light blue are ones where you can enter information. The dark blue cells have formulas and are locked. In the below example I’ve filled in some sample employee information:
You can replace the data in the table and you can also add rows by just typing the employee name in the next blank row in column A and the table will automatically re-size.
If you do not want to track dollars accrued then you can simply leave the wage field blank. This template will allow you to track vacation dollars and hours even if there are wage or vacation rate changes. In the above example, Jack Smith had a wage of $10 from January 1 until May 31. And from June 1st his wage changed to $15 and vacation rate increased from 4% to 6%. I’ll go over this calculation later in the post to show you that it has calculated properly in the reconciliation tab.
If there are no rate changes you can leave the end date field blank. You only need to use the end date field if the employee has a change in vacation days or wages, or is no longer with the company. In the latter case you will also want to set their status to Inactive. Doing so will exclude them from the JE tab and calculation.
Also on this tab is a section to enter the holidays for the year, and this is to ensure that the vacation calculation ignores these non-working days. The next section is to ensure the calculation is done correctly and the right percentages are used – daily hours will likely stay as 8 and the annual hours to used in the vacation rate calculation. So if you are paying an employee 4% vacation that is equivalent to 10 vacation days based on 2,000 hours.
The cutoff date is up to what date you want to reconcile to. So if you wanted to know what the vacation liability looks like at the end of the year you would set it to Dec 31, as I have below.
The next tab is the TimeOff tab. This is where you can select when the employee took vacation, and I added an option for sick as well in case you wanted to track that.
In the above example, John Burns is away January 4th, 5th, and 6th, so I enter the 4th as the start date, and the 6th as the end date. The next business day would be the day he is back at work. The hours taken reflects the average daily hours from the previous tab. The wage information also comes from that tab. On the Calendar tab you will see a year-long calendar off the time off. This isn’t part of the vacation calculation but allows you to visually track vacation and sick time taken. You see above that the days for January 4, 5, and 6 are highlighted for John Burns to indicate he took vacation on these days. Sick days will show in a different colour. Weekends and holidays are also filled in with light blue. This would be more of a scheduling tool when you are dealing with many employees and wanted to minimize time off conflicts and overlaps. Accrued Vacation ReconciliationThe Reconciliation tab shows a summary of all of the time earned and vacation time taken off
You can enter a starting date (this does not affect any calculations), a starting balance if you carry forward vacation from the previous year, and adjustments for any items not captured in the hours earned or time off taken (e.g. vacation pay out). The other cells are formulas and are locked.
I will refer to my example above where Jack Smith had a change in wage and vacation rate. His wage won’t affect this calculation but his vacation rate will.
From January 1 to May 31 he will accrue at a rate of 4%. Since he works 8 hours a day x 104 working days which fall within this range once holidays are factored in, that is a total of 832 working hours. 4% of this total is 33.28 vacation hours.
The second range is from June 1 until the end of the year. During this range there are 146 working days x 8 hours for a total of 1,168 total hours worked. At his new rate of 6% this is 70.08.
If I total 33.28 and 70.08 that gives me 103.36 hours earned which is what the reconciliation shows.
If you track dollars there is a separate section for the dollars accrued which works in the same way
Again going back to my example earlier, Jack Smith had a $10 wage from January 1 to May 31st at a 4% vacation rate. If I multiply 104 working days by his daily pay (daily hours of 8 x wage of $10 = $80 per day) then I arrive at 104 x 80 = $8,320. And 4% of this is $332.80
Now from June 1st to Dec 31st that is 146 working days x his new daily rate of $120 ($15 wage x 8 daily hours) is a total wage of 17,520. 6% of 17,520 is $1,051.20.
If I add the two amounts together, I get $1,384 (1,051.20 + 332.80). As you see this equals the dollars earned for Jack Smith.
Vacation Accrual Journal Entry
The last tab is the JE tab which is used to generate your vacation accrual journal entry.
The light blue fields are ones you can enter. You will need to specify the GL accounts, pay period, start, and end dates. There is also a space where you can put an adjustment for an employee. For instance, your accrual might be for a two week period but if someone was terminated or shouldn’t have accrued for any period of time here is where you can make the adjustment.
The employees listed as active will show up in this list as well as their wage during the dates you enter – as long as you don’t have rate changes that occur in the middle of a pay period there should be no issues here.
Please note this spreadsheet should be used to help reconcile to your payroll provider’s data to ensure accuracy and completeness. Do not use this spreadsheet alone in determining your vacation liabilities as I do not offer any guarantees as to its accuracy.