Question for the PH Excel wizards

[Kermit power]

Afternoon all,

I'm trying to get my head around building an automatic commission calculator, and am hoping the usual PH combined expertise can help me.

We have a fairly typical commission structure, whereby we get paid a percentage of any sale made, and that percentage varies based on where you are against overall target attainment. The table looks like this:

Target Attainment	Commission Rate
0 - 50%	5%
50 - 100%	8%
100 - 150%	15%
150%+	10%

I then have a list of sales with a monetary value, and my target. Obviously if I was on a flat percentage the calculation would be a doddle, but I'm trying to figure out a way to automatically account for the tiers.

Let's say my target is £100.

Closing a deal for £40 would pay me £2, since it's all paid at 5% in the 0-50% tier, but if I then close another £40 deal, that would pay me £2.90, as the first £10 is paid at 5% and the remaining £30 is paid at 8%.

Is there a way to have Excel automatically calculate the payout on deals based on achievement against target?

[Basil Brush]

I'd do the calc on a cumulative basis, deducting the previous cumulative to give the marginal commission amount.

[Kermit power]

Basil Brush said:

I'd do the calc on a cumulative basis, deducting the previous cumulative to give the marginal commission amount.

How do I automate that though?

[sparkyhx]

You can do it a few ways, but personally I'd break it down and add it up. If necessary hide the columx or put them on another sheet.

I'm on my phone otherwise I'd be more helpful

[paulrockliffe]

Kermit power said:

Basil Brush said:

I'd do the calc on a cumulative basis, deducting the previous cumulative to give the marginal commission amount.

How do I automate that though?

It relies on you putting your deals in in date order. You have a column that is =SUM(A$1:A1). When this formula is copied down it extend its range automatically and give you a cumulative to date figure for each deal. Then calculate the commission from this figure and your percentage structure.

To make it work across your thresholds you would do the formula conditionally; look at the cumulative total from the previous transaction and see what percentage you were at, compare the new cumulative total to see which percentage you end at. If they're the same then add that percentage. If they're not, calculate the split based on the cliff-edge value and do two calculations at the two different rates and amounts and add those both on to the transaction.

It's quite tricky and a bit messy as it leaves a lot of scope to do it slightly wrong and not realise or for it to be broken in the future though.

Personally I would do the whole thing in the Power Query bit of Excel as you can load all your tables in there and then iterate over the rows using DAX in a much more controlled way (ie you can lose the need to keep your transactions in the right order) but that's probably a pretty steep learning curve if you've not used that before.