Calculate comm based on full collection within 60days

[Khu]

Hi,

i need some guidance on the following:-

1) Calculate the commission based on the target sales & following by collection.
mean sales target achieve then the team entitle to get the commission based the table in % if not no comm given
2) If achieve the sales target , then the commission will base on the collection but within 60days due .

Can someone show me the formula to calculate the commission rate.

attach is the data for your advise and reply....

[tlafferty]

What did you mean by collection within 60 days? I see an amount in collection for each of the amounts that is greater than what appears to have been invoiced for that month.

See if the table in the attached book is headed in the right direction.

[Khu]

Hi , million thx for your help...

Sorry , may b i not given a very clear info..
1) First, we hv to look in to the sales target achieve , if achieve then , commission will be calculated based on the full collection / paid by customer..
Example : Sales invoice amt 1,000 , customer will be paid in installment for 3 mths period assume sales targetr achive in mth Jan & customer paid 500 then following mth is 250 & 250 in Feb & March
2) as 60 days due mean the sales team will be get the paid for the collection period 60 days , if over 60days ... the paid will not be offer

Million thx in advance...
Best regards
SK

[tlafferty]

OK - to be clear, the business rules are:
1. Sales for a given month must be at least 80% of target in order to receive any commisison.
2. Sum of amount collected for Jan, Feb, and Mar must be equal to the invoiced amount in order for commission to be paid.
3. Assuming conditions 1 and 2 are met, then pay the commission based on the percentage in the commision table.

If I understand these rules correctly, then for the calendar month of January, the team acheived 100% of the target of 7,000, so commission should be calculated at 200%. What remains unclear is the collection table. The Jan column has a total of $25,000 in it, which is more than enough to cover the sales amount of $7,000 in sales. It looks like there's a one-to-one relationship between that talbe and the sales summary table, and if that's the case, then I'm having a tough time figuring out how much has been collected since it seems that the invoices for January total 7,000 SUM(E13:E16) but the collected amount is only 6,400 SUM(J13:L16).

Should the commission be paid? If so, the commision should be 200% of something, but surely it's not 200% of the sales? This would mean that we are paying the sales team twice as much as we're collecting - a formula for business disaster and failure...

Can you clarify some more? I've attached a revised book...

[Khu]

Hi..

Million thx for your assit and you're correct - commission only will be given based on the sales target achieve n full collection on the invoice

I've attached the revised file, sorry on the typo error in the collection column.

Thanks in adavance
Best regards
Sk

[tlafferty]

Done -
One adjustment may yet need to be made:
This workbook assumes that the sales team is not going to be paid 100% of the invoice amount, but 100% of the base commission %, whatever that is. Often it's 7%. Based on that:
80% target = 100% X 7% X invoice
90% target = 150% X 7% X invoice
100% target = 200% X 7% X invoice
120% target = 300% X 7% X invoice

Base commission % is highlighted in yellow.

Note that the column headings marked Jan, Feb etc. are actually 01/01/2011 and 02/01/2011 and have been formatted to show as abbreviated months. You must enter full dates like this going forward for the formulas to continue working.

[Khu]

Hi ,

Million thx for your assist and guide, appreciate.....

best regards
SK