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]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td][/td][td="bgcolor:#F3F3F3"]

Sales​

[/td][td="bgcolor:#F3F3F3"]

Rate​

[/td][td="bgcolor:#F3F3F3"]

Delta​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td][/td][td]

$0​

[/td][td]

10%​

[/td][td="bgcolor:#CCFFCC"]

10%​

[/td][td="bgcolor:#CCFFCC"]C2: =B2-N(B1)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td][/td][td]

$200,000​

[/td][td]

8%​

[/td][td="bgcolor:#CCFFCC"]

-2%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td][/td][td]

$400,000​

[/td][td]

7%​

[/td][td="bgcolor:#CCFFCC"]

-1%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td="bgcolor:#F3F3F3"]

Mon​

[/td][td="bgcolor:#F3F3F3"]

Sales​

[/td][td="bgcolor:#F3F3F3"]

Cumu​

[/td][td="bgcolor:#F3F3F3"]

Comm​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]Jan[/td][td]

$140,000​

[/td][td]

$140,000​

[/td][td="bgcolor:#CCFFFF"]

$14,000​

[/td][td="bgcolor:#CCFFFF"]C7: =SUMPRODUCT((B7 > $A$2:$A$4) * (B7 - $A$2:$A$4) * $C$2:$C$4) - SUM(C$6:C6)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]Feb[/td][td]

$80,000​

[/td][td]

$220,000​

[/td][td="bgcolor:#CCFFFF"]

$7,600​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]Mar[/td][td]

$60,000​

[/td][td]

$280,000​

[/td][td="bgcolor:#CCFFFF"]

$4,800​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]Apr[/td][td]

$140,000​

[/td][td]

$420,000​

[/td][td="bgcolor:#CCFFFF"]

$11,000​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]May[/td][td]

$100,000​

[/td][td]

$520,000​

[/td][td="bgcolor:#CCFFFF"]

$7,000​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]Jun[/td][td]

$120,000​

[/td][td]

$640,000​

[/td][td="bgcolor:#CCFFFF"]

$8,400​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]Jul[/td][td]

$80,000​

[/td][td]

$720,000​

[/td][td="bgcolor:#CCFFFF"]

$5,600​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]Aug[/td][td]

$60,000​

[/td][td]

$780,000​

[/td][td="bgcolor:#CCFFFF"]

$4,200​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td]Sep[/td][td]

$60,000​

[/td][td]

$840,000​

[/td][td="bgcolor:#CCFFFF"]

$4,200​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td]Oct[/td][td]

$110,000​

[/td][td]

$950,000​

[/td][td="bgcolor:#CCFFFF"]

$7,700​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

17​

[/td][td]Nov[/td][td]

$150,000​

[/td][td]

$1,100,000​

[/td][td="bgcolor:#CCFFFF"]

$10,500​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

18​

[/td][td]Dec[/td][td]

$110,000​

[/td][td]

$1,210,000​

[/td][td="bgcolor:#CCFFFF"]

$7,700​

[/td][td][/td][/tr]
[/table]

 

[Sadies173]

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

 

[shg]

You're welcome.

 

[Sadies173]

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

 

[shg]

Oops:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td][/td][td="bgcolor:#F3F3F3"]

Sales​

[/td][td="bgcolor:#F3F3F3"]

Rate​

[/td][td="bgcolor:#F3F3F3"]

Delta​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td][/td][td]

$0​

[/td][td]

10%​

[/td][td="bgcolor:#CCFFCC"]

10%​

[/td][td="bgcolor:#CCFFCC"]D2: =C2-N(C1)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td][/td][td]

$200,000​

[/td][td]

8%​

[/td][td="bgcolor:#CCFFCC"]

-2%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td][/td][td]

$400,000​

[/td][td]

7%​

[/td][td="bgcolor:#CCFFCC"]

-1%​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td="bgcolor:#F3F3F3"]

Mon​

[/td][td="bgcolor:#F3F3F3"]

Sales​

[/td][td="bgcolor:#F3F3F3"]

Cumu​

[/td][td="bgcolor:#F3F3F3"]

Comm​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]Jan[/td][td]

$140,000​

[/td][td]

$140,000​

[/td][td="bgcolor:#CCFFFF"]

$14,000​

[/td][td="bgcolor:#CCFFFF"]D7: =SUMPRODUCT((C7 > $B$2:$B$4) * (C7 - $B$2:$B$4) * $D$2:$D$4) - SUM(D$6:D6)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]Feb[/td][td]

$80,000​

[/td][td]

$220,000​

[/td][td="bgcolor:#CCFFFF"]

$7,600​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]Mar[/td][td]

$60,000​

[/td][td]

$280,000​

[/td][td="bgcolor:#CCFFFF"]

$4,800​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]Apr[/td][td]

$140,000​

[/td][td]

$420,000​

[/td][td="bgcolor:#CCFFFF"]

$11,000​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]May[/td][td]

$100,000​

[/td][td]

$520,000​

[/td][td="bgcolor:#CCFFFF"]

$7,000​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]Jun[/td][td]

$120,000​

[/td][td]

$640,000​

[/td][td="bgcolor:#CCFFFF"]

$8,400​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]Jul[/td][td]

$80,000​

[/td][td]

$720,000​

[/td][td="bgcolor:#CCFFFF"]

$5,600​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]Aug[/td][td]

$60,000​

[/td][td]

$780,000​

[/td][td="bgcolor:#CCFFFF"]

$4,200​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td]Sep[/td][td]

$60,000​

[/td][td]

$840,000​

[/td][td="bgcolor:#CCFFFF"]

$4,200​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td]Oct[/td][td]

$110,000​

[/td][td]

$950,000​

[/td][td="bgcolor:#CCFFFF"]

$7,700​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

17​

[/td][td]Nov[/td][td]

$150,000​

[/td][td]

$1,100,000​

[/td][td="bgcolor:#CCFFFF"]

$10,500​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

18​

[/td][td]Dec[/td][td]

$110,000​

[/td][td]

$1,210,000​

[/td][td="bgcolor:#CCFFFF"]

$7,700​

[/td][td][/td][/tr]
[/table]

 

[Sadies173]

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