Allocate rebates formula

Titian

Well-known Member
Joined
Dec 17, 2004
Messages
567
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.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Well-known Member
Joined
Dec 17, 2004
Messages
567
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.
 

Jubjab

Well-known Member
Joined
Jan 3, 2007
Messages
993

ADVERTISEMENT

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

Well-known Member
Joined
Dec 17, 2004
Messages
567
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

Well-known Member
Joined
Jan 3, 2007
Messages
993

ADVERTISEMENT

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.
 

Titian

Well-known Member
Joined
Dec 17, 2004
Messages
567
Thanks Jubjab it looks promising at first glance.

I'll work on it later and post back.

Thanks again.
 

Titian

Well-known Member
Joined
Dec 17, 2004
Messages
567
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

Well-known Member
Joined
Jan 3, 2007
Messages
993
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,129,806
Messages
5,638,472
Members
417,026
Latest member
UDK

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top