After the end of the year, it is time to start thinking about budgets and goals. It is also time to set goals for next years bonuses. The linked Excel workbook shows six year- end objectives (targets), along with a threshold and a stretch for each goal. The threshold is a level not quite up to target, but close enough to warrant some positive effect on the bonus. Stretch is the most optimistic outcome and has more of an effect on the bonus than just hitting target (you determine the effects).
The image below shows part of the dashboard. The six objectives, each have four radio buttons (Not Met, Threshold, Target, and Stretch). After year-end when the numbers are in, the appropriate buttons are selected. During the year however, you can gauge individual bonus by selecting various radio buttons for each goal.
As the various buttons are chosen, the scrollbar next to the goals updates each employee’s bonus instantly. You can scroll hundreds of employees, 24 at a time or one-by-one.
In addition, the each pay grade is summarized with dollar amounts, averages, number of employees in that pay grade, and the percent of the total bonus going to each pay grade.
The data is entered on separate sheets. Here you enter the six objectives (dark brown cells) along with Threshold, Target, and Stretch for each (light yellow cells):
Next you weight the six goals as to their importance, and select the percent of Target that Threshold and Stretch will apply (all yellow cells):
Next, you differentiate employees by pay grade by adding the percent of their salary each pay grade will receive:
Finally, enter the employees, their salaries, and pay grade (the pay grades are drop down boxes). The bonus is then calculated based upon the radio buttons selected on the dasboard:
The last two inputs are tables, so they expand as new data is entered. This does however restrict the full use of this dashboard to Excel 2007 or higher.
The workbook is titled “Incentives“.