Excel Challenge 8: Create a Performance Incentive Worksheet

Ready for this month’s Excel challenge? 

How about helping out with an incentive for agent performance at a contact center? The data is already there: you just need to pull them together to calculate who qualifies for the incentive and who doesn’t.

Here’s what to do:

• First, watch the challenge video and read the instructions below the video.
• Review the previously published video(s) and article(s) suggested.
• Download the Excel worksheet you will use to complete the challenge tasks.
• Put yourself to the test!

Want to chat about your approach and process with other Excel-heads? Join our Slack channel to share your insights and questions with like-minded learners.

The challenge

The download file has four worksheets: Attendance, Call Quality, Sales, and Incentive. The first three contain the raw data used to measure each agent’s performance, and the last sheet has the criteria for awarding the incentive.

If you’re pretty comfortable with the VLOOKUP and IF functions, this shouldn’t be too difficult. If you need a refresher, check out these videos:

VLOOKUP function

• XLOOKUP vs VLOOKUP in Excel - What's the Difference?

• What are VLOOKUP and HLOOKUP Functions in Excel?

• VLOOKUP Exact vs. Approximate Match

IF function

Here’s how the incentive program works

Agents can earn a maximum of 5 points per one-month period. 

Attendance percentage is calculated based on the number of days the employee came to work, measured against the number of business days. Agents get 1 point if their attendance is 95 percent or higher, and zero if their attendance is below 95 percent.

Call quality is based on the employee’s quality score as measured by the Quality Assurance Department. Agents get 2 points if they score 100 percent, 1 point if they score between 90 percent and 99 percent, and zero if their call quality is below 90 percent.

Average Daily Sales checks the employee’s total sales against the number of days they came to work. Agents get 2 points if they average 2 or more sales per day, 1 point if they average less than 2 but at least 1 sale, and zero points if their daily sales average is below 1.

On the Incentive worksheet, Column J should have the total points earned, formatted with green icons for 5 points, amber for 4 points, and red for fewer than 4 points. 

Employees with 5 points will get a $100 bonus, and those with 4 points will earn $50.

Ready to roll up your sleeves on this one? Which formulas should go where? They’re a little more involved this time, so take your time and double-check your results for accuracy.

Download the file to get started!

The solution

We hope you'll enjoy taking part in this challenge!

Stay tuned to the GoSkills Excel Resource hub for more Excel challenges, and check out our range of expert-led Excel courses for all skill levels to further sharpen your skills.

If you enjoyed this challenge, try the Basic and Advanced Excel course to help you learn more essential formulas, functions, and practical, real-world Excel skills.