Tiered Commissions based on Accumulated Sales

[mlgrossman]

I am working on creating a schedule that will allow me to calculate commissions based on the following variables:

Sales Volume Bonus %
500,000.00 2,000,000.00 0.75%
2,000,001.00 6,000,000.00 0.50%

Profit Sales Volume Bonus %
500,000.00 3,000,000.00 10.00%
3,000,001.00 6,000,000.00 7.50%

When a PM is awarded a job, we will pay them a commission on the contract amount. When the job is complete, we will pay another commission based on the profitability.

I have attached the workbook I am using. I tried to copy the cells into this message, but it did not copy the greatest.

The issue I am having is calculating the commissions when the job is complete. The percentages for the Profit Sales Volume above applies to the profit on the job. So if a job amount is the cummulative range of 3,000,0001 to 6,000,000 range it will earn 7.5% commission on the profit, not the contract amount. It sounds simple enough until job pushes the cummulative amount from one threshold to another. If the PM have cummulative sales of 2.1MM and a new job at 1.9MM is awarded the profit amount need to be prorated according to the split e.g.;

Accumulative Balance = 2,164,755
Awarded Job = 1,986,000
New Balance 4,150,000

This means 1,150,000 will be calculated at 7.5% and the remaining will be at 10%.

I have been playing with this schedule for days and cannot figure it out.

I need help please!

[Alf]

Perhaps this link could be of help?

https://www.excelcampus.com/modeling...ate-structure/

Alf

[Roel Jongman]

It is a bit of a difficult formula because the percentage of profit bonus depends on the sales amount.
I made a formula, but it will only work as long as you have 2 tiers as in the example. if there are more tiers we need more formula or a different approch..

Edit: just noticed I only put my formula in cells J7 and J8 but if you copy the formula in J4:J6 it will also work for those lines..

[shg]

	A	B	C	D	E	F	G	H	I
1			Sales	Bonus	Delta	Profit	Bonus	Delta
2			0	0.00%	0.00%	0	0.00%	0.00%	E2: =D2-N(E1)
3			500,000	0.75%	0.75%	500,000	10.00%	10.00%
4			2,000,000	0.50%	-0.25%	3,000,000	7.50%	-2.50%
5
6	Job No	Job Description	Sales	Cumu	Bonus	Profit	Cumu	Bonus
7	MN0118	SCSU Student Health & Academic	248,000.00	248,000.00	0.00	62,000.00	62,000.00	0.00	D7: =SUM(C7, D6)
8	MN0218	2018 Ext Wall Rprs ISC School	478,755.00	726,755.00	1,700.66	119,688.75	181,688.75	0.00	E7: =SUMPRODUCT((D7 > C$2:C$4) * (D7 - C$2:C$4) * E$2:E$4) - SUM(E$6:E6)
9	MN0518	Kinnick Stadium & Paul W. Brec	1,438,000.00	2,164,755.00	10,373.11	359,500.00	541,188.75	4,118.88
10	MN0718	Anderson Labs	1,986,000.00	4,150,755.00	9,930.00	496,500.00	1,037,688.75	49,650.00