- Sales trend and next quarter forecast
- ANOVA of leads vs advertising spend
- Demand model and price elasticity
- Calibrate a two-rate electricity tariff
- Polynomial trend of user growth
- Trend forecast straight from the sales database
- Cost model through the origin
- Promotion uplift from the weekly sales table
- Forecast accuracy and bias test
- Seasonal indices by the ratio-to-moving-average method
- Seasonal forecast with Holt-Winters
- Stationarity test then ARIMA forecast
- Prediction bands that widen
- How long does an advertising push last? (VAR impulse responses)
- Monthly electricity load: seasonal ARIMA by maximum likelihood
- Choose the forecasting model automatically: ARIMA or exponential smoothing?
Sales trend and next quarter forecast
Fit a linear trend on 12 months of sales, compute the slope with its 95 % confidence half-width and R2, forecast months 13 to 15 and draw the measures, the trend line and the forecast bars, with the figures in the chart title.
#1. Twelve months of sales in thousands of EUR, as x,y pairs for the regression; -> "[mx]" "[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12]"; -> "[my]" "[102.4, 108.1, 111.9, 118.6, 121.0, 127.3, 131.8, 135.2, 142.7, 146.1, 151.9, 155.4]"; json load "rx_my" [my]; -> "[sales]" "["; for (-> "[i]" 0) (< [i] 12) (++ "[i]") { -> "[sales]" (concat [sales] (if (== [i] 0) {""} {", "}) "[" (+ [i] 1) ", " (json select "rx_my" (concat "/[" [i] "]")) "]"); }; -> "[sales]" (concat [sales] "]"); json unload "rx_my"; #2. Fit the trend y = a + b*month; pa rl load_from_json "rx_sales" [sales]; -> "[b]" (math round (pa rl slope "rx_sales") 3); -> "[a]" (math round (pa rl intercept "rx_sales") 3); -> "[ci]" (math round (pa rl slope_confidence_interval "rx_sales") 3); -> "[r2]" (math round (pa rl r_square "rx_sales") 4); -> "[n]" (pa rl count "rx_sales"); #3. Forecast the next quarter, months 13 to 15; -> "[p13]" (math round (pa rl predict "rx_sales" 13) 1); -> "[p14]" (math round (pa rl predict "rx_sales" 14) 1); -> "[p15]" (math round (pa rl predict "rx_sales" 15) 1); pa rl close "rx_sales"; -> "[title]" (concat "n=" [n] ": sales = " [a] " + " [b] " x month, slope 95% CI +/- " [ci] ", R2=" [r2] ", next quarter " (+ (+ [p13] [p14]) [p15]) " kEUR"); #4. Chart: measures, trend line and forecast bars; plot chart (concat "{\"title\": \"" [title] "\", \"x_label\": \"month\", \"y_label\": \"kEUR\", \"series\": [{\"name\": \"sales\", \"type\": \"points\", \"x\": " [mx] ", \"y\": " [my] "}, {\"name\": \"trend\", \"x\": [1, 15], \"y\": [" (+ [a] [b]) ", " [p15] "]}, {\"name\": \"forecast\", \"type\": \"bars\", \"x\": [13, 14, 15], \"y\": [" [p13] ", " [p14] ", " [p15] "]}]}")
The chart is shown in the Plot tab of the editor.
- Fit the trend directly on a table with
pa rl loadand a SQL query. - Compare with
stat fitand the models exp, log or power.
ANOVA of leads vs advertising spend
Feed a regression one week at a time with ad spend and leads, then print the ANOVA decomposition (SST = SSR + SSE), the standard errors, the p-value of the slope and its 90 % confidence half-width.
#1. Weekly ad spend in kEUR and leads, the model is fed one week at a time; pa rl load_empty "rx_ads"; json load "rx_x" "[2.0, 3.5, 1.0, 4.2, 5.0, 2.8, 6.1, 3.9]"; json load "rx_y" "[41, 62, 25, 70, 88, 50, 97, 66]"; for (-> "[i]" 0) (< [i] (json count "rx_x" /)) (++ "[i]") { pa rl add_data "rx_ads" (json select "rx_x" (concat "/[" [i] "]")) (json select "rx_y" (concat "/[" [i] "]")); }; json unload "rx_x"; json unload "rx_y"; #2. ANOVA table: SST = SSR + SSE, and inference on the slope; json load "rx_anova" "{}"; json iobject "rx_anova" / "leads_per_kEUR" (math round (pa rl slope "rx_ads") 3) NUM; json iobject "rx_anova" / "SSR" (math round (pa rl sum_squares "rx_ads") 2) NUM; json iobject "rx_anova" / "SSE" (math round (pa rl sum_squared_errors "rx_ads") 2) NUM; json iobject "rx_anova" / "SST" (math round (pa rl total_sum_squares "rx_ads") 2) NUM; json iobject "rx_anova" / "Sxx" (math round (pa rl x_sum_squares "rx_ads") 4) NUM; json iobject "rx_anova" / "MSE" (math round (pa rl mean_square_error "rx_ads") 4) NUM; json iobject "rx_anova" / "r" (math round (pa rl r "rx_ads") 5) NUM; json iobject "rx_anova" / "s_b1" (math round (pa rl slope_std_err "rx_ads") 4) NUM; json iobject "rx_anova" / "s_b0" (math round (pa rl intercept_std_err "rx_ads") 4) NUM; json iobject "rx_anova" / "p_value" (pa rl significance "rx_ads") NUM; json iobject "rx_anova" / "slope_ci90_half_width" (math round (pa rl slope_confidence_interval "rx_ads" 0.1) 4) NUM; json iobject "rx_anova" / "models_in_memory" (pa rl show) ARRAY; json iobject "rx_anova" / "rx_ads_exists" (pa rl exist "rx_ads") NUM; pa rl close "rx_ads"; json doc "rx_anova"
{
"p_value": 0.00000023019891126629943,
"models_in_memory": [
"rx_ads"
],
"SSE": 35.49,
"rx_ads_exists": 1.0,
"s_b1": 0.5636,
"s_b0": 2.1843,
"Sxx": 18.6188,
"r": 0.99548,
"slope_ci90_half_width": 1.0952,
"SSR": 3898.39,
"SST": 3933.88,
"leads_per_kEUR": 14.47,
"MSE": 5.9146
}- SSR + SSE = SST: check it on the result.
pa rl close_allfrees every regression of the session.
Demand model and price elasticity
Explain weekly demand by the unit price and the promo budget with an OLS multiple regression, test the price coefficient (t statistic), compute the price elasticity at the mean point and simulate a price and promo scenario.
#1. Weekly demand in units vs unit price in EUR and promo budget in kEUR; -> "[X]" "[[9.9, 2.0], [9.5, 3.0], [10.5, 1.0], [8.9, 4.0], [9.2, 2.5], [10.9, 0.5], [8.5, 5.0], [9.8, 3.5], [10.2, 1.5], [8.7, 4.5]]"; -> "[Y]" "[520, 585, 455, 660, 575, 410, 720, 570, 490, 690]"; pa rm load_from_json "rx_dem" [X] [Y]; #2. OLS coefficients b0, b_price, b_promo and the t statistic of the price; json load "rx_b" (pa rm estimate_regression_parameters "rx_dem"); json load "rx_se" (pa rm estimate_regression_parameters_standard_errors "rx_dem"); -> "[t_price]" (math round (/ (json select "rx_b" "/[1]") (json select "rx_se" "/[1]")) 2); #3. Price elasticity at the mean point; -> "[p_mean]" (stat mean "[9.9, 9.5, 10.5, 8.9, 9.2, 10.9, 8.5, 9.8, 10.2, 8.7]"); -> "[elast]" (math round (/ (* (json select "rx_b" "/[1]") [p_mean]) (stat mean [Y])) 2); #4. What-if: price 9.00 EUR with a 4 kEUR promo; -> "[pred]" (math round (pa rm predict "rx_dem" "[9.0, 4.0]") 0); -> "[r2]" (math round (pa rm calculate_r_squared "rx_dem") 4); -> "[r2a]" (math round (pa rm calculate_adjusted_r_squared "rx_dem") 4); -> "[eq]" (concat "demand = " (math round (json select "rx_b" "/[0]") 1) " " (math round (json select "rx_b" "/[1]") 2) " x price + " (math round (json select "rx_b" "/[2]") 2) " x promo"); pa rm close "rx_dem"; json unload "rx_b"; json unload "rx_se"; concat [eq] " | t(price)=" [t_price] ", R2=" [r2] ", adjusted R2=" [r2a] " | price elasticity=" [elast] " | 9.00 EUR + 4 kEUR promo: " [pred] " units/week"
demand = 1098.7 -64.99 x price + 33.94 x promo | t(price)=-7.66, R2=0.9965, adjusted R2=0.9955 | price elasticity=-1.1 | 9.00 EUR + 4 kEUR promo: 650 units/week
- Load up to five regressors from a table with
pa rm load. - An elasticity below -1 means that a price cut increases revenue.
Calibrate a two-rate electricity tariff
Recover the fixed monthly fee and the peak and off-peak prices per kWh from eight bills with a multiple regression, with the standard errors of the prices and the fit diagnostics (residual variance, regression standard error, worst residual).
#1. Monthly kWh at peak and off-peak hours vs the bill in EUR; -> "[kwh]" "[[210, 340], [185, 300], [250, 410], [160, 280], [230, 360], [275, 450], [198, 330], [240, 395]]"; -> "[bill]" "[90.9, 81.6, 105.8, 73.0, 97.0, 115.4, 86.6, 102.0]"; pa rm load_from_json "rx_tariff" [kwh] [bill]; #2. Fee and unit prices with the covariance matrix of the estimates; json load "rx_b" (pa rm estimate_regression_parameters "rx_tariff"); json load "rx_v" (pa rm estimate_regression_parameters_variance "rx_tariff"); -> "[res]" (pa rm estimate_residuals "rx_tariff"); json load "rx_out" "{}"; json iobject "rx_out" / "fixed_fee" (math round (json select "rx_b" "/[0]") 2) NUM; json iobject "rx_out" / "p_peak" (math round (json select "rx_b" "/[1]") 4) NUM; json iobject "rx_out" / "se_p_peak" (math round (math sqrt (json select "rx_v" "/[1]/[1]")) 4) NUM; json iobject "rx_out" / "p_offpeak" (math round (json select "rx_b" "/[2]") 4) NUM; json iobject "rx_out" / "se_p_offpeak" (math round (math sqrt (json select "rx_v" "/[2]/[2]")) 4) NUM; #3. Goodness of fit; -> "[ssr]" (pa rm calculate_residual_sum_of_squares "rx_tariff"); -> "[ssto]" (pa rm calculate_total_sum_of_squares "rx_tariff"); json iobject "rx_out" / "R2_check" (math round (- 1 (/ [ssr] [ssto])) 5) NUM; json iobject "rx_out" / "error_variance" (math round (pa rm estimate_error_variance "rx_tariff") 4) NUM; json iobject "rx_out" / "regression_std_err_EUR" (math round (pa rm estimate_regression_standard_error "rx_tariff") 3) NUM; json iobject "rx_out" / "var_bill" (math round (pa rm estimate_regressand_variance "rx_tariff") 2) NUM; json iobject "rx_out" / "worst_residual_EUR" (math round (stat max (concat "[" (math abs (stat min [res])) ", " (stat max [res]) "]")) 3) NUM; json iobject "rx_out" / "loaded_models" (pa rm show) ARRAY; json iobject "rx_out" / "exists" (pa rm exist "rx_tariff") NUM; pa rm close "rx_tariff"; json doc "rx_out"
{
"R2_check": 0.99968,
"loaded_models": [
"rx_tariff"
],
"p_peak": 0.2781,
"error_variance": 0.0853,
"var_bill": 189.9,
"worst_residual_EUR": 0.347,
"fixed_fee": 12.12,
"se_p_peak": 0.0674,
"regression_std_err_EUR": 0.292,
"exists": 1.0,
"p_offpeak": 0.0591,
"se_p_offpeak": 0.044
}pa rm estimate_regressand_variancegives Var(y), the scale of the problem.- Draw the residuals with
plot histogramto check that they look normal.
Polynomial trend of user growth
Fit a quadratic trend on 10 months of active users, forecast months 11 to 15 and draw the data, the fitted curve and the forecast points on the same chart.
#1. Monthly active users in thousands, growth is accelerating; -> "[x]" "[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]"; -> "[y]" "[12.1, 12.9, 14.2, 16.0, 18.1, 20.9, 23.8, 27.4, 31.2, 35.5]"; -> "[pts]" "[[1, 12.1], [2, 12.9], [3, 14.2], [4, 16.0], [5, 18.1], [6, 20.9], [7, 23.8], [8, 27.4], [9, 31.2], [10, 35.5]]"; #2. Quadratic trend c0 + c1*x + c2*x^2 by least squares; -> "[c]" (pa polynomial_curve_fit_get_coeff [pts] 2); json load "rx_c" [c]; -> "[f]" (concat (json select "rx_c" "/[0]") "+(" (json select "rx_c" "/[1]") ")*x+(" (json select "rx_c" "/[2]") ")*x^2"); #3. Forecast months 11 to 14 with a step of 1, split into x and y arrays; json load "rx_fc" (pa polynomial_curve_fit_eval_incr [c] 11 14 1); -> "[fx]" "["; -> "[fy]" "["; for (-> "[i]" 0) (< [i] (json count "rx_fc" /)) (++ "[i]") { -> "[sep]" (if (== [i] 0) {""} {", "}); -> "[fx]" (concat [fx] [sep] (json select "rx_fc" (concat "/[" [i] "]/[0]"))); -> "[fy]" (concat [fy] [sep] (math round (json select "rx_fc" (concat "/[" [i] "]/[1]")) 1)); }; #4. Month 15 evaluated on its own, end of the planning horizon; -> "[m15]" (pa polynomial_curve_fit_eval [c] 15); -> "[fx]" (concat [fx] ", 15]"); -> "[fy]" (concat [fy] ", " (math round [m15] 1) "]"); json unload "rx_c"; json unload "rx_fc"; #5. Data and fitted trend, then the forecast points on the same chart; plot fit [x] [y] [f] "x"; plot add (plot points [fx] [fy])
The chart is shown in the Plot tab of the editor.
- Try degree 3 and compare the forecasts: higher degrees diverge fast outside the data.
pa xy_scatteropens a scatter chart of the raw points.
Trend forecast straight from the sales database
The monthly sales are in a SQL table. The script fits the linear trend directly on a SELECT, forecasts the next quarter (months 13 to 15), prepares the scatter plot of the history for the editor and cleans up.
#1. Monthly sales history in the sales database; cm set "tuto_p4h_sales" {execute "db.h2.embedded.config.get" "[database]" "./tmp/tuto_p4h_db/sales" "[user]" "sa" "[password]" "sa" ;}; sql connect "rx_p4h_sales" {cm get "tuto_p4h_sales";}; sql dml "rx_p4h_sales" "create table monthly (month_no int primary key, units double)"; sql dml "rx_p4h_sales" "insert into monthly values (1, 1210), (2, 1185), (3, 1302), (4, 1350), (5, 1388), (6, 1421), (7, 1405), (8, 1497), (9, 1530), (10, 1562), (11, 1610), (12, 1648)"; #2. Linear trend fitted directly on the SQL query; pa rl load "rx_p4h_trend" "tuto_p4h_sales" "month_no" "units" "select month_no, units from monthly order by month_no"; -> "[q1]" (+ (pa rl predict "rx_p4h_trend" 13) (pa rl predict "rx_p4h_trend" 14) (pa rl predict "rx_p4h_trend" 15)); -> "[report]" (concat "trend +" (math round (pa rl slope "rx_p4h_trend") 1) " units/month, R2 " (math round (pa rl r_square "rx_p4h_trend") 3) ", " (pa rl count "rx_p4h_trend") " months | next quarter forecast: " (math round [q1] 0) " units | model in memory: " (string contains (pa rl show) "rx_p4h_trend")); #3. Scatter of the history for the editor, then clean up; -> "[scatter]" (pa xy_scatter "tuto_p4h_sales" "month_no" "units" "select month_no, units from monthly"); pa rl close "rx_p4h_trend"; sql dml "rx_p4h_sales" "drop table monthly"; sql disconnect "rx_p4h_sales"; cm remove "tuto_p4h_sales"; file delete "tmp/tuto_p4h_db/sales.mv.db"; file delete "tmp/tuto_p4h_db"; [report]
trend +40.8 units/month, R2 0.971, 12 months | next quarter forecast: 5195 units | model in memory: 1
- Return the [scatter] variable as the last statement to see the points in the editor.
- Give an interval to the forecast with
pa rl slope_confidence_interval. - Write the forecast back with
sql dmlinsert into a forecast table.
Cost model through the origin
A delivery round costs a.km + b.stops with no fixed fee. The script fits the model with an intercept (to check the fee is near 0), refits it through the origin and quotes a 60 km round with 10 stops.
#1. Delivery rounds: km, stops and cost in EUR, no fixed fee expected; -> "[X]" "[[42, 8], [65, 12], [30, 5], [88, 15], [51, 9], [73, 14], [25, 6]]"; -> "[y]" "[61.5, 94.2, 43.0, 124.8, 73.9, 106.1, 39.8]"; #2. Model with an intercept; pa rm load_from_json "rx_p4h_cost" [X] [y]; json load "rx_p4h_w" (pa rm estimate_regression_parameters "rx_p4h_cost"); -> "[r2_with]" (pa rm calculate_adjusted_r_squared "rx_p4h_cost"); #3. Same data, regression through the origin; pa rm close "rx_p4h_cost"; pa rm load_from_json "rx_p4h_cost" [X] [y]; pa rm set_no_intercept "rx_p4h_cost" true; json load "rx_p4h_b" (pa rm estimate_regression_parameters "rx_p4h_cost"); -> "[se]" (pa rm estimate_regression_parameters_standard_errors "rx_p4h_cost"); -> "[quote]" (pa rm predict "rx_p4h_cost" "[60, 10]"); pa rm close "rx_p4h_cost"; concat "with intercept: fixed fee " (math round (json select "rx_p4h_w" "/[0]") 2) " EUR, adj R2 " (math round [r2_with] 4) " | through the origin: " (math round (json select "rx_p4h_b" "/[0]") 3) " EUR/km + " (math round (json select "rx_p4h_b" "/[1]") 3) " EUR/stop | quote 60 km, 10 stops: " (math round [quote] 2) " EUR"
with intercept: fixed fee 1.52 EUR, adj R2 0.9999 | through the origin: 1.062 EUR/km + 2.103 EUR/stop | quote 60 km, 10 stops: 84.73 EUR
- Check the precision of the rates with
pa rm estimate_regression_parameters_standard_errors. - Through the origin the R2 is not comparable with the model with intercept: compare the residuals with
pa rm estimate_residuals. - Add the waiting time as a third variable.
Promotion uplift from the weekly sales table
Ten weeks of sales with price, promotion flag and temperature are in a database. The script fits units = b0 + b1.price + b2.promo + b3.temp from SQL and forecasts a planned promotion week against the same week without promotion.
#1. Weekly sales with price, promotion flag and temperature; cm set "tuto_p4h_retail" {execute "db.h2.embedded.config.get" "[database]" "./tmp/tuto_p4h_db/retail" "[user]" "sa" "[password]" "sa" ;}; sql connect "rx_p4h_retail" {cm get "tuto_p4h_retail";}; sql dml "rx_p4h_retail" "create table weeks (wk int primary key, price double, promo double, temp_c double, units double)"; sql dml "rx_p4h_retail" "insert into weeks values (1, 2.49, 0, 14, 820), (2, 2.49, 1, 15, 1105), (3, 2.29, 0, 17, 910), (4, 2.59, 0, 19, 760), (5, 2.29, 1, 21, 1240), (6, 2.49, 0, 22, 845), (7, 2.39, 1, 18, 1150), (8, 2.59, 0, 16, 735), (9, 2.29, 0, 23, 960), (10, 2.39, 1, 24, 1235)"; #2. Multiple regression units = b0 + b1.price + b2.promo + b3.temp from SQL; pa rm load "rx_p4h_promo" "tuto_p4h_retail" "price" "promo" "temp_c" "" "" "units" "select * from weeks"; json load "rx_p4h_beta" (pa rm estimate_regression_parameters "rx_p4h_promo"); -> "[r2]" (pa rm calculate_r_squared "rx_p4h_promo"); #3. Plan: promo week at 2.39 EUR and 20 C; -> "[plan]" (pa rm predict "rx_p4h_promo" "[2.39, 1, 20]"); -> "[base]" (pa rm predict "rx_p4h_promo" "[2.39, 0, 20]"); pa rm close "rx_p4h_promo"; sql dml "rx_p4h_retail" "drop table weeks"; sql disconnect "rx_p4h_retail"; cm remove "tuto_p4h_retail"; file delete "tmp/tuto_p4h_db/retail.mv.db"; file delete "tmp/tuto_p4h_db"; concat "promo uplift +" (math round (json select "rx_p4h_beta" "/[2]") 0) " units/week | price effect " (math round (json select "rx_p4h_beta" "/[1]") 0) " units per EUR | R2 " (math round [r2] 3) " | planned week: " (math round [plan] 0) " units vs " (math round [base] 0) " without promo"
promo uplift +302 units/week | price effect -535 units per EUR | R2 0.993 | planned week: 1186 units vs 884 without promo
- Up to 5 drivers can be given to
pa rm load: add the holidays flag. - Test the significance of each driver with
pa rm estimate_regression_parameters_standard_errors(t = b / se). - Use the price effect to compute the price elasticity at the mean price.
Forecast accuracy and bias test
The demand forecast of the last 10 weeks is compared with the actual demand. The script computes MAE, RMSE and MAPE, then a z-test of the mean error (known error sigma 25 units) to detect a biased model.
#1. Last 10 weeks: actual demand and the forecast of the model; -> "[act]" "[520, 610, 575, 640, 590, 700, 655, 610, 680, 720]"; -> "[fc]" "[505, 590, 600, 615, 610, 660, 640, 630, 650, 690]"; -> "[err]" (matrix sub (concat "[" [act] "]") (concat "[" [fc] "]")); -> "[e]" (string replace (string replace [err] "[[" "[") "]]" "]"); #2. Accuracy: MAE, RMSE, MAPE; -> "[mae]" 0; -> "[ape]" 0; json load "rx_p4h_e" [e]; json load "rx_p4h_a" [act]; for (-> "[i]" 0) (< [i] 10) (++ "[i]") { -> "[ei]" (json select "rx_p4h_e" (concat "/[" [i] "]")); -> "[mae]" (+ [mae] (math abs [ei])); -> "[ape]" (+ [ape] (/ (math abs [ei]) (json select "rx_p4h_a" (concat "/[" [i] "]")))); }; -> "[rmse]" (math sqrt (/ (stat sum_sq [e]) 10)); #3. Bias test: mean error = 0 with the known error sigma of 25 units; json load "rx_p4h_z" (stat ztest [e] 0 25); concat "MAE " (/ [mae] 10) " | RMSE " (math round [rmse] 1) " | MAPE " (math round (* 10 [ape]) 2) "% | mean error " (stat mean [e]) " z=" (math round (json select "rx_p4h_z" "/z") 3) " p=" (math round (json select "rx_p4h_z" "/p_value") 4) (if (< (json select "rx_p4h_z" "/p_value") 0.05) {" -> biased forecast, recalibrate"} {" -> no significant bias"})
MAE 24 | RMSE 25.1 | MAPE 3.77% | mean error 11 z=1.391 p=0.1641 -> no significant bias
- When sigma is unknown, use
stat ttest_1on the errors. - Track the tracking signal (sum of errors / MAE) week after week.
- Plot actual and forecast with
plot chartand two series.
Seasonal indices by the ratio-to-moving-average method
Three years of quarterly revenue are decomposed: centred 2x4 moving average, ratio of each quarter to the trend, mean ratio by quarter normalised to a mean of 1. These indices deseasonalise the series before the trend forecast.
#1. Quarterly revenue in kEUR over 3 years; json load "rx_p4h_q" "[120, 95, 88, 142, 131, 104, 96, 155, 140, 112, 101, 168]"; #2. Centred moving average 2x4 and ratio to the trend; json load "rx_p4h_ratio" "{\"0\": [], \"1\": [], \"2\": [], \"3\": []}"; for (-> "[t]" 2) (< [t] 10) (++ "[t]") { -> "[cma]" (/ (+ (/ (json select "rx_p4h_q" (concat "/[" (- [t] 2) "]")) 2) (json select "rx_p4h_q" (concat "/[" (- [t] 1) "]")) (json select "rx_p4h_q" (concat "/[" [t] "]")) (json select "rx_p4h_q" (concat "/[" (+ [t] 1) "]")) (/ (json select "rx_p4h_q" (concat "/[" (+ [t] 2) "]")) 2)) 4); json iarray "rx_p4h_ratio" (concat "/" (math mod [t] 4)) (/ (json select "rx_p4h_q" (concat "/[" [t] "]")) [cma]) NUM; }; #3. Seasonal index of each quarter, normalised to a mean of 1; -> "[idx]" ""; -> "[s]" (+ (stat mean (json select "rx_p4h_ratio" "/0")) (stat mean (json select "rx_p4h_ratio" "/1")) (stat mean (json select "rx_p4h_ratio" "/2")) (stat mean (json select "rx_p4h_ratio" "/3"))); for (-> "[k]" 0) (< [k] 4) (++ "[k]") { -> "[idx]" (concat [idx] " Q" (+ [k] 1) "=" (math round (/ (* 4 (stat mean (json select "rx_p4h_ratio" (concat "/" [k])))) [s]) 3)); }; concat "seasonal indices:" [idx]
seasonal indices: Q1=1.112 Q2=0.869 Q3=0.782 Q4=1.237
- Divide each quarter by its index to get the deseasonalised series, then fit
pa rl load_from_jsonon it. - Multiply the trend forecast by the index of the quarter to get the final forecast.
- Check the stability of the indices year by year with
stat stddev.
Seasonal forecast with Holt-Winters
Forecast the next 6 months of 3 years of monthly passengers with a multiplicative Holt-Winters model whose smoothing parameters are estimated, with the 95 % interval of the first month.
#1. Monthly passengers in thousands; -> "[p]" "[112, 118, 132, 129, 121, 135, 148, 148, 136, 119, 104, 118, 115, 126, 141, 135, 125, 149, 170, 170, 158, 133, 114, 140, 145, 150, 178, 163, 172, 178, 199, 199, 184, 162, 146, 166]"; json load "rx_hw" (ml ts forecast "holt_winters" [p] 6 "{\"period\": 12, \"seasonal\": \"multiplicative\"}"); -> "[f]" ""; for (-> "[i]" 0) (< [i] 6) (++ "[i]") {-> "[f]" (concat [f] (math round (json select "rx_hw" (concat "/forecast/[" [i] "]")) 0) " ");}; -> "[res]" (concat "next 6 months: " [f] "| month 1 interval " (math round (json select "rx_hw" "/lower_95/[0]") 0) " - " (math round (json select "rx_hw" "/upper_95/[0]") 0) " | alpha " (math round (json select "rx_hw" "/params/alpha") 3) " | RMSE " (math round (json select "rx_hw" "/rmse") 2)); json unload "rx_hw"; [res]
next 6 months: 158 168 189 185 175 196 | month 1 interval 147 - 169 | alpha 0.564 | RMSE 5.26
ml ts decomposeshows the trend and the seasonal index of each month.- Compare with "seasonal_naive" in
ml ts forecast: a good model must beat it.
Stationarity test then ARIMA forecast
Test the stationarity of a price index with the augmented Dickey-Fuller test, difference it if needed, fit an ARIMA(1, d, 0) and forecast 3 periods with the 95 % interval.
#1. Monthly price index; -> "[x]" "[100.0, 101.2, 102.1, 103.5, 104.2, 105.8, 106.5, 107.1, 108.9, 109.4, 110.8, 111.5, 112.9, 113.6, 115.0, 115.8, 117.1, 118.3, 119.0, 120.6, 121.4, 122.9, 123.5, 125.0]"; json load "rx_adf" (ml ts adf [x] "auto"); -> "[d]" (if (== (json select "rx_adf" "/stationary_5pct") 1) {0} {1}); json load "rx_ar" (ml ts forecast "arima" [x] 3 (concat "{\"p\": 1, \"d\": " [d] "}")); -> "[res]" (concat "ADF " (math round (json select "rx_adf" "/statistic") 3) " p = " (math round (json select "rx_adf" "/p_value") 3) " -> d = " [d] " | AR1 " (math round (json select "rx_ar" "/params/ar/[0]") 3) " | forecast " (math round (json select "rx_ar" "/forecast/[0]") 2) ", " (math round (json select "rx_ar" "/forecast/[1]") 2) ", " (math round (json select "rx_ar" "/forecast/[2]") 2) " | 95 % at t+3: " (math round (json select "rx_ar" "/lower_95/[2]") 2) " - " (math round (json select "rx_ar" "/upper_95/[2]") 2)); json unload "rx_adf"; json unload "rx_ar"; [res]
ADF 1.636 p = 0.998 -> d = 1 | AR1 0.79 | forecast 126.19, 127.12, 127.86 | 95 % at t+3: 123.37 - 132.36
ml ts acfandml ts pacfhelp to choose the orders p and q.- The AIC key of
ml ts forecastcompares several ARIMA orders.
Prediction bands that widen
The demand grows with the temperature and so does its spread: three quantile regressions (10 %, 50 %, 90 %) give an 80 % band that widens with the temperature, where a constant band of a linear model would be wrong.
#1. Temperature and demand; -> "[X]" "[[13.6], [14.0], [15.2], [15.4], [18.9], [9.1], [2.7], [1.3], [29.5], [18.5], [4.7], [1.8], [5.7], [13.9], [13.2], [19.2], [15.0], [8.3], [29.9], [21.2], [9.5], [2.1], [23.0], [11.6], [28.7], [6.3], [27.3], [11.9], [2.2], [8.1], [2.6], [22.7], [3.5], [1.8], [23.9], [13.4], [5.7], [19.3], [3.5], [8.1], [29.1], [26.5], [6.3], [19.3], [3.0], [7.7], [23.2], [2.2], [2.7], [18.0], [11.2], [14.5], [17.2], [4.6], [27.3], [5.7], [22.2], [28.5], [26.5], [3.1], [1.2], [21.1], [7.7], [8.8], [5.3], [6.9], [16.8], [8.4], [27.5], [8.1], [13.4], [11.1], [17.2], [26.7], [29.4], [17.5], [4.2], [27.3], [21.0], [19.1]]"; -> "[y]" "[80.0, 87.6, 92.7, 94.2, 107.5, 75.7, 59.2, 51.8, 152.4, 103.3, 67.1, 55.5, 67.2, 93.0, 92.9, 100.8, 91.8, 71.7, 158.9, 113.3, 78.8, 57.7, 106.6, 89.9, 136.4, 68.8, 106.6, 88.2, 54.6, 70.0, 55.6, 135.7, 60.6, 56.1, 126.3, 96.1, 67.0, 104.4, 59.2, 75.4, 140.1, 122.4, 64.5, 116.0, 60.3, 73.1, 116.4, 57.8, 56.9, 101.7, 72.9, 97.5, 104.3, 62.7, 134.6, 65.2, 121.3, 133.2, 122.0, 58.1, 54.5, 112.2, 75.1, 72.0, 65.6, 69.4, 105.3, 71.3, 133.0, 74.8, 93.0, 84.2, 105.4, 136.5, 129.9, 94.6, 63.1, 132.3, 114.6, 106.9]"; ml train "rx_lo" "quantile_regression" [X] [y] "{\"quantile\": 0.1}"; ml train "rx_md" "quantile_regression" [X] [y] "{\"quantile\": 0.5}"; ml train "rx_hi" "quantile_regression" [X] [y] "{\"quantile\": 0.9}"; json load "rx_a" (ml model predict "rx_lo" "[[5], [25]]"); json load "rx_b" (ml model predict "rx_md" "[[5], [25]]"); json load "rx_c" (ml model predict "rx_hi" "[[5], [25]]"); -> "[res]" (concat "5 C: " (math round (json select "rx_b" "/[0]") 1) " [" (math round (json select "rx_a" "/[0]") 1) ", " (math round (json select "rx_c" "/[0]") 1) "] | 25 C: " (math round (json select "rx_b" "/[1]") 1) " [" (math round (json select "rx_a" "/[1]") 1) ", " (math round (json select "rx_c" "/[1]") 1) "]"); ml model delete "rx_lo"; ml model delete "rx_md"; ml model delete "rx_hi"; json unload "rx_a"; json unload "rx_b"; json unload "rx_c"; [res]
5 C: 64.9 [61.8, 67.4] | 25 C: 125.3 [115.6, 135.7]
- About 80 % of the new values should fall between the 10 % and 90 % lines.
- The median regression is robust to the outliers, unlike the mean.
How long does an advertising push last? (VAR impulse responses)
Weekly advertising spend and sales: a VAR model links each series to the past of both. The impulse response gives the extra sales, week after week, after an unexpected push of advertising; the variance decomposition tells which share of the uncertainty of the sales comes from the advertising.
#1. Weekly series: [advertising (k$), sales (k$)]; -> "[Y]" "[[10.0, 50.0], [10.37, 57.6], [11.28, 58.8], [11.29, 59.51], [11.33, 58.79], [9.97, 55.88], [8.87, 54.47], [10.1, 55.24], [10.67, 54.56], [11.12, 54.64], [8.76, 59.01], [9.02, 53.43], [9.35, 58.97], [10.96, 55.43], [9.42, 58.63], [8.9, 57.71], [8.0, 52.06], [10.08, 44.14], [10.27, 47.52], [11.23, 48.0], [11.45, 58.54], [9.3, 56.29], [10.25, 50.43], [8.85, 55.69], [10.55, 54.48], [11.53, 56.65], [10.04, 50.64], [10.68, 47.52], [10.52, 51.75], [9.09, 51.66], [11.49, 51.62], [9.19, 54.33], [9.74, 52.86], [10.78, 51.29], [10.28, 55.78], [8.78, 58.12], [9.87, 50.78], [9.46, 51.26], [8.32, 42.49], [9.67, 46.35], [9.78, 47.48], [9.97, 45.24], [10.5, 49.89], [10.37, 54.2], [9.2, 54.44], [8.9, 49.45], [9.8, 47.74], [10.85, 50.42], [9.27, 56.49], [8.06, 51.99], [9.39, 46.41], [10.24, 46.56], [9.13, 51.08], [9.53, 45.78], [9.84, 46.29], [10.27, 45.62], [10.32, 47.48], [11.81, 48.15], [12.22, 52.99], [12.27, 64.26], [10.75, 64.45], [9.45, 58.3], [9.15, 53.94], [10.53, 53.08], [9.77, 53.57], [8.39, 53.37], [8.95, 44.85], [8.73, 46.15], [9.47, 46.62], [9.28, 47.17], [9.4, 41.93], [9.54, 43.3], [9.07, 47.27], [11.72, 50.48], [10.9, 50.86], [10.26, 50.75], [10.29, 54.72], [10.09, 49.2], [11.42, 49.91], [12.1, 50.68]]"; json load "rx_v" (ml ts var [Y] "{\"irf\": 4}"); #2. irf[step][response][shock]: the sales (1) after a shock on the advertising (0); -> "[res]" (concat "order " (json select "rx_v" "/p") " | sales after an advertising shock: week 0 " (math round (json select "rx_v" "/irf/[0]/[1]/[0]") 2) ", 1: " (math round (json select "rx_v" "/irf/[1]/[1]/[0]") 2) ", 2: " (math round (json select "rx_v" "/irf/[2]/[1]/[0]") 2) ", 4: " (math round (json select "rx_v" "/irf/[4]/[1]/[0]") 2) " | share of the sales variance due to the advertising at 4 weeks: " (math round (* 100 (json select "rx_v" "/fevd/[4]/[1]/[0]")) 0) " %"); json unload "rx_v"; [res]
order 1 | sales after an advertising shock: week 0 0.36, 1: 2.27, 2: 2.13, 4: 0.66 | share of the sales variance due to the advertising at 4 weeks: 50 %
- The order of the columns matters (Cholesky): put first the series that reacts within the week the least, here the advertising budget.
ml ts grangertests the direction: the past of the advertising improves the prediction of the sales, not the reverse.
Monthly electricity load: seasonal ARIMA by maximum likelihood
Seven years of monthly electricity load with a trend and a winter peak. The airline model SARIMA(0,1,1)(0,1,1)12 is fitted by exact maximum likelihood (Kalman filter, the same estimates as R arima), with the standard error of each coefficient, then forecasts the next 3 months with 95 % intervals.
#1. Monthly load (GWh), 84 months; -> "[y]" "[485.0, 465.5, 419.5, 378.0, 361.5, 365.5, 371.0, 367.5, 365.5, 384.0, 427.5, 475.5, 497.0, 471.8, 435.7, 389.9, 372.4, 370.5, 391.4, 388.2, 396.3, 400.4, 450.4, 498.8, 516.9, 494.1, 464.6, 416.8, 407.9, 414.0, 435.3, 444.2, 429.4, 449.4, 485.2, 545.9, 564.2, 541.9, 510.9, 460.0, 448.2, 442.0, 479.6, 471.6, 469.5, 469.0, 503.3, 555.9, 586.5, 566.9, 533.5, 480.1, 485.3, 466.9, 488.3, 495.4, 481.5, 496.8, 542.3, 591.4, 619.7, 596.8, 557.7, 483.0, 478.9, 475.1, 484.2, 488.3, 485.0, 507.6, 531.2, 583.8, 614.0, 595.7, 545.9, 508.6, 488.7, 495.1, 506.8, 514.5, 521.9, 537.6, 570.1, 612.9]"; json load "rx_s" (ml ts sarimax [y] "{\"order\": [0, 1, 1], \"seasonal\": [0, 1, 1, 12], \"horizon\": 3}"); -> "[res]" (concat "ma1 " (math round (json select "rx_s" "/ma/[0]") 3) " (se " (math round (json select "rx_s" "/se/ma/[0]") 3) "), sma1 " (math round (json select "rx_s" "/seasonal_ma/[0]") 3) " (se " (math round (json select "rx_s" "/se/seasonal_ma/[0]") 3) "), AIC " (math round (json select "rx_s" "/aic") 1) " | next months: " (math round (json select "rx_s" "/forecast/[0]/forecast") 0) " [" (math round (json select "rx_s" "/forecast/[0]/lower") 0) ", " (math round (json select "rx_s" "/forecast/[0]/upper") 0) "], " (math round (json select "rx_s" "/forecast/[1]/forecast") 0) ", " (math round (json select "rx_s" "/forecast/[2]/forecast") 0) " GWh"); json unload "rx_s"; [res]
ma1 -0.428 (se 0.109), sma1 -0.89 (se 0.523), AIC 519.1 | next months: 642 [626, 658], 621, 582 GWh
- A coefficient whose interval (estimate +/- 2 se) contains 0 can be removed: compare the AIC of the two models.
- Add the temperature as exog (and its forecast as exog_future) to explain the peaks; method css is faster on very long series.
Choose the forecasting model automatically: ARIMA or exponential smoothing?
Eight years of monthly demand. Auto ARIMA chooses the differences and the orders, ETS chooses the error, trend and season types; both by AICc on all the data. To choose between the two families, keep the last 12 months aside and compare the forecast errors.
#1. 96 months: 84 to fit, 12 to test; -> "[y]" "[215.8, 227.7, 224.2, 229.2, 232.5, 235.9, 226.1, 207.0, 180.2, 171.7, 182.2, 211.7, 237.3, 252.1, 243.0, 253.7, 255.3, 258.0, 250.4, 220.4, 192.2, 182.3, 189.7, 232.5, 260.7, 277.5, 273.1, 271.8, 283.8, 281.2, 268.9, 242.4, 214.7, 189.1, 215.5, 249.3, 285.4, 295.4, 299.3, 303.8, 304.8, 305.4, 292.2, 259.6, 229.0, 213.6, 228.4, 270.4, 300.2, 313.4, 326.3, 317.8, 330.0, 329.4, 315.0, 283.8, 242.3, 225.1, 244.0, 283.4, 324.6, 348.4, 340.7, 338.1, 347.6, 351.1, 339.7, 298.7, 262.9, 244.9, 257.9, 305.6, 338.7, 362.5, 360.1, 358.5, 372.3, 377.1, 360.1, 322.6, 276.9, 265.4, 285.7, 326.5]"; -> "[test]" "[363.9, 386.8, 386.5, 388.3, 388.0, 389.7, 376.3, 335.4, 294.4, 278.1, 293.8, 334.2]"; json load "rx_a" (ml ts auto_arima [y] "{\"period\": 12, \"horizon\": 12}"); json load "rx_e" (ml ts ets [y] "{\"period\": 12, \"horizon\": 12}"); -> "[fa]" (json select "rx_a" "/forecast_values"); -> "[fe]" (json select "rx_e" "/forecast_values"); -> "[res]" (concat (json select "rx_a" "/model_name") " (" (json select "rx_a" "/models_tried") " models tried): MAE " (math round (ml metrics mae [test] [fa]) 1) " | " (json select "rx_e" "/model") ": MAE " (math round (ml metrics mae [test] [fe]) 1)); json unload "rx_a"; json unload "rx_e"; [res]
ARIMA(2,0,0)(1,1,0)[12] (12 models tried): MAE 5.4 | ETS(M,A,M): MAE 3.4
- The AICc of an ARIMA and of an ETS model are not comparable (different likelihoods): compare them on data kept aside, or average the two forecasts.
- Refit the chosen model on the 96 months before forecasting the next year.
