The need is this.
We want to pay an increasing level of incentive for an increasing level of actual revenue earned. And we want to pay it in bands.
1.5% for the first 50% or target actually earned
3.0% for the next 20%
6.0% for the next 20%
8.0% for the last 10%
and finally any amount above target to earn at the rate of 10.0%.
Each of the target, actual, incentive percentage and the band upper limit have to be variable. In other words these will change so they need to reference a cell that we can change. Again see the attached sheet.
So here’s how it should work. (See col K in the attached spreadsheet as the example)
If the target was say $100,000, and the sales person earned actual revenue of $85,000 we want to pay (say) 1.5% for the first 50% of that target reached. 100,000*50%=50,000*1.5%=$750. Then we want to pay 3% for the next 20% of the target achieved. In other words pay 3% for all the actual earned after the first 50% and for the next 20% of the target. $100,000*20%=20,000*3%= $600. And then (in this case) pay the balance of the actual earned using the next band up of commission. In this case the balance is 15,000*6%=$900
The challenge is that when the "actual" goes into the next band (tipping from $70,000 to $85,000 for example but not reaching the full amount of the next band – which would be 90,000) the calculator needs to pay the incentive for the actual amount earned. In this case the balance or extra bit is 15,000 (85,000-75,000) and the incentive to be applied is the incentive in that next band 6% but only against the balance amount of (in this case) 15,000*6%=$900
I want the one same formula for all the cells to make these correct calculations.
I want to be able to amend any of the grey cells and see the total outcome.
Hi there, Aussie Freelancer here. I am working as business process improvement professional with extensive knowledge in Excel and Processes streamlining. I managed Excel improvements materials for such companies as AOL, DELL and L’Oreal etc.
I will provide you with a high quality and time committed service with outcomes match your needs and look professional and attractive. I can provide you with Excel that will automate majority of these tasks .
Kind Regards, Stephanie
Dear Sir’ I see your project description. I can do the job accurate perfect. Recent I create a new team now totally free my team. So need lots of work. my team are 5 person everyone have lots of experience with minimum 3 years about Copy Typing, Data Entry, Data Processing, web search & Excel. The work will be accurate perfect in time. Sir’ can you give a chance to work with you. Waiting for your reply. Thanks
I can make your green formula cells display the answers you desire that will be variable according to the cells you wish , however i will need to use more formula cells perhaps located off page or on a hidden and protected sheet that will cover more formula that is needed to make your calculations.
I am already working on your Project , cell ranges E11-E15, F11-F15, G11-G15,..... will all work exactly how your discription in cell P22-P26 work
Hello,
The formula(s) for this request is complete and I shall be able to provide you with the same for the sheet attached. You may have a proof reading of the same and get back if any discrepancy.
Please feel free to reach out for any queries/concerns.
Thanks,
Sri.
I was a sales manager with Malaysian yellow pages for 11 years. I am able to work long hours as I don't have any job now. Hope you can give me a chance.
Dear Sir,
I'm very expert in any kind of data entry job, and have interest to do this project,
I'm ready to start this project from right now.
Sincerely,
JulfikarVutt