The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

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

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

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with premium.
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

1)

You say lets fix it at £100 what is it ? his yearly salary ?

if yes then to earn bonus he has to bill over 3 X £100 = £300 is that correct ?

2)

What do you mean by

This bonus scheme has 4 payout levels ?

gowflow

1) - that is all correct

2) the 4 levels are the percentage they get as a bonus -

so in this instance everything he bills over his 'quarterly adjusted' target is paid at 30% up until he reaches an annual billing of £250, lets say he bills £300

£0 - £75 = nothing

£75 - £250 = 30% (ie 30% of £175 in this example = £52.50)

£250- £300 = 40% (ie 40% of £50 in this example = £20)

So in this instance his total bonus - £52.50 + £20 = £72.50

Hopefully this makes sense

One more

You have in level2 40% then Level3 40% then Level4 40% I am sure it does not make sence but you forgot to mention the percentages

Right ??

gowflow

Just realised my answer never got posted - yep, my mistake the percentages should be

level 1 = 30%

level 2 = 40%

level 3 = 45%

Level 4 = 50%

I made a guess that the bonus should be calculated on an incremental basis using the annual sales table for amounts over 250 pounds, less that which has already been paid for previous quarters. To this should be added 30% on the amount over the quarterly adjusted quota. No bonus shall be paid unless quarterly sales exceed the quarterly adjusted quota.

The attached workbook uses this formula for the

