Solved

Excel formula to set banding

Posted on 2014-11-15
42
195 Views
Last Modified: 2014-11-21
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
0
Comment
Question by:correlate
  • 19
  • 17
  • 6
42 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 40444557
Just for me to understand

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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40444580
Some specifics

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
0
 

Author Comment

by:correlate
ID: 40444660
Hi gowflow, sorry for the delay - had to head out, re your questions:

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
0
 

Author Comment

by:correlate
ID: 40444662
The input fields would really be just salary & how much he has billed by quarter.  The output fields would just be the quarterly bonus
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40444663
Excellent !!! I am almost there.

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
0
 

Author Comment

by:correlate
ID: 40444741
Hi 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%
0
 
LVL 80

Expert Comment

by:byundt
ID: 40444841
This problem is extremely sensitive to how the data are laid out. You didn't say, so I am posting a sample workbook with some assumptions.

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 second quarter bonus:
=IF(E2<I2,0,
$T$2*MAX(0,SUM($D2:E2)-$C2*COLUMNS($L2:M2))+
$T$3*MAX(0,SUM($D2:E2)-$R$3)+
$T$4*MAX(0,SUM($D2:E2)-$R$4)+
$T$5*MAX(0,SUM($D2:E2)-$R$5)
-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
0
 
LVL 80

Expert Comment

by:byundt
ID: 40444851
There is an interesting edge case in which the salesman sells 75 pounds each quarter.

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
0
 

Author Comment

by:correlate
ID: 40444873
Thank you so much, I'll have a good through - as far as your last question "so do you intend a £20 bonus ..." really good point, I have no idea, but I suspect the powers that be will say no bonus as they haven't hit their quarterly target
0
 
LVL 80

Expert Comment

by:byundt
ID: 40444876
Well, as long as you are yanking their chain, make the sales in Q4 be 76 pounds. A bonus is clearly required--but at what rate, and applied to which amount?

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

Author Comment

by:correlate
ID: 40444878
I've started to take a look at the sheet, looks v promising, I'll have to take a look tomorrow to play with it properly.  Looks very different to how I've tried to solve this previously which is a big relief
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40445049
ok sorry for late reply, I was out Sat night ... :) just got back to finish what was started and glad to see my old friend byundt also taking part of this challenge. I have not looked at your solution yet !

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
0
 

Author Comment

by:correlate
ID: 40447079
Hi gowflow

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
0
 

Author Comment

by:correlate
ID: 40447111
Hi byundt

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?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40447138
Sorry
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
0
 
LVL 80

Expert Comment

by:byundt
ID: 40447378
Note change to cell I2 formula (copied across):
=$C2+MAX($C2-D2,0)+IF(D2<H2,MAX(H2-$C2,0),0)

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
0
 

Author Comment

by:correlate
ID: 40449488
Hi byundt

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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40449572
Any comment on my solution ?

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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40449595
Looking at the figures Maybe I got something mis-understood.

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
0
 

Author Comment

by:correlate
ID: 40449612
Alas not, the levels remain throughout the year, so irrespective of which quarter up to £300,000 in billings pays out at 35%, then up to £400,000 pays out at 40%, up to £500,000 45% and above £500,000 is 50%.

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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40449629
Sorry Sorry !!!!

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
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:correlate
ID: 40449678
I'm really sorry for any confusion caused, the 35% is the old 30%, by all means keep to 30, I can adjust that through, it was just when I took this to the management team they decided to raise the payout from 30% to 35%.

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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40449686
Ok fine. But pls without any offence It is only 'nice' to have a feedback when we post a solution whether a correct one or wrong one as it helps all readers to understand when is right form wrong and also help move in the positive direction. Sometimes you are bombarded by several Experts proposing solutions and it is understandable that answering each one can sometimes be a 'pain' :) but as one should say Experts have also spend precious time developing solutions and only fair to get a feedback. I am in no way whatsoever pointing to issue of Points that is solely the choice and decision of asker.

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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40449848
I don't know pls give me indications.

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
0
 

Author Comment

by:correlate
ID: 40449849
Hi gowflow,

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%
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40449853
Before I look at your reply did our answers crossed ???
Did you see the file I posted? ??

gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40449860
is the  £300,000 , £400,000 , £500,000 specific to this salesman or these are amounts cut in stone regardless of salesman salary???

gowflow
0
 

Author Comment

by:correlate
ID: 40449874
I think our comments crossed, I'll take a look at the attachment now.

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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40449893
What is the amount for Level1 then ?

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
0
 

Author Comment

by:correlate
ID: 40449939
level one is anything over his quarterly adjusted target up to £300,000

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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40449968
ok pls check out this

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
0
 

Author Comment

by:correlate
ID: 40450064
Hi gowflow

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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40450703
I do not understand how you get Level2

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
0
 

Author Comment

by:correlate
ID: 40450913
HI 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
0
 
LVL 80

Expert Comment

by:byundt
ID: 40451515
A desirable feature in a sales compensation system is that annual bonus shouldn't be sensitive to gaming through timing of the sales. The ones used by my past employers were all based on annual quotas. Payout might be more frequent, but you couldn't earn more by shifting sales to earlier periods.

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.
0
 
LVL 80

Accepted Solution

by:
byundt earned 250 total points
ID: 40451643
Assuming that you have described corporate policy correctly, I tried to encapsulate it in a user-defined function. This makes the formulas appear quite simple (and easy to recast):
=QuarterlyBonus(PriorSales, CurrentSales, AdjustedQuota, AnnualSalesBracketBottoms, Bonus%)

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

Open in new window


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
0
 

Author Comment

by:correlate
ID: 40451658
Hi blunt

Totally 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.
0
 

Author Comment

by:correlate
ID: 40451664
Sorry byunt (joys of predictive text!) - will check the sheet out when I get in
0
 
LVL 29

Assisted Solution

by:gowflow
gowflow earned 250 total points
ID: 40451955
Sorry for late reply I was sick in bed. Pls chk this version and let me know.
gowflow
Bonnus-V04.xlsx
0
 

Author Comment

by:correlate
ID: 40452030
Hi Both

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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40452644
Welcome
gowflow
0
 

Author Closing Comment

by:correlate
ID: 40457130
Guys,

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
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Macro to Send Appointment from Excel 1 30
problem with vb editor in excel 2016 10 26
Excel - Row Height +1 VBA 2 24
Name Rotation 11 29
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now