I recently developed a quick template to look at potential commission structures for a business with customer concentration and large contracts. I thought a simple version of the Excel workbook would provide some decent Excel formula practice.
The idea was to create a structure where a salesperson would receive a tiered commission for each new customer gained. To provide the ability to evaluate different structures, the workbook attached applies the percentages shown to the dollar sums listed in “Tiers.” For example, per the image below, the salesperson would earn 4% on the first $2M of revenue, 3% on the second $2M of revenue and 2% on the last $1M of revenue. In year two, for that same customer, the percentages would change to 3%, 2% and 1%, respectively. This same logic applies to the remaining years.
For the calculation to work, the first year that the salesperson lands the customer needs to count as year 1, which is what the third schedule, titled “Count,” calculates. Finally to arrive at “Commission” the worksheet uses the HLOOKUP function to pull the appropriate percentage from the figures under “Commission Structure.”
