Sales Commissions Accumulative formula issues

[Naf59]

Hi,

I have attached my worksheet for reference

Background: looking for an accumulative sales target, so if one month the account manager does not reach the minimum amount he/she will not get commission for that month, for the next month as they reach the minimum accumulated target they will.

My issue is with the accumulative formula; I would like to pay from the “commission start amount” and 100% of the monthly accumulated target. Anything over 100% goes toward the next month. In the month 100% of the annual quota is reached then the accelerator factor kicks in and payment is on anything over the annual quota that amount.

Hope that makes sense

Thanks
Nick

[shg]

Maybe ...

	B	C	D	E	F	G	H
4	Monthly Quota	$ 31,000					C4: Input
5	Annual Quota	$ 372,000					C5: =C4*12
6	Comm starts at	70%					C6: Input
7	Comm start at	$ 21,700					C7: =C4*C6
8	Comm rate:	20%					C8: Input
9	Accelerator @ annual	125%					C9: Input
10
11	Month	Sales	Cumu Sales	Cumu Comm	Comm Paid
12	July 2013	$ 36,000	$ 36,000	$ 2,860	$ 2,860		D12 and down: =SUM(C12,D11)
13	August 2013	$ 23,000	$ 59,000	$ 3,120	$ 260		E12 and down: =(D12 - ROWS(C$12:C12)*$C$7)*$C$8 + MAX(0, (D12-$C$5)*$C$8*$C$9)
14	September 2013	$ 42,000	$ 101,000	$ 7,180	$ 4,060		F12 and down: =MAX(0, E12-SUM(F$11:F11))
15	October 2013	$ 38,000	$ 139,000	$ 10,440	$ 3,260
16	November 2013	$ 25,000	$ 164,000	$ 11,100	$ 660
17	December 2013	$ 31,000	$ 195,000	$ 12,960	$ 1,860
18	January 2014	$ 35,000	$ 230,000	$ 15,620	$ 2,660
19	February 2014	$ 36,000	$ 266,000	$ 18,480	$ 2,860
20	March 2014	$ 22,000	$ 288,000	$ 18,540	$ 60
21	April 2014	$ 33,000	$ 321,000	$ 20,800	$ 2,260
22	May 2014	$ 24,000	$ 345,000	$ 21,260	$ 460
23	June 2014	$ 40,000	$ 385,000	$ 28,170	$ 6,910

[Naf59]

Hi

Thank you, haven't tested fully but it looks like it is what I was trying to achieve

Thanks Very much
Nick

[shg]

You're welcome.

[Naf59]

Hi

I have attached your workings and in most cases it seems to work well, I have entered some numbers that may or may not happen but the result is not what I expected. I would assume the on target which is at 374k at any stage during the 12 months would give a total commission of 22,320?

Any advice would be appreciated

Thanks
Nick

[shg]

Because in the last two months the commission is negative (the cumulative commission goes down), and my assumption was that you don't withhold pay for negative commissions.

If you move those two zero-sales months earlier in the year, you get the result you expect:

	C	D	E	F
11	Sales	Cumu Sales	Cumu Comm	Comm Paid
12	$23,000	$23,000	$260	$260
13	$0	$23,000	-$4,080	$0
14	$0	$23,000	-$8,420	$0
15	$38,000	$61,000	-$5,160	$0
16	$10,000	$71,000	-$7,500	$0
17	$12,000	$83,000	-$9,440	$0
18	$30,000	$113,000	-$7,780	$0
19	$46,000	$159,000	-$2,920	$0
20	$100,000	$259,000	$12,740	$12,480
21	$48,000	$307,000	$18,000	$5,260
22	$40,000	$347,000	$21,660	$3,660
23	$25,000	$372,000	$22,320	$660
24				$22,320

[shg]

See the workbook at https://app.box.com/s/etysu13dpy3pl5i7f986. That may more clearly expose any misunderstandings.

[Naf59]

Hi

Thanks again for your support, I will try and get my head around it

Thanks
Nick