Creating a Sliding Commission Scale in Excel

Copper Contributor

Hi

 

I have a Sliding Scale Commission that I pay my staff. 

They have to submit an invoice to me to show how much sales have been done and then how much I need to pay them in commission. 

I would like to create a generic Excel spreadsheet that they complete the total sales amount and it works out their commission pay in another box. 

The scale is:

£0-£2000 @ 15%
£2000-£3000 @ 16%

£3000-£4000 @ 17%
£4000+ @18%

So for example, if their sales is £2500:

they would get 15% of the £2000 = £300

then they would get 16% on the £500 = £80

 

so I would pay them £380

 

Is there a way to do this? 

TIA

5 Replies

@JoelSharpe1989 

This is a MS365 solution which tends to differ from traditional methods.  Defining the Lambda function COMMISSION to be

COMMISSION
= LAMBDA(sales,
    LET(
      limit, SORT(VSTACK(DROP(threshold, 1), sales)), 
      SUMPRODUCT(limit - threshold, percentage)
    )
  )

the worksheet formula would be

Single sales calculation
= COMMISSION(sales)

List of sales calculation
= MAP(salesList, COMMISSION)

 depending on whether a single calculation is required or whether it requires working down a list.

 

image.png

Not to hijack the question, but @Peter Bartholomew, here's a fun (to me at least) question - how would you modify your COMMISSION function to work in reverse: given a final commission (460, for example) find the sale amount (3000)?

@TheDub 

I confess, there was an element of playing with this.  I first used the function I had already written to determine the the commission corresponding to each threshold.  Then I calculated the commission shortfall for each band.  What I should have done then is select the band with the smallest positive amount and base the calculation upon that.

 

Being perverse, I performed the calculation for each band and relied upon convexity of the piecewise-linear sales/commission curve to determine that lowest value is the correct value.  The final return values include a check that the 'required' sales indeed returns the target commission.

= LET(
    delta,    target - MAP(Threshold, COMMISSIONλ),
    uplift,   delta / Percentage,
    required, MIN(Threshold + uplift),
    HSTACK(required, COMMISSIONλ(required))
  )

... and then I wonder why so many standard Excel users fail to recognise the solutions I offer as having anything to do with Excel :lol:!

 

@TheDub 

I returned to the problem you posed.  She solutions are so far from the OP requirements that I hesitated to post, but there is an important point that modern Excel supports problem solving which is far closer to programming than it is to traditional spreadsheet authoring on the grid.

 

The commission problem is simply the evaluation of points on a piecewise-linear function expressed in terms of a sequence of threshold values at which the slope changes.  The inverse problem is also that of evaluating a piecewise-linear function but with different thresholds and slopes.  To solve the new problem, I first Curried the Commissionλ function

COMMISSIONλ
= PWLINEARλ(Threshold, Percentage);


PWLINEARλ
= LAMBDA(thresholdValues, slopes, LAMBDA(values,
    LET(
        limit, SORT(VSTACK(DROP(thresholdValues, 1), values)),
        SUMPRODUCT(limit-thresholdValues, slopes)
    )
));

 

The worksheet formula then requires one to evaluate the commission applicable at each sales threshold and combine the values obtained with the inverse of the commission rates

 

Worksheet formula
= LET(
    thresholdCommission,  MAP(Threshold, COMMISSIONλ),
    salesRequired,        MAP(target, PWLINEARλ(thresholdCommission, 1/Percentage)),
    HSTACK(salesRequired, COMMISSIONλ(salesRequired))
  )

 

A massive change, very little of which is visible from the worksheet.

 

 


... there is an important point that modern Excel supports problem solving which is far closer to programming than it is to traditional spreadsheet authoring on the grid.

 


Entirely agree - though I'm still now sure what this will do the future of Excel and its massive number of users.  Will they change their approach to problem solving or just drop out?

 


  To solve the new problem, I first Curried the Commissionλ function


Yes, tomorrow morning when my colleagues come back from their (US) Memorial Day weekend I'll try to explain this one to them and see where that takes me:lol: