# Seasonality Forecasting with Time Series Decomposition

## Excel Help for Seasonality Forecasting With Time Series Decomposition in Managing Supply Chain and Inventory

### Seasonality Forecasting With Time Series Decomposition

Rate this: Forecasting time series data for inventory management or any other analysis often removes volatility that is inherent in the historical data. For simple analysis this can be acceptable to identify the linear trend for predicted values in the future. More specific predictions, however, often require the prediction of variances such as seasonality and other period specific nuances that can affect decision making. Such cases include cash flow and budget planning in business scenarios. Common period volatility can be identified within periodic data through time series decomposition which can then be applied to the forecast results to then predict with embedded risk dynamics.

One solution within the built in Excel statistical functions is the FORECAST.ETS.SEASONALITY function; however little control is given on the algorithm applied to seasonality calculations. An alternative approach is to apply a VBA function that allows control for periodicity and outlier thresholds to replicate historical fluctuates that are likely to repeat in future periods within the forecast. To facilitate automating the periodic variation quantification, the function can detect the frequency of the time series data as either daily, weekly, monthly, quarterly or annual from the dates. This could also, of course be overridden with modification to the function variables.

The following VBA function takes the following arguments:
• arrData - A 2-dimensional array of dates and values for the historical data.
• per_for - The number of periods to forecast.
• thres - A threshold to ignore outliers that have a multiple of more than this amount compared to other same time category observations. A value of 3 would ignore any observations that have a magnitude of more than 3 times the average variation compared to other time category observations of the same periodicity. Use zero to ignore or a very low number such as 0.0001 to remove time decomposition from the forecast.
• fmeth - A string as either Linear, 2nd Poly, 3rd Poly or Exponential to define how the base forecast method is defined before applying seasonal variation.
VBA Code:
 Function Decomposition_Forecast(arrData As Variant, per_for As Long, thres As Double, fmeth As String) 'arrData 0-date, 1-value Dim arrOut As Variant Dim arrWork As Variant Dim strCat As String Dim arrCat As Variant Dim arrTmp As Variant Dim i As Long Dim j As Long Dim counta As Long Dim suma As Double Dim sumw As Double Dim minj As Double Dim maxj As Long Dim arr1 As Variant Dim arr2 As Variant Dim freq As String Dim ddiff As Long Dim per_ma As Long Dim arrCoef As Variant Dim b1 As Double Dim b2 As Double Dim b3 As Double Dim intc As Double 'work out frequency interval suma = 0 counta = 0 For i = 1 To UBound(arrData, 1) suma = suma + CLng(arrData(i, 0) - arrData(i - 1, 0)) counta = counta + 1 Next i ddiff = CLng(suma / counta) freq = "d" If ddiff > 5 Then freq = "ww" End If If ddiff > 20 Then freq = "m" End If If ddiff > 70 Then freq = "q" End If If ddiff > 250 Then freq = "yyyy" End If 'setup working arrays ReDim arrWork(UBound(arrData, 1) + per_for, 6) ReDim arr1(UBound(arrData, 1)) ReDim arr2(UBound(arrData, 1)) ReDim arrOut(per_for - 1, 1) strCat = "" For i = 0 To UBound(arrData, 1) 'count arrWork(i, 0) = i + 1 'date arrWork(i, 1) = arrData(i, 0) 'category arrWork(i, 2) = DatePart(freq, arrWork(i, 1), 2) If InStr(1, strCat, "[[[" & arrWork(i, 2) & "]]]", vbTextCompare) = 0 Then If i > 0 Then strCat = strCat & "|" End If strCat = strCat & "[[[" & arrWork(i, 2) & "]]]" End If 'value arrWork(i, 3) = arrData(i, 1) Next i 'forecast part For i = UBound(arrData, 1) To UBound(arrData, 1) + per_for 'count arrWork(i, 0) = i + 1 'date arrWork(i, 1) = DateAdd(freq, 1, arrWork(i - 1, 1)) 'category arrWork(i, 2) = DatePart(freq, arrWork(i, 1), 2) Next i 'category array arrTmp = Split(strCat, "|") ReDim arrCat(UBound(arrTmp), 2) For i = 0 To UBound(arrTmp) arrCat(i, 0) = arrTmp(i) arrCat(i, 1) = 0 arrCat(i, 2) = 0 arrCat(i, 0) = Replace(arrCat(i, 0), "[[[", "", 1, , vbTextCompare) arrCat(i, 0) = Replace(arrCat(i, 0), "]]]", "", 1, , vbTextCompare) Next per_ma = UBound(arrCat, 1) + 1 'smoothed moving average and ratio For i = 0 To UBound(arrData, 1) counta = 0 suma = 0 minj = i - CLng(per_ma / 2) If minj < 0 Then minj = 0 End If For j = minj To i suma = suma + arrData(j, 1) counta = counta + 1 Next j maxj = i + CLng(per_ma / 2) - 1 If maxj > UBound(arrData, 1) Then maxj = UBound(arrData, 1) End If For j = i + 1 To maxj suma = suma + arrData(j, 1) counta = counta + 1 Next j arrWork(i, 4) = 0 If counta > 0 Then arrWork(i, 4) = suma / counta arr1(i) = arrWork(i, 0) arr2(i) = arrWork(i, 4) 'ratio arrWork(i, 5) = 1 If arrWork(i, 4) <> 0 Then arrWork(i, 5) = arrWork(i, 3) / arrWork(i, 4) End If 'threshold check If Abs(thres) > 0 Then If arrWork(i, 5) > 1 + thres Then arrWork(i, 5) = 1 + thres End If If arrWork(i, 5) < 1 - thres Then arrWork(i, 5) = 1 - thres End If End If End If Next i 'forecast coefficients arrCoef = Forecast_Coefs(arr2, CStr(fmeth)) b1 = arrCoef(0) b2 = arrCoef(1) b3 = arrCoef(2) intc = arrCoef(3) 'get category counts For i = 0 To UBound(arrCat, 1) counta = 0 For j = 0 To UBound(arrWork, 1) If CStr(arrWork(j, 2)) = CStr(arrCat(i, 0)) And arrWork(j, 5) <> "" Then counta = counta + 1 End If Next j arrCat(i, 2) = counta Next i 'category ratio average For i = 0 To UBound(arrCat, 1) suma = 0 counta = 0 sumw = 0 For j = 0 To UBound(arrWork, 1) If CStr(arrWork(j, 2)) = CStr(arrCat(i, 0)) And arrWork(j, 5) <> "" Then counta = counta + 1 suma = suma + (arrWork(j, 5) * counta / arrCat(i, 2)) sumw = sumw + (counta / arrCat(i, 2)) End If Next j arrCat(i, 1) = 0 If sumw > 0 Then arrCat(i, 1) = suma / sumw End If For j = 0 To UBound(arrWork, 1) If CStr(arrWork(j, 2)) = CStr(arrCat(i, 0)) Then arrWork(j, 6) = arrCat(i, 1) End If Next j Next i 'forecast and output array counta = 0 For i = UBound(arrData, 1) + 1 To UBound(arrData, 1) + per_for 'base forecast 'default to linear arrWork(i, 3) = (b1 * arrWork(i, 0)) + intc If fmeth = "Exponential" Then arrWork(i, 3) = intc * Exp((b1 - 1) * arrWork(i, 0)) End If If fmeth = "3rd Poly" Then arrWork(i, 3) = (b3 * (arrWork(i, 0) ^ 3)) + (b2 * (arrWork(i, 0) ^ 2)) + (b1 * arrWork(i, 0)) + intc End If If fmeth = "2nd Poly" Then arrWork(i, 3) = (b2 * (arrWork(i, 0) ^ 2)) + (b1 * arrWork(i, 0)) + intc End If 'apply ratio For j = 0 To UBound(arrCat, 1) If CStr(arrWork(i, 2)) = CStr(arrCat(j, 0)) Then arrWork(i, 3) = arrWork(i, 3) * arrCat(j, 1) End If Next j arrOut(counta, 0) = arrWork(i, 1) arrOut(counta, 1) = arrWork(i, 3) counta = counta + 1 Next i Decomposition_Forecast = arrOut End Function

