Grand Company awards bonuses to its sales force based on sales performance. The measure of performance for
Question:
Grand Company awards bonuses to its sales force based on sales performance. The
measure of performance for awarding bonuses is the percentage achieved above the sales
target. Based on this metric, a salesperson is placed into one of five bonus bands and
awarded bonus points. Below is the award table:
Intervals | Bonus point |
0~20% | 0 |
20~50% | 10 |
50~80% | 15 |
80~100% | 25 |
100%+ | 40 |
It is greater than or equal to the lower limit and strictly less than the upper limit.
After all salespeople are placed in a band and awarded points, each is awarded a percentage
of the bonus pool, based on the percentage of the total points awarded. So, if there were
two salespeople (Bob and Mary). Bob got 20 bonus points and Mary got 30 bonus points.
The total is 50 points. Bob gets 20/50 or 40% of the bonus pool and Mary gets 30/50 or
60% of the bonus pool.
The bonus pool is $200,000 for this year. Below are the salespeople and % above target
sales values:
Name | % Above Target Sales |
Robert | 23% |
Chloe | 64% |
Marisa | 5% |
Ryan | 108% |
Sophie | 78% |
Build a spreadsheet with a table lookup to determine the following for each salesperson
(using both vlookup and index-match functions):
Please provide your answer by Excel File
Smith and Roberson Business Law
ISBN: 978-0538473637
15th Edition
Authors: Richard A. Mann, Barry S. Roberts