<div>
Just for me to understand<br />
<br />
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.<br />
gowflow
</div>


<div>
Some specifics<br />
<br />
1)<br />
You say lets fix it at £100 what is it ? his yearly salary ?<br />
if yes then to earn bonus he has to bill over 3 X £100 = £300 is that correct ?<br />
2)<br />
What do you mean by<br />
This bonus scheme has 4 payout levels ?<br />
<br />
gowflow
</div>


<div>
Hi gowflow, sorry for the delay - had to head out, re your questions:<br />
<br />
1) - that is all correct<br />
<br />
2) the 4 levels are the percentage they get as a bonus - <br />
<br />
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<br />
<br />
£0 - £75 = nothing<br />
£75 - £250 = 30% (ie 30% of £175 in this example = £52.50)<br />
£250- £300 = 40% (ie 40% of £50 in this example = £20)<br />
<br />
So in this instance his total bonus &nbsp;- £52.50 + £20 = £72.50<br />
<br />
Hopefully this makes sense
</div>


<div>
The input fields would really be just salary &amp;&nbsp;how much he has billed by quarter. &nbsp;The output fields would just be the quarterly bonus
</div>


<div>
Excellent !!! I am almost there.<br />
<br />
One more<br />
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<br />
Right ??<br />
gowflow
</div>


<div>
Hi gowflow<br />
<br />
Just realised my answer never got posted - yep, my mistake the percentages should be<br />
<br />
level 1 = 30%<br />
level 2 &nbsp;= 40%<br />
level 3 = 45%<br />
Level 4 = 50%
</div>


<div>
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.<br />
<br />
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.<br />
<br />
The attached workbook uses this formula for the <b>second</b> quarter bonus:<br />
=IF(E2&lt;I2,0,<br />
$T$2*MAX(0,SUM($D2:E2)-$C2<wbr />*COLUMNS($<wbr />L2:M2))+<br />
$T$3*MAX(0,SUM($D2:E2)-$R$<wbr />3)+<br />
$T$4*MAX(0,SUM($D2:E2)-$R$<wbr />4)+<br />
$T$5*MAX(0,SUM($D2:E2)-$R$<wbr />5)<br />
-SUM($L2:L2))<br />
<br />
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.<br />
<br />
Quarterly sales are in columns D:G, and Quarterly Adjusted Quota in H:K. The quarterly bonus is figured in columns L:O.<br />
<br />
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.<br />
<a href="https://filedb.experts-exchange.com/incoming/2014/11_w46/883263/BonusCalcsQ28562753.xlsm" target="_blank" class="file-inline" title="Sample file (10 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>BonusCalcsQ28562753.xlsm</a>
</div>


BonusCalcsQ28562753.xlsm

<div>
There is an interesting edge case in which the salesman sells 75 pounds each quarter. <br />
<br />
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)?<br />
<br />
Brad
</div>


<div>
Thank you so much, I'll have a good through - as far as your last question &quot;so do you intend a £20 bonus ...&quot; 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
</div>


<div>
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?<br />
<br />
This problem isn't clearly defined, and will only serve to frustrate all parties involved until management thinks things through completely.
</div>


<div>
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. &nbsp;Looks very different to how I've tried to solve this previously which is a big relief
</div>


<div>
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 !<br />
<br />
I hope I have grasped and understood your requirement and here is my solution.<br />
<br />
It &nbsp;consist of 2 sheets<br />
Settings<br />
Is where you input Salesman name and yearly salary all the rest filled automatically.<br />
<br />
Data<br />
in Col A you input Salesman name that it should match what was input in Settings.<br />
Only area in data where you input figures are the blue shaded columns which are the Billings per quarter.<br />
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.<br />
<br />
For sure at the far right you have a total recap per year for billing and total bonuses.<br />
<br />
<br />
<br />
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.<br />
<br />
Pls do let me know in any case how much far off I am or ... ???<br />
This is actually my first challenge in this area which I always welcome these kind as they keep on teaching us.<br />
<br />
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.<br />
gowflow<br />
<a href="https://filedb.experts-exchange.com/incoming/2014/11_w46/883288/Bonnus.xlsx" target="_blank" class="file-inline" title="Bonnus per quarter (30 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>Bonnus.xlsx</a>
</div>


Bonnus.xlsx

<div>
Hi gowflow<br />
<br />
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. &nbsp;However, if he bills £80 in Q1, his Q2 target remains at £75) rather than a reduced target.<br />
<br />
Any ideas? many thanks
</div>


<div>
Hi byundt<br />
<br />
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 &amp;&nbsp;Q3, then his adjusted target should for Q4 should equal his annual target, at present it shows:<br />
<br />
1Q &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;2Q &nbsp; &nbsp; &nbsp; &nbsp;3Q &nbsp; &nbsp; &nbsp; 4Q<br />
£75 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;£150 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;£150 &nbsp; &nbsp;£150<br />
<br />
Q1 &amp;&nbsp;2 are correct, Q3 should be £225 &amp;&nbsp;Q3 £300<br />
<br />
Any ideas?
</div>


