Tiered Commission rate

[Ang Ang]

Hi Excel Expert,

I need help on the Part 1 & Part 2 of my attached sample tiered commission rate.


Part 1: How to calculate the tiered rate?

Total annual variable = 120,000
Annual Quota = 1,600,000
Rate = 120,000/1,600,000 = 7.50%


If my commission = (110,000/120,000)*3% the rate would be 2.75% blended rate for sales between 0 -480,000

Part 2:
any orders between 0-480,000 (480,000 sales) would be paid at 3% or blended rate
any orders between 480,000-1,100,000 (next 640,000 sales) would be paid at the next rate of 8% or blended rate

How can I showed the commission tiered rate and commission amount?

 

[NARAYANK991]

Hi ,

Your detailed description assumes there are financial people who will understand all the terms you have used.

Instead , if you can describe your requirement in terms of worksheet cells , with reference to the workbook you have already uploaded , you might get an answer faster.

Narayan

 

[Hui]

Ang Ang

Firstly, Welcome to the Chandoo.org Forums

Your post has been cross-posted, which means it has been seen elsewhere
This is considered poor practice, as it can waste peoples time, which could be spent elsewhere, especially if you get a solution and don't notify us.

I encourage you to please read the site rules at:
http://forum.chandoo.org/link-forums/new-users-please-read.17/

 

[Ang Ang]

Thank you Hui.

Below is the link to my other post:
http://www.msofficeforums.com/excel/36211-tiered-commissions.html#post117572

 

[Hui]

Yes, We knew where it was.

 

[Ang Ang]

Hi Excel Expert,

I have updated my file and example for the commission tier. I hope someone can help me.


Part 1:
For example:
Under Sales Rep= Sandy, I would need to add cumulative sales based on the sales rep. I was able to add the cumulative of sales but not able to figure out a formula sum by the sales rep.

order 180 then another order 900 so the total order = 1,080

sales rep Sales Cumulative Sales (need formula) Commission($)
david 100 100 3
sam 200 200 6
sandy 180 180 order = 180 5.4
david 500 600 24
sandy 900 1,080 = 900 + 180 = 1,080 62.40
sandy 300 1,380 95.50
sandy 620 2,000


Part 2 (need formula):

Sales between 0 - 480 paid at 3% commission rate
Sales between 480 - 1,120 paid at 8% commission rate

example: Sandy has total sale 1,080 (1,080 - 480 = 600 would be paid 8%).
480 * 3% (tier 1) + 600 * 8% (tier 2) = 62.40