Incentivizing Sales People - Multiple Scenario Planning with Comparison Visuals

 Structuring salesmen or saleswomen commission plans can be difficult to get right. There are plenty of ways to do it, so what is best? In my opinion, it is better to make the commission rate a function of gross profit generated (if this is possible to calculate for your situation) rather than revenue sold. If revenue sold is the only thing trackable, that is fine and the model will still work.



Pricing


After purchase, the template will be e-mailed to you. Note, this template is also included in the category bundle: "Helpful Organization and Tracking Tools in Excel and Google Sheet"

The idea behind giving sales people a commission structure is to incentivize them to sell more. In this study, it is all about the money. I chose to focus on gross profits rather than top line revenues because that is more important to the company and in my opinion this makes figuring out how much money to pay your salesmen or saleswomen easier.

The model looks at two possible methods:

Method 1: Giving a fixed percentage for all gross revenue generated.

Method 2: Giving a scaled percentage i.e. as more gross revenue is generated, the commission rate goes up.

Both methods incentive more selling because the salesperson is earning more, the more they sell, however method 2 of a scaled percentage is more intense because it means the commissions go higher relative to gross profit as gross profit goes up. So, Selling $100 may earn $2 in commissions (2% rate), while selling $1,000 may earn $50 in commissions (5% rate).

The purpose of this commission analysis tool is to show the varying levels of commissions as well as company earnings therein and how that can change with varying revenue/gross profit numbers. The thinking is that a company is willing to give up some of their margin in exchange for more overall profits.

For example, if a company can get to $5,000,000 in gross profits, but have to pay $500,000 in commissions (10% rate) and earning $4,500,000, that is better than a company earning $3,000,000 in gross profits, any only paying $150,000 in commission (5% rate) and earning $2,850,000. The company should rationally pick to pay 10% commission rather than 5% if it means making an extra $1,650,000 in gross profit after commission. This is relevant if sales people will sell more knowing their commission rate goes up as their sales go up.

There are a lot of different strategies for defining the buckets and commission rates, and that will depend on the structure of your specific company. In general, you want to make the buckets for what $ value of sales gets a certain commission rate based on historical data as well as sales goals.

If you can't track gross profits and can only enter in revenues, then just make the revenue and gross profit numbers at the top of the 'model' tab the same and then change all text that says 'gross profit' to 'revenue'. The rest of the analysis wills take make sense.