Math Help Needed for my job

[multiham]

In all my years here, I don't think I have ever seen anyone ask a work question, but I am so frustrated right now and there are so many people on this forum that are WAY smarter than me.


I am trying to develop an index that will allow me to evaluate share growth for a large amount of sales people. I want to compare the share growth of our brands at a sales rep level versus the share growth of the same products nationally. In other words, is the sales rep I'm evaluating growing their share as fast as the national growth.

To add a wrinkle to the equation, I want to also factor in share opportunity. For instance, it is much easier to grow 1 share pt when your share is 20% vs. growing 1 share pt when your share is 60%.

I have absolutely no problem with the formula when both the sales rep and the national share growth are positive. Where I run into trouble is when the share growth for either the rep or national is negative.

Here is an example of both being positive and the calculation:

Rep share this year 20%, Rep share last year 18%
National share this year 10%, National share last year 9%
My formula is ((20%-18%)/82%)/((10%-9%)/91%) =2.219 telling me the rep is growing their share 2.2 times faster than national. Time for a nice bonus!
In the above the 82% represents the max the share can grow for the rep (100%-18%) and the 91% is the max the national share can grow (100%-9%)

What do I do if either one or both of the share changes are negative. My formula above does not work. For instance, if the rep share increased from 18% to 20% and the National share decreased from 9% to 8%. The result is a negative number even though the rep is really doing well.

Any thoughts would be appreciated.

[Puma Cat]

Originally Posted by multiham

In all my years here, I don't think I have ever seen anyone ask a work question, but I am so frustrated right now and there are so many people on this forum that are WAY smarter than me.


I am trying to develop an index that will allow me to evaluate share growth for a large amount of sales people. I want to compare the share growth of our brands at a sales rep level versus the share growth of the same products nationally. In other words, is the sales rep I'm evaluating growing their share as fast as the national growth.

To add a wrinkle to the equation, I want to also factor in share opportunity. For instance, it is much easier to grow 1 share pt when your share is 20% vs. growing 1 share pt when your share is 60%.

I have absolutely no problem with the formula when both the sales rep and the national share growth are positive. Where I run into trouble is when the share growth for either the rep or national is negative.

Here is an example of both being positive and the calculation:

Rep share this year 20%, Rep share last year 18%
National share this year 10%, National share last year 9%
My formula is ((20%-18%)/82%)/((10%-9%)/91%) =2.219 telling me the rep is growing their share 2.2 times faster than national. Time for a nice bonus!
In the above the 82% represents the max the share can grow for the rep (100%-18%) and the 91% is the max the national share can grow (100%-9%)

What do I do if either one or both of the share changes are negative. My formula above does not work. For instance, if the rep share increased from 18% to 20% and the National share decreased from 9% to 8%. The result is a negative number even though the rep is really doing well.

Any thoughts would be appreciated.

One thing I can tell you straightaway is that you cannot make any statistically valid conclusion about a "trend" from two data points (e.g. this year and last year). You need at least three data points to be able make any valid inference about a trend in any data set. If you can get quarterly rather than yearly data, that will get you started. Also, drawing business performance conclusions by looking at the year ago period, quarter, interval is also not advisable. Acc. to Donald J Wheeler, evaluating business performance by comparing to the classic "year ago quarter" or the monthly report, etc. is like driving down a twisty mountain road using the only the rear view mirror (reference: Understanding Variation: The Key to Managing Chaos, page 4, chapter 1, 2000, SPC Press). This is not an advisable approach. Your best approach is to gather more data and plot it in a control chart. Only then can you start to make reasonable inferences about your data.

[multiham]

Stephen - You are preaching to the choir. I have tried to convince the organization I work for (Fortune 50 company) that comparing this year to last year is not the way to go. However, it is like talking to a wall. I still live in the "Thats the way it has always been done" environment

So that leaves me no choice but to do my best with what I have.

I'm trying my best to at least make this fair and equitable for everyone no matter how developed your share is. In the past, a rep who had a 60% share at their customer and gained.9 share points while Nationally the same products had a 20% share and gained 1 share pt was rated below average. Its much easier to grow 1 share pt in a market that is underdeveloped vs. .9 share points in an overdeveloped one.

Hopefully that helps explain why I used the word frustrating in my original post.

Also, thank you for all of your thoughts.

[Puma Cat]

Originally Posted by multiham

Stephen - You are preaching to the choir. I have tried to convince the organization I work for (Fortune 50 company) that comparing this year to last year is not the way to go. However, it is like talking to a wall. I still live in the "Thats the way it has always been done" environment

So that leaves me no choice but to do my best with what I have.

Boy, I hear ya on that one. Urgh. Man, that's a tough one. I encountered the same challenges in my corporate worklife.

The problem with the "Thats the way it has always been done" mentality is that it does not provide a path for kaizen (continuous improvement) nor does it allow the data to provide insight as to WHAT must be done and WHY.

What's really hard is when you have hard data with full statistical analysis and p-values that tell you what the truth is and the degree of statistical confidence, and if it goes against management wants to believe, its like spitting into the wind. Its like telling them: these are facts of the universe: f=ma and the execs are saying, "No, we don't like that, we we want f≠ma".

Deming was right; the reason most businesses (corporations) fail is not because how hard "the people who work" work, but because of the incompetence of the executive management.

Best of luck!

[Masterlu]

Which is more like...

[multiham]

Originally Posted by Masterlu

Which is more like...

How I wish we could just spin a wheel and that would be your rating. Might me more accurate than my calculations

[GSOphile]

I feel your pain. One sure way to have a really bad year was to have an exceptional year the previous year.

[Cohibaman]

Originally Posted by GSOphile

I feel your pain. One sure way to have a really bad year was to have an exceptional year the previous year.

Ding, ding, ding! Especially if it was a one-off project. “You need to find another project then.”

[antipop]

Originally Posted by multiham

What do I do if either one or both of the share changes are negative. My formula above does not work. For instance, if the rep share increased from 18% to 20% and the National share decreased from 9% to 8%. The result is a negative number even though the rep is really doing well.

Any thoughts would be appreciated.

Percentage of change will not work in this case and will give you wrong results. I’m not sure you can do what you’re trying to do easily but you need to work on absolute numbers and not percentages

[FreddieFerric]

OP, all you need to know is right here:


https://www.youtube.com/watch?v=MWsjQkY-TkM