Commission Calculations using Excel Iterative Calculations

Sales commissions can be calculated in a wide variety of ways; based on sales, based on quantity of sales, based on GP and probably a bunch of other ways. However if you are currently calculating commission based on GP, you are probably doing it wrong. By making one simple correction in your calculation you could possibly save your company hundreds of thousands a year, and probably get yourself a bit of a bad reputation in the Sales Department, but oh well…. You win some you lose some.

Let’s consider a situation where a car salesman gets 15% of the GP generated by the sales he makes. I am presenting the simplest scenario where there are no commission scales, however the concept would apply regardless of the complexity of the GP calculation. So generally you would calculate the commission as follows:

GP * 15% = Commission

So if I made a GP of R100 000 then I would earn R15 000 commission.

Right? Wrong! Why is this wrong? Because the commission would lower the GP. Which would mean the formula must look like this.

(GP Before Commission - Commission) * 15% = Commission

This formula will keep you up at night if you think about it a bit. So commission is based on GP, but GP is based on Commission? Which means if the commission changes the GP changes, but if the GP changes the commission changes, which changes the commission… Argh…

Luckily Excel has a way of dealing with this type of problem. It’s called iterative calculations and it was created specifically for these types of problems. However Excel will generally think you are making a mistake and give you a Circular Reference error, this is to stop you from making silly mistakes in general, like including a total row in a SUM function.

If however you would like to create a calculation where the answer does indeed form a part of the question, you would typically get the same error and the result would be zero:

So how can we solve this seemingly insurmountable obstacle! Quite simply, by just enabling iterative calculations in your Excel Options. To do this (in Office 2010,2013 or 2016), click on the File Menu -> Options -> Formulas and then click on Enable Iterative calculations.

Iteration is the repeated recalculation of a worksheet until a specific numeric condition is met. If this is done the formula is possible. Below I illustrated the results as well as the possible savings that could have been achieved by calculating salaries in this way. 

So that’s quite a massive saving. I hope it helps. Enjoy iterative calculations. And change your thinking.

Contact Beth Horner & Associates for Excel training. Phone 011 011 9064.

Leave a comment

You are commenting as guest.