ExcelUser logo Free guides and templates
Work In Progress...After nearly ten years, I'm redesigning ExcelUser.com. This is the new design. You can learn more here. Also, if you find something wrong with the site, please tell me about the problems. And thanks for your patience.--Charley Kyd

Sales Forecasting

How to Create a Rolling Forecast of  Seasonal Sales in Excel

The variability experienced by companies with seasonal sales makes it difficult to forecast them. You can do so easily if you first deseasonalize your sales data to reveal your hidden sales trend.


Charley Kyd is a Microsoft Excel MVP by Charley Kyd, MBA
Microsoft Excel MVP
The Father of Spreadsheet Dashboards

(Note: At Generate a Rolling Forecast of Seasonal Sales In Excel, with Charts you can get access to a working version of the following workbook, which will allow you to experiment with a completed version.)

This Excel chart shows the typical saw-tooth pattern of seasonal sales. So how do you forecast them?

This Excel chart shows the typical saw-tooth pattern of seasonal sales.

Seasonal sales have about the same pattern every year, every week, or both. In some specific months or days, sales are always above average; and in other specific periods, sales are below average.

Seasonal sales typically are difficult to analyze. In good periods, it's hard to know whether good sales are better than usual; in bad periods, it's hard to know if bad sales are worse than usual.

Forecasting seasonal sales can be even more difficult. It's hard to picture any method that could generate a reliable sales forecast that has a pattern like the one shown above.

Years ago, I faced these issues when I was the CFO for a small company whose monthly sales varied from a low in winter months of about $50,000 to a high in the spring of about $1,000,000 per month.

To deal with my analytical challenges, I worked out some spreadsheet techniques that really helped. The most useful technique was to report, analyze, and forecast using deseasonalized sales.

Deseasonalized Sales

This forecast is easy to generate and updates automatically each month.The sawtooth blue line in this Excel chart shows actual sales over the past four and a half years. The black line shows deseasonalized sales...sales with the effects of seasonality removed.

By looking at deseasonalized data, we easily can see that sales fell in 2009 and in 2011, and increased slightly in the other periods.

To forecast sales, we estimate the trend of the deseasonalized sales data, and then seasonalize the trend to produce a specific sales forecast.

Let's see how that's done...

How to Deseasonalize Your Sales

The Seasonal Index is key to forecasting seasonal sales in Excel.The idea behind deseasonalized sales is easy to understand. For each month of the year we calculate a Seasonal Index (column C in this figure). This value tells us, on average, how much each month's sales vary from average sales for the year. Then to deseasonalize sales, we divide the sales for each month by its Seasonal Index. 

The most important step is to calculate the correct value for the average sales. Specifically, you need to calculate the "centered moving average" (CMA) for each period in your historical data. To find the daily CMA within a week, the averaging period is seven days. To find a monthly CMA within a year, the averaging period is 12 months...well, sort of.

Let's start with a week. If you average the sales for Monday through Sunday, you find the centered moving average for Thursday, because Thursday is at the mid-point of those seven days, as shown in this figure:

The Centered Moving Average for Weekly sales.

To find the CMA for Friday, you average the sales for Tuesday through Monday. And the CMA for Saturday is the average of the sales for Wednesday through Tuesday, as shown here:

The Centered Moving Average is calculated for each period.

Finding the CMA for seven days is easy. This is because seven is an odd number of days, which therefore has a center day.

But a year has an even number of months. So when we try to calculate the CMA for the twelve months of January through December, we have this problem:

The average for even-numbered periods falls between two periods.

This is a problem, because the average of the sales for January through December doesn't apply to either June or July. (And no, we can't cheat by arbitrarily assigning the average to one of the two months.)

To get around this problem, we also find the average for February through January of the next year, and then average these two averages, as shown here:

To center a moving average we must average two averages.

To find the CMA for August, we simply copy the Excel formula for July's CMA to the next cell. And we do the same for each month thereafter.

What are those Excel formulas? Well, let's see.

Aside 1: How to Create Worksheet-Scoped Names in Excel

The next section below asks that you create worksheet-scoped range names. Before I get into that, I need to explain why we're doing it and how to do it easily.

In Excel, range names have scope.

A name with a workbook scope can be referenced directly from any sheet in your workbook, like this:
=MyBookName

In a chart, however, and in certain other circumstances, names with a workbook scope must include the name of the workbook. In a chart, for example, a series formula that references a book-scoped name will look like this:
=SERIES(,,Book2.xlsx!Sales,1)

A name with a worksheet scope can be referenced directly from the sheet where it's defined. Otherwise, the sheet name must be included, like this:
=Sheet1!Expenses

In a chart, you use the same reference method. That is, a series formula that references a sheet-scoped name could look like this:
=SERIES(,,Sheet1!Expenses,1)

