Home > Excel Dashboards  > 

Create Cycle Plots in Excel
To Chart Seasonal Sales Data

If you have seasonal sales, or other measures of performance,
Cycle Plots can offer greater insight into your performance than
traditional charting techniques.

by Charley Kyd
February, 2008

If your company's sales are seasonable, you've probably seen a chart that looks something like this:

This Excel chart, shows the continuous trend in sales over an eleven-year period. Although the chart makes the general pattern obvious, it provides little help in understanding the details.

For example, which months have the most sales? Are sales becoming more seasonal or less so? On average, how much do we sell in June? Which month is growing most quickly? Most slowly?

Figure 2 shows another common way to present data like this. Rather than showing a continuous trend, we can use Excel to plot one data series per year.

But this approach offers little improvement. We can see the typical trends more easily during the year, but it's virtually impossible to compare year-to-year performance.

From another perspective, this approach creates an administrative problem: In January of every year we need to modify the chart to display another line. That's a maintenance step that we would rather avoid.

Naomi Robbins, writing in the January 2008 edition of the Perceptual Edge newsletter, introduces a new way to display the same data, using the Cycle Plot:

In this Excel chart, we easily can compare the performance of each month of the year. The lines with markers show the performance for each January, each February, and so on for all eleven years. The green horizontal lines show the average sales for each month during those years. And the brown lines show the trend in each month's sales during the years.

This data shows that June's sales are growing more quickly than those of the other months, and that sales in November and December are growing more slowly. Because our high-sales months are growing more quickly, this plot shows that our sales are becoming more seasonal.

In contrast, this Excel chart shows our sales by day of the week:

Here, no particular seasonality is evident during the week. Saturday's sales seem to be growing more slowly, but the random nature of the results makes the trend line fairly useless in this instance.

How to Create a Cycle-Plot Chart in Excel

Three steps are needed to create a Cycle Plot like those in Figures 3 and 4. First, set up the worksheet and its data. Second, assign range names to the data. And finally, create the chart.

To keep this article from turning into a short book, I'm going to ignore several questions that you might have about this process:

1. The data begins as one long column of sales, as charted in the first figure. To arrange the data for Figures 2 through 4, I use SUMPRODUCT formulas to summarize the data by month and year, and also by weekday and year. You can learn more about SUMPRODUCT in the article, Use Excel's SUMPRODUCT To Summarize Worksheet Data.

2. With the exception of their formatting, the line charts in Figures 1 and 2 are quite common. Virtually any information about Excel charts can show you how to create similar charts.

3. The colors in the charts above aren't standard Excel colors. Users of Classic Excel can learn how to set up colors like this in Display Any Colors in Excel. Users of New Excel can assign the colors directly.

4. The charts have a dashboard-style appearance because I copied them from an unfinished Excel dashboard report that includes Cycle Plots. (Excel users can add Cycle Plots to Excel dashboards, just as we add any other chart type.) You can learn how to create and format Excel dashboards in my ebook, Dashboard Reporting With Excel.

So, with those items aside, let's concentrate on how to set up a Cycle Plot chart as shown in Figure 3.

Step 1: Set Up the Workbook and Data

Create a new workbook with one worksheet. Name the worksheet Chart and then save the workbook as CyclePlot.xls or CyclePlot.xlsx, depending on your version of Excel. Set up the top area of the worksheet like this:

This table shows data from January, 1997, through January, 2008. The remainder of row 18 contains #N/A. To eliminate the work of entering the numbers yourself, you can select the cells in the table below, copy them, and then paste them into Excel.

Be sure to format the gray border rows as shown in rows 6 and 19. And make sure the border rows are empty.

The formula at the cell address below contains an array formula:

C20:  =ROWS(C6:C19)-SUM(IF(ISNA(C6:C19),1,0))-2

To enter it as an array formula, type in the formula as shown, but hold down your Ctrl and Shift keys before you press Enter. After you do so, Excel will begin and end your formula with braces: { and }. Then copy the cell to the right as shown.

You now can enter the formulas that return the averages and the trends. To do so, add the following sections to your worksheet:

Columns A and B have the values shown. Here are representative formulas for the other numbers:

C24:  =AVERAGE(OFFSET(C$6,1,0,C$20))
C25:  =C24

Copy cell C24 to the right through cell N24. Copy cell C25 to the range C25:N35.

C40:  =TREND(OFFSET(C$6,1,0,C$20),OFFSET($A$6,1,0,C$20),$A40)

Copy cell C40 to the range C40:N51.

(Continued on Page 2)