Avatar of Tom Crowfoot
Tom Crowfoot
Flag for United Kingdom of Great Britain and Northern Ireland

asked on 

Excel formula to set banding

Dear Experts

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

Many thanks
Microsoft Excel

Avatar of undefined
Last Comment
Tom Crowfoot

8/22/2022 - Mon