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.
(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 sawtooth 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
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
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 midpoint of those seven days, as shown in this
figure:
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:
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:
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 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 WorksheetScoped Names in Excel
The next section below asks that you create worksheetscoped
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 bookscoped 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 sheetscoped name could look
like this:
=SERIES(,,Sheet1!Expenses,1)
The seasonal forecast typically uses sheetscoped 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 sheetscoped names more easily, I first set up the
bookscoped names as placeholders. That way, I can create any
additional sheetscoped 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 bookscoped 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.
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))}
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
rowoffset, columnoffset, 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)))
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.
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 doublequotes ("").
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
