Allocate rebates formula

Titian

Well-known Member
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

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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.

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.

Any ideas anyone?

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

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.

Ok, I gave this a shot.
rebate_band.xls
ABCDEF
7CumsalesRate
8200001%
9350002%
10450002,50%
11
12Customer1stband2ndband3rdbandTotalrebate
13A15000420074003400275
14B9009009
15C70004200280098
16D3000300030
17E5005005
18F900042004800138
19G2000200020
20H1000100010
21total3840020000150003400585
2220000150003400
Taul1

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

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.

Thanks Jubjab it looks promising at first glance.

I'll work on it later and post back.

Thanks again.

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.

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).

Replies
2
Views
565
Replies
10
Views
970
Replies
2
Views
554
Replies
4
Views
2K
Replies
0
Views
353

1,217,326
Messages
6,135,906
Members
449,970
Latest member
pfisher99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back