Formula to calculate retroactive sales commissions

[VJRhodes]

Hello
Using Win7/Excel 2007. I sell cars and need to record accumulated gross profits per vehicle sold to calculate commissions paid to me. Commissions are paid monthly so each month start again at zero. Spreadsheet set up as follows:
S6:S35 contains gross profit earned per vehicle sold, in $Can
T6:T35 contains accumulated gross profit, a "running total"
U6:U35 contains the commissions earned per vehicle
We are paid a percentage of the gross profit for each unit sold, with a minimum commission of $200 per vehicle.
We are paid 25% of the gross earned per unit, up to $10,000 accumulated profit. For >$10,000-<$15,000 accumulated $$, rate changes to 30% commission.
For >$15,000 rate is 30%, and now we are paid 30% on ALL sales for the month. This means the <$10,000 at 25% now is paid at 30% commission rate.

How do I calculate U6:U35 to calculate 25% up to $10,000, then to 30% when accumulated gross profit exceeds $15,000 for the month. I believe a SUMPRODUCT formula can do this, but I haven't learned how to build it to calculate retroactively. Nested IF's would work too I think.

Thank you very much for any suggestions.
"Wisdom is profitable to direct"

 

[jbeaucaire]

Here's how I would do it. Notice the formula in T7 is different from T6, that's the one that is copied down. If you add one more $3000 entry, all the formulas going up the commission column would switch to the higher rate.

Excel Workbook
	R	S	T	U
5		Gross Profit	MTD	Commission
6	Day1	$ 300.00	$ 300.00	$ 200.00
7	Day2	$ 3,000.00	$ 3,300.00	$ 750.00
8	Day3
9	Day4
10	Day5	$ 600.00	$ 3,900.00	$ 200.00
11	Day6
12	Day7	$ 3,000.00	$ 6,900.00	$ 750.00
13	Day8	$ 3,000.00	$ 9,900.00	$ 750.00
14	Day9	$ 3,000.00	$12,900.00	$ 900.00
15	Day10
16	Day11
17	Day12
18	Day13
19	Day14
20	Day15
21	Day16
22	Day17
23	Day18
24	Day19
25	Day20
26	Day21
27	Day22
28	Day23
29	Day24
30	Day25
31	Day26
32	Day27
33	Day28
34	Day29
35	Day30
36	Day31
Sheet1

 

[shg]

Does that account for the number of cars sold each day, JB?

       -R- ---S--- ---T---- ----U---- ---V---- --W---
   5   Day NumSold    GP     GP MTD   Comm MTD  Comm 
   6    1       1    $ 300     $ 300    $ 200  $ 200 
   7    2       2  $ 3,000   $ 3,300    $ 825  $ 625 
   8    3                    $ 3,300    $ 825    $ - 
   9    4                    $ 3,300    $ 825    $ - 
  10    5       1    $ 600   $ 3,900    $ 975  $ 150 
  11    6                    $ 3,900    $ 975    $ - 
  12    7       2  $ 3,000   $ 6,900  $ 1,725  $ 750 
  13    8       3  $ 3,000   $ 9,900  $ 2,475  $ 750 
  14    9       1  $ 3,000  $ 12,900  $ 3,370  $ 895 
  15   10       1  $ 2,000  $ 14,900  $ 3,970  $ 600 
  16   11       1    $ 100  $ 15,000  $ 4,500  $ 530 
  17   12                   $ 15,000  $ 4,500    $ - 
  18   13                   $ 15,000  $ 4,500    $ -

In U6 and down,

=SUM(U5,T6)

In V6 and down,

=MAX(200*SUM(S$5:S6), 25%*U6 + 5%*(U6>=10000)*(U6-10000) + (U6>=15000)*(5%*10000))

In W6 and down,

=V6 - SUM(W$5:W5)

 

[VJRhodes]

Thanks Jerry!!
I hadn't thought of that configuration. Works perfectly!

VJRhodes
"Wisdom is profitable to direct" and in this case, directed well!

 

[jbeaucaire]

SHG, I had the some thought initially, but opted to stay with the VJ's layout assuming he has some additional way of dealing with multiples. Perhaps he's noting additional same day sales separately on a day he didn't work?

 

[VJRhodes]

Hello SHG/JB
In the Columns A-R, I will track information about each vehicle sold, including buyer name, model number, etc. S6:S35 provides space to record up to 30 vehicles sold in one month, each in their own row, whether 3 in one day or one in a week. It would be simple to expand the spreadsheet if I ever sold more than 30 units in a month. Therefore, I don't need to track units sold with a formula. I tried SHG's formula for a running total of commissions earned for Column V, but it has errors.
=MAX(200*SUM(S$5:S6), 25%*U6 + 5%*(U6>=10000)*(U6-10000) + (U6>=15000)*(5%*10000))
The 200*SUM element returns $480,000 if I have $2400 gross profit in S6, so changed to MAX(200,S6), but still not getting correct totals for accumulated commissions. Will work on it to see if I can get it to calculate correctly.

Thanks again for your input and efforts. A big help already!
VJRhodes
"Wisdom is profitable to direct" but sometimes you still need more wisdom!

 

[jbeaucaire]

If you want to see a "running total" on commissions, too, it's actually simpler to have a "summary" section that gives you that, this also allows you to simplify the commission formula too to reference the total profits already summed into the summary:

Excel Workbook
	R	S	T	U
