Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Bonus % Targets



I am setting up bonus % targets which is calculated every 6 months - 50% based on profitability, 20% timekeeping and attendance and 30% team based targets (individual).

The trouble I am having is the formula to calculate the timekeeping and attendance. Staff are only allowed one day sick and 2 lates in a 6 month period. Which would be the most appropiate/accurate to use?

Many Thanks!



Selected Answer

The rules (1 days sick and 2 times late) are immaterial for your calculation because you do intend to reward those who are never sick and never late. The most simplistic way - just to show the system - is to say at each employee has 20 points (1 point = 1%) for time keeping & attendance. Each day missed costs 3 points and each time he is late it costs 1 point. At the end of the 6 months the perfect employe still has 15 points left and gets 15% of his bonus, or 75% of the bonus in this category.

You can convert this system to a monthly basis by adding the remaining bonus points for 6 months and divide the total into 6 (pay monthly bonus at average of past 6 months). For phase-in and new employees grant 10 bonus points for every month in which they were not eligible to earn demerit points.


Answer the Question

You must create an account to use the forum. Create an Account or Login