Calculating 5% & 10% of Commission with a £5 cap in place

Sorry - now attached.

red card.. What's that?

There's probably a fairly easy solution for this, but unfortunately it's a little difficult to follow exactly how the commission payment is calculated.

My gut feeling is that we need to know the total commission for the actives above the £5 cap, so this can then be deducted from the overall commission, so we end up with something like:

total commission - commission from actives above £5 cap + (5*actives above £5 cap)

which you can then use to get your 5% or 10% figure from?

Originally Posted by brokenbiscuits

There's probably a fairly easy solution for this, but unfortunately it's a little difficult to follow exactly how the commission payment is calculated.

My gut feeling is that we need to know the total commission for the actives above the £5 cap, so this can then be deducted from the overall commission, so we end up with something like:

total commission - commission from actives above £5 cap + (5*actives above £5 cap)

which you can then use to get your 5% or 10% figure from?

Hmm...

There is no such thing as "commission from actives above £5", because £5 is the cap... So however many users generated £5 commission, is the amount * £5.. If that makes sense?

We have the AMOUNT of actives who hit the £5.. there's a column for it..

Any ideas?

This is really confusing, because in your example for Device 1 you have 3,986 'total actives', only 232 of which exceeded £5 commission, yet they generate a 'total commission' of £115,085 - £28.87 each??

My assumption would be that this 'total commission generated' is actually a 'total generated on which commission needs to be paid'. In which case is it possible to get the same figure but only for the 232 which exceeded the £5 commission?

If my assumption is wrong, I'm totally confused and need you to explain further as to how the total commission generated column is populated.

Originally Posted by brokenbiscuits

This is really confusing, because in your example for Device 1 you have 3,986 'total actives', only 232 of which exceeded £5 commission, yet they generate a 'total commission' of £115,085 - £28.87 each??

My assumption would be that this 'total commission generated' is actually a 'total generated on which commission needs to be paid'. In which case is it possible to get the same figure but only for the 232 which exceeded the £5 commission?

If my assumption is wrong, I'm totally confused and need you to explain further as to how the total commission generated column is populated.

If you imagine we take 5% of all orders placed. That's how the "Total Commission Generated" column is derived.

In this scenario, I'm calculating giving the other party 5% (or 10% - testing both) OF the 5% we make. So we take 5% of the orders and give 5% OF THAT 5% to the other party.

So I looked at the data for ALL orders over a given month and calculated the £ we'd be giving to the third-party based on 5% and 10% of the 5% that we take, with a straight £5 to anyone who hit the £5 ceiling.

It's difficult to explain lol.


Imagine this:

• We take 5% of an order (this is our total commission column)
• We give 5% of this 5% to the other party (the "Device")
• We cap this at £5 per "Device", per month (so the most a "Device" can make in a month is £5)

So the first question is, what is the best formula to use? Or is there still data missing to complete this?

And secondly, what sort of formula can I use to gauge a rough, but best estimate for a 12-moth period?

Any thoughts?

I've lost all hope loL!

Regards,

Z

As the data is all summed up together it's impossible to know the contribution made by the 232 who went above the commission rate, and therefore the amount of total commission generated that you then need to pay 5%/10% on.

The sums would therefore be better done before summarising in a table.

Presumably you have a list of individual active devices and the commission they generated, which then is used to create this table? With that list, you would need to add another column (or 2 columns, 1 for 5%, 1 for 10%) to work out each individual commission payment, using the following formula:

=if(B1*0.05)>5,5,B1*0.05

I think that's probably your best bet anyway.

As for your second question, what do you want to estimate for the 12 month period?

Using one month's worth of data, it would be difficult anyway.

Without any further info, I'd suggest having a look at the FORECAST function.

Can you explain the formula in more detail if you wouldn't mind?

Thanks again,

Z

The formula simply says 'if 5% of the total order is over £5, pay £5, otherwise pay 5%'.

Change this to *0.1 for 10% commission.