im hoping someone can help me as math is far from my strong point. Please excuse me if this post isnt to protocol but i’ll try my best to explain the problem Im trying to solve.

I want to set some fair targets for a team of sales people , without using the ‘gut feeling’ approach.

Heres the background:

we have 6 sales people between whom an increase in a global target for 2019 financial year needs to be shared. The overall increase is £2m.

The total sales target for this team 2019 is £9m, it was £7m in 2018.

The overal global increase can of course be represented by a percentage (28.57%), and last year the SUM of the 6 sales peoples individual targets was equal to the total target of £7m.

Therefore it stands to reason that to get to the £9m next year i could simply increase each individuals target by the flat 28.57%.

However, each individual didnt have an equal share of the £7m last year, as different members of the team have differing skill sets and levels of experience.

Also, last year each individual member performed differently against their own target meaning potentially some peoples targets were too easy, some were too hard. Therefore increasing each respective target with a flat 28.57% doesnt seem entirely fair.

Ive been using excel to crunch figures, using the 28.57% increase as a starting point for each person, and then trying to reduce or increase this 28.57 % for each individual, based on factors which I can proportion a value to, whilst still ensuring the resulting total global increase remains at £2m. For example, for each person the following values can be expressed as a % or decimal figure.

Indviduals 2018 revenue as a % of 2018 total revenue - percentage share of revenue

Indviduals 2018 revenue as a % of their own 2018 target - percentage to target

individual back order value as a % of global 2019 target - this is how much they already sold in 2018 but will not invoice until 2019, therefore this figure will count towards their 2019 target

id like to be able to create an excel spreadsheet which uses math to set 2019 targets dynamically.

Now I appreciate this is a long post, and my first, so apologies if I’m missing something or this isnt the right forum for this type of problem, but maybe someone will find this interesting enough to warrant some suggestions.

Many thanks to anyone reading this far and for any suggestions you might be able to offer.

Kr

Rob