The main function of this template is designed to give the salesperson a view of their potential earnings based on gross profit earned multiplied by a fixed rate vs. a tiered rate (tiered rate meaning lower attainment means a lower commission rate and higher attainment means a higher commission rate (up to 5 buckets).
There are visuals to make things very clear per given gross profit production. There are two main tabs. The first shows a single revenue and gross profit result as well as the final gross profit after commissions for the company in each of the fixed and tiered bucket strategies.
The second tab is a sensitivity analysis that shows how the commission and company earnings can change at 5 different revenue/gross profit levels in a fixed rate environment vs. a tiered rate environment. This makes it easy to see differences at different gross profit generation levels.
The model defaults to target gross profit since that is the more telling metric of production that a company should be focused on. You want salespeople focused on selling things that generate the most gross profit rather than revenue. If you are unable to track gross profit in this way on a salesperson by salesperson basis, that is ok and you can easily set the gross profit rate to 100% and then it effectively is working off revenue.
The tabs can be duplicated any amount of time if you want to use them for multiple salespeople.
The main way this model can be used to analyze potential compensation plans for salespeople is by seeing that even though a tiered commission rate plan could end up meaning less margin for the company, it means more motivation for salespeople to make more gross profit overall. It is better to have 1,000,000 of gross profit that requires a 5% commission payout (50,000) than making 500,000 of gross profit that requires a 2.5% commission payout (12,500). In the 1,000,000 scenarios, the margin goes down, but the total earnings are much higher (950,000 vs. 487,500. This assumes that salespeople will sell more if they know that means a higher proportion of whatever is sold goes to them. So, as long as people are motivated by money, the incentive makes sense.
Important Note: This template is in a google sheet. The download is a word document that contains the view-only link for the model. To get edit access, hit File > Make a Copy once you go to the google sheet link provided in the word doc. You can then download the google sheet copy into excel if needed or continue using it in google sheets.