Forecasting von Sales Zahlen

Beispiel: Eine Kategorie, 14 Modelle

R
time series
Predifo
forecasting
Author

Jürgen Mayer

Published

April 15, 2024

Im folgenden werden Forecasts von Saleszahlen erzeugt. Das entsprechende R script wird exportiert und kann dann weiterverwendet werden.

Setup

[1] "English_United States.1252"

##Aufbereitung und Filterung der Daten

Namen werden für die Standardisierung vereinheitlicht:

  1. date

  2. category

  3. value

Forecasting

Übergabe aus ist tsbl. 000 Zielsetzung: Bestimmung eines Modells, u.a. für die Verwendung in M Query.

Folgende 14 Modelle sind in diesem Beispiel implementiert:

  • Naive” = NAIVE(value),

  • Mean” = MEAN(value),

  • Seasonal naive” = SNAIVE(value),

  • Arima” = ARIMA(log(value)),

  • Arima stepwise” = ARIMA(value),

  • Arima search” = ARIMA(value, stepwise = FALSE),

  • Drift” = NAIVE(value ~ drift()),

  • ETS” = ETS(value ~ error(“A”) + trend(“N”) + season(“N”)),

  • Holt’s Method” = ETS(value ~ error(“A”) + trend(“A”) + season(“N”)),

  • Damped Holt’s Method” = ETS(value ~ error(“A”) + trend(“Ad”, phi = 0.9) + season(“N”)),

  • Holt-Winter add” = ETS(value ~ error(“A”) + trend(“A”) + season(“A”)),

  • Holt-Winter mul” = ETS(value ~ error(“M”) + trend(“A”) + season(“M”)),

  • Holt-Winter damp” = ETS(value ~ error(“M”) + trend(“Ad”) + season(“M”)),

  • NNETAR” = NNETAR(sqrt(value)),

Für jede Kategorie werden alle 14 Modelle trainiert und geprüft.

Training

Die vorliegenden Daten werden in die Bereiche Training und Prüfung aufgeteilt:

Weitere Darstellungen

Genauigkeit

Für die Bewertung der Berechnungen gibt es diverse Ansätze. Hier wird der kleinste Wert des Mean Average Percentage Error (MAPE) gesucht, wobei andere Ansätze möglich und implementiert werden können.

