Tiered Level Sales Bonus Formula

[dandeisel]

So here’s the deal:

I’m working on salesman’s bonuses for last month. I need to Figure out the formula for Column N where it calculates the salesman’s UNIT bonus based on the structure below


Here’s a Commissioned Salesperson Bonus Structure so we can work the formula around this.

Total units (New and Used Sold) is in Column L:L
The $300 Demo is in Column M:M
I have Column M:M figured out for the Demo Bonus
It’s the Cells in column N:N that I need help with…


12 units = $300 Demo
14 Units = $300 Demo + 500 Unit Bonus*
18 Units = $300 Demo + 750 Unit Bonus*
20 Units = $300 Demo + $1,000 Unit Bonus*
22 Units = $300 Demo + $1,250 Unit Bonus*
25 Units = $300 Demo +$1,500 Unit Bonus*
$100 Bonus per Car Over 25 Units


*Here’s the qualifier
2 Used Cars Delivered = 100% of Unit Bonus
4 Used Cars Delivered = 115% of Unit Bonus
5 Used Cars Delivered = 130% of Unit Bonus


Keep in mind the used cars totals for each salesman are in column H:H & J:J (2 different manufacturers under one roof ie. Used Hyundai (H:H) AND Used Mazda's column (J:J)

Depending on the number of used cars sold determines the percentage of UNIT bonus in addition.

Thanks for your help in advance.

 

[jorismoerings]

Re: **Tiered Level Sales Bonus Formula Help**

Hi,

Could you share a desensitized sample of your data and complete it with expected results manually.