<div>
Sorry<br />
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.<br />
<br />
gowflow<br />
<a href="https://filedb.experts-exchange.com/incoming/2014/11_w47/883488/Bonnus-V01.xlsx" target="_blank" class="file-inline" title="Wrong mask to prevent 0 (30 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>Bonnus-V01.xlsx</a>
</div>


Bonnus-V01.xlsx

<div>
Note change to cell I2 formula (copied across):<br />
=$C2+MAX($C2-D2,0)+IF(D2&lt;H<wbr />2,MAX(H2-$<wbr />C2,0),0)<br />
<br />
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.<br />
<a href="https://filedb.experts-exchange.com/incoming/2014/11_w47/883532/BonusCalcsQ28562753.xlsm" target="_blank" class="file-inline" title="Sample file (10 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>BonusCalcsQ28562753.xlsm</a>
</div>


<div>
Hi byundt<br />
<br />
Thanks for this - I have been testing it though with some real numbers &amp;&nbsp;unfortunately I have come up with an issue in it. &nbsp;I have attached an example where as salesman is on £50,000 salary and bills as follows:<br />
<br />
Q1 = 0<br />
Q2 &nbsp;= £300,000 = Payout of £78,750 (all correct)<br />
Q3 = 0<br />
Q4 = £100,000 = Payout should be £5,000 (Excel shows £13,750)<br />
<br />
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 &gt;£300,000) Its doesn't calculate properly ...<br />
<br />
His Q4 bonus &quot;written Statement&quot; should be - &quot;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)<br />
<br />
In the second banding I did notice the following:<br />
<br />
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?<br />
<br />
or<br />
<br />
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)<br />
<br />
Any ideas?<br />
<br />
<a href="https://filedb.experts-exchange.com/incoming/2014/11_w47/883730/Copy-of-BonusCalcsQ28562753.xlsm" target="_blank" class="file-inline" title="Bonus Calc Example (8 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>Copy-of-BonusCalcsQ28562753.xlsm</a>
</div>


Copy-of-BonusCalcsQ28562753.xlsm

<div>
Any comment on my solution ?<br />
<br />
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 !<br />
<br />
gowflow
</div>


<div>
Looking at the figures Maybe I got something mis-understood.<br />
<br />
for me the levels are as follows:<br />
Q1 = Annual Salary - Annual Salary / 4<br />
Q2 = 2.5 times the Annual Salary<br />
Q3 = 3.5 times the Annual Salary<br />
Q4 = 4.5 times the Annual Salary<br />
<br />
Pls let me know.<br />
gowflow
</div>


<div>
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%.<br />
<br />
So in Q1 if they bill £325,000<br />
they will earn SUM(£300,000 - 1/4ly Adjusted Target) * 35% + £25,000 at 40%<br />
<br />
if then in Q2 they bill £50,000, they will earn SUM(£50,000 - 1/4ly Adjusted Target) * 40% &nbsp;because they have already cross the £300000 threshold for the year
</div>


<div>
Sorry Sorry !!!!<br />
<br />
What is the 35% coming from ??? You always mentioned 30%<br />
Also never tied up an amount with a percentage !!! or otherwhyse you explanation what misleading<br />
<br />
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 ??<br />
<br />
gowflow
</div>


<div>
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%.<br />
<br />
Yes that's right about carrying over the accumulated billings, its the accumulated billings that set his/her percentage.<br />
<br />
Thank you so much for your help here, its really appreciated
</div>


<div>
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.<br />
<br />
Now all is clear but I need a confirmation on the following:<br />
for me the levels are as follows:<br />
&nbsp;Q1 = Annual Salary / 4<br />
&nbsp;Q2 = 2 times the Annual Salary / 4<br />
&nbsp;Q3 = 3 times the Annual Salary / 4<br />
&nbsp;Q4 = 4 times the Annual Salary / 4<br />
<br />
Pls advise<br />
gowflow
</div>


<div>
I don't know pls give me indications.<br />
<br />
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.<br />
<br />
Pls let me know as not sure of the figures as you changed percentages and changed amt per quarter so a bit lost here.<br />
<br />
gowflow<br />
<a href="https://filedb.experts-exchange.com/incoming/2014/11_w47/883755/Bonnus-V02.xlsx" target="_blank" class="file-inline" title="taking Level in consideration (34 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>Bonnus-V02.xlsx</a>
</div>


Bonnus-V02.xlsx

