Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculate tiered commisions on commulative monthly sales
Hi -
I have done a lot of reseach regarding tiered commision formulas in excel but none of them addresses how to calculate tiered commisions on cummulative monthly sales. The annual quota is $1,200,000. There are four commision tiers 0 - 80% of quota = 3% commision 80%- 100% of quota = 9% comission 100%-110% of quota = 5% commision 110%- of quota = 8% commision I have variable sales made in each of 12 months. I need to calculate the comission on cummulative sales basis each month. For instance if in first month the whole $1,200,000 is achieved the comission will be as follow: 3% on 960000 9% on rest 240000 On other hand if the sales for first month were $20,000 and sales for seond month were $1,400,000 then the commision for first month would be 3% on $20,000 and comission for second month would be as follow 3% on 940,000 (960000-20000) as commision is paid on annual cumulative sales. 9% on 260,000 5% on 12,000 8% on 80,000 Now this is my dilema as usually tiered comission are paid on monthly or quarterly basis and not on commulative annual sales. I can seem to get my head around this. Any help would be appreciated. Thanks AL! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I'm looking for a tiered sales commision tracker | Excel Discussion (Misc queries) | |||
Help monthly sales by person | New Users to Excel | |||
formula to calculate sales tax from total sales | Excel Worksheet Functions | |||
Formula help in a monthly sales report. | Excel Worksheet Functions | |||
How would I calculate commisions (with a cap)? | Excel Worksheet Functions |