Forecasting using a macro trend (moving average) and seasonality (seasonal index) is a common way to forecast data into the future. The two components, seasonal index and moving average, are based on prior historical trends. They come together to form a model that can be projected out for the near future.

# Seasonality (Seasonal Indexing)

Seasonal indexing is the process of calculating the high’s and low’s of each time period into an index. This is done by finding an average for an entire set of data that includes the same number of matching periods, then dividing the individual period average into that total average. This gives us an index whose total is the number of periods in a full cycle.

**Example**: There needs to be the same number of periods for each cycle. For a trailing 36 month period, there are 3 Januarys, 3 Februarys, etc. This is to not give a single month more/less weighting. Once we have the average for each month within that time period, we can divide that into the average for the entire time period. This giving us the general seasonality for each month regardless of year.

Average(Revenue for all Januarys)/Average(Revenue for all months) = January Index

# Macro Trend (Moving Average)

The moving average calculates the macro trend that has been occurring for recent periods. In a monthly model, this would be the average for the prior full 12 months.

**Example**: If it is currently August 2015, the periods included would be August 2014 to July 2015. We do not include August 2015 because it has not concluded yet. We do not have all the necessary data.

# Combining the Moving Average and Seasonal Index

To get a forecast for future dates, simply multiply the moving average and the corresponding seasonal index for the forecast month. The results will be the forecast value for each month going forward.

# Creating this Model in SiSense

**Seasonal Index SQL**

Note that this takes in daily data and builds a monthly model. This SQL breaks down the season index by month and a specific dimension (Product in this example). This can be used later to find the forecast for that dimension. However it can be removed if desired. Simply take this script, create a custom table in your ElastiCube and replace the relevant fields ( <DATE>, <PRODUCT>, <VALUE>) and table name (<TRANSACTIONS>) highlighted below.

SELECT month,product, (value/avg_value) AS index

from

(

SELECT a1.month, a1.product, avg(a1.value) AS value, a4.avg_value

from

(SELECT getmonth(<DATE>) AS month,<PRODUCT>,<VALUE>AS value FROM<TRANSACTIONS>) a1

cross join (SELECT AVG(value) AS avg_value

from (SELECT month, product, avg(value) AS value

from

(SELECT getmonth(<DATE>) AS month,<PRODUCT>,<VALUE>AS value FROM<TRANSACTIONS>) a2

GROUP BY month, product )a3

)a4 GROUP BY month, product, avg_value

)a5

The resulting dataset should look like this:

**Moving Average SQL**

This SQL finds the macro trend for recent performance. The table’s output contains a forecast first of month, month, the dimension (product), and the forecast value (trailing 12 month average). Simply replace the same fields/table as above.

SELECT

addmonths(FirstOfMonth,12) AS forecast_FirstOfMonth,

b2.month,

b2.product,

avg(a3.value) AS value

from

(SELECT year, month,createdate(year,month,1) AS FirstOfMonth, product, sum(value) AS value

from

(

SELECT getyear(<DATE>) AS year, getmonth(<DATE>) AS month,<PRODUCT>,<VALUE>

FROM<TRANSACTIONS>t

WHERE<DATE>BETWEEN createdate(getyear(addmonths(now(),-12))

,getmonth(addmonths(now(),-12))

,1)

AND

adddays(createdate(getyear(now()),getmonth(now()),1),-1)

)a1

GROUP BY year, month, product

)b2 FULL OUTER join

(SELECT product,avg(value) AS value

from

(SELECT year, month,createdate(year,month,1) AS FirstOfMonth, product, sum(value) AS value

from

(

SELECT getyear(<DATE>) AS year, getmonth(<DATE>) AS month,<PRODUCT>,<VALUE>

FROM<TRANSACTIONS>t

WHERE<DATE>BETWEEN createdate(getyear(addmonths(now(),-12))

,getmonth(addmonths(now(),-12))

,1)

AND

adddays(createdate(getyear(now()),getmonth(now()),1),-1)

)a1

GROUP BY year, month, product

)a2

GROUP BY product

)a3

ON a3.product = b2.product GROUP BY

FirstOfMonth,

b2.month,

b2.product ORDER BY

firstofMonth

The resulting dataset should look like this.

The resulting tables should be connected as seen below.

In the dashboard, the formula is as seen below. Sum(CMA_Rolling12.Value * Index.Index)

**Final Result**

Happy Forecasting!!