Tiered Commission Rate Formula

[J Lee]

I have a spreadsheet that needs to calculate commission payouts. Commission rates are tiered - everything up through 50% of their goal earns 3%, 50-100% earns 4%, above 100% earns 5%.

My problem is that commissions are earned for an entire month, but payed out weekly. Can anyone help me with a formula for week 3's commission payout (column I)?

Thanks!

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Week 1	Week 2	Week 3	Week 4	Week 5	Month Total	Month Goal	Week 3 Commission Payout				% Achieved	Rate
2	John	10,000	20,000	20,000			65,000	80,000	700				Above 100%	5%
3	Laura	10,000	15,000	10,000			35,000	80,000	300				50.01% - 100%	4%
4	Allison	30,000	35,000	40,000			105,000	100,000	1,650				Up to 50%	3%
Sheet

 

[etaf]

how do you pay weekly on a monthly goal ?
do you have a 4 week month or are they calendar months

John for example - has sold 65K , but the months target is 80K - so would you Pay 4% of the 20K
then in Week 4 - if john sold 40K - thats over 100% so 5%
would you then recalculate the months figures
as
week 1 - you paid 3% of 10k
Week 2 - you paid 3% of 20k
week 3 - you paid 4% of 20k - so you also owe 1% for week 1 and 2
week 4 - assuming 40K - over 100% - then you owe 5% for week 4 , 1% for week 3 and another 1% for week 2 and 1

is that how it would work ?

Week 5 - is that part of the Month goal as well
how do you work that

 

[J Lee]

We would pay the commission for what they sold during that week.

For John, the payout would have been as follows
Week 1 - $300 - 3% of 10000
Week 2 - $600 - 3% of 20000
Week 3 - $700 - 3% of 10000 + 4% of 10000 (since he exceeded 50% of his goal during this time period)

For Allison, the payout would have been
Week 1 - $900 - 3% of 30000
Week 2 - $1200 - 3% of 20000 + 4% of 15000
Week 3 - $1650 - 4% of 35000 + 5% of 5000

In this example, weeks 4 and 5 haven't happened yet. But eventually I will want to translate the formula to those weeks as well.

 

[shg]

Row\Col	C​	D​	E​	F​	G​	H​	I​
3​	% Ach​	Rate​	Delta​
4​	0%​	3%​	3%​
5​	50%​	4%​	1%​
6​	100%​	5%​	1%​
7​
8​	Sales​	Month Goal​	Week 1​	Week 2​	Week 3​	Week 4​	Week 5​
9​	John	80,000​	10,000​	20,000​	20,000​
10​	Laura	80,000​	10,000​	15,000​	10,000​
11​	Allison	100,000​	30,000​	35,000​	40,000​
12​
13​	Commissions​
14​	John	DoNotUse	300​	600​	700​	0​	0​
15​	Laura	DoNotUse	300​	450​	300​	0​	0​
16​	Allison	DoNotUse	900​	1,200​	1,650​	0​	0​

The formula in E14 and copied right and down is

=SUMPRODUCT((SUM($E9:E9) > $C$4:$C$6 * $D9) * (SUM($E9:E9) - $C$4:$C$6 * $D9) * $E$4:$E$6) - SUM($D14:D14)

 

[J Lee]

Thanks shg
Worked like a charm!

 

[shg]

You're welcome.