theduchess1977
New Member
- Joined
- Dec 18, 2023
- Messages
- 1
- Office Version
- 365
- Platform
- Windows
Hi
I am trying to write a commission plan based around EBITDA targets which is paid quarterly to staff members based on a percentage of their salary.
The company needs to hit a target of 85% of budgeted EBITDA for staff to start receiving a tiered commission, the higher the result the higher the commission. The issue I am having is that we also have a catch up commission, if we failt to hit target in quarter 1 but we hit YTD target for quarter 2 then staff will receive the commission in that quarter x 2 as they would receive the percentage for both quarter 1 and quarter 2. But if we hit target in quarter 1 and then are running at a higher percentage in quarter 2 they would only receive that higher percentage for quarter 2 as they already received a commission for quarter 1. This would progress throughout the year, i.e. hit target quarter 1, miss targets for Quarters 2-3 but hit targets for quarter 4 so would have a catch up commission paid for the previous two quarters.
Does anyone know how I could write this as a formula? Any help appreciated!
I am trying to write a commission plan based around EBITDA targets which is paid quarterly to staff members based on a percentage of their salary.
The company needs to hit a target of 85% of budgeted EBITDA for staff to start receiving a tiered commission, the higher the result the higher the commission. The issue I am having is that we also have a catch up commission, if we failt to hit target in quarter 1 but we hit YTD target for quarter 2 then staff will receive the commission in that quarter x 2 as they would receive the percentage for both quarter 1 and quarter 2. But if we hit target in quarter 1 and then are running at a higher percentage in quarter 2 they would only receive that higher percentage for quarter 2 as they already received a commission for quarter 1. This would progress throughout the year, i.e. hit target quarter 1, miss targets for Quarters 2-3 but hit targets for quarter 4 so would have a catch up commission paid for the previous two quarters.
Does anyone know how I could write this as a formula? Any help appreciated!
| Annual Salary | £40,000.00 | |||||||
| Target | 125% | 110% | 100% | 90% | 85% | |||
| Bonus | 25% | 20% | 15% | 10% | 5% | |||
| Yr Bonus | £10,000.00 | £8,000.00 | £6,000.00 | £4,000.00 | £2,000.00 | |||
| Quarter Bonus | £2,500.00 | £2,000.00 | £1,500.00 | £1,000.00 | £500.00 | |||
| EBITDA Target | ||||||||
| Q1 | Q1 Total | Q2 | Q2 YTD Total | Q3 | Q3 YTD Total | Q4 | 2024 Total | |
| Target | £500,000.00 | £500,000.00 | £500,000.00 | £1,000,000.00 | £500,000.00 | £1,500,000.00 | £500,000.00 | £2,000,000.00 |
| Actual | £415,000.00 | £415,000.00 | £800,000.00 | £1,215,000.00 | £580,000.00 | £1,795,000.00 | £425,000.00 | £2,220,000.00 |
| Percentage of Target | 80.00% | 121.50% | 116.00% | 111.00% | ||||
| Commssion due | £- | £4,000.00 | £2,000.00 | £2,000.00 |