Maintaining a list of Reserved Names makes it easier to assign worksheet-scoped range names.The seasonal forecast typically uses sheet-scoped names, most of them created using the Create Names dialog. The first time you create a name using that dialog, Excel automatically gives it a workbook scope. Then, if you use the dialog to create the name in a different sheet in that workbook, Excel creates the name with a worksheet scope.

Therefore, to create sheet-scoped names more easily, I first set up the book-scoped names as placeholders. That way, I can create any additional sheet-scoped names with no problem.

To do so, launch a new workbook and name one of its worksheets Control. Enter the names shown here. Enter the formula =NA() in cell B3 and copy it down the column as shown.

Next, select the range A3:B15, press Ctrl+Shift+F3 to launch the Create Names dialog, make sure that only Left column is checked, then choose OK.

Now that you've assigned the book-scoped names, you can take the next step.

How to Calculate Centered Moving Averages and the Sales Ratios

Column A in the bottom table of the following figure shows the sequence number for each row of data. Columns B and C show actual sales by month for the fictional company.

The red lines show where I hid rows so that you can see the relevant areas of the table. And the green fill shows areas that the charts reference.

To get started, add a second worksheet to your workbook, if necessary, and name it "A" (without the quotes). In that worksheet, enter the labels in column A of the top table and row 29 of the bottom table. Format the gray cells in row 30. Copy the row of gray cells to row 104.  

The green areas are referenced by charts.Select the range B29:F104. Then choose Formulas, Defined Names, Create From Selection, or choose Ctrl+Shift+F3.

In the Create Names dialog, make sure that only Top Row is checked, then choose OK. 

Enter this formula in the cell shown:

A31:  =A30+1

Copy and paste it down is column to row 103.

Copy and paste several years of your own monthly historical sales data into column C, and the appropriate monthly dates in column B. Make sure that you paste between the top and bottom gray borders.

Enter the text shown for cell A4 and then enter this formula for the cell shown:

B4:  =COUNT(ActSales)

This formula tells us the actual number of periods of data, even when we have more rows in the table than we have data to fill them.

To name this cell, select the range A4:B4, Ctrl+Shift+F3, make sure that only Left Column is checked, then choose OK.

Cell D37 contains a formula that divides the sales in cell C37 by the centered moving average for July 2009. Here's the formula for the cell shown:

D37:  =IF($A37+6<NumSalesPds,$C37/
          AVERAGE(OFFSET($C37,-6,0,12,1),OFFSET($C37,-5,0,12,1)),"")

