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?


1 Answer 1


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.

  • $\begingroup$ Yeah. Annualization would work but it would have to be done manually. :( $\endgroup$
    – Scott
    May 30, 2012 at 20:02
  • $\begingroup$ 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. $\endgroup$ May 30, 2012 at 20:24
  • $\begingroup$ I am comfortable with PHP for programming and I have been able to make some visual basic work within excel... $\endgroup$
    – Scott
    May 31, 2012 at 13:25
  • $\begingroup$ 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. $\endgroup$ May 31, 2012 at 15:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.