An excel dashboard to display employees Incentive payout
Incentive or Performance payout dashboard/report is very critical for any organization. This dashboard has been designed for 5 incentive riders like – Calls, AHT, Sale, Quality Score and Client Escalations. Each metrics have different weightages. On the base of each metrics, Total Score is calculated and on the base of Total Score final Incentive amount it calculated for Eligible Employees (Who have less than 3 Absent count). All the weightages Maximum Payout amount, Eligibility criteria and Payout% is dynamic and available on Weightage sheet tab.
Key Functionalities of Incentive Dashboard:
Below are key functionalities of Incentive dashboard-
1. Index:
In the index sheet tab 3 button(Summary, Data and Weightage) are available which have hyperlink to the respective sheet tab.
2. Summary :
In Summary sheet tab 3 circular cards are available which display process level information for Total payout, Average payout per employee(for Eligible employees) , Average payout per employee(for Eligible employees) . In the below section a Metrics drop down available for Calls, AHT, Sale, Quality Score and Client Escalations. A Line chart which connected with metrics drop down which shows Frequency Analysis.
A beautiful Speedometer Chart available on the bottom-right side. This speedometer shows the Eligible Employees%.
3. Data:
Data sheet tab is the main sheet tab in Incentive dashboard because all the important calculations to calculate the final payout of employee are available in this sheet tab. In this sheet tab there 2 sections – Input data and Formulas.
Input data has the headers’ color dark blue. In this section employee level information like – EMP Name, A (absent count for the month) , Calls, AHT, Sale, Quality and Client Escalation to be entered manually.
Formulas has the headers’ color sky blue. In this section employee level information like – Calls Score, AHT Score, Sale Score, Quality Score and Client Escalation score, Total Score, Eligibility(on the base of absent count) and Payout will be calculated automatically on the base of input data and weightages.
4. Weightage:
Weightage sheet tab is also very useful because any change in the number on this sheet, will make direct impact on payout amount.
There are 9 small tables available in sheets.
- Weightage : In this table metric wise weightages are available. Sum of the weightages for all metrics always must be 100%.
- Calls : This is used to calculate the Calls Score in Data sheet tab.
- AHT : This is used to calculate the AHT Score in Data sheet tab.
- Sales : This is used to calculate the Sales Score in Data sheet tab.
- Quality : This is used to calculate the Quality Score in Data sheet tab.
- Client Escalation :This is used to calculate the Client Escalation Score in Data sheet tab.
- Eligibility : This table is used to check the employee eligibility for incentive. If employee has more than 2 Absents count in a month then he/she is not eligible for payout irrespective of Total Score.
- Payout amount% : In this table first column is for Total Score and second is for % of Max Payout. For example if an employee achieved Target Score between 40% and 50% then he/sheet will get 20% of Max Payout.
- Max Payout : A maximum amount which can be achieved by an employee.
4. Support:
Support sheet tab is a hidden sheet wherein few calculation have been done to create the Summary sheet tab.
