Panel Chart - Monthly Cycle Chart
Trend charts show the progression of your data over time. Some of our time series data exhibit cyclical patterns, possibly annual cycles related to seasons or weekly patterns related to work schedules. In this tutorial, we will walk through producing monthly cycle chart in Excel.
You can download the example workbook for this tutorial here.
1. Trend Chart and Data
The chart below-left- shows a long term trend chart; the chart on the right shows a Monthly cycle chart of the same. This data set reflects monthly residential real estate listing inventory for the period Jan., 1993 to April, 2006.
Review of this trend chart shows a strong cyclical pattern as well as a significant drop in inventory in late 1999.
2. Monthly Cycle Chart
The monthly cycle chart, with the 12 months of the year across the X axis, shows both the average monthly values and the yearly trends for each month.
The monthly cycle chart shows that July is the highest inventory month and December is the lowest month. It also shows that every month had a marked drop-off in late 1999. This chart helps us look deeper into our data to se if there is an underlying structure not apparent in the overall trend.
3. Making a Monthly Cycle Chart in 3 Steps
To see 3 minute how-to Flash video, press arrow in video window.
The Mo_Cycle_Chart_VBA.xls workbook includes several VBA
procedures that automate Monthly Cycle Chart preparation. User
prepares the chart in 3 steps:
Building a Monthly Cycle Chart
To build this chart we use a data transformation technique to convert the X axis date values so that they plot in the appropriate month panels, as illustrated in the schematic below.