I’m trying to make a spreadsheet to model a salesperson’s commission schedule.
The commission schedule is this:
In each quarter, a salesperson earns a commission based on what percentage of their quota the meet:
0% to 79% pays a commission of 2% of the total sales in that quarter.
80% to 99% pays a commission of 3% of the total sales in that quarter.
100% or greater pays a commission of 5% of the total sales in that quarter.
(These percentages are not cumulative: a salesperson won’t earn 2, 3 AND 5 percent for reaching 100% of the goals, just 5% total.)
My spreadsheet models this rule in two ways. It allows me to tweak the % for each quarter to see commissions, or to tweak the actual sales achieved per quarter to see commissions (model1 and model2).
But there is a hitch.
At the point where total commission earned PER YEAR (called the ‘threshold’) reaches $150000, any sales made after this threshold is met are paid at a straight 2% only. I have no idea how to calculate this possibility. It’s additionally difficult because a salesperson can reach 150000 in commission in any quarter, and a single sale could push the persons total over the 150k limit, effectively splitting the commission paid on that single sale between the 5% pre-threshold and 2% post-threshold levels.
Can anyone help?
(I have pasted the source code for my spreadsheet, created using HTMLMAKER, but it's not showing. I'd be happy to email anyone who wants to see it my model, but essentially I calculate the commission pre-theshold using a nested IF formula.)
Edited by Von Pookie
The commission schedule is this:
In each quarter, a salesperson earns a commission based on what percentage of their quota the meet:
0% to 79% pays a commission of 2% of the total sales in that quarter.
80% to 99% pays a commission of 3% of the total sales in that quarter.
100% or greater pays a commission of 5% of the total sales in that quarter.
(These percentages are not cumulative: a salesperson won’t earn 2, 3 AND 5 percent for reaching 100% of the goals, just 5% total.)
My spreadsheet models this rule in two ways. It allows me to tweak the % for each quarter to see commissions, or to tweak the actual sales achieved per quarter to see commissions (model1 and model2).
But there is a hitch.
At the point where total commission earned PER YEAR (called the ‘threshold’) reaches $150000, any sales made after this threshold is met are paid at a straight 2% only. I have no idea how to calculate this possibility. It’s additionally difficult because a salesperson can reach 150000 in commission in any quarter, and a single sale could push the persons total over the 150k limit, effectively splitting the commission paid on that single sale between the 5% pre-threshold and 2% post-threshold levels.
Can anyone help?
(I have pasted the source code for my spreadsheet, created using HTMLMAKER, but it's not showing. I'd be happy to email anyone who wants to see it my model, but essentially I calculate the commission pre-theshold using a nested IF formula.)
| Sales Commission Model 1 w gary and execs.xls | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| P | Q | R | S | T | U | V | |||
| 22 | %ofQuarerlyGoalMet | QuarterlyCommission | |||||||
| 23 | From | To | RatePaidAt | ||||||
| 24 | 0.00% | 79.90% | 2% | ||||||
| 25 | 80.00% | 99.90% | 3% | ||||||
| 26 | 100.00% | 1000.00% | 5% | ||||||
| 27 | Threshold | $150,000.00 | |||||||
| 28 | CommissionforSalesPastThresholdPaidat | 0.02 | |||||||
| 29 | |||||||||
| 30 | SalesQ1 | SalesQ2 | SalesQ3 | SalesQ4 | Totals | ||||
| 31 | Goal | $2,000,000 | $1,000,000 | $2,000,000 | $1,000,000 | $6,000,000 | |||
| 32 | |||||||||
| 33 | Model1:StartwiththePercentageofQuarterlyQuotamet | ||||||||
| 34 | ActualSales(donotchange) | $2,000,000 | $990,000 | $1,100,000 | $0 | $4,090,000 | |||
| 35 | TweakthePercentofGoalreached | 100% | 99% | 55% | 0% | 68% | |||
| 36 | QuarterlyCommissionwithNoThreshold | $100,000 | $29,700 | $22,000 | $0 | $150,000 | $151,700 | ||
| 37 | CumulativeQrtCommission | $100,000 | $129,700 | $151,700 | $151,700 | ||||
| 38 | |||||||||
| 39 | Model2:StartwiththeActualQuarterlySales | ||||||||
| 40 | TweaktheActualSales | $1,000,000 | $1,000,000 | $1,200,000 | $1,000,000 | $4,200,000 | |||
| 41 | PercentofGoalreached(donotchange) | 50% | 100% | 60% | 100% | 70% | |||
| 42 | QuarterlyCommissionwithNoThreshold | $20,000 | $50,000 | $24,000 | $50,000 | $144,000 | $144,000 | ||
| 43 | CumlativeCommission | $20,000 | $70,000 | $94,000 | $144,000 | ||||
sheet 1 | |||||||||
Edited by Von Pookie
