Need Help with formulas to Help Me work out my Commissions?

[tonywatsonhelp]

Hi Everyone,

OK so I need a formula or formulas to calculate Commissions payable?

We work on a 4 level commission structure and I need to be able to change the rates but lets say for now its

Level1 £0 to £100,000 = 10%
Level2 £100,001 to £200,000 =20%
Level3 £200,001 to £300,000 =30%
Level4 Over £300,000 =40%

These Amounts are IN Range B4:B7 (Amount Up To/above as shown) and %'s In C4:C7

Now heres the problem,

I Need to work out the Commision payable for this Month but The levels are a running total for the year.

So In Cell A1 I have Opening Sales (i.e. The amount of sales I've Made aready this year)
In Cell B2 I Have This Months Sale (I.e The amount I need to get paid Commision on!)
and In C1 Total Sales.

So some how I need to do this

Anywhere in the sheet, show Opening Sales In a matrix To show Where I'm upto
And show the levels of commission I'm reciving this month and why.

So for example using the levels above lets say my Opening sales are £170,000
and this month I sold £210,000.

So Level1 has been reached (up to £100,000) so no Commsions in this cat.
Level2 £100,001 to £200,000 Has £50,000 already reached, so I start here with £50,000 of my sales being at 20%
Level3 £200,001 to £300,000 I have £100,000 at 30%
Level4 Over £300,000 =40% leveing £20,000 at 40%


Any ideas how I can calculate this? I'm totally Lost?

thanks

Tony

 

[Eric W]

This is not too difficult, but it really does help if you've seen it before.

Excel 2012

	A	B	C	D	E	F	G	H	I
1	170000
2	210000
3		Tier	Rate		Opening sales			Current Sales
4		0	10%		100000	10000		0	0
5		100000	20%		70000	14000		30000	6000
6		200000	30%		0	0		100000	30000
7		300000	40%		0	0		80000	32000
8
9						24000			68000

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula E4 =MIN(A1,B5) F4 =E4*C4 E5 =MEDIAN(0,A1-B5,B6-B5) F5 =E5*C5 E6 =MEDIAN(0,A1-B6,B7-B6) F6 =E6*C6 E7 =MAX(0,A1-B7) F7 =E7*C7 H4 =MIN(A1+A2,B5)-E4 I4 =H4*C4 H5 =MEDIAN(0,A1+A2-B5,B6-B5)-E5 I5 =H5*C5 H6 =MEDIAN(0,A1+A2-B6,B7-B6)-E6 I6 =H6*C6 H7 =MAX(0,A1+A2-B7)-E7 I7 =H7*C7 F9 =SUM(F4:F7) I9 =SUM(I4:I7) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

The formulas in E and F calculate the bonus for the Opening Sales. The MIN, MEDIAN, and MAX formulas are used to figure out how much of the amount in A1 is to be applied to each range. Then the formulas in F just calculate those amounts by the appropriate rate, and F9 just sums them up.

The formulas in H do basically the same thing, only for the sum of the amounts in A1:A2, and then subtracts out the amount in each tier that has already been used, from column E.

Let me know how this works for you!

 

[tonywatsonhelp]

Eric,

This is Brilliant
Exactly what I needed and works perfectly
Thanks Mate
Tony

 

[Eric W]

Glad it works for you!