RFY Exponential Smoothing Forecast Model

Hi, all and welcome to our new post!

Today, I’d like to talk about one of the most important things in Sales Excellence – forecasting from scratch, when you couldn’t lean on sales pipeline and forecasted projects, or prepaid orders, waiting for products to be delivered.
Initially, my target was to compute model, based on follow parameters:

It could be automated from sales report;
It could be checked with known past results;
It could be automated to split forecast in different perspective of sales report (customers, sales managers, products, etc. you have in);
It could be automated to set up sales targets based on its prediction;
It could be clear visualized in common PBI mode, without Python or R scripts.

As a sample to use logic I used Holt-Winters exponential smoothing model

Current model use for smoothing 4 parameters:

3 LM Rolling average
3 LM PY Rolling average
PY rolling average
CY rolling average

In addition, to setup prediction clearer I also add Seasonal factor based on it.

So, these values work in prediction of current (already know values) after 1^st year of sales data.

To compute forecasting values for 3 future period I used updated scenario adding calculations with Rolling average of predicted values and K as period over period growth dynamic value.

Inside the model used RFY logic, where Revenue equals to Customer * Orders Frequency * Orders Yield

All these help to smooth high deviations through sales history, like big deal or another unnormal situation on market.

Segmented and total model errors parameters:

Customer perspective:
RMSE: 4.219163
MAE: 2.835990
MAPE: -0.220831%

Frequency perspective:
RMSE: 0.010791
MAE: 0.008935
MAPE: -0.234230%

Yield perspective:
RMSE: 13.504834
MAE: 11.619896
MAPE: 0.064393%

Predicted Revenue:

RMSE: 19 513.17
MAE: 13 718.91
MAPE: -0.38%

Comparison with original H-W predicted values is in table

	Customers Prediction			Frequency Prediction			Yield Prediction
Period	Current Model	Holt-Winters	Fact	Current Model	Holt-Winters	Fact	Current Model	Holt-Winters	Fact
13	457	453	456	1.48	1.51	1.50	2341.07	2364.26	2353.19
14	456	410	468	1.44	1.56	1.45	2412.50	2408.91	2408.34
15	485	469	488	1.45	1.48	1.46	2423.03	2434.22	2395.94
16	437	469	438	1.48	1.47	1.49	2326.54	2393.96	2312.12
17	471	482	472	1.49	1.48	1.49	2275.15	2443.23	2255.08
18	461	493	458	1.42	1.48	1.42	2500.59	2319.90	2510.44
19	454	447	457	1.47	1.44	1.47	2365.98	2309.22	2374.56
20	455	467	452	1.50	1.38	1.50	2386.02	2329.33	2394.57
21	459	449	459	1.48	1.43	1.48	2362.13	2347.00	2366.53
22	450	451	449	1.44	1.45	1.42	2337.05	2345.08	2344.13

and below you could find forecast results:
Forecast for 1^st period:

Parameter	Current model	Holt-Winters
Customers	427	423
Frequency	1.37	1.38
Yield	2 332.34	2 308.85
Revenue	1 370 017.88	1 345 141.71

Forecast for 2^nd period:

Parameter	Current model	Holt-Winters
Customers	439	439
Frequency	1.41	1.43
Yield	2 539.74	2 499.47
Revenue	1 580 007.82	1 569 622.08

Forecast for 3^rd period:

Parameter	Current model	Holt-Winters
Customers	439	442
Frequency	1.42	1.44
Yield	2 408.10	2 376.50
Revenue	1 506 914.03	1 514 424.19

So, you could see that the difference between two models at the total level is very little:

1^st period variance: 1.85%

2^nd period variance: 0.66%

3^rd period variance: -0.50%

But using manual model with Rolling average inside the logic is more common to PBI business users from business and visualization points, that Python or R scripts, which could predict but couldn’t setup targets or use prediction from other perspectives of sales report.

I’ve check todays morning comparison on other dataset where I’ve use 48 months row model to compute 2 months forward prediction (these two months are already know fact) and get the same quality results!

Just few words about buttons “Sales target: Managers” and “Sales target: Products”.

In case you’d like to hedge some target amount and setup increased sales plan – just use Sales target growth parameter with 5% step to calculate desired value.

Next step, which I’m going to add is PowerApp button, which would automatically export sales targets to Excel sheet. In current version you need just to click … bellow sales targets table and export to CSV and save as Excel 😊

eyJrIjoiY2ZhYTRiM2ItOTdiZC00YWJhLThhMmItNzQwMzIzOWIwM2QxIiwidCI6IjE4OTY2NWM5LWRjYTItNDY5My1iMWE2LTkyMDBlODI3YjUxZCIsImMiOjl9

Business