ABCDEFG
1
1. Describe the type of data in each column (as it would be in a CRM or a database).
2
See comments in column headers
3
4
5
2. Enrich the Deals table with the name of the Sales Rep and the sector of the account.
6
Sales Rep name can be found in column I (sales_rep) in the deals table
7
Sector of the account can be found in column C (sector) in the deals table
8
9
10
3. Calculate for each deal, the "boosted" amount to be used for commission calculation.
11
See column G (boosted_amount) in deals table
12
13
14
4. In the Sales Reps table, calculate for each Sales the total closed won MRR on new business deals, only those in February (a tip here).
Calculate the non-boosted total and the boosted total.
15
See columns G and H in sales reps table
16
17
18
5. In the Sales Reps table, display the February target for each Sales Rep.
19
See column I in sales reps table
20
21
22
6. Calculate the target achievement in February.
23
See column J in sales reps table
24
25
26
7. How would you easily show these same values for January, December...? (without duplicating everything)
27
Add a drop down that references the different dates/months and incorporate it into the formulas so that just the drop down would need to be toggled to update the data
28
29
30
8. Calculate the commission amount for each person in February.
31
Assuming this is meant to be "calculate the bonus"? If so, see column K (commission / bonus) in sales reps table
32
33
34
9. Improve the presentation at your convenience, and sort the Sales Reps by order of their target achievement.
35
Sales Reps sorted in order of target achievement % - see column J in sales reps sheet
36
37
38
10. Using a Pivot Table, calculate the total and average commission paid by country and by team, and the average target achievement.
39
See sales reps pivot table sheet. Breakdown also below
40
41
FranceGermanySpain
42
Total commision paid by country€31,000.00€19,300.00€25,400.00
43
Average commission paid by country€1,476.19€1,286.67€1,494.12
44
Average target achievement by country81.01%73.65%79.60%
45
46
47
Sales Reps FR - Team CobraSales Reps FR - Team PythonSales Reps DE - Team AnacondaSales Reps DE - Team ViperSales Reps ES - Team BoaSales Reps ES - Team Mamba
48
Total commision paid by team€21,900.00€9,100.00€12,500.00€6,800.00€14,400.00€11,000.00
49
Average commission paid by team€1,564.29€1,300.00€1,136.36€1,700.00€1,440.00€1,571.43
50
Average target achievement by team83.34%76.34%67.81%89.70%76.80%83.60%
51
52
53
11. Snake Corp. would like to better reward its top performers, without increasing its total commissions paid. What do you recommend?
54
Assuming that "Without increasing total commissions paid" refers to the total comp paid out to all reps across all countries - €75,700.00
55
56
Recommendation 1
57
I discovered (see pivot table here) that reps who achieve above 100% in Feb had lower targets than those who didn't achieve about 100%.

I'd want to dig into this further to see if this data correlates across all months, though raising targets to appropriately reflect achievement rates, especially for over-achievers should be considered.
58
59
Recommendation 2
60
Where ~32% of reps are only hitting between 50-70% of target and are subsequently earning a bonus of €1,000.00 the team should consider reducing the bonus rate for this threshold and reallocating it to the higher target achievement rates (70-100% and 100%+)
61
62
Recommendation 3
63
As per this pivot table, though there are boosted MRR's on specialized centers and hospital centers, the team has the most success closing opportunities in the office sector. Adjusting the boosted MRR's should be considered.

It should be further evaluated why specialized centers and hospital centers are considered high priority accounts for the company.
64
65
Recommendation 4
66
This may contradict "without increasing total commissions paid" but accelerators (with payout caps) should be considered for those who achieve above 100%.

Subsequently decelerators should be considered for those not hitting targets.
67
68
Recommendation 5
69
Non-monetary rewards could also be considered for those who achieve above 100%. Though an argument could be made that though this may not increase total commissions paid out, it could incur costs in other areas.
70
71
72
Additional
73
It may also be unrealistic to expect to "reward" top performers without increasing total commissions paid. If targets and payouts are adjusted, it may better incentivize reps across all thresholds. Commission payouts could increase across the board, especially if lower performing reps start to perform better. A total commission cap would have to be introduced though this may demoralize reps.
74
75
76
77
Breakdown
78
Percent of reps overachieving >1.032.08%
79
Percentage of reps between 0.7<1.0 16.98%
80
Percentage of reps between 0.5<0.7 32.08%
81
Percent of reps with no bonus <0.518.87%
82
83