1

I have been reading the book, Good to Great by Jim Collins. It says that you should focus on information that "cannot be ignored," and that you should focus on the right "denominator."

I thought it would be good to measure "Average Growth Rate per Producing Entity." If we are providing value and support, we would expect to be helping them "produce more."

I have production data in an excel spreadsheet that I can summarize.

I primarily use "pivot tables" for my data analysis. I know that you can do "percentage change over previous time period" but I end up with distortions for the first year, when people don't have a full period to produce. I also am not sure how to summarize it.

Is there a better program that I should use for this? Is there a good way to do this in excel?

I was thinking you could take the geometric mean (what growth rate would you need on the initial production number in order to end up with the final number -- or so I believe it is explained) and besides that I'm not sure where to start. I would love to use pivot tables or something similar where I can just hit refresh and get the updated version of the numbers.

Any ideas out there?

Glen_b
  • 257,508
  • 32
  • 553
  • 939
Scott
  • 153
  • 1
  • 4

1 Answers1

2

I think the standard thing to use is the annualized rate. If you have less than one year, you simply multiply the current observed value by the reciprocal of the proportion of a year that it represents. For example, if you have one month (1/12th of a year), you multiply by 12, for one quarter, you multiply by 4, with just 16 days, you multiply by 365/16, etc. With observations over a shorter time period, you will have more measurement error in your estimate, as you suspect. Unfortunately, this is unavoidable. As for better software, I would argue that a programming language (such as R) may offer advantages in the long run, but require a lot of effort to learn up front. For a project such as this, it may not be worth it.

gung - Reinstate Monica
  • 132,789
  • 81
  • 357
  • 650
  • Yeah. Annualization would work but it would have to be done manually. :( – Scott May 30 '12 at 20:02
  • If you have a column in which the proportion of the year covered by each datum is noted, you should be able to pretty easily code up a new column w/ a function that will compute the appropriate multiplicative factor & then drag or copy-&-paste that function across the needed range of cells. That shouldn't take too long. As I note above, learning a programming language that will allow you to do this with a few lines of reusable code will pay off in the long run, but will require an investment of time up front. – gung - Reinstate Monica May 30 '12 at 20:24
  • I am comfortable with PHP for programming and I have been able to make some visual basic work within excel... – Scott May 31 '12 at 13:25
  • I don't know PHP, nor do I really know visual basic, but you should be able to make the latter work to calculate the multiplicative factors for you in excel. – gung - Reinstate Monica May 31 '12 at 15:12