Tier Calculation on Sales Commission - Accumulative on Payout Rate

Sadies173

New Member
Joined
Nov 1, 2018
Messages
5
I am having issues creating this formula for a Projected Cash Flow Budget regarding the Commission Sales Payout. Here is the scenario as followed:

The first $200,000 = 10%
The second $200,000 = 8%
Any sales dollar exceeding $400,000 = 7%

Lets say our Sales guy sales $280,000 the first month:
$200,000 @ 10%
$80,000 @ 8%

The next month, he sales $300,000:
$220,000 @ 8%
$80,000 @ 7%

All sales after will remain at 7% (exceeding $400,000).

Someone please help.
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,072
Office Version
365
Platform
Windows
Hi. How does the machine know what is the first month??
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,072
Office Version
365
Platform
Windows
As you are doing an accumulation of sales to base the commission on then you need to know the start (and end) dates.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,746
Office Version
2010
Platform
Windows
A​
B​
C​
D​
E​
1​
Sales
Rate
Delta
2​
$0​
10%​
10%​
C2: =B2-N(B1)
3​
$200,000​
8%​
-2%​
4​
$400,000​
7%​
-1%​
5​
6​
Mon
Sales
Cumu
Comm
7​
Jan
$140,000​
$140,000​
$14,000​
C7: =SUMPRODUCT((B7 > $A$2:$A$4) * (B7 - $A$2:$A$4) * $C$2:$C$4) - SUM(C$6:C6)
8​
Feb
$80,000​
$220,000​
$7,600​
9​
Mar
$60,000​
$280,000​
$4,800​
10​
Apr
$140,000​
$420,000​
$11,000​
11​
May
$100,000​
$520,000​
$7,000​
12​
Jun
$120,000​
$640,000​
$8,400​
13​
Jul
$80,000​
$720,000​
$5,600​
14​
Aug
$60,000​
$780,000​
$4,200​
15​
Sep
$60,000​
$840,000​
$4,200​
16​
Oct
$110,000​
$950,000​
$7,700​
17​
Nov
$150,000​
$1,100,000​
$10,500​
18​
Dec
$110,000​
$1,210,000​
$7,700​
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,746
Office Version
2010
Platform
Windows
You're welcome.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,746
Office Version
2010
Platform
Windows
Oops:

A​
B​
C​
D​
E​
1​
Sales
Rate
Delta
2​
$0​
10%​
10%​
D2: =C2-N(C1)
3​
$200,000​
8%​
-2%​
4​
$400,000​
7%​
-1%​
5​
6​
Mon
Sales
Cumu
Comm
7​
Jan
$140,000​
$140,000​
$14,000​
D7: =SUMPRODUCT((C7 > $B$2:$B$4) * (C7 - $B$2:$B$4) * $D$2:$D$4) - SUM(D$6:D6)
8​
Feb
$80,000​
$220,000​
$7,600​
9​
Mar
$60,000​
$280,000​
$4,800​
10​
Apr
$140,000​
$420,000​
$11,000​
11​
May
$100,000​
$520,000​
$7,000​
12​
Jun
$120,000​
$640,000​
$8,400​
13​
Jul
$80,000​
$720,000​
$5,600​
14​
Aug
$60,000​
$780,000​
$4,200​
15​
Sep
$60,000​
$840,000​
$4,200​
16​
Oct
$110,000​
$950,000​
$7,700​
17​
Nov
$150,000​
$1,100,000​
$10,500​
18​
Dec
$110,000​
$1,210,000​
$7,700​
 

Watch MrExcel Video

Forum statistics

Threads
1,098,868
Messages
5,465,169
Members
406,415
Latest member
silungwewilliam

This Week's Hot Topics

Top