In business, it is important to track KPIs over time to evaluate performance. Often we see graphs by month or even by quarter, sometimes with trends lines sometimes without. However, with your basic line and bar charts, it is difficult to track seasonal variation and to compare one year to the next directly. Let me give you an example:
This chart shows the performance by quarter over the past ten years. Although it appears that the results have varied significantly over time, it is difficult to draw any conclusions from this data using only the table. Let’s say that you suspect that there is seasonal variation. We could plot each quarter as a separate series in the graph (with a different colour) using a stacked bar chart. Please note you can download the excel sheet I am using to create these graphs here.
Suddenly it becomes clearer that Q1 is usually the lowest performing quarter each year and that Q3 is usually the highest. This would then allow us to analyse why there is such seasonal variation – very powerful stuff! The chart isn’t very elegant though so we could also see this with a series of charts, each showing just one year:
The placement of the charts also makes it clear that there is much lower performance in Q1 each year. However setting up one graph / year is time consuming. Surely there is a better way? What about instead of plotting the actual result each year, you plotted the change in results?
I’m only showing the percentage change of three years here, but suddenly you can see that not only is Q1 always the lowest performer, but that in 2004 performance increased in Q1 by 5% and then decreased in 2005 by over 10%. For both years each quarter improved (or worsened) on the quarter before. You can start to see statistical trends. This type of chart is called a Year Over Year chart or YOY chart. It is also known as an Index Year Ago IYA chart and is used a lot in sales and statistics. In short, a YOY chart is obviously more powerful than a standard bar (or line) chart.
To plot this type of analysis, you simply need to perform a simple calculation. Imagine 2003 Q1 is in cell B4 and 2004 Q1 data is in cell B8. The calculation would be =B4/B3-1. This will give you the percentage change. To make the chart easier to read, you could also move the X axis to the bottom or top of the chart.
You can download the sample excel sheet YOY chart. I hope you find it useful!