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:
I know that this is a few years old, but I’d caution people against putting too much stock into creating a general trend with this method. Taking an average of an average can give you some strange results, because the annual changes are not weighted.
For instance, use the same method above using this annual sales date:
$386
$290
$169
$345
$402
$330
$224
$199
$277
If my calculations are correct, you end up with a general trend of POSITIVE 4.06%. This is counter intuitive since the most recent year is significantly less than the first year. This is mostly due to year 4 more than doubling from the previous year, but it is an average of an average.
Please let me know if you think that I missed something or if you know of a better way!
Thanks for calling this out. In this example, however, we are looking at the average of a changes to a percent (% of GDP), not absolute values. Since we only have percentages in our data set, there is no easy way to “weigh” the different years. With absolute values in your example, you could perhaps use number of units sold as a way to “weight” each year’s sales volume to get the weighted average.