Excel formula to set banding
Posted on 2014-11-15
I am trying to create a bonus spreadsheet and am having problems on various aspects: the bonus scheme works as follows (the numbers here are fictional and rounded for ease of maths) ...
A salesman has to bill 3 x his salary to earn a bonus (let's fix that at £100) - this is set as his 'annual target', so his annual target is £300, if we split this down by quarter (£300 / 4) this equals £75 - this is his 'Quarterly Target'
He is paid on a Quarterly basis, and as a result will earn a % bonus on everything he bills in that quarter over his Quarterly Target. So if he bills £80 he will earn a % bonus on £5 (£80 minus his quarterly threshold of £75 = £5)
If he fails to reach his threshold in a quarter then the deficit rolls over to the next quarter (i.e. in Quarter 1 if he bills £70, in Quarter 2 he will have to bill £80 before he earns any % bonus (This is his quarter 2 target (£75) + the deficit from the last quarter (£5)). So let's call this his quarterly 'adjusted target'
The real nightmare bit is this ... This bonus scheme has 4 payout levels which are determined primarily by his annual target, not his quarterly target ...
Level 1 - over his 'quarterly adjusted' target is paid at 30% up until he reaches an annual billing of £250
Level 2, as soon as he has billed £250 in year, this payout rises to 40% for everything over £250
Level 3 is re same calculation as level 2 but for billings over £350
Level 4 is re same calculation as level 3 but for billings over £450
I have spent days trying to get this right, and am desperate for any help or an example of something similar I can adapt