Tiered commission plan paid monthly

[Cmar]

Hello all,

I have been trying to calculate this worksheet for the past couple days. This is a tiered commission plan paid monthly. So depending on the salesperson's cumulative sales, that will trigger the rate of pay for that monthly salary. I've attached the spreadsheet for help.

I need to get some sleep so I would greatly appreciate the help!

[AliGW]

You need to add some specific detail to the spreadsheet: the cell where you want this to appear, a mock calculation and what you would expect the result to be.

[Cmar]

I am trying to calculate the salesperson's monthly commission. The rate would be determined on monthly sales plus the *** sales.

For example, in cell C4, the sales person achieved $1.8M. So he would get commission calculated at 1.0%, 2.25%, and 3.5%. Keep in mind that he already got "paid" on $171k of April's revenue.

I tried to break the formulas out in rows vs. nesting the formula within a cell. I was able to make Tier 1 work, but struggled with Tier 2 etc. See my efforts of Tier 1 on rows 17, 18, and 20. Line 20 is the actual commission that will be paid. D20 does not have any commission because he would stop earning commissions at Tier1 rate (the limit is $10,449, see cell f5). So the remaining balance would have to get calculated at Tier 2 and Tier 3 .

I would like the answers to appear on rows 20, 21, 22, and 23. I've titled the rows.

BTW, these are just made up scenarios...I wanted to make sure that the spreadsheet would be able to withstand the various scenarios, windfall and all!

I looked thru this site, and will try the sumproducts (although I've never used it) and a lookup table.

thanks for your help!

[mattbau43]

You may need to reformat you data arrangement. I am unsure what you are trying to accomplish. Also, your reference to C4 being 1.8M is not accurate either.(it says "End" in C4 on my copy)

Id like to help but some clarity is needed

[Cmar]

Hi Mattbau43
$1.8M is in cell c14 not c4, my bad.

Sure I'm open to rearranging the data. So long as the answer is correct and automatic once we enter in the achieved monthly sales in row 14.

Let me know if you need further clarification.

thanks so much!

[shg]

	B	C	D	E	F	G	H
4	Ann Sales	Comm	Delta
5	$ 0	1.00%	1.00%				D5: =C5 - N(C4)
6	$ 1,045,000	2.25%	1.25%
7	$ 1,567,500	3.50%	1.25%
8	$ 1,881,000	5.66%	2.16%
9	$ 2,090,001	7.36%	1.70%
10
11	Month	Sales	Cumu Sales	Comm	Eff Rate
12	Apr	$ 171,000	$ 171,000	$ 1,710	1.00%		D12: =N(D11)+C12
13	May	$ 1,800,000	$ 1,971,000	$ 36,563	2.03%		E12: =SUMPRODUCT( (D12 > $B$5:$B$9) * (D12 - $B$5:$B$9) * $D$5:$D$9) - SUM(E$11:E11)
14	Jun	$ 180,000	$ 2,151,000	$ 11,225	6.24%		F12: =E12/C12
15	Jul	$ 1,800,000	$ 3,951,000	$ 132,480	7.36%
16	Aug	$ 186,010	$ 4,137,010	$ 13,690	7.36%
17	Sep	$ 179,740	$ 4,316,750	$ 13,229	7.36%
18	Oct	$ 177,650	$ 4,494,400	$ 13,075	7.36%
19	Nov	$ 163,020	$ 4,657,420	$ 11,998	7.36%
20	Dec	$ 169,290	$ 4,826,710	$ 12,460	7.36%
21	Jan	$ 175,560	$ 5,002,270	$ 12,921	7.36%
22	Feb	$ 177,650	$ 5,179,920	$ 13,075	7.36%
23	Mar	$ 612,730	$ 5,792,650	$ 45,097	7.36%
24			Total:	$ 317,523

[Cmar]

Shg
Thank you thank you thank you!!! It worked perfectly

[shg]

You're welcome.