Accuracy Training.
category .model .type RMSE MAE MAPE MASE RMSSE
10 Arima Test 11161.39 8819.45 30.63 1.67 1.51
10 Arima search Test 10603.42 8586.25 31.63 1.63 1.44
10 Arima stepwise Test 11121.69 8818.31 30.78 1.67 1.51
10 Damped Holt’s Method Test 9800.20 7535.10 30.25 1.43 1.33
10 Drift Test 19293.91 17348.78 71.99 3.28 2.62
10 ETS Test 11297.16 8937.77 30.74 1.69 1.53
10 Holt’s Method Test 27607.87 23991.63 94.67 4.54 3.75
10 Holt-Winter add Test 11175.10 8861.66 31.13 1.68 1.52
10 Holt-Winter damp Test 11943.42 9561.87 33.17 1.81 1.62
10 Holt-Winter mul Test 11588.87 9150.40 32.99 1.73 1.57
10 Mean Test 16193.32 13581.01 38.71 2.57 2.20
10 Naive Test 15234.38 12643.64 55.09 2.39 2.07
10 Seasonal naive Test 15068.32 12323.61 45.96 2.33 2.04
20 Arima Test 823.32 635.23 28.29 1.17 1.14
20 Arima search Test 838.93 648.29 28.50 1.19 1.16
20 Arima stepwise Test 843.56 647.72 28.32 1.19 1.17
20 Damped Holt’s Method Test 814.69 640.15 29.24 1.18 1.13
20 Drift Test 840.83 633.26 28.35 1.16 1.16
20 ETS Test 831.33 646.88 28.76 1.19 1.15
20 Holt’s Method Test 1827.02 1481.72 44.97 2.72 2.53
20 Holt-Winter add Test 777.24 587.25 28.58 1.08 1.08
20 Holt-Winter damp Test 822.66 636.01 28.60 1.17 1.14
20 Holt-Winter mul Test 791.30 583.17 28.94 1.07 1.10
20 Mean Test 954.91 744.90 29.40 1.37 1.32
20 Naive Test 931.58 721.28 28.96 1.32 1.29
20 Seasonal naive Test 900.54 718.92 29.80 1.32 1.25
30 Arima Test 622.37 502.55 33.63 0.67 0.62
30 Arima search Test 747.86 589.85 32.70 0.79 0.75
30 Arima stepwise Test 636.81 509.00 32.67 0.68 0.64
30 Damped Holt’s Method Test 606.90 502.50 35.65 0.67 0.61
30 Drift Test 728.31 563.56 31.27 0.75 0.73
30 ETS Test 633.96 507.40 32.76 0.68 0.63
30 Holt’s Method Test 816.44 633.99 32.46 0.84 0.81
30 Holt-Winter add Test 858.57 707.62 36.16 0.94 0.86
30 Holt-Winter damp Test 670.74 548.89 33.49 0.73 0.67
30 Holt-Winter mul Test 708.16 547.99 30.41 0.73 0.71
30 Mean Test 639.50 534.25 41.27 0.71 0.64
30 Naive Test 679.28 534.69 32.05 0.71 0.68
30 Seasonal naive Test 760.17 641.25 38.24 0.85 0.76
40 Arima Test 1360.20 1027.19 29.50 0.66 0.67
40 Arima search Test 1433.65 1074.37 28.53 0.69 0.70
40 Arima stepwise Test 1433.65 1074.37 28.53 0.69 0.70
40 Damped Holt’s Method Test 1451.85 1089.45 28.55 0.70 0.71
40 Drift Test 1573.97 1381.80 47.96 0.89 0.77
40 ETS Test 1383.59 1050.84 29.38 0.68 0.68
40 Holt’s Method Test 1332.97 1006.53 31.30 0.65 0.65
40 Holt-Winter add Test 1416.63 1119.35 35.34 0.72 0.69
40 Holt-Winter damp Test 1589.46 1273.85 41.94 0.82 0.78
40 Holt-Winter mul Test 1476.31 1134.67 33.34 0.73 0.72
40 Mean Test 1450.44 1088.58 28.56 0.70 0.71
40 Naive Test 1440.78 1214.50 40.32 0.78 0.71
40 Seasonal naive Test 2148.98 1724.47 50.91 1.11 1.05
50 Arima Test 1859.82 1533.93 62.96 1.25 0.79
50 Arima search Test 1683.20 1409.64 57.35 1.15 0.72
50 Arima stepwise Test 1724.88 1449.11 53.99 1.18 0.74
50 Damped Holt’s Method Test 1798.59 1559.30 61.27 1.27 0.77
50 Drift Test 1677.67 1374.46 49.28 1.12 0.72
50 ETS Test 1721.65 1378.64 47.31 1.12 0.74
50 Holt’s Method Test 1796.82 1524.76 60.93 1.24 0.77
50 Holt-Winter add Test 1673.72 1258.38 47.27 1.02 0.71
50 Holt-Winter damp Test 2081.37 1520.02 54.56 1.24 0.89
50 Holt-Winter mul Test 2191.36 1470.62 57.66 1.20 0.94
50 Mean Test 2173.26 1520.92 36.12 1.24 0.93
50 Naive Test 1739.88 1339.42 43.02 1.09 0.74
50 Seasonal naive Test 2087.81 1401.06 43.48 1.14 0.89
60 Arima Test 498.32 373.46 51.99 2.06 1.41
60 Arima search Test 423.88 303.13 42.53 1.67 1.20
60 Arima stepwise Test 423.88 303.13 42.53 1.67 1.20
60 Damped Holt’s Method Test 415.94 277.52 34.15 1.53 1.18
60 Drift Test 417.58 275.55 32.34 1.52 1.18
60 ETS Test 412.48 274.74 33.55 1.51 1.17
60 Holt’s Method Test 476.50 322.34 33.83 1.77 1.35
60 Holt-Winter add Test 428.87 305.35 41.84 1.68 1.21
60 Holt-Winter damp Test 458.59 312.40 38.43 1.72 1.30
60 Holt-Winter mul Test 494.06 333.20 42.66 1.83 1.40
60 Mean Test 540.12 400.96 37.38 2.21 1.53
60 Naive Test 455.04 309.67 32.21 1.71 1.29
60 Seasonal naive Test 422.74 283.17 32.14 1.56 1.19
70 Arima Test 4875.65 3919.87 79.34 1.77 1.61
70 Arima search Test 4969.81 3901.54 84.43 1.76 1.64
70 Arima stepwise Test 5037.22 3914.14 83.62 1.77 1.66
70 Damped Holt’s Method Test 4800.17 3865.56 79.25 1.75 1.58
70 Drift Test 6073.86 5074.24 69.44 2.29 2.00
70 ETS Test 5135.78 4108.54 75.28 1.86 1.69
70 Holt’s Method Test 4639.71 3674.27 77.83 1.66 1.53
70 Holt-Winter add Test 4669.40 3394.13 83.60 1.53 1.54
70 Holt-Winter damp Test 4851.09 3551.87 79.58 1.61 1.60
70 Holt-Winter mul Test 4996.81 3766.61 86.13 1.70 1.65
70 Mean Test 6978.56 5931.09 70.43 2.68 2.30
70 Naive Test 6500.59 5471.53 70.66 2.47 2.14
70 Seasonal naive Test 5338.98 3878.36 94.55 1.75 1.76
Ausgewählte Modelle
Auswahl per kleinstem MAPE
category .model .type Kriterien
RMSE MAE MAPE MASE RMSSE
10 Damped Holt's Method Test 9,800.20 7,535.10 30.25% 1.43 1.33
20 Arima Test 823.32 635.23 28.29% 1.17 1.14
30 Holt-Winter mul Test 708.16 547.99 30.41% 0.73 0.71
40 Arima search Test 1,433.65 1,074.37 28.53% 0.69 0.70
50 Mean Test 2,173.26 1,520.92 36.12% 1.24 0.93
60 Seasonal naive Test 422.74 283.17 32.14% 1.56 1.19
70 Drift Test 6,073.86 5,074.24 69.44% 2.29 2.00
fit_accuracy, alle MAPE-Werte
category Naive Mean Seasonal naive Arima Arima stepwise Arima search Drift ETS Holt’s Method Damped Holt’s Method Holt-Winter add Holt-Winter mul Holt-Winter damp
10 26.87 23.57 22.70 17.25 17.59 16.47 26.99 17.91 20.45 18.34 16.92 17.55 16.96
20 25.99 20.06 21.57 15.38 16.30 15.98 26.07 16.83 18.68 16.59 14.78 14.98 13.31
30 36.23 29.18 39.08 27.47 29.95 27.80 36.16 30.33 28.04 29.19 25.52 26.34 25.54
40 63.28 49.15 56.00 40.44 48.01 48.01 63.56 45.52 48.57 48.00 45.30 43.72 44.50
50 71.02 74.50 48.95 30.25 45.16 43.69 71.43 40.37 46.99 44.57 46.49 42.74 42.03
60 43.83 43.23 23.42 17.62 24.96 24.96 44.04 29.59 31.25 29.00 20.51 22.46 22.54
70 37.77 35.46 25.98 21.85 21.53 21.25 37.78 23.75 25.64 23.38 22.48 22.25 21.74

