Allocate rebates formula

[Titian]

The number of transactions is scaled down to provide this example.

Rebate scheme (Cum Sales = Cumulative sales) based on the total turnover of a group of customers.

Cum Sales....Rate....Sales Band...Rebate
20,000........1.0%.......20,000........200
35,000........2.0%.......15,000........300
45,000........2.5%.........3,400.........85
...................Total......38,400.......585 average rebate 1.5%

I would like to correctly allocate the £585 rebate across the individual customers who have generated the turnover i.e. NOT by using the simple 1.5%.

Clearly the large sales value customers will have earned a higher % rebate than the lower sales value customers - but how to calculate?

Customer....Sales value...Rebate
A...................15,000
B.......................900
C....................7,000
D....................3,000
E.......................500
F.....................9,000
G.....................2,000
H.....................1,000
Total...............38,400.........585

Your help is appreciaated.

 

[Andrew Poulsom]

If your data is in the range A1:C10, in C2 enter:

=ROUND(SUM(B$2:B2)/$B$10*$C$10,0)-SUM(C$1:C1)

and copy to C3:C9.

 

[Titian]

Thanks Andrew for your reply but it isn't generating the response that I would have expected.

The first rebate band of £20,000 generating 1% whould apply to customers B & E who have not reached the next rebate band. The balance of the £20,000 rebate band would be spread amongst the other customers, plus their share of the next two higher rebate bands.

I hope I am explaing this clearly.

 

[Titian]

Any ideas anyone?

 

[Jubjab]

Thanks Andrew for your reply but it isn't generating the response that I would have expected.

The first rebate band of £20,000 generating 1% whould apply to customers B & E who have not reached the next rebate band. The balance of the £20,000 rebate band would be spread amongst the other customers, plus their share of the next two higher rebate bands.

I hope I am explaing this clearly.

It seems to me that none of the customers have reached the next "rebate band". If you want to allocate a higher % of rebate to the large customers than to the smaller ones it really boils down to how you make the rules. And that is then not really an Excel question

 

[Titian]

Thanks for your reply Jubjab however I must not have explained my problem clearly enough.

The GROUP of customers enjoys a rebate based on the total GROUP sales, which in this case is £38,400, on which a total rebate of £585 is earned. Clearly some element of the rebate is earned at 1%, some at 2% and the balance at 2.5%.

What I am trying to achieve is to split back that total rebate amongst the Companies who make up the Group in relation to their differing amounts of turnover.

Perhaps I am asking for the impossible or an exceptionally complicated answer?
I really don't know.

 

[Jubjab]

Ok, I gave this a shot.

rebate_band.xls
	A	B	C	D	E	F
7	Cumsales	Rate
8	20000	1%
9	35000	2%
10	45000	2,50%
11
12	Customer		1stband	2ndband	3rdband	Totalrebate
13	A	15000	4200	7400	3400	275
14	B	900	900			9
15	C	7000	4200	2800		98
16	D	3000	3000			30
17	E	500	500			5
18	F	9000	4200	4800		138
19	G	2000	2000			20
20	H	1000	1000			10
21	total	38400	20000	15000	3400	585
22			20000	15000	3400
Taul1

The HTML file shows the output. Clear the data in the range C13:E20, then run the macro. Here is the code:

Option Base 1
Sub a()

Dim customers(20) As Long

For i = 13 To 20

customers(i) = Cells(i, 2).Value

Next i

For z = 1 To 3
    
    target = Cells(22, z + 2).Value
       
    If Cells(21, z + 2).Value< target Then
    
        Do While Cells(21, z + 2).Value< target
        
            For i = 13 To 20
            If customers(i) > 0 Then
                If Cells(i, 2).Value > Cells(i, z + 2).Value Then Cells(i, z + 2).Value = Cells(i, z + 2).Value + 1
                customers(i) = customers(i) - 1
            End If
            Next i
        
        Loop
        
    
    End If

Next z

End Sub

You'll need to of course adjust the code to suit your data. Also note that the code will be very slow if you have large sets of data, but it should be easy enough to speed the code up in case this is something you are looking for.

 

[Titian]

Thanks Jubjab it looks promising at first glance.

I'll work on it later and post back.

Thanks again.

 

[Titian]

Running your code Jubjab there are initially two questions:-

1. Do I enter C22,D22 & E22 values manually before running the code?

2. How are cells C21,D21 and E21 updated by the code?

Thanks.

 

[Jubjab]

Running your code Jubjab there are initially two questions:-

1. Do I enter C22,D22 & E22 values manually before running the code?

2. How are cells C21,D21 and E21 updated by the code?

Thanks.

1. Yes. That could also of course be done with a formula.
2. They are not. They are simply SUM formulas (see the html).