Microsoft Excel Sales Commission Spreadsheet Formulas (NO MACROS)

Im looking for someone that can help me with some advanced formulas on Excel and think though some things that I might not have covered in my spread sheet. I found a spreadsheet online and modified it, but its not working correctly. If someone wants to start over thats fine too.

Inputs: Date of a sale, and the amount of the sale. This should track all sales over time, and well use this to build additional data from later.

The deliverable is a spreadsheet that I can edit and input sales of my reps, it will calculate the following:

Current month to date sold MRR
Current quarter to date sold MRR
Commission due on each sale
How much more to get to individual goal
How much more the team needs to get to the team goal
What THIS months commission payout should be.
What the next months commissions should be.

Bonus:
If we can show a table of all the commissions, and when those commissions fall off for the residual.


Facts:
The sales are anywhere from 1000 to 40000 per deal, the average being around 2200.

Sales Reps are comped in the following ways:
Quota is $4000 per month, and paid monthly, but the team and individual bonuses are quarterly.
Each deal sold the rep gets:
- 50% the first month, and another 40% broke out over the next 11 months.
If they are over quota and up to 125% of quota they get 60% the first month, and 40% broke out over 11 months.
If they are over 125% of quota they get 70% of one month, and 40% broke out over 11 months.

Each sale should be sold with onboarding. Commissions for onboarding:
If its 50-100% of one months MRR then they get 25% of the total. Example, $2000 dollar deal has a $2000 onboarding fee, rep gets $500.
If the rep chooses to sell it will less than 50% onboarding, then they lose 10% of their commissions on the deal.

Project dollars. Reps are not commissions on project dollars, but every 10 project dollar counts towards 1 dollar of their MRR goal.

Example: Project sold for 10K they would get credit for $1000 towards the bonuses, but NOT get any money for it. Its to get them closer to goals only.

Teams:
If the whole sales team is over 110% of bonus they get an additional $500 per person that is on the team, so in this commission sheet I have 2 reps, so each rep would get $1000. $500 each x 2 reps.
If the whole sales team is over 125% they get 750 per rep. So $1500 each. This would scale as I add reps, the goal would be harder, but the bonus would get bigger if everyone hit it.

Teams can cover for each other on goals. If rep 1 hits 100%, and rep two hits 200% they would still be eligible for the large team bonus.

Sales manager gets 10% (see the first page)

Hopefully that makes sense. The issues I ran into was when the rep has 11000 dollars as an example, and they sell a $5000 deal. They only get the 50% commission on the first $1000 to get to the goal, then they bump up to 60% on the remainder of the sale.

Happy to talk through it, or answer questions. You can see the forumulas I tried.
About the recuiter
Member since Nov 11, 2022
Amol Nirmala Waman
from Waikato, New Zealand

---

---

Offer to work on this project closes in 50 days!
Are you interested in this Opportunity?

Looking for help? Checkout our video tutorial
How to search and apply for jobs

How to apply? Do you have more questions about the Job?
See frequently asked questions