Forecast

# A tsibble: 1,183 x 7 [1M]
# Key:       category, .model [91]
   category .model Datum_ym            value .mean                    `80%`
      <dbl> <chr>     <mth>           <dist> <dbl>                   <hilo>
 1       10 Naive  2021 Dec N(7345, 9.3e+07)  7345 [ -5015.333, 19705.33]80
 2       10 Naive  2022 Jan N(7345, 1.9e+08)  7345 [-10135.151, 24825.15]80
 3       10 Naive  2022 Feb N(7345, 2.8e+08)  7345 [-14063.725, 28753.73]80
 4       10 Naive  2022 Mar N(7345, 3.7e+08)  7345 [-17375.667, 32065.67]80
 5       10 Naive  2022 Apr N(7345, 4.7e+08)  7345 [-20293.546, 34983.55]80
 6       10 Naive  2022 May N(7345, 5.6e+08)  7345 [-22931.510, 37621.51]80
 7       10 Naive  2022 Jun N(7345, 6.5e+08)  7345 [-25357.368, 40047.37]80
 8       10 Naive  2022 Jul N(7345, 7.4e+08)  7345 [-27615.302, 42305.30]80
 9       10 Naive  2022 Aug N(7345, 8.4e+08)  7345 [-29736.000, 44426.00]80
10       10 Naive  2022 Sep N(7345, 9.3e+08)  7345 [-31741.806, 46431.81]80
# ℹ 1,173 more rows
# ℹ 1 more variable: `95%` <hilo>

Vergleich der Jahressummen

Jahressumme
-
category Jahr .model
2016 2017 2018 2019 2020 2021 2022
10 283,039.00 295,891.00 353,212.00 335,876.00 461,236.00 427,514.00 470,420.28 Damped Holt's Method
20 34,470.00 31,974.00 33,763.00 32,579.00 38,141.00 34,048.00 34,317.30 Arima
30 27,444.00 18,674.00 20,807.00 23,839.00 23,776.00 20,122.00 20,259.28 Holt-Winter mul
40 39,489.00 43,764.00 46,321.00 38,067.00 47,156.00 45,781.00 46,130.59 Arima search
50 29,264.00 36,978.00 42,858.00 38,070.00 40,004.00 46,878.00 30,379.77 Mean
60 9,390.00 13,007.00 11,684.00 11,036.00 12,507.00 12,948.00 13,546.00 Seasonal naive
70 108,913.00 116,704.00 141,101.00 130,070.00 156,409.00 169,346.00 38,851.66 Drift

Export

Die generierten Daten werden exportiert.

Möglichkeiten:

  • lokales file

  • Speichern in SQL Server