Calculate bonuses based on company and individual results
This excel spreadsheet template is set up for annual bonuses or variable compensation plans, that are based on both corporate and individual performance (can be used with any rating system), and that are based on employees having a bonus target percentage based on their salary band or grade. Make it easy on yourself and save time with a template that is already set up with all the formulas you’ll need.
Easily manage the bonus program for your company
If part of your job is managing bonus programs for your company, this template is set up to make everything easy for you to administer – from calculating the bonuses based on company and individual results to calculating the total amount over or under budget.
Quick customization with the Bonus Key Page
You will be able to customize a lot of the features, however a Bonus Key page is set up so you can easily add in/ customize how the bonuses are calculated. This page includes 3 sections –
*Corporate performance results – here you will enter 1 percentage representing the financial results and/or how much the bonuses will be funded. At 100% all targets have been met, and if you put above 100% the bonus pools will fund above target.
*Individual Performance Key – here you can enter your performance ratings (up to 20) and a recommended percentage related to the performance rating. Example – If a rating of 3 is on target, you would enter 100%, you could enter 75% for lower ratings or 110%+ when the performance rating is above and beyond expectations. This will become the ‘HR Recommended increase’ section that can help guide the managers when determining the final payouts.
*Bonus Weights and Targets by Salary Band (or grade) – In this chart you will list out all the salary bands that are eligible (up to 20), the bonus target percentage per band, as well as the weights between corporate performance and individual performance.
Color-coded for ease of use
This template consists of a few sections, however everything is color-coded to make it easier to understand –
Grey cells – contain the formulas or links Green cells – where you’ll need to enter data White cells – there for your information but not vital to making all the links work. Yellow Cells – where managers would enter their recommended or proposed amounts, when the file is distributed in sections to different areas in the company.
Required employee information
You will need this information to enter or copy/paste into the template in order for all the links to work correctly:
An employee file containing – Employee ID, base pay (or annul earnings for nonexempt), salary band or grade. An employee file containing – Employee ID and performance rating.
This template can be used for up to 1,000 employees in 1 file, however if you need to fit more in 1 file let me know and I can add that in as an option. You will be able to save different versions of the file.
Each purchase will come personalized with a name or the company name of your choice. All templates are to be used by the company bought for only and are not to be distributed outside the organization.
Custom Changes – I would love to help customize a spreadsheet for your specific situation! My background is in Compensation/HR. I earned the CCP (certified compensation professional) in 2014 and PHR in 2004. I have almost 10 years experience working in a Compensation Analyst role at a large cement manufacturing company and a retail company. I’ve been creating templates and custom excel work for all industries since 2013. Feel free to contact me here for more information or a custom quote.
You will receive an Excel spreadsheet, along with PDF directions via email. Open office versions are available upon request.