We help IT Professionals succeed at work.

Formula to Adjust Commission

248 Views
Last Modified: 2012-06-10
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
Comment
Watch Question

JohnBusiness Consultant (Owner)
Most Valuable Expert 2012
Expert of the Year 2018

Commented:
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
Mechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
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 DhunnaSelf Employed

Author

Commented:
and Brad, the commission rate is uniform, ALWAYS !
If that helps !
JohnBusiness Consultant (Owner)
Most Valuable Expert 2012
Expert of the Year 2018

Commented:
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
JohnBusiness Consultant (Owner)
Most Valuable Expert 2012
Expert of the Year 2018

Commented:
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 DhunnaSelf Employed

Author

Commented:
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
JohnBusiness Consultant (Owner)
Most Valuable Expert 2012
Expert of the Year 2018

Commented:
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
JohnBusiness Consultant (Owner)
Most Valuable Expert 2012
Expert of the Year 2018

Commented:
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 DhunnaSelf Employed

Author

Commented:
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
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
Based at India !
I used the term Dollar to make it more understandable for the Experts.
JohnBusiness Consultant (Owner)
Most Valuable Expert 2012
Expert of the Year 2018

Commented:
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
JohnBusiness Consultant (Owner)
Most Valuable Expert 2012
Expert of the Year 2018

Commented:
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
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
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 DhunnaSelf Employed

Author

Commented:
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
JohnBusiness Consultant (Owner)
Most Valuable Expert 2012
Expert of the Year 2018

Commented:
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
Kanwaljit Singh DhunnaSelf Employed

Author

Commented:
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
JohnBusiness Consultant (Owner)
Most Valuable Expert 2012
Expert of the Year 2018

Commented:
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 DhunnaSelf Employed

Author

Commented:
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
JohnBusiness Consultant (Owner)
Most Valuable Expert 2012
Expert of the Year 2018

Commented:
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
JohnBusiness Consultant (Owner)
Most Valuable Expert 2012
Expert of the Year 2018

Commented:
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 DhunnaSelf Employed

Author

Commented:
Thanks Brad !

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.