 |
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.
[Download this Excel seasonal forecasting solution]
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
|
|