In a previous article I explained how sales commissions with tiered rates and product groups can be calculated using the table-valued functions of our .net library Finaquant Calcs.
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:
Commission Scenario
- 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:
1) Download and install our Excel Add-in for Table Valued Functions. See Finaquant in Excel for downloads and installation
2) Run Create Test Tables to generate all the four input tables required for commission calculation:
Input tables:
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:
Results
Two output tables will be generated:
1) Sales Commissions per Product Pool and Dealer
2) Sales Commissions per Dealer
Conclusions
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.
Developer Notes
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
