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.
How the Vacation Accrual Template Works
This template will help you track and reconcile vacation liability owed to employees. It will calculate this both in hours accrued and taken, and in dollar values if you need to as well.
The first tab is the EmployeeInfo tab which is where you will need to enter your employee information. The Per Day $ and Vacation Rate fields are automatically calculated. Everything else, you’ll need to enter in. 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 this 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.
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.
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 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 as well.
In the above example, John Burns is away January 3rd, 4th, and 5th, so I enter the 3rd as the start date, and the 5th 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 3,4, and 5 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 grey. This would be more of a scheduling tool when you are dealing with many employees and wanted to minimize time off conflicts and overlaps.
The Reconciliation tab shows a summary of all of the time earned and vacation time taken off.
You can enter 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 should not be modified.
Verifying the Vacation Reconciliation Calculations
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 106 working days which fall within this range once holidays are factored in, that is a total of 848 working hours. 4% of this total is 33.92 vacation hours.
The second range is from June 1 until the end of the year (you can adjust the cut-off date in the EmployeeInfo tab). During this range there are 145 working days x 8 hours for a total of 1,160 total hours worked. At his new rate of 6% this is 69.60.
If I total 33.92and 69.60 that gives me 103.52 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 106 working days by his daily pay (daily hours of 8 x wage of $10 = $80 per day) then I arrive at 106 x 80 = $8,480. And 4% of this is $339.20.
Now from June 1st to Dec 31st that is 145 working days x his new daily rate of $120 ($15 wage x 8 daily hours) is a total wage of 17,400. And 6% of 17,400 is $1,044.00.
If I add the two amounts together, I get $1,383.20 (1,044.00 + 339.20). 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.
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.
Download the Vacation Accrual Template
Free version: Download Here. The free version is limited to 4 spots for employees on the EmployeeInfo tab and the sheets are locked and there is an ad in the Excel ribbon.
For the full version (no ads, locks, or limits to # of employees) click on the following button:
Add a Comment
You must be logged in to post a comment