Dashboard for Bonuses

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.

buttons large

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.

Scroll Bar

 bonus

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.

Distribution

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):

Objectives

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):

weightings

Next, you differentiate employees by pay grade by adding the percent of their salary each pay grade will receive:

paygrades

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:

employees2

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“.

http://www.pistulka.com/Excel_Shared/

 

 

 

 

 

 

Don Pistulka
Don Pistulka

Retired Credit Union CFO - Finance
Background: over 40 years in investments, asset/Liability management, banking, securities trader.
Worked for: California Credit Union, WesCorp, CalFed S&L, Crocker Bank, Carroll McEntee, Federal Home Loan Bank Board (D.C.), Western Asset Management, Security Pacific National Bank.

Leave a Reply

Your email address will not be published. Required fields are marked *