Avatar of Kanwaljit Singh Dhunna
Kanwaljit Singh Dhunna
Flag 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.

Regards
Kanwaljit
EEE-27.05.12-Adjustment.xls
Microsoft Excel

Avatar of undefined
Last Comment
Kanwaljit Singh Dhunna

8/22/2022 - Mon
John

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
ASKER CERTIFIED SOLUTION
byundt

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Kanwaljit Singh Dhunna

ASKER
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.

Regards
Kanwal
Kanwaljit Singh Dhunna

ASKER
and Brad, the commission rate is uniform, ALWAYS !
If that helps !
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
John

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
John

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
Kanwaljit Singh Dhunna

ASKER
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.

Kanwaljit
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
John

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
John

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
Kanwaljit Singh Dhunna

ASKER
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.

Kanwaljit
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Kanwaljit Singh Dhunna

ASKER
Based at India !
I used the term Dollar to make it more understandable for the Experts.
John

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
John

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
byundt

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:
=SUMIF($C$2:C2,C2,G$2:G2)/E2-SUMIF($C$2:C2,C2,F$2:F2)

This formula returns a positive number if the cumulative commissions resulted in rounding up and a negative number if they resulted in rounding down.
Kanwaljit Singh Dhunna

ASKER
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.

Regards
Kanwal
John

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
Your help has saved me hundreds of hours of internet surfing.
fblack61
Kanwaljit Singh Dhunna

ASKER
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.

Kanwaljit
John

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
Kanwaljit Singh Dhunna

ASKER
Hi,

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.

Kanwaljit
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
John

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
John

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
Kanwaljit Singh Dhunna

ASKER
Thanks Brad !
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck