Calculate Average Trends in Excel

Helping some students at Columbia with an Excel exercise and though I’d post the exercise here since it brings up some interesting concepts around calculate trends and changes in values. This specific exercise is on how to calculate average trends in Excel and how to project future values based on these trends. So assume you have the data below:

Based on the data, here are the objectives of the exercise:

  • Calculate each country’s yearly change trend, by taking the average of its year-over-year difference
  • Project each country’s 2011 value, based on its 2010 value and its average trend value

Calculating Trends

When I think of “yearly change trend,” I think of the percentage change in value. For instance, the yearly change trend from 2006 to 2007 is 15.9% (5.29/6.29 – 1). So now we know the year-over-year difference for 2006-2007, we should find the same changes for 2007-2008, 2008-2009, etc.

In cell G15, the formula I entered was  =AVERAGE((C15/B15-1),(D15/C15-1),(E15/D15-1),(F15/E15-1)) . We are simply taking the average of each year-over-year change.  This comes out to 9.1% for Afghanistan, which translates to Afghanistan’s tax revenue as a % of GDP increased 9.1% from 2006 to 2007. Now if we want to project this to 2011 based on the trend we just found, the formula in H15 would simply be  =F15*(1+G15) which comes out to 9.07 as the tax revenue for Afghanistan (as a % of GDP). Here is what the table looks like once all the values are filled out:

What Does Yearly Trend Change Really Mean?

Another way to think about this instead of yearly change as a % is the absolute value change for the tax revenue. Now our formula in G15 looks like this:  =AVERAGE((C15-B15),(D15-C15),(E15-D15),(F15-E15))  which equals 0.51 for Afghanistan in cell G15. This value represents the absolute change from year to year in tax revenue as a % of GDP instead of the percentage change. Is this correct?

Since these differences are values in tax revenue as a percent of GDP, a 2% change in value is different from a 2% increase in the tax revenue. Therefore, to compare apples to apples, you must take the percentage change year-over-year in order to accurately find a benchmark across all countries. Consequently, by applying the average of the differences to 2010’s number to find the 2011 projected tax revenue, you are not accurately applying a growth factor to the 2010 number. Then, when you are comparing projections across the entire list of countries, the benchmarks are all different since you are not applying a percentage growth factor to 2010’s number but rather a value growth factor. This is what the incorrect data looks like: