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

Answer 1

As per Chegg rules, excel sheet cannot be attached. However, I am writing the formula which is to be used to use vlookup and match functions.

Intervals, I wrote manually. For using these functions, the same figures should be used without any spelling mismatches. Then only, excel can take it. The bonus share above is computed as a mere average...200000 multiplied by 12.5%=25000 and so on.

Hope it helps,

Thank you.