This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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

Do more with

EXPERT OFFICE^{®} is a registered trademark of EXPERTS EXCHANGE^{®}

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

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

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

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

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

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

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

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

gowflow

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

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

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

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

gowflow

Bonnus-V04.xlsx

## Premium Content

You need an Expert Office subscription to comment.Start Free Trial