Collaborate on Hourly Billing Template for Personnel with Ease Using airSlate SignNow

Hourly billing template for Personnel

welcome to another video from ins Ericom in this video we will be discussing about a free Excel template to calculate paid time off balances for your hourly employee in the last couple of videos I have provided one on the key factors in determining PTO policy for hourly employee and in the second video I also covered how to calculate the PTO balances for the hourly employee so I would definitely recommend you checking out those two videos if you're interested in PTO calculators for hourly employees in this video I will not go into those topics instead I will focus on this free excel template that you can download from ins Ericom and then enter your own information about the earlie employee and get automated PTO report in this video I will go over the key features of the template and also step by step how to use the template I will be doing a live demo as well so if you're interested in this free excel template please subscribe to this channel and also post your interest in the comment section below now let's get started the purpose of this excel template is to provide a simple and effective solution to PTO tracking for your hourly employee as we covered in the previous videos it's not very easy to automatically calculate the PTO balances because there are so many nuances so many flavors so many combinations and scenarios that needs to be taken into account this template makes all of that simple but it's still very effective in addressing your specific business situations the key features of the template are that it covers the most common business scenarios it is very flexible so that you can enter your own information and customize for your business the PTO balance will be automatically calculated not only the balance as of now but it can also project the future expected balance in the PTO report which is fully interactive you can print or export to P and then share with your employee it supports right now two years or more of tracking so it supports about 108 periods so if you're doing a weekly then you'll have two years of tracking but then there are ways to extend it or you can restart it with a new file for the next two years this template requires Microsoft Excel installed in your machine to use there is a limit on 500 leave entries that you can enter so that means if your employee is taking PTO you can enter up to 500 entries after which you need to start using a new file this template can be used to track PTO for one employee if you need to do this for a second employee just make the copy of the entire file and then do it for the second employee the template is built in such a way that you cannot do and two employees in one file however I am working on an advanced version of this template that will support multiple employees in one file so if you're interested in that solution please post your interest in the comment section below I will get back to you as soon as I can now before I get into the step-by-step instructions I want to just provide an overview of the steps so what they're going to do is to enter some policy settings then we will choose which days are the weekends and holidays in our company and then we will enter the number of hours worked by the employee and also then the PTO used by the employee that's it and then the final step is we will view or print the employee report whenever we need so this is the high-level overview of steps now let's go directly into the template and do a live demo so this is our template when you download the template it will not have any data populated but just for this illustration I have some sample data entered so let's go through the different sheets first so the info sheet provides you some key articles and links that you can use to further understand more about the template and then we have the PTO policy settings which is where we will enter the policy related settings and the employee details and then we go to the weekends and holidays where we choose our weekends and holidays and then the worked hours so we enter the day data here about hours work by the employee and in the PTO used by the employee and then we can now view or print or export to PDF the employee report which is fully automated now there is also an extra sheet which is optional if you want to make adjustments to the PTO balances so the template provides you all the flexibility to make sure that it works for your needs so now let's go back to the step 1 and start entering information about our employee so the first step is to enter the policy settings so in the settings we have the starting date and this is the date when the template will start tracking the PTO balances from so whenever you want to start using this template that is the date you put in here anything before any PTO that the employee took before that starting date will not be counted and so this is the date when you are beginning to use this template the employee details employee name straightforward employee hire date please enter the actual hire date even if it was many years ago before you start using the template we want the exact hire date because we will be calculating the tenure the starting balance is the balance how many hours of PTO when we start using this template so for example in this case I'm you are my starting date is third July so as of 2nd July end of 2nd July what is the starting balance meaning what is the PTO balance for your employee so this is provided so that if you are using another solution or a tool or a software to track PTO balance right now but you want to switch to this template then the date when you are switching you put that as a starting date and then whatever balance the employee already has in your previous tool then you can put that in here so that you can continue to use this template moving forward by carrying over the information from your previous tool if it's a brand new employee then you will start with a zero balance so you can put zero now the regular work hours this is a an option that I've provided to make sure that you don't have to manually enter data if the employee always works eight hours a day if the employee is working then you don't need to you know put 8 8 8 every day so what we have provided is a default work hours option so if the employee by default works 8 hours a day then you can put that in there if your employee works varying hours every day you can you can also put 0 here and then I will you know shortly tell you how you can enter the varying work hours by the employee so in in summary if your employee has a default number of work hours on a daily basis then make sure that you put that in here employee termination date so that this is if the employee has left the company then you can put that in there so that the template will stop tracking PTO after that date the PTO policy the PTO accrual period so I've covered all these settings options in the previous video so I'm not going to go deep into this in terms of what they are and why they or what they mean I'm gonna more focus on what options the template provides so in terms of the PTO accrual period you have four options so you can do a weekly accrual or every two weeks twice a month or monthly if you're doing weekly accrual that would that's what I've chosen now and then you'd have to enter the when is the first accrual date so I went out for example 5th july 2019 so I'm beginning to track from 3rd July but my accrual periods are every Friday so I put in my first Friday here and then you can see on the right side what will be my first six accrual days so you can see that it's v 12 1926 2nd and 9th August and these are all Fridays so that makes sense that we are doing a weekly accrual so let's say I want to change it to every two weeks now you see that it still starts on the 5th July which I have given as input all these gray cells are inputs but it now jumps every other Friday so fifth nineteen second sixteen and all so this is how the template creates the accrual days and accrual windows and make sure that you check here and ensure that it meets your company's policy so if I want to do twice a month then I don't need to enter a date instead I have to give two days of the month so here I've said 15 and the last day and 15 31 1531 so basically if the month is a 30-day month it will automatically go to the 30th so because we put last day so let's say for example I want to do 1st and 15th I can do that and now it becomes 15 July is the first one 1st August 15th August and all so the template will automatically figure out the accrual days there's also another option to do monthly and if we do 15 monthly on the 15th so it goes all the way if I do monthly first then every 1st will be the accrual days so this is how the template decides or calculates automatically the accrual windows so now that we talked about the accrual period then we go into the accrual limit I'm sorry rollover limit the rollover policy has three options so zero roll over roll over limit and unlimited zero roll over limit means you can specify a specific number of hours to be carried over and then unlimited means everything will be carried over so if I do zero then every nothing will be carried over in terms of when the roll over will happen we have two options so calendar year or the work anniversary so you can either do it based on the from December 31st to 1st January it usually will roll over or you can do work anniversary which means for this employee 3rd July is the hire date which means 3rd July every year the roll over will happen probationary period is the period where you employee is not going to earn any pto so you can put 90 days if that's the case if if there is no probationary period then put zero maximum allowed pto balance is the maximum pto balance limit at any time that their employee cannot go beyond so that's you know you can type in your hours there's also an option to rounding the pto balance two different decimals so you can put in your values like if you want to do it three decimals or two you can just type that in there the last one here or the more in a very important one though is the accrual rate based on tenure so this means that the employee is employee who is starting which means they have complete at 0 months which means they are brand-new they will earn one hour of pto for forty hours worked but then once they complete 12 months of tenure they will earn two pto hours or forty hours worked once they reach 24 they will earn three so all the employees after 24 months of tenure will earn 3 pto hours per 40 hours worth this is the tiered accrual rate structure based on tenure which means that the loyal cost in please will be rewarded more if in your organization you have different tiers you can enter them here by default you can have up to six tiers but if you need more you can unprotect the sheet and extend the table and add more tiers however if all your employees are having the same accrual rate then you can just for example remove this part and that means every employee will earn the same rate which is one for every 40 hours work so I'm gonna keep that there for now there is some data entry validation here so if we enter the wrong dates here and the these two dates cannot be out of sync for example let's me go back to weekly so if I put in a very let's say I put December my first approval is happening in December but I'm starting to track on 3rd July so that doesn't make sense for a weekly and so you will get an error if there is an error please fix them before you move to the next sheet so ensure that your the dates you are entering here are valid dates now it says no data entry errors so we should be good to go so now that's about the policy settings the more complicated settings part now let's go into the next month the weekends and holidays are very straightforward you just choose which days or weekends in your company and in this case I've chosen Saturday and Sunday to be true and company holidays are the list of days that your company you know there's not equi require employees to work so you can just put in all these dates here and you can enter for multiple years just like I've done here and you can just keep adding the dates and if once you reach to the end you type in the next one the table will automatically expand that's straightforward the number of hours worked is the next input one thing to note here one important thing to note here is that you are only going to enter the adjustment so for example if I show you again the default work hours for this employee is 8 that means the employee is going to work for 8 hours normally every weekday and if it's a holiday or a weekend it will not count as work hours it will become 0 now if you want to adjust let's say for example if the employee is working in this case minus 8 means that the employee this is not going to work on 14th October and that means I have to put minus 8 because I already have default 8 hours so the employee total will become 0 for that day because I'm doing minus 8 so enter a negative value if the employee worked less than the default hours enter a positive value if the employee worked more so for example in this case on November 1st the employee worked for totally 9 hours but I'm not going to enter 9 here I'm going to enter 1 because I have already entered 8 as my default in the settings so this will simplify and reduce your data entry because you're only going to enter the work hours when it deviates from the default hours otherwise if the employee is always working the regular hours then you just don't have to do anything in the sheet next we go to the PTO used this is the these are the dates when the employee is actually using the PTO hours so this means from 25th October to 2050 this sheet can handle multi-day vacations so if your employee is doing it only in wall taking a PTO for one day then you will enter the same date so 25 to 25 it's just one day and the number of hours every day that the employees taking PTO is one hour so that means on 25th October the employee took one hour of PTO this means that from seventh May to 8th May 2020 the employees planning to take one hour of PTO everyday so one over on seventh one hour on eighth this is very important that you need to be entering that date daily hours not the total so you should not be entering two hours here because there are two days you should be entering the daily so that means one hour each on 7th and 8th in in all these sheets you can and you can add more columns easily by just typing in another column here and then entering the data as long as you are entering it with the right most to the end it's ok please do not insert in between the existing date columns now that we have entered all the information now we are ready to go and take a look at the PTO report so the PTO report is fully automated so let's break it into two sections the one in the top does the employee summary let's say which shows you the balance current balance so today is 24th November and so you can see the current balance for that employee you can also type in another date and say ok as of then what will be my projection so it will project automatically for that specific date so you can customize this and then this is already automatically set today and the balances are always the balances at the end of that day so end of 24th November it'll be six hours the details provided here are policy details so whether you have the weekly accrual period or monthly everything that you entered in the settings are also displayed here so that you can make this one-page printout which you can give it to an employee which shows not only the balance but also the policy the role or policy probationary period maximum balance default hours work everything is displayed and here we have employee specific details like the date when the employee was hired when is the employee eligible to accrue from because there could be a probationary period and then the first day of a cool is this day so everything is shown it also shows the from when this template is tracking PTO from this is our starting date now the bottom section is more interactive where you can type in two dates so from date and to date and then this will allow you to customize or make make this calendar dynamic so for example I went to a first July to 30th 30th June 2020 so that is what I will see in this calendar view and it will show the number of hours totally worked and then the number of PTO hours used and then the black background filled dates are the dates when the approvals are happening so October 4th 11th 18 so these are the dates when the accrual are happening and we don't see anything in the first three months because we have a probationary period of 90 days and that's why there's nothing shown on that and then the other option to customize this view is basically you can change it to say do I want to see the work tower so how many hours the employee worked and then it will now show the number of hours worked by the employee if you want to change it to balance change this is how many hours by how many hours that the balance change and so for example when I change it to PTO used I can see that the employee used October 25th one hour of PTO May 20 twenty seventh and eighth of May one hour of PT oh so if you remember that as the PTO used we entered here everything automatically comes to your report then on the right side we have the start and the end balances so if you we look at the beginning of that month and then the end of that month what is the balance so you can see that it goes from zero to one let me also show balance change so you can see that the balance that is changing once a week then at the end of the month we had one our PTO balance and then the next month it became seven Levin and then it went down to five again and this is because there is a rule over zero roll over policy so at the end of December we had eleven and then when we begin January it's 11 however the first window of January we will apply the rollover policy so since we had zero that's where the balance is going down and then at the end of the month we still came back up to five because we have earned the balances like five weeks of work and that added up to five hours of PTO and similarly it goes on and if you have like a maximum limit that'll automatically get applied so the balances at the beginning and the end of the month are shown here and then they are also displayed here in a you know chart at the bottom where you can see the balances going up or down now this covers the PTO report and in the last sheet which is optional is if you want to make any adjustments so for example any specific date if you want to make an adjustment so you don't have to make any adjustments for normal working or PTO used because you're already entering them here right however let's say the balance that the employee shows today let's say it's a six hours but you are making an ad-hoc adjustment so you maybe you're saying I'm gonna reward my employee with two extra hours because he has done a great job or she has done a great job at work and you may do such adjustments in that case you can come in and enter that information he put a date and then it'll automatically add to the balance so it will just it will get displayed here on the balance as the you know we depending on which date you put in on that date the balance will get updated using your adjustment so that covers pretty much everything we have in this template just to do a quick recap is enter your policy settings ensure that you know the cool days are matching your expectation and then you go to and enter your weekends and holidays if the employee is working different hours enter the work our adjustment and every day that the employee has taken PTO are planning to take in the future put them in here and then you can go to the PTO report automatically see the current balance also project a balance for the date that you want to see and you can also customize this calendar for any from and to dates and you can change what you want to display on the calendar and you can print or export to PDF it's just a simple excel file there is nothing there's no macro or hidden code all the other features would work and if the sheet is protected if you want to unprotect it you can do that using in Tzara as the password however before you make any changes ensure that the formulas are not broken and you always should make a backup copy before you make some changes you get you are free to change all the colors and formatting and the fonts and you can customize it ing to your design needs as well if you have any questions about this template please post your questions in a comment section below if you have any feedback if you find this template useful please subscribe to this channel and then share this with your friends and you know it'll be great to get your feedback in the comment section if you're interested in the template but will track multiple employees in one file please post it in the comments and I am working on such a template to be published thank you very much for watching this video I'll see you in the next video