Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sales Commission Calculation
Hi,
I am trying to calculate a sales commission using excel, I can do bits of it but cant get every break point to calculate. The main variables are : Sales Target : e.g. 120000 Actual Sales : 140000 Commission is calculated as follows: Commission starts at 80% of target (96000) 0% commission is paid if sales fail to reach 80% of target. 1% commission is paid on sales from 80-90% of target 1.5% commission is paid on sales from 90-100% of target after target is reached commission is paid in bands Target plus 10000 2% of sales Target plus 10000 - 20000 2.25% of sales over 20000 2.5% I will give a example based on the above 1% of 120000 = 120 1.5% of 120000 = 180 + 10000 = 200 + 10000 to 20000= 225 Therefore the total commission is 725. Thanks in advance Colin Thornton |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sales Commission Calculation
It's a long and ugly formula, but here goes:
=SUM((MIN(0.9*D10,D11)-0.8*D10)*0.01*IF(D11<0.8*D10,0,1), (MIN(D10,D11)-0.9*D10)*0.015*IF(D11<0.9*D10,0,1), (MIN(D10+10000,D11)-D10)*0.02*IF(D11<D10,0,1), (MIN(D10+20000,D11)-(D10+10000))*0.0225*IF(D11<D10+10000,0,1), (D11-(D10+20000))*0.025*IF(D11<D10+20000,0,1)) I have the target in D10 and the actual in D11. You might want to do a test or two to be sure I got it right. "C Thornton" wrote: Hi, I am trying to calculate a sales commission using excel, I can do bits of it but cant get every break point to calculate. The main variables are : Sales Target : e.g. 120000 Actual Sales : 140000 Commission is calculated as follows: Commission starts at 80% of target (96000) 0% commission is paid if sales fail to reach 80% of target. 1% commission is paid on sales from 80-90% of target 1.5% commission is paid on sales from 90-100% of target after target is reached commission is paid in bands Target plus 10000 2% of sales Target plus 10000 - 20000 2.25% of sales over 20000 2.5% I will give a example based on the above 1% of 120000 = 120 1.5% of 120000 = 180 + 10000 = 200 + 10000 to 20000= 225 Therefore the total commission is 725. Thanks in advance Colin Thornton |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sales Commission Calculation
Brendan,
Thanks for your help, I cut and pasted the formula in to the cell and it worked perfectly. Your a star. Thanks Colin "Brendan" wrote in message ... It's a long and ugly formula, but here goes: =SUM((MIN(0.9*D10,D11)-0.8*D10)*0.01*IF(D11<0.8*D10,0,1), (MIN(D10,D11)-0.9*D10)*0.015*IF(D11<0.9*D10,0,1), (MIN(D10+10000,D11)-D10)*0.02*IF(D11<D10,0,1), (MIN(D10+20000,D11)-(D10+10000))*0.0225*IF(D11<D10+10000,0,1), (D11-(D10+20000))*0.025*IF(D11<D10+20000,0,1)) I have the target in D10 and the actual in D11. You might want to do a test or two to be sure I got it right. "C Thornton" wrote: Hi, I am trying to calculate a sales commission using excel, I can do bits of it but cant get every break point to calculate. The main variables are : Sales Target : e.g. 120000 Actual Sales : 140000 Commission is calculated as follows: Commission starts at 80% of target (96000) 0% commission is paid if sales fail to reach 80% of target. 1% commission is paid on sales from 80-90% of target 1.5% commission is paid on sales from 90-100% of target after target is reached commission is paid in bands Target plus 10000 2% of sales Target plus 10000 - 20000 2.25% of sales over 20000 2.5% I will give a example based on the above 1% of 120000 = 120 1.5% of 120000 = 180 + 10000 = 200 + 10000 to 20000= 225 Therefore the total commission is 725. Thanks in advance Colin Thornton |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating sales commission that changes based on a sales volume | Excel Worksheet Functions | |||
Sales V Commission updated calculation | Excel Discussion (Misc queries) | |||
Sales V commission | Excel Worksheet Functions | |||
Sales Commission calculations | Excel Worksheet Functions | |||
sales commission template | Excel Discussion (Misc queries) |