2	Gross Profit	$12,400.00
3	Gross Commission	$ 3,425.00
4
5		Gross Profit	MTD Profit	Commission
6	Car1	$ 300.00	$ 300.00	$ 200.00
7	Car2	$ 3,000.00	$ 3,300.00	$ 750.00
8	Car3	$ 600.00	$ 3,900.00	$ 200.00
9	Car4	$ 3,000.00	$ 6,900.00	$ 750.00
10	Car5	$ 2,500.00	$ 9,400.00	$ 625.00
11	Car6	$ 700.00	$10,100.00	$ 210.00
12	Car7	$ 2,300.00	$12,400.00	$ 690.00
13	Car8
14	Car9
15	Car10
16	Car11
17	Car12
18	Car13
19	Car14
20	Car15
21	Car16
22	Car17
23	Car18
24	Car19
25	Car20
26	Car21
27	Car22
28	Car23
29	Car24
30	Car25
31	Car26
32	Car27
33	Car28
34	Car29
35	Car30
36	Car31
Sheet1

 

[VJRhodes]

Thanks again JB!
I was just working on this and realized I have another small challenge.

Column Headings as follows:
S = Gross Profit
T = Gross Profit Running Total
U = Commission Earned (using JB's solution-works good!)
V = Commission Earned Running Total
W = "Spiff" a one time cash bonus offered to help move older units, weekend sale etc. Must be entered on same row as vehicle sale that earned the spiff to show correct earnings per unit.
X = Dealer Volume Bonus - another retroactive bonus payment. Zero bonus for 1-9 units. At 10 units sold in month, earn $25 per unit for each of the 10 sales ($250 bonus effectively at tenth sale) 11th unit earns same $25 bonus, but for Unit 12 sold the bonus jumps to $50 per vehicle sold, again retroactive to first unit sold in month. This applies to units 12, 13, & 14. So, if I sold 13 units in a month, my volume bonus is $650 (13 x $50) Then, at 15th sold unit, bonus per unit increases to $100 each, all the way back to first unit sold. If I sold 18 in a month, volume bonus is 18 x $100 = $1800.
Y = Ford Canada Bonus. Periodically they will offer a cash bonus on certain new vehicles, which must be entered in same row as sold unit that earned it, to accumulate combined earnings per vehicle sold correctly.
Z = Total Earnings. This simply sums all the individual payments that may be generated depending on promotions by my employer or the manufacturer.

Now I am working on using JB's commission formula (see in this thread) to figure out how to calculate the cash volume bonus earned and recorded in Column X.

Thanks again for any suggestions and solutions offered. I tried to copy/paste text from spreadsheet but couldn't format it, and I haven't figured out how to post an embedded code like SHG's reply in this thread.

VJRhodes
"Wisdom is profitable to direct". Currently, still being directed.

 

[jbeaucaire]

Like so?

Excel Workbook
	R	S	T	U	V	W	X	Y	Z	AA
5	Item	Gross Profit	MTD Profit	Commission	MTD Comm	"Spiff"	Dealer Vol	Ford Canada	Total	MTD Total
6	Car1	$ 300.00	$ 300.00	$ 200.00	$ 200.00		$ 50.00		$ 250.00	$ 250.00
7	Car2	$ 3,000.00	$ 3,300.00	$ 900.00	$ 1,100.00	$ 50.00	$ 50.00		$ 1,000.00	$ 1,250.00
8	Car3	$ 600.00	$ 3,900.00	$ 200.00	$ 1,300.00		$ 50.00		$ 250.00	$ 1,500.00
9	Car4	$ 3,000.00	$ 6,900.00	$ 900.00	$ 2,200.00		$ 50.00	$ 400.00	$ 1,350.00	$ 2,850.00
10	Car5	$ 2,500.00	$ 9,400.00	$ 750.00	$ 2,950.00		$ 50.00		$ 800.00	$ 3,650.00
11	Car6	$ 700.00	$ 10,100.00	$ 210.00	$ 3,160.00	$ 300.00	$ 50.00		$ 560.00	$ 4,210.00
12	Car7	$ 2,300.00	$ 12,400.00	$ 690.00	$ 3,850.00		$ 50.00		$ 740.00	$ 4,950.00
13	Car8	$ 1,100.00	$ 13,500.00	$ 330.00	$ 4,180.00		$ 50.00		$ 380.00	$ 5,330.00
14	Car9	$ 1,200.00	$ 14,700.00	$ 360.00	$ 4,540.00		$ 50.00		$ 410.00	$ 5,740.00
15	Car10	$ 700.00	$ 15,400.00	$ 210.00	$ 4,750.00		$ 50.00		$ 260.00	$ 6,000.00
16	Car11	$ 1,100.00	$ 16,500.00	$ 330.00	$ 5,080.00		$ 50.00		$ 380.00	$ 6,380.00
17	Car12	$ 1,200.00	$ 17,700.00	$ 360.00	$ 5,440.00		$ 50.00		$ 410.00	$ 6,790.00
18	Car13	$ 700.00	$ 18,400.00	$ 210.00	$ 5,650.00		$ 50.00		$ 260.00	$ 7,050.00
19	Car14
20	Car15
21	Car16
22	Car17
23	Car18
24	Car19
25	Car20
26	Car21
27	Car22
28	Car23
29	Car24
30	Car25
31	Car26
32	Car27
33	Car28
34	Car29
35	Car30
36	Car31
Sheet1

 

[VJRhodes]

You nailed it JB! Minor correction to X6/X7 formulae - you missed the last plateau of 15 units > $100 bonus-easy fix!
Your MVP rating is well-deserved, to me, for all your insight and design.
Thanks so much!

"Wisdom is profitable to direct" - and has been directed with excellence. I call you blessed!