Learn How to Calculate Sales Commission in Excel

Multi-Tier Sales Commission Plans

**

Problem: The VP of Sales in your company dreamt up the most convoluted sales and commission plan in the history of the world. Rather than just pay the reps a straight commission, this plan involves a base rate of 2 percent, bonuses based on the product sold, and the monthly profit sharing bonuses.

For the spreadsheet shown in Fig. 179 (click any image for a larger view), using Relative, Mixed, and Absolute formulas, create a formula that can be copied to all rows and all months.

Calculating the Commission

This formula will contain all four reference types. While entering the first formula in H6, you will want to base the commission calculation on the January sales in E6. As you copy the formula from January to February, you will want the E6 reference to be able to change to F6. As you copy the formula down to other rows, you will want the E6 to change to E7, E8, etc. Thus, the E6 portion of the formula needs to be a relative reference and will have no dollar signs.

You will multiply the sales times the base rate in B1. As you copy the formula to other months and rows, it always needs to point to B1. Thus, you need to use dollar signs to before the B and before the 1: $B$1.

To incorporate the product bonus, you will need to multiply sales by the Product Rate in column C. All of the months in row 6 will have to refer to C6. All of the months in row 7 will have to refer to C7. Thus, you need a mixed reference where column C is locked. Use the address of $C6.

Finally, the VP of Sales added the monthly profit sharing bonus. The entire commission calculation is multiplied by the bonus factor shown in row 1. The January commission calculation uses the factor in E1. The February factor is in F1. The March factor is in G1. In this case, you need to allow the formula to point to different columns but always to row 1. This requires a mixed reference of E$1.

Now that you have the four components of the formula, you can enter this formula in E6, as shown in Fig. 180: =E6*($B$1+$C6)*E$1.

Result: As shown in Fig. 181, you have created one single formula that can be copied to all columns and rows of your dataset.

Summary: The concept of relative, absolute, and mixed references is one of the most important concepts in Excel. Being able to use the right reference will allow you to create a single formula that can be copied everywhere.

More Excel Tips

If you’re interested in learning more tips and tricks, be sure to check out the entire library of Microsoft Excel tutorials and user guides at Bright Hub, including the following collections.

91 Tips for Calculating with Excel – This set of short, simple tutorials offers numerous tips and strategies for solving a wide range of problems in Excel that involve cell calculations. In particular, learn more about the absolute and mixed references discussed in the sales commission tutorial explained above. Also, discover other useful tips, such as how to isolate and extract portions of a cell value and how to create your own formulas.

Excel Formatting Tips from Mr. Excel – Excel’s formatting capabilities not only make a spreadsheet look more visually appealing, but they also add functionality to your charts, tables, and other objects. These 72 Excel formatting tips will help you learn how to stretch Excel’s abilities even further than before.