(I presented this formula in two lines, but you'll enter it in one long line, of course.)

The IF function in this formula returns a null string (the "" near the end of the formula) when we run out of data at the bottom of the figure. The AVERAGE function in this formula calculates the Centered Moving Average by calculating the average of two ranges of twelve cells: C31:C42 and C32:C43.

Copy the formula in cell D11 down the column to cell D103.

Now that we have the sales ratio, we need to complete another table before we can calculate the deseasonalized sales shown in column E.

How to Calculate the Seasonal Index

The figure below shows the Seasonal Index in column C. To set up this figure, add the text shown in rows 12 and 13, and the numbers shown for column A.

Enter this array formula for the cell shown:

B14:  {=AVERAGE(IF((MONTH(Dates)=$A14)*(SlsRatio>0),SlsRatio))}

The Seasonal Index is key to forecasting seasonal sales.To emphasize, this is an array formula. You type it into the formula bar as you would any other formula. (Don't type the braces.) Then hold down Ctrl+Shift and press Enter to enter the formula. When you do so, Excel will add the beginning and ending braces as shown.

This formula finds the average of all January sales ratios in column D of the preceding figure...but it does so only for the sales ratios greater than zero.

To calculate similar averages for the other eleven months, copy this formula from cell B14 to the range B15:B25.

Enter the formula for the cell shown:

B26:  =SUM(B14:B25)

The total in cell B26 is almost 12. But we need the Seasonal Index numbers to total exactly 12. So we must scale the averages to make them do so. Therefore, enter this formula for the cell shown:

C14:  =(12/$B$26)*B14

Copy the formula to the range C15:C25.

Copy the SUM formula in cell B26 to cell C26. When you do so, you'll see that the total of the SeasIndex column now equals exactly 12.

To finish off this figure, assign the title in cell C13 as the name of the twelve cells below that cell. To do so, select the range C13:C25. Then launch the Create Names dialog again by pressing Ctrl+Shift+F3. In the dialog, make sure that only Top Row is checked, then choose OK.

Aside 2: How to Create Dynamic Names

We'll be using the TREND function to calculate the trend in recent deseasonalized data. But we need to be able specify the date and data ranges that TREND relies on. We therefore must set up two dynamic range names.

Usually, dynamic range names rely on the OFFSET function, which returns a reference that's specified by settings like row-offset, column-offset, height, and width. But here, we rely on two INDEX functions joined by Excel's range operator, ":".

Here's how it works: When you specify a reference like A1:C3, you're telling Excel to return a reference that defines the area with one corner in cell A1 and the opposite corner in cell C3. Similarly, when we define a reference like =INDEX(whatever1):INDEX(whatever2), we're telling Excel to return a reference that begins with INDEX(whatever1) in one corner and INDEX(whatever2) in the opposite corner.

You now need to define two dynamic range names that rely on this and other ranges to specify the area for the TREND function to use.

By convention, I begin all dynamic range names with "d.". This groups the names together alphabetically, and it makes them easier to find and manage. (Excel treats a period (.) in a range name just like any other character.)

Therefore, to define the two following range names, press Ctrl+F3 to launch the Name Manager dialog; then choose New to launch the New Name dialog. (You also could press Ctrl+Alt+F3 to launch the New Name dialog immediately.)

Copy and paste the first name below as the name. Using the Scope dropdown list, choose sheet A. Then copy and paste the first formula below in the Refers to box. Press OK. Then repeat this process for the second dynamic range name.

d.CalcTrendDates
  =INDEX(A!Dates,A!RowTrendStarts):INDEX(A!Dates,A!NumSalesPds)

d.CalcTrendSales
   =INDEX(A!DesSales,A!RowTrendStarts):INDEX(A!DesSales,A!NumSalesPds)


Set Up the Deseasonalized Sales Formulas

With the Seasonal Index calculated in the table above, we now can deseasonalize our sales as shown in column E of the figure below and as charted near the top of this page.

Enter the formula for the cell shown:

E31:  =IF($A31>NumSalesPds,NA(),C31/INDEX(SeasIndex,MONTH(B31)))

The data that supports a rolling forecast of seasonal sales.Copy the formula down the column to cell E103.

The formula in cell E31 deseasonalizes the sales for January, 2009, by dividing those sales by the Seasonal Index for January. The formula in cell E32 divides the sales for February by the Seasonal Index for February, and so on.

The key to this formula is the INDEX function, which uses this syntax:

=INDEX(array, row_or_column_num)

Here, the "array" is the SeasIndex range name. And the row number calculated by the MONTH function. When this function references a date serial number, it returns 1 for January, 2 for February, and so on.  We can use MONTH this way in the formula because the first row of SeasIndex has January's data; the second row has February's data, and so on.

Calculate the Forecast

To begin, enter the text shown in cells A6 and A7 in the figure above. To assign the text as names in column B, select the range A6:B7; press Ctrl+Shift+F3 to launch the Create Names dialog; make sure that only Left column is checked; then choose OK.

An Excel chart of seasonal sales, deseasonalized sales, and the sales forecast.The forecast starts with the deseasonalized sales data. By looking at a chart of that data, you probably will be able to find a span of recent performance that can serve as the basis for a forecast of future periods. Then enter the date for the beginning month of that span in cell B6.

To illustrate, the black line in this chart started to follow a fairly consistent trend beginning in February, 2012. So I entered that date in cell B6.

Now enter the formula for the cell shown:

B7:  =MATCH(DateTrendStarts,Dates,0)

This formula returns the row index number for the date in cell B6.

Now comes the long formula that calculates the first forecast value:

F31:  =IF($A31<NumSalesPds,NA(),IF($A31=NumSalesPds,$C31,
      TREND(d.CalcTrendSales,d.CalcTrendDates,$B31)*INDEX(SeasIndex,MONTH($B31))))

(I presented this formula in two lines, but you'll enter it in one long line, of course.)

This formula returns #N/A for dates prior to the last period of actual sales. By doing so, I'm forcing the charts to ignore those cells. However, if you're creating this column of forecasts for other purposes, you might want to replace "NA()" in the formula with double-quotes ("").

After you enter the formula, copy it down the column to row 103.

How to Update Your Forecast 

Your forecast will update automatically each time you enter another value for Actual Sales.

To extend your forecast farther into the future, just insert additional rows immediately above the bottom gray border, and then copy the last row of formulas downward into the new area that you've added.

How to Experiment With a Completed Version of the Rolling Seasonal Forecast

I've added the workbook described here, to The Excel Experimental Laboratory at Generate a Rolling Forecast of Seasonal Sales In Excel, with Charts.



Tags: #excel, #chart, #rolling forecast, #seasonality, #seasonal index, #array formula, #TREND, #SERIES, #INDEX, #OFFSET, #COUNT, #MATCH

How to Create Cycle Plots in Excel to Chart Seasonal Sales Data

Excel Charts, Seasonality, & Analysis: Five Lessons from the WSJ

Use Excel's SUMPRODUCT To Summarize Worksheet Data

Click to see more testimonials

"Your Swipe Files have been an amazing help. Here are quotes from emails I received from my boss..."  Rondi Stearns

"We have been using IncSight QnE...It is such a great and affordable product for a nonprofit with no budget..."  Norm Frampton

Click to see more users.

Charley's SwipeFile charts


IncSight DB Excel Dashboards


IncSight Colors