I've been looking through multiple posts and am familiar with utilizing SUMPRODUCT to calculate commission tiers that pay out different rates based on sales thresholds.
As an example: Pay 10% from $0-$99; 15% from $100-$199; 20% from $200-$299
We have a new wrinkle in the comp plans this year that requires different rate payouts for each sale. Each now has a recurring component and a miscellaneous component that are paid at different rates (in addition to taking the tiers into account). I can't figure out how to accommodate it. Here's an example:
The more straightforward part first. Commission is paid out on a monthly basis. There is a running cumulative total for sales that is to be paid out based on the following tiers:
So if $190,000 is closed in January, commission is paid at 14%. Then with another $100,000 closed in February, the running total is now $290,000.00. $6,000.00 is paid at 14% and $94,000 is paid at 25%, etc for March, April, May, and so on.
The wrinkle is with the 2 components applied to every sale. #1 ) Recurring (to be paid at the tier rates ie. 14%, 25%, 40%) #2 Miscellaneous at 5%.
So now if you close $190,000 in January the breakdown components could be $150,000 as Recurring paid at 14%, and $40,000 as Miscellaneous paid at 5%. Once in February, The $100,000 closed at $80,000 Recurring and $20,000 Miscellaneous, would still pay the $20,000 at 5%, but the $80,000 would have to take into account the old tier of 14% and the new one of 25%. The way the different from Tier 1 to Tier 2 of $6,000 is accounted for (as you cross into the new tier) is by taking the percentage of Recurring and Miscellaneous for that month ie. 80% Recurring / 20% Miscellaneous for February, chopping up the $6,000 as 80% (paid at 14%) and 20%, then moving into Tier 2 with the 25% payout number for the remaining amount.
It's pretty confusing. I'd like to hopefully find a way to manage this with Excel. Thank you!
As an example: Pay 10% from $0-$99; 15% from $100-$199; 20% from $200-$299
We have a new wrinkle in the comp plans this year that requires different rate payouts for each sale. Each now has a recurring component and a miscellaneous component that are paid at different rates (in addition to taking the tiers into account). I can't figure out how to accommodate it. Here's an example:
The more straightforward part first. Commission is paid out on a monthly basis. There is a running cumulative total for sales that is to be paid out based on the following tiers:
- Tier 1 (14% Rate) $0.00
- Tier 2 (25% Rate) $196,000.00
- Tier 3 (40% Rate) $392,000.00
So if $190,000 is closed in January, commission is paid at 14%. Then with another $100,000 closed in February, the running total is now $290,000.00. $6,000.00 is paid at 14% and $94,000 is paid at 25%, etc for March, April, May, and so on.
The wrinkle is with the 2 components applied to every sale. #1 ) Recurring (to be paid at the tier rates ie. 14%, 25%, 40%) #2 Miscellaneous at 5%.
So now if you close $190,000 in January the breakdown components could be $150,000 as Recurring paid at 14%, and $40,000 as Miscellaneous paid at 5%. Once in February, The $100,000 closed at $80,000 Recurring and $20,000 Miscellaneous, would still pay the $20,000 at 5%, but the $80,000 would have to take into account the old tier of 14% and the new one of 25%. The way the different from Tier 1 to Tier 2 of $6,000 is accounted for (as you cross into the new tier) is by taking the percentage of Recurring and Miscellaneous for that month ie. 80% Recurring / 20% Miscellaneous for February, chopping up the $6,000 as 80% (paid at 14%) and 20%, then moving into Tier 2 with the 25% payout number for the remaining amount.
It's pretty confusing. I'd like to hopefully find a way to manage this with Excel. Thank you!
