Tier Calculation on Sales Commission - Accumulative on Payout Rate

[Sadies173]

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.

 

[steve the fish]

Hi. How does the machine know what is the first month??

 

[Sadies173]

Hi. How does the machine know what is the first month??

I honestly can't answer that question, I need this formula to project. We're projecting a minimum of $200k sales a month.

 

[steve the fish]

As you are doing an accumulation of sales to base the commission on then you need to know the start (and end) dates.

 

[shg]

	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​

 

[Sadies173]

Thank you! That has helped!!!!!!

 

[shg]

You're welcome.

 

[Sadies173]

C2: =B2-N(B1) is giving me 0%...

 

[shg]

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​

 

[Sadies173]

Thank you!!! This formula worked. You are a life saver!!!! <3