<div>
Hi gowflow,<br />
<br />
I appreciate your comments, &nbsp;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 &amp;&nbsp;this reply is exactly that. &nbsp;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 &amp;&nbsp;consider myself lucky to have 2 experts looking at this with different approaches.<br />
<br />
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?<br />
<br />
The bonus rules are this in order of precedence<br />
1. &nbsp;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%<br />
<br />
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.<br />
<br />
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.<br />
<br />
4. &nbsp;When he has billed over £500,000 that year he get paid out at 50%
</div>


<div>
Before I look at your reply did our answers crossed ???<br />
Did you see the file I posted? ??<br />
<br />
gowflow
</div>


<div>
is the &nbsp;£300,000 , £400,000 , £500,000 specific to this salesman or these are amounts cut in stone regardless of salesman salary???<br />
<br />
gowflow
</div>


<div>
I think our comments crossed, I'll take a look at the attachment now.<br />
<br />
re &quot;is the &nbsp;£300,000 , £400,000 , £500,000 specific to this salesman or these are amounts cut in stone regardless of salesman salary???&quot; - yes that's set in stone &amp;&nbsp;correct
</div>


<div>
What is the amount for Level1 then ?<br />
<br />
as we hv Level2 &nbsp;£300,000 <br />
Level3 &nbsp;£400,000 <br />
Level4 &nbsp;£500,000 <br />
<br />
So all salesmans need to reach that one and specific target per Level regardless of their salary RIGHT ?<br />
gowflow
</div>


<div>
level one is anything over his quarterly adjusted target up to £300,000<br />
<br />
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<br />
<br />
Eg If his Quarterly target is £37,500 ....<br />
&nbsp;<br />
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<br />
<br />
if in Q1 he bills £50,000 he earns a bonus on £12,500, his Q2 target remains at £37,500
</div>


<div>
ok pls check out this<br />
<br />
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.<br />
<br />
Also if figures correct.<br />
gowflow<br />
<a href="https://filedb.experts-exchange.com/incoming/2014/11_w47/883772/Bonnus-V03.xlsx" target="_blank" class="file-inline" title="Fixed levels (34 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>Bonnus-V03.xlsx</a>
</div>


Bonnus-V03.xlsx

<div>
Hi gowflow<br />
<br />
The level setting in row 2 (ie Joe) is all correct<br />
<br />
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 &nbsp;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. <br />
<br />
I'm really excited as I think we're nearly there
</div>


<div>
I do not understand how you get Level2<br />
<br />
Q1 billing = 0 Adjusted Target = -37500<br />
Q2 billing = 300000 Adjusted Target = 225000<br />
Q3 billing = 0 Adjusted Target = -375000<br />
Q3 billing = 100000 Adjusted Target = 25000<br />
--------------------------<wbr />----------<wbr />----------<wbr />----------<wbr />----------<wbr />------<br />
Tot billing 400000 Adjusted = 2500000<br />
<br />
Volume for Level2 is 3000000 of Adjusted Target this means still Level1<br />
<br />
Pls clarify<br />
gowflow
</div>


<div>
HI gowflow,<br />
<br />
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 &quot;thresholds&quot; it doesn't matter what his target is, it is just if he bills over this amount<br />
<br />
Thank you for your help here
</div>


<div>
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.<br />
<br />
Compare, for example, &quot;Steady Eddy&quot; with &quot;Boomer Bill&quot;. 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, &nbsp;26,250, &nbsp;28,125 &amp;&nbsp;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.<br />
<br />
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.
</div>


<div>
Hi blunt<br />
<br />
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.
</div>


<div>
Sorry byunt (joys of predictive text!) - will check the sheet out when I get in
</div>


Bonnus-V04.xlsx

<div>
Hi Both<br />
<br />
I have had an initial play with both of your version and they both look perfect<br />
<br />
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<br />
<br />
Thank you so much for your help here - really appreciated
</div>


<div>
Guys,<br />
<br />
Thank you very much for your help &amp;&nbsp;patience with this one - both work brilliantly, so have split 50:50 if that's ok<br />
<br />
Many thanks
</div>


<div>
<div class="content wysiwyg-content">
Dear Experts<br />
<br />
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) ...<br />
<br />
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'<br />
<br />
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)<br />
<br />
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, &nbsp;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'<br />
<br />
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 ... &nbsp;<br />
<br />
Level 1 - over his 'quarterly adjusted' target is paid at 30% up until he reaches an annual billing of £250<br />
<br />
Level 2, as soon as he has billed £250 in year, this payout rises to 40% for everything over £250<br />
<br />
Level 3 is re same calculation as level 2 but for billings over £350<br />
<br />
Level 4 is re same calculation as level 3 but for billings over £450<br />
<br />
I have spent days trying to get this right, and am desperate for any help or an example of something similar I can adapt<br />
<br />
Many thanks
</div>
</div>


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

Excel formula to set banding

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.