Coefficients and intercept values for the base forecast method are returned from a helper function which could also be used independently to classic forecasting of time series or non-time series data.

VBA Code:
 Function Forecast_Coefs(y_vals As Variant, meth As String) Dim vectorX() As Double Dim vectorY() As Double Dim i As Long Dim arrCoef As Variant Dim b1 As Double Dim b2 As Double Dim b3 As Double Dim intc As Double Dim arrOut(3) As Variant ReDim vectorX(0 To UBound(y_vals), 0 To 0) ReDim vectorY(0 To UBound(y_vals), 0 To 0) For i = 0 To UBound(y_vals) vectorX(i, 0) = i + 1 vectorY(i, 0) = y_vals(i) Next i b1 = 0 b2 = 0 b3 = 0 intc = 0 On Error GoTo skiphere If meth = "Exponential" Then arrCoef = Application.WorksheetFunction.LogEst(vectorY, vectorX) b1 = arrCoef(1) intc = arrCoef(2) End If If meth = "3rd Poly" Then arrCoef = Application.WorksheetFunction.LinEst(vectorY, Application.Power(vectorX, Array(1, 2, 3))) b1 = arrCoef(3) b2 = arrCoef(2) b3 = arrCoef(1) intc = arrCoef(4) End If If meth = "2nd Poly" Then arrCoef = Application.WorksheetFunction.LinEst(vectorY, Application.Power(vectorX, Array(1, 2))) b1 = arrCoef(2) b2 = arrCoef(1) intc = arrCoef(3) End If skiphere: If meth = "Linear" Then arrCoef = Application.WorksheetFunction.LinEst(vectorY, vectorX) b1 = arrCoef(1) intc = arrCoef(2) End If arrOut(0) = b1 arrOut(1) = b2 arrOut(2) = b3 arrOut(3) = intc Forecast_Coefs = arrOut End Function

We can apply this function as a user defined function formula in Excel by selection the ranges of dates and values and specifying the number of periods to forecast and threshold multiplier. Note that the number of cells corresponding to the number of forecast periods need to be then selected and the formula entered as an array formula by using Control-Shift-Enter. The VBA for the user defined function is:
VBA Code:
 Public Function DECOMPFORECAST(date_range As Range, value_range As Range, periods As Long, thres As Double) Dim arrRes As Variant Dim arrD As Variant Dim arrV As Variant Dim i As Long Dim arrData As Variant arrD = date_range.Value arrV = value_range.Value ReDim arrData(UBound(arrD, 1) - 1, 1) For i = 1 To UBound(arrD, 1) arrData(i - 1, 0) = arrD(i, 1) arrData(i - 1, 1) = arrV(i, 1) Next i arrRes = Decomposition_Forecast(arrData, periods, thres) DECOMPFORECAST = arrRes End Function