Add Bonus Plan Signature Service with airSlate SignNow

Esign incentive plan

hey there it's Aaron with time-saving templates and today I'm gonna go over an example of the variable compensation bonus calculation spreadsheet template and you'll be able to find this template at time-saving templates calm in the Human Resources spreadsheet section um so let me get into the file I'm gonna go over some examples of how to use this spreadsheet basically mmm almost all of my templates are color coded they're color coded so it's easy for you to quickly tell that the light green cell is where you need to enter your information and where something needs to be entered in order for other calculations or links to be made and then the white cells or blank cells are just there for your information if you want to be tracking that information and then the gray cells are formulas that you want to be careful not to write over or delete the formulas and most of the time my templates come with the formulas protected so this page I leave unprotected in case you need to make some specific customizations you can do that on this one and so those are the three color coatings that I usually use throughout most of my templates now with this one I do have a yellow column here if you're working in HR and you're distributing the spreadsheets to different managers who are filling it out that you actually only want them to enter their information in the yellow column that's just to make things easier when you're distributing the file and then also receiving it back from them so that is why that one is yellow so that's specific to this file but you'll see that I've already entered some sample data some example employee numbers some salary bands and the current employee salary this is the bonus details page so the bonus is calculated on several things which were assuming that you have a performance management system with some sort of standardized ratings for employee performance for this file we're assuming it's it's an annual bonus so we're also making that assumption and we're several things you'll need to fill out and I'm gonna go ahead and fill it out so you can see an example the corporate achievement is gonna be how much the bonus is is funded for this one hundred percent ties to the corporate weighting so there's a two components to the the bonus and that's the corporate rating and then the also the individual weight weighting which looks at the performance rating of the individual and also depending on what salary band they are there could be different targets per band for the individual performance rating we also have a performance rating page where you would need to copy and paste the employee number and the performance rating they receive I believe that you can copy as many employees as you need it's just that right now it's going down to a thousand you can just drag these formulas down if you need it for more employees than that so let me go ahead and copy the sample information and so you can see this is a sample that's already filled out so I have the individual performance key we're just doing a rating of one to ten you might have letters for your ratings you might have a combination of letters and numbers you might have you can even put words you could have excellent needs improvement you could have it be whatever you want the point is that it needs to be the same rating that you have in this chart needs to be the same rating that you are copying the however you have it labeled and attached to each employee number on this page so so this performance rating here um also needs to get listed here in here so for this one I for the sake of simplicity I just have ratings one through ten one into we're recommending a zero percent increase for those up to ten high performers getting up to 150% increase our bonus for their individual weight and that is to incentivize the high performers and then say six is on target those are getting it a hundred percent so you may already have someone in your organization who manages the performance ratings and what they mean so you might need to look into that to determine how you want to set this up so you could have everybody be at a hundred percent you could have everybody getting something and you know you can change up these percentage however your organization determines is best for the salary bands uh let's just do VP to associate and I just have for example I've just listed out the alphabet you could have alphanumeric you could have a one A two you could have the dance or the grades could be numbers whatever your organization has determined for the salary band you also have the corporate and individual weighting ously these two added together needs to equal 100 percent so we have you know the top we're having VP at 75% of his target bonus is gonna be weighted it what the corporate achievement was and 25% it's gonna be what he got for his individual performance rating and then as you go down sixty forty percent this person gets there are these people in this grade get fifty fifty and then towards the bottom of forty percent corporate 60% individual so I'm gonna go ahead and copy all this and of course the target bonus perb and this information you would either have to fill out or copy into the file I'm just gonna fill something out here since we have these missing 5% 5% and so now that we filmed this out we also need to get the employee number in performance reading and I believe I had an example of this already over here okay I didn't get very far but I'll just add some more in for this example so you can see what happens here we need an employee number that's unique to each employee so looking back into the file now that we've entered the information and the other pages you'll see that the total bonus pool is now being populated this number is coming from column W you scroll right you'll see that now per employee ID the performance rating is getting populated so with that performance rating we're getting the HR recommended percentage of the bonus target based on the performance rating so this is what we're saying this is HR recommended you could change that to be just recommended it's just basing it off of what was their rating and this is a percentage that it's pulling in and then the manager still needs to enter if they agree with this recommendation they would enter the hundred percent here one hundred and thirty or maybe they say one hundred fifty maybe that's too much maybe they want to add more zero percent maybe maybe they don't want to give a zero percent maybe they want to give something still okay so as these numbers are entered you'll see it's also pulling in the corporate weighting that we had entered from this page based on their band so it's pulling in all this information the corporate factoring the individual weight the individual factoring and this is taking into account what's recommended in oh so that's just making a note that this will say zero until the the final amount that the manager is requesting is entered in here so the total actual bonus factored the corporate and personal weighting combined just click in the cell if you want to see what it's adding up the corporate factor an individual and then that's also pulling in the target bonus based on their band these columns are for the bone poll calculation so you'll see that was referencing here so that's just to help you see how that pool is being calculated okay so there's a reason that this proposed bonus is still zero even though we have actual bonus because we haven't finished filling out the length of service its prorating the bonuses are prorated based on a date range that you need to enter so the beginning and ending of the current year and this is to calculate proration so we're gonna say we're in 2019 January 1st of 2019 and then say it's ending January 31st of 29 so now that this is entered the length of service it's just giving everybody a full 12 months for this timeframe if I had somebody that started in the middle of the year say they started June 1st 2019 you'll see that they are now getting prorated for six months of the year so this column will be their actual bonus before it's prorated and even the bonus pool is not getting prorated so that's really something that's up to your organization are you going to well it gives you extra room to work with the bonus within the organization if you do allow for that bonus pool to not be prorated as we add that in the proposed bonus is gonna go up so let me see if we just drag this all the way down and say we're just giving everybody a hundred percent just for the sake of simplicity then we're now having a difference of thousand so that's basically how you would use this and if you have any other questions and even changes or customizations that you will would make to this please let me know because I'm always looking at adding new variations and versions and if you're wanting to know more about compensation also check out I have a free compensation metrics cheat sheet at time-saving templates comm slash free resources and you'll see a section for human resources and compensation and until next time don't forget that I'm here to help you streamline and save time when it comes to using your Excel spreadsheets thanks