=IF(E2<I2,0,

$T$2*MAX(0,SUM($D2:E2)-$C2

$T$3*MAX(0,SUM($D2:E2)-$R$

$T$4*MAX(0,SUM($D2:E2)-$R$

$T$5*MAX(0,SUM($D2:E2)-$R$

-SUM($L2:L2))

The formula refers to a 3-column table in R2:T5 with annual sales levels in column R, bonus % in column S and incremental bonus in column T.

Quarterly sales are in columns D:G, and Quarterly Adjusted Quota in H:K. The quarterly bonus is figured in columns L:O.

Since I have made a number of guesses as to how the calculations should be made, the workbook will likely need revision. The best way of communicating how you want the calcs to be done is to post sample data along with expected results. Please show annual sales from 200 to say 1000 pounds, with varying amounts in each quarter.

BonusCalcsQ28562753.xlsm

According to what we have been told so far, no bonus would be due at 30% because sales never exceed the adjusted quarterly quota. But the bonus % chart shows 40% bonus on annual sales over 250 pounds--so do you intend there to be a 20 pound bonus in Q4 (40% of 300 pounds annual sales less 250 pounds)?

Brad

This problem isn't clearly defined, and will only serve to frustrate all parties involved until management thinks things through completely.

I hope I have grasped and understood your requirement and here is my solution.

It consist of 2 sheets

Settings

Is where you input Salesman name and yearly salary all the rest filled automatically.

Data

in Col A you input Salesman name that it should match what was input in Settings.

Only area in data where you input figures are the blue shaded columns which are the Billings per quarter.

The columns Pay Lev1, Pay Lev2, Pay Lev3, Pay Lev4 will show you the amount of adjusted bonus broken down by level the column you want to look at is obviously the Bonnus which is the product of each level by its respective percentage.

For sure at the far right you have a total recap per year for billing and total bonuses.

I have mocked some examples that you can see but best for you to cut short and see if calculation is correct is to go REAL Simply put real salary for 1 salesman that you know and his real billings for say last year 4 quarters and surely you know what is the output or bonus and check is it is correct.

Pls do let me know in any case how much far off I am or ... ???

This is actually my first challenge in this area which I always welcome these kind as they keep on teaching us.

Last but not least the formulas are extended to row 13 you can simply select the last unused row say 13 from Col A to Col AI and drag down to extend the formulas.

gowflow

Bonnus.xlsx

Thanks for this - I think we're almost there, but the adjusted target doesn't look right, so in the scenario that you provided if Joe only billed £10 in Q1 then his Q2 target should be his target (£75) + the shortfall from last quarter (£65) which should set his adjusted target for Q2 as £140 - this is the figure he needs to get over before he earns a bonus. However, if he bills £80 in Q1, his Q2 target remains at £75) rather than a reduced target.

Any ideas? many thanks

Thanks for this, I have a not too dissimilar issue with this as well - but the problem here relates to carrying over the adjustment, so in your example if bill bills nothing in Q1, Q2 & Q3, then his adjusted target should for Q4 should equal his annual target, at present it shows:

1Q 2Q 3Q 4Q

£75 £150 £150 £150

Q1 & 2 are correct, Q3 should be £225 & Q3 £300

Any ideas?

It was there but was masked by the fact that there was no billing in Q2 so it was removed reason why I modified the mask. Extreemly sorry for this.

gowflow

Bonnus-V01.xlsx

=$C2+MAX($C2-D2,0)+IF(D2<H

BTW, I like gowflow's layout with a second worksheet for the targets. It will be easier to set different targets for each salesman than the way I had done it. My lookup table requires that all targets either are the same or in a fixed % relationship to each salesman's wage.

BonusCalcsQ28562753.xlsm

Thanks for this - I have been testing it though with some real numbers & unfortunately I have come up with an issue in it. I have attached an example where as salesman is on £50,000 salary and bills as follows:

Q1 = 0

Q2 = £300,000 = Payout of £78,750 (all correct)

Q3 = 0

Q4 = £100,000 = Payout should be £5,000 (Excel shows £13,750)

So the first bonus is all correct (the band has been changed from 30-35%), however when he goes over that band's threshold (annual Billings >£300,000) Its doesn't calculate properly ...

His Q4 bonus "written Statement" should be - "He has reached his £300,000 threshold, so will be paid out at 40% going forward, for billings above his adjusted target in that Quarter (£100,000 - £75,000 = £25,000 paid out at 40% = £5,000)

In the second banding I did notice the following:

If I change the incremental % Bonus in cell T2 to zero then I got the £5,000, but the Q2 bonus is now nil, so maybe its double counting somewhere?

or

I also noticed (this maybe pure coincidence) that £100,000 * 5% = £5,000 (ie its the sales figure in Q4 * the second incremental band (cell T3)

Any ideas?

Copy-of-BonusCalcsQ28562753.xlsm

BTW you only need to change in Q1 the percentages as in all the formulas this is where they get their refrence from the other Quarter percentages are only there to look the same !

gowflow

for me the levels are as follows:

Q1 = Annual Salary - Annual Salary / 4

Q2 = 2.5 times the Annual Salary

Q3 = 3.5 times the Annual Salary

Q4 = 4.5 times the Annual Salary

Pls let me know.

gowflow

So in Q1 if they bill £325,000

they will earn SUM(£300,000 - 1/4ly Adjusted Target) * 35% + £25,000 at 40%

if then in Q2 they bill £50,000, they will earn SUM(£50,000 - 1/4ly Adjusted Target) * 40% because they have already cross the £300000 threshold for the year

What is the 35% coming from ??? You always mentioned 30%

Also never tied up an amount with a percentage !!! or otherwhyse you explanation what misleading

So Can I understand that you Meant to carry over during the year the accumulated billings that will be used to set his/her percentage ??

gowflow

Yes that's right about carrying over the accumulated billings, its the accumulated billings that set his/her percentage.

Thank you so much for your help here, its really appreciated

Now all is clear but I need a confirmation on the following:

for me the levels are as follows:

Q1 = Annual Salary / 4

Q2 = 2 times the Annual Salary / 4

Q3 = 3 times the Annual Salary / 4

Q4 = 4 times the Annual Salary / 4

Pls advise

gowflow

I create a Bonnus Col Level that will point to the Column number in sheet settings that the salesman have reached for calculation of bonus (do not confuse with Bonnus Level (subtract 3 to get your real level.) and basis which the amount is calculated. Once a level is reached it is kept.

Pls let me know as not sure of the figures as you changed percentages and changed amt per quarter so a bit lost here.

gowflow

Bonnus-V02.xlsx

I appreciate your comments, I always try to give as prompt a feedback as I can, unfortunately my job takes me out regularly to meetings whilst trying to juggle a host of other things - the 1 hour delay between your last post & this reply is exactly that. I know how frustrating that can be for you, I can only apologise as its way out of my control. I am very appreciative of your time & consider myself lucky to have 2 experts looking at this with different approaches.

I'm also really sorry, but the level bit you mention above isn't correct ... the level of payout only goes up once a salesman has reached the band. The level of payout isn't related to the quarters. The banding is for the entire year. Hopefully the rules below will help?

The bonus rules are this in order of precedence

1. To be paid out each quarter he has to clear is adjusted threshold, once he has cleared that in the quarter he will get paid out at 35%

2. Once he has billed over £300,000 in the year his percentage bumps up to 40% for all new billings up until the point where he reaches £400,000 billed.

3. Once he has billed over £400,000 in the year his percentage bumps up to 45% for all new billings up until the point where he reaches £500,000 billed.

4. When he has billed over £500,000 that year he get paid out at 50%

gowflow

re "is the £300,000 , £400,000 , £500,000 specific to this salesman or these are amounts cut in stone regardless of salesman salary???" - yes that's set in stone & correct

as we hv Level2 £300,000

Level3 £400,000

Level4 £500,000

So all salesmans need to reach that one and specific target per Level regardless of their salary RIGHT ?

gowflow

His Quarterly adjusted target is made up of his Quarterly Target (Annual Target divided by four) + if he failed to reach his quarterly target the last quarter then the deficit gets added on

Eg If his Quarterly target is £37,500 ....

In Q1 he bills £35,000, so he is short of £2,500 - this is added onto this Q2 quarterly target, making it £40,000

if in Q1 he bills £50,000 he earns a bonus on £12,500, his Q2 target remains at £37,500

I would appreciate you look at levels in Setting and advise if figures are correct these are supposed to be the upper level amount for each level.

Also if figures correct.

gowflow

Bonnus-V03.xlsx

The level setting in row 2 (ie Joe) is all correct

I have taken a look at the data and for Joe - Level 1 payout is all correct, in Q4 he should be on payout level 2 (now as he has already billed £300,000). So shouldn't cell AH3 be zero and AI3 should be £25,000? - This would give us figure of £10,000 in AG3 which would be all 100% correct.

I'm really excited as I think we're nearly there

Q1 billing = 0 Adjusted Target = -37500

Q2 billing = 300000 Adjusted Target = 225000

Q3 billing = 0 Adjusted Target = -375000

Q3 billing = 100000 Adjusted Target = 25000

--------------------------

Tot billing 400000 Adjusted = 2500000

Volume for Level2 is 3000000 of Adjusted Target this means still Level1

Pls clarify

gowflow

Apologies for the delay - I had to be out for a corporate evening, in the scenario we got to over £300,000 because his billings were above £300,000 (to these "thresholds" it doesn't matter what his target is, it is just if he bills over this amount

Thank you for your help here

Compare, for example, "Steady Eddy" with "Boomer Bill". Both of them have annual quotas of 150,000 pounds. Steady Eddy sold 450,000 pounds via four quarters, each at 112,500 pounds. Boomer Bill sold his 450,000 pounds all in the first quarter, and then partied the rest of the year with zero sales. I believe that your system would reward Steady Eddy with bonuses of 26,250, 26,250, 28,125 & 32,500 = 113,125 pounds. But Boomer Bill would earn 154,375 pounds in Q1--thus paying for a lot of merriment during the remainder of the year. I'd hate to explain that discrepancy to Steady Eddy, especially in the face of Boomer Bill's partying.

Have you have accurately described how the calcs should be done? Surely, your management would prefer the results of Steady Eddy over those of Boomer Bill.

=QuarterlyBonus(PriorSales

The required code (which I put in a regular module sheet) might be:

```
Function QuarterlyBonus(PriorSales As Double, CurrentSales As Double, Quota As Double, SalesBrackets As Range, BonusPercent As Range) As Double
Dim i As Long, j As Long, n As Long
Dim Amount, Bonus As Double, Base As Double, Rate As Double
n = SalesBrackets.Cells.Count
If CurrentSales > Quota Then
Base = PriorSales + Quota
i = Application.Match(Base, SalesBrackets, 1)
For j = i To n
If j < n Then
Amount = Application.Min(SalesBrackets.Cells(j + 1).Value, PriorSales + CurrentSales) - Base
Base = SalesBrackets.Cells(j + 1).Value
Else
Amount = CurrentSales - Base
End If
If Amount > 0 Then Bonus = Bonus + Amount * BonusPercent.Cells(j).Value
Next
End If
QuarterlyBonus = Bonus
End Function
```

To test the code, I used the example you gave previously, plus the "Steady Eddy" & "Boomer Bill" cases I mentioned in my previous Comment. I believe the Q4 Bonus for Bill (your example) should be 10,000 pounds rather than 5000 as it ought to be 40% of 25,000 pounds.

Brad

Copy-of-BonusCalcsQ28562753.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trialTotally agree with ur comments and these have already been highlighted, but this is the scheme. They want to run with this scheme I'm afraid.

gowflow

Bonnus-V04.xlsx

I have had an initial play with both of your version and they both look perfect

I will, if its ok, take a bit of time to thoroughly test both with a range of scenarios which I am confident will be ok

Thank you so much for your help here - really appreciated

Thank you very much for your help & patience with this one - both work brilliantly, so have split 50:50 if that's ok

Many thanks

Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with premium.
Start your 7-day free trial.

What are the fields you want to have as input and variables and what are the expected output fields. I understand the whole add sub dilemma but need to know what you want as input and output.

gowflow