Calculating with Tiers help

[edlim85]

Hi there,


I'm trying to do a macro to calculate bonus here, using tier system. and Im stuck.

<TABLE style="WIDTH: 164pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=219><COLGROUP><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3328" width=91><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 68pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=91>First</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64 align=right>$100,000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64 align=right>4.0%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Next</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>$150,000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>5.0%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Next</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>$250,000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>6.0%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Next</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>$250,000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>7.0%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Next</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>$250,000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>8.0%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Next</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>$250,000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>10.0%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Thereafter</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 align=right>10.0%</TD></TR></TBODY></TABLE>

Say if my sales is $900,000, how to do vba code excel to calculate?

Have anyone do this before. Do advise me please


Regards
Edmund

 

[jbeaucaire]

Tiered bonus schedules can mean two things...

1) As the sale total goes up, the entire bonus percentage goes up

2) The "tiers" apply only to the "portion" of the sale.

Which is yours? To be accurate, provide a couple examples of sales and bonuses so we know we have your math correct.

 

[edlim85]

Hi jbeaucaire,


you guys reply real fast! I think i have found my solution....i think

http://www.techonthenet.com/excel/macros/calc_cost.php

let me go through the code.
thanks for the trouble.


regards
Edmund

 

[jbeaucaire]

Here's the basic non-VBA solution for differential tiered pricing. A SUMPRODUCT first does a ">" test listing each tier, then does a subtraction test on the same tiers, finally indicates how much more/less to charge in the final test, the differential.

=SUMPRODUCT(--(A1>{0,10,50,100}), A1-{0,10,50,100}, {10,-2,-2,-1})

=SUMPRODUCT(--(A1 > {0,100000,250000,500000,750000,1000000}), A1 - {0,100000,250000,500000,750000,1000000}), {0.4,0.1,0.1,0.,0.1,0.2})


Here's a sample file that shows you an example of TIERED discounting structure:

Complex vs Simple discount techniques​

I plugged your numbers into the table shown there and got this:

Excel Workbook
	A	B	C	D	E	F
3		Tier Begins	Commiss %	Differential
4	Tier 1 Commissions	-	4%	4%		TIERED
5	Tier 2 Commissions	100,000.00	5%	1%		The complex tier pays the commission/percentage based
6	Tier 3 Commissions	250,000.00	6%	1%		on each tier range, so the first portion is always paid at the
7	Tier 4 Commissions	500,000.00	7%	1%		first tier, the second portion at the second tier, etc. Then the
8	Tier 5 Commissions	750,000.00	8%	1%		tiered payment values are added.
9	Tier 6 Commissions	1,000,000.00	10%	2%
10						This structure always pays MORE than "Simple" if the differential
11						values are decreasing, always pays LESS than "Simple"
12		Sales	Tiered Disc	Simple Disc		if the differential values are increasing.
13	Sales Person 1	$ 33,000.00	$ 1,320.00	$ 1,320.00
14	Sales Person 2	$ 135,000.00	$ 5,750.00	$ 6,750.00		SIMPLE
15	Sales Person 3	$ 190,000.00	$ 8,500.00	$ 9,500.00		The simple tier takes the grand total and applies the appropriate
16	Sales Person 4	$ 425,000.00	$ 22,000.00	$ 25,500.00		percentage for that amount to the entire total.
17	Sales Person 5	$ 1,000,000.00	$ 64,000.00	$ 100,000.00
18	Sales Person 6		$ -	$ -		This structure always pays MORE than "Complex" if the differential
19	Sales Person 7		$ -	$ -		values are increasing, always pays LESS than "Complex"
20	Sales Person 8		$ -	$ -		if the differential values are dencreasing.
Sheet1