In this article, I will explain how sales commissions can be calculated in Excel. The commission scenario is the same as explained in the previous article:
- Dealers receive sales commissions proportional to their sales totals for each product group in a quarter or month (commission or payment period).
- Product groups (or pools) can be defined by product category or individual products for each dealer.
- The commission scale with tiered rates (class or level scale logic) for each product group (or pool) determines the amount of commissions to be paid at the end of each commission period.
Steps for Commission Calculation
Following 2.5-minute video shows all the steps explained below:
SalesTable Sale transactions per each dealer
ComScaleTable Commission scale table with tiered commission rates
ProdPoolTable Data table which defines product groups by assigning categories or individual products to a group ID.
ScalePoolTable Data table which assigns a commission scale to each product group.
3) Run Calculate Sales Commissions after selecting and entering proper parameters into the parameter form in Excel:
Two output tables will be generated:
This little demonstration shows you that you don’t need an expensive software, or elaborate database programming to implement a rule-based calculation like the commission example here. You can simply do it in Excel, using table-valued functions.
Commission Calculation was added to the Excel Add-in (Finaquant in Excel) as an example for a user-defined table function. Excel or .NET developers may see the related article: How to Add a User-Defined Table Function to Excel
Any Questions or Comments?
Please direct them to our community forum.
Tunc Ali Kütükcüoglu