Link to home
Get AccessLog in
Avatar of Kanwaljit Singh Dhunna
Kanwaljit Singh DhunnaFlag for India

asked on

Formula to Adjust Commission

Hi Experts,

We need to pay commission at a certain %age to the selling agents. The Commission is paid on a cumulative basis requiring adjustment due to rounding off. But the rounding off results in a difference of $1 (Less or Excess) on entry wise basis. What I need is a Formula to adjust the INCOME itself so as to set the difference to Zero without changing the overall Income. The Commission amount is to remain intact. All the Negative differences must be adjusted, but +ve difference may remain. Any Negative Income is to be adjusted in next entry to the extent possible.

I have attached a sample file.

Avatar of John
Flag of Canada image

I take a very simple-minded approach to such problems.

Calculate your commission (Column G) to the accurate amount (no rounding). Calcuate a paid commission (Column H) that rounds to $1. Leave your Diffence column in place. At the end of a period (say quarterly), add the rounded subtotal of difference (rounded to $1) to column H at the end of that period.

Now Column H will always be (a) correct to $1 at any point and correct to within $1 at the end of the period (say quarterly).

Alternatively, it is always reasonable to pay in dollars and cents. Then it is always accurate and never needs any adjustment.

... Thinkpads_User
Avatar of byundt
Flag of United States of America image

Link to home
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
Avatar of Kanwaljit Singh Dhunna


Hi Thinkspad_User,

Commission is paid by a different party and we have no control over the payments as they have their own systems :)
What we are required to do is to file a return of such commissions and I cannot change what has been paid. So that's why I needed to change the Income itself and that too just to iron out the -ve figures. In other words, I am under compulsion to do the changes to the Income only.

Hope I am clear :)

Brad, Very nice to see you again ! It's been such a long time we inter-acted here. Your presence is too calming, As ever.

and Brad, the commission rate is uniform, ALWAYS !
If that helps !
In that case, make a separate column F1 (G) in between F and G and make it Calculated Income. Back calculate the income from the commission. Now you may want another column F2 so that you can work out the differences between Income and Calculated Income.

So now you have Income unchanged, Commission unchanged, and then book the income differences where you wish to. That should work as well.

.... Thinkpads_User
By the way, if Income cannot change and Commission cannot change, leave all the payments as set out (that was assumed above) and book small differences to miscellaneous expense or set up a miscellaneous wages account. .... Thinkpads_User
We need to file a return and there any -ve figure is counted as a default even if the overall figures are correct, as the validation is entry based. I cannot challange or change that as the system is still developing. Off course I can do the reverse calculation, but I only wish to make minimal changes to the basic data only where these are required and not to all the entries.

Let's see now:
1. You said Income cannot be changed.
2. You said Commissions cannot be changed (paid somewhere else).

So then the differences have to go somewhere (not Income and not Commission).

So just summarize the differences to quarterly or annual points and book the differences. The differences will be small (cumulative rounding errors) and the amount of work will be small (several entries in a year).

Given your constraints, there is no need to know the difference by employee. No one would care since to two key items have not been changed. So you just need a total.

Again, given the constraints, this turns into a simple accounting problem (at least that is how I would do it at my clients).

.... Thinkpads_User
Another thing you can do is "nothing".

The commission was calculated and paid out somewhere else. The total commission over the course of a year for one employee cannot be out by more than 3 or 4 dollars because of positive and negative roundings.

Where I am, we would never go to an employee and deduct money because rounding said it should be $3 less.

So just let it be. Then your income numbers are what they are and your commission numbers are what they are, and the Feds will be happy (I do this part all the time, and utter clarity is neccessary for the Feds).  ... Thinkpads_User
Constraints are not internal !
They are Legal ! I am to file a return with Government authorities and so -ve figures are not acceptable. But I wish to do only the minimal changes and so I am here.

Based at India !
I used the term Dollar to make it more understandable for the Experts.
I am not clear.

You pay an amount called income. The government does not care so long as reported.
Some other system pays an amount called commission. Again the government does not care so long as reported.

Use the paid commissions in your system for reporting and there is no difference recorded. If some one were to look, the commission rate for A would be 9.99999 % and the commission rate for B would be 10.00001 % and then really, would anyone care? I rather think not. I deal with the Feds all the time where I am.

The difference you are calculating is perceived, not real. Just let it go because it is inconsequential.

I hope that helps. .... Thinkpads_User
Hello Kanwal_No2

I can imagine that System A in Branch A culcates an amount of commission based on 10% (or whatever rate).

I can imagine that Branch B calculates and rounds the commission creating a perceived difference.

However, I am still of the view that you bring the commission from B into A by employee/date and mark that as Commission Paid. This would be an extra field in System A. The calculated commission in System A would be ignored.

Again, I hope this helps. .... Thinkpads_User
On a person by person basis, the difference between Income that has been covered by Commissions actually paid and that which has been earned is given by:

This formula returns a positive number if the cumulative commissions resulted in rounding up and a negative number if they resulted in rounding down.
I can understand yours mindset. But let me tell you there are other factors too which I don't control and so the differences might well be above -1. But those factors are not important for me as I am given just the final data to file the return with Indian Authorities. And so my only requirement is to make minimum possible changes to the data received within the overall Income limit and the wipe all the -ve differences.

I understand what you are trying to do. But you have said:

1. Commissions paid by the other system are fixed. I assume that is true.
2. Income numbers are fixed (first post). I assume that is also true.

But 1 and 2 are in different systems by different people (later post).

So in System 1, either:

1. Record minor differnce in income to wipe out differences (you say this might be possible).
2. Use the commission paid from System 2 in System 1. This means no differnces and no regular work. But it does mean another data column to get started.

... Thinkpads_User
Off Course, Income numbers are fixed, but I am only trying to do small changes which keeps me within the overall Income figures but wipe the -ve differences.

Do you really need to change anything?  

Income numbers are fixed (you just confirmed).
Commission numbers are fixed (you have confirmed on multiple posts).

So just use these numbers in your tax return. The are not differences then.

What am I missing? I do this all the time, and I deal with the Feds, and I do not have this kind of problem.

.... Thinkpads_User

I am not to file these with Feds but with Indian authorities in a digital form, who does the calculation on this basis and so -ve figures are not uploaded.

I used "Feds" as a generic term. I know in your case it is the Indian Government.

>>>> who does the calculation on this basis and so -ve figures are not uploaded.

Yes, of course. So file the income figure (fixed) and the commission figure (fixed) and don't bother with any differences. They are inconsequential as they are roundings.

I also know that the income figure and the commission figure come from different systems, but just put the two figures (income from one system and commission from the other system) into the digital submission.

Again, I do this a lot, and I cannot see that I am missing anything here.

... Thinkpads_User
Hello Kanwal_No2,

I would like to summarize after thinking about all this some more.

You have a set of Income numbers (actual revenue earned and received) which (a) are part of a tax return and (b) you correctly say you cannot change. I agree with that.

You have a set of Commission numbers (actual payments to employees) which (a) are part of a tax return and (b) you correctly say you cannot change. I agree with that.

Separately you prepared a set of commission numbers to great accuracy and providing a rounding difference in your eyes to the actual paid commissions.

On reflection, and based on my experience with accountants, I say this set of calculated numbers is immaterial and makes no difference to the authorities, neither does the small difference to the paid Commissions.

So then just use the Income numbers (fixed) as given and the Commission numbers (fixed) as given in your tax submission. I cannot imagine anyone in the tax department could disagree with this.

This is about all I can do here.  ... Thinkpads_User
Thanks Brad !