If you are looking for a simple annual bonus planner/calculator for your organization, read along because your search ends here!
Download this free Google spreadsheet template and with simple input data, get the bonuses calculated for all your employees for a given review period.
This template supports the most commonly used method of calculating the employee bonus: based on employee, department and organization’s performance.
Based on the performance settings defined as per your organizations’ requirements, view the detailed statement on the bonus calculations.
View the fully automated dashboard based to aid you in informed decision making, all within a few minutes!
Features of Employee Bonus Plan Template
All that’s needed is a single Google sheets file, and this template is packed with a lot of amazing features:
- Simple data entry process
- Single Google sheets file for all employee data entries for one review period.
- Customize settings based on organizational standards
- Customize the bonus payouts based on employee grades.
- Customize the weightages for each grades by individual performance and of the department and organizations’ performance.
- Practical Features
- All the components of the bonuses along with the overall bonus payout per employee, are automatically calculated.
- Dashboard view with bonus details based on Grades, performance for reporting.
- Ease of Use
- One time settings for employee and performance settings.
- All employee data in a single google sheets file for one review period.
- Re-use a copy of the same file for different review periods.
- Automated Calculations
- Fully automated bonus calculations and interactive dashboard.
How to use the Employee Bonus Planner Template
Once you download the template, please make a copy of the same in your drive to use. (i.e. to gain edit access)
Here is the overview of steps on how to use the template.
- Enter all the employee and job related attributes in the Settings sheet.
- In Performance settings sheet, enter the bonus targets allowed along with their weightages based on department and organization.
- Enter the achieved targets for each department and the overall organizations’ in the Achieved sheet.
- In the Employees sheet enter the relevant employee details for the particular performance review, whose bonuses are to be calculated.
- Once these details are entered, view the bonus % and achieved values calculated based on the given inputs and the interactive dashboard fully automate.
Step 1: Enter Settings
Enter all the employee related details in the Settings sheet as shown below:
Step 2:Enter Performance Settings
In the Performance sheet, enter the target bonus for each grade; the maximum payout % decided up on. Along with these enter the split i.e., the weightage for each grade for individual performance, department and the organization, a sample of which is shown below.
In the above scenario, for the CXO job grade, each employee belonging to that grade is entitled to a maximum of 20% bonus from their salary. Which, further is compartmented into weightages for the individual performance(10%), the department’s (10%) and the overall organizational performance (80%) for the given review period.
It can be observed here in this example, as the grades move up in the organizational hierarchy more weightage is given to the organizational achievements than the departments’ or individual performances.
This may vary based on your company’s bonus payout policies.
The maximum range applicable is the percentage above the set target bonus which can be allowed, in case of employees whose performance exceeds the expectations.
In the example, the maximum payout range is input as 150% meaning, for the grade CXO, the maximum bonus payout can be 150% of 20% i.e. 24% of the total salary.
Step 3: Enter Organizational and Department achieved
Now, for this data entry section, we move to the Dept/Org Achieved sheet.
This is also straight forward: enter each department’s target achieved as a % value.
Also, in the same sheet enter the overall organizational achievement % and the maximum payout bonus value.
Step 4: Enter Employee Data
In this sheet, enter all the relevant employee details as per the given column header. Also, enter the individual target achieved % for each employee for that performance review period.
Once these data are entered the google sheet automatically calculates the overall achieved bonus value and its % based on the weightage vs. achieved details as entered in step 2 and 3.
This template also gives a detailed statement of the bonus payout for each employee. The template is also designed to show if the bonus exceeds the maximum payout range value as defined.
The sheet also calculates the bonus achieved as a split up between individual, department ad organizational achievements. This is automatically calculated by the sheet based on the achieved % of the department and the weightage assigned in step 2.
Step 5: View Dashboard
The dashboard, once all the data gets input, is fully automated; view the total number of employees, the total bonus payout value and the % of bonus used from the maximum payout value (entered in step 3).
The dashboard shows the overall bonus % spread as count of total employees for the review and the same across different grades.
As shown in the below screenshot, the dashboard also gives the total bonus payout value as percentages per department and grades. For example, the grade Vice-president has received 23% of the total bonus payout value of $1,009,479.
The dashboard gives the list employees with top bonus value based on Grade. Along with this, get top individual performers, also based on grades and performs for further analysis.
The charts in the dashboard can be further drilled-down using multiple filter options (given to the right) on Location, Department, Job Type, Job Category and Leave types.
Please note that the template supports up to 1000 employees and 20 grades and 20 departments, by default which can be easily extended. Please write to us at firstname.lastname@example.org for any customizations / support.
Performance Dashboard Google Sheets Template
- Track key performance metrics and reports using a simple Google Sheet template.
Salary Structure Google Sheets Template
- Salary structure calculations made simple, supports the most commonly used scenarios for salary calculations.
I hope this template is useful. If you like it, please share it with your friends. If you have any suggestions or questions , please leave them in the comments section. I will continue to enhance this template based on feedback.