Forecasting sales using 2 variables

[bartoni79]

Dear All,

Im in the process of trying to set targets for the total sales team. I have the current sales and the total team sales required for the next quarter. Essentially I need to divide the total sales target amongst the sales team which I can do by forecasting where I would expect each sales person to be. However, this assumes thatthey have equal opprtinity of growing; some of the sales team have a much higher market share than others and therefore their growth will be unable to continue at the same rate. Does anyone know of a function in Excel that will enable me to divide the sales teams targets up based on
1) Historical Sales
2) Another metric such as market share or growth?

Many Thanks

 

[Oaktree]

There's no Excel function that will be a substitute for business knowledge and priorities.

Instead of looking for the algebraic solution, my suggestion would be to do a driver level build for your customers.

For example, starting with your base (probably this year's forecast if you're planning next year's targets):

1) How much growth would you expect from population or category growth? Maybe it's +3% across the board. Maybe it's +10% for some customers and -5% for others.

2) How much are you investing in each customer? Is growing ABC customer a priority for your business so that your degree of partnership is much stronger? Maybe this focus causes you to task them with a higher growth rate than everyone else.

3) Do you have innovation that will be more quickly accepted at some customers than others?

etc.

If you have a build according to your business drivers, your targets will align with your business priorities, and it should be more closely aligned with your business' (and your sales team's) objectives for compensation.