Forecast Bonus Calculator - Require a formula to calculate the bonus' due

**The_Snook** · 11-05-2013, 06:16 PM

Hi,

I'm trying to create a bonus calculator based on sales figures and differing bonus rates applicable based on the 12 month cumulative sales value.

I've provided a fair amount of explanation/what I require within the attached example spread sheet.

If you have any queries just give me a shout.

Thanks in advance,

Snook

**shg** · 11-05-2013, 06:56 PM

This look right?

	B	C	D	E	F	G	H	I	J	K
2	Rolling Cumu [$K]	Comm	Delta		Month	Sales [$K]	Rolling Cumu [$k]	Comm
3	0	0%	0%
4	5,000	1%	1%		Apr 12	455	455	-		D3 and down: =C3-N(C2)
5	10,000	2%	1%		May 12	149	604	-		H4 and down: =SUMIFS($G$2:$G4, $F$2:$F4, ">" & F4-350)
6	15,000	3%	1%		Jun 12	2,354	2,958	-		I4 and down: =SUMPRODUCT((H4 > $B$3:$B$6) * (H4 - $B$3:$B$6) * $D$3:$D$6) - SUM(I$2:I3)
7					Jul 12	191	3,149	-
8					Aug 12	2,288	5,437	4,367
9					Sep 12	1,829	7,266	18,291
10					Oct 12	668	7,934	6,682
11					Nov 12	555	8,489	5,550
12					Dec 12	755	9,244	7,550
13					Jan 13	926	10,170	10,960
14					Feb 13	357	10,527	7,140
15					Mar 13	1,136	11,663	22,720
16					Apr 13	448	11,656	(137)
17					May 13	675	12,182	10,521
18					Jun 13	813	10,641	(30,823)
19					Jul 13	249	10,699	1,165
20					Aug 13	738	9,149	(22,494)
21					Sep 13	1,757	9,077	(722)
22					Oct 13	1,296	9,704	6,275
23					Nov 13	599	9,748	441
24					Dec 13	174	9,167	(5,810)
25					Jan 14	2,321	10,562	19,572
26					Feb 14	214	10,420	(2,857)
27					Mar 14	708	9,991	(8,476)
28					Apr 14	689	10,233	4,738
29					May 14	1,516	11,073	16,813
30					Jun 14	818	11,079	106
31					Jul 14	1,534	12,363	25,691
32					Aug 14	2,222	13,847	29,679
33					Sep 14	1,117	13,207	(12,804)
34					Oct 14	736	12,647	(11,197)
35					Nov 14	2,376	14,424	35,540
36					Dec 14	691	14,941	10,348
37					Jan 15	466	13,087	(37,091)
38					Feb 15	838	13,711	12,480
39					Mar 15	962	13,965	5,088

**shg** · 11-05-2013, 07:32 PM

Missed this:

Bonuses aren't paid when the current rolling sum is below a previous high. There is effectively a rolling 'bonus ceiling' in operation.

So the formula in I4 and down should be

=MAX(0, SUMPRODUCT((H4 > $B$3:$B$6) * (H4 - $B$3:$B$6) * $D$3:$D$6) - SUM(I$2:I3))

	F	G	H	I
2	Month	Sales [$K]	Rolling Cumu [$k]	Comm
3
4	Apr 12	455	455	-
5	May 12	149	604	-
6	Jun 12	2,354	2,958	-
7	Jul 12	191	3,149	-
8	Aug 12	2,288	5,437	4,367
9	Sep 12	1,829	7,266	18,291
10	Oct 12	668	7,934	6,682
11	Nov 12	555	8,489	5,550
12	Dec 12	755	9,244	7,550
13	Jan 13	926	10,170	10,960
14	Feb 13	357	10,527	7,140
15	Mar 13	1,136	11,663	22,720
16	Apr 13	448	11,656	-
17	May 13	675	12,182	10,384
18	Jun 13	813	10,641	-
19	Jul 13	249	10,699	-
20	Aug 13	738	9,149	-
21	Sep 13	1,757	9,077	-
22	Oct 13	1,296	9,704	-
23	Nov 13	599	9,748	-
24	Dec 13	174	9,167	-
25	Jan 14	2,321	10,562	-
26	Feb 14	214	10,420	-
27	Mar 14	708	9,991	-
28	Apr 14	689	10,233	-
29	May 14	1,516	11,073	-
30	Jun 14	818	11,079	-
31	Jul 14	1,534	12,363	3,619
32	Aug 14	2,222	13,847	29,679
33	Sep 14	1,117	13,207	-
34	Oct 14	736	12,647	-
35	Nov 14	2,376	14,424	11,538
36	Dec 14	691	14,941	10,348
37	Jan 15	466	13,087	-
38	Feb 15	838	13,711	-
39	Mar 15	962	13,965	-
40	Apr 15	1,679	14,955	264
41	May 15	854	14,293	-
42	Jun 15	402	13,877	-
43	Jul 15	1,453	13,796	-
44	Aug 15	811	12,385	-
45	Sep 15	1,528	12,796	-
46	Oct 15	527	12,588	-
47	Nov 15	1,919	12,131	-
48	Dec 15	2,351	13,791	-
49	Jan 16	2,238	15,563	17,785
50	Feb 16	2,069	16,793	36,925