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
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 SQLNote that this takes in daily data and builds a monthly model.
SELECT month,product, (value/avg_value) AS index
from
(
SELECT a1.month, a1.product, sum(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, sum(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!
