Calculating Sales Commissions

Sales managers often need to calculate the commissions earned by their sales forces. The calculations in the function example presented here are based on a sliding scale: Employees who sell more earn a higher commission rate (see Table 25-1). For example, a salesperson with sales between $10,000 and $19,999 qualifies for a commission rate of 10.5 percent.

Table 25-1: COMMISSION RATES FOR MONTHLY SALES
Open table as spreadsheet

Monthly Sales	Commission Rate
Less than $10,000	8.0%
$10,000 to $19,999	10.5%
$20,000 to $39,999	12.0%
$40,000 or more	14.0%

You can calculate commissions for various sales amounts entered into a worksheet in several ways. You can use a complex formula with nested IF functions, such as the following:

 =IF(A1<0,0,IF(A1<10000,A1*0.08, IF(A1<20000,A1*0.105, IF(A1<40000,A1*0.12,A1*0.14)))) 

This may not be the best approach for a couple of reasons. First, the formula is overly complex, thus making it difficult to understand. Second, the values are hard-coded into the formula, thus making the formula difficult to modify.

A better approach is to use a lookup table function to compute the commissions. For example:

 =VLOOKUP(A1,Table,2)*A1 

Using VLOOKUP is a good alternative, but it may not work if the commission structure is more complex. (See the next subsection for more information.) Yet another approach is to create a custom function.

A Function for a Simple Commission Structure

The following COMMISSION function accepts a single argument (Sales) and computes the commission amount:

 Function COMMISSION(Sales As Double) As Double '   Calculates sales commissions     Const Tier1 As Double = 0.08     Const Tier2 As Double = 0.105     Const Tier3 As Double = 0.12     Const Tier4 As Double = 0.14     Select Case Sales         Case Is >= 40000             COMMISSION = Sales * Tier4         Case Is >= 20000             COMMISSION = Sales * Tier3         Case Is >= 10000             COMMISSION = Sales * Tier2         Case Is < 10000             COMMISSION = Sales * Tier1     End Select End Function 

The following worksheet formula, for example, returns 3,000 (the sales amount-25,000-qualifies for a commission rate of 12 percent):

 =COMMISSION(25000) 

This function is very easy to understand and maintain. It uses constants to store the commission rates as well as a Select Case structure to determine which commission rate to use.

Note 

When a Select Case structure is evaluated, program control exits the Select Case structure when the first true Case is encountered.

A Function for a More Complex Commission Structure

If the commission structure is more complex, you may need to use additional arguments for your COMMISSION function. Imagine that the aforementioned sales manager implements a new policy to help reduce turnover: The total commission paid increases by 1 percent for each year that a salesperson stays with the company.

The following is a modified COMMISSION function (named COMMISSION2). This function now takes two arguments: the monthly sales (Sales) and the number of years employed (Years).

 Function COMMISSION2(Sales As Double, Years As Long) As Double '   Calculates sales commissions based on '   years in service     Const Tier1 As Double = 0.08     Const Tier2 As Double = 0.105     Const Tier3 As Double = 0.12     Const Tier4 As Double = 0.14     Select Case Sales         Case Is >= 40000             COMMISSION2 = Sales * Tier4         Case Is >= 20000             COMMISSION2 = Sales * Tier3         Case Is >= 10000             COMMISSION2 = Sales * Tier2         Case Is < 10000             COMMISSION2 = Sales * Tier1     End Select     COMMISSION2 = COMMISSION2 + (COMMISSION2 * Years / 100) End Function 

Figure 25-3 shows the COMMISSION2 function in use. The formula in cell D2 is

 =COMMISSION2(B2,C2) 

Figure 25-3: Calculating sales commissions based on sales amount and years employed.

On the CD 

The workbook,  commission function.xlsm, shown in Figure 25-3, is available on the companion CD-ROM.