Introduction
Over the weekend, I watched a few of the sessions from the Excel Weekend 7 series and, mostly, I profited from what I watched. However, there was one session that made my blood boil: the discussant presented a small data set and promised to show us how to make better forecasts.
The truth is, this discussant clearly does not know one end of a forecast from another and despite being very highly regarded in the Excel community, does not seem to understand the Excel functions she was demonstrating.
As a matter of interest, I posted a comment on the video page where that webinar was hosted and they deleted it! Very shallow, insecure people living there, it seems.
The Problems
In the table that follows, screenshot 1, this introduction, the discussant sets out her raw data and then takes averages of each quarter. She then weights each quarter's average quarter by quarter and calls the results her seasonal indices.
- Firstly, she seems to be applying the Ratio to Moving Averages method
- Secondly, assuming she is, she got the method ... wrong!
- Thirdly, her seasonal indices are therefore significantly wrong
The discussant goes on to discuss her method and includes FORECAST.LINEAR() and FORECAST.ETS() in her answer. Finally, she applies the Forecast Sheet utility in Excel and claims her results are in line with the results from that! Again, wrong!
Ratio to Moving Averages Method
I have updated my Excel file for this exercise so some of the figures you see below have been changed in that file.
In this post, I set out the Ratio to Moving Averages in full here and I contrast it with decomposition using the Additive Method, simply because I can! I believe the data provided are stationary and therefore the additive method would be the appropriate method to use for forecasting. However, the ratio to moving average method is seen as an all round method that can successfully be used both for additive and multiplicative data sets.
You can download my Excel file for this post from the link at the bottom of the page and follow along with me. The ratio to moving average method is not complex but there are many steps to take. Once you have created a working file for the method, though, you can save it as a template and use it again and again.
Unpivot the Data
Since the data came in the form of a matrix style table, the sensible thing to do next is to unpivot it in Power Query ... I will not demonstrate that here but here is a link to a video I created that illustrates the process in detail: unpivoting data
Here is my worksheet, in two screenshots, screenshots 2 and 3. I will walk you through the method by referring to them, step by step.
The actual sales data have been unpivoted to column M and I have changed the dates to Q No: quarter numbers.
The 4CMA is the 4 quarter centred moving average column and we create that with this formula, starting in cell N8 with this formula that you then drag down to cell N19:
=AVERAGE(AVERAGE(J7:J9),AVERAGE(J8:J10))
For the 4CMA Ratios, that start the seasonal indices calculations, enter this formula in cell O10 =M10/N10 and drag it down to O17.
Look at the next screenshot to see that I have copied the 4CA Ratios to that part of the worksheet to carry out the process of finding the Seasonal Indices: from O10 to AA8, from O11 to AB8 and so on. In this small example, we can just use =O10 in cell AA8 and so on. In a bigger example, you should use =TRANSPOSE(O10:O11) in cell AA8 and so on to complete the table in the range Y8:AB10
Now find the averages for each quarter with this formula in cell Y11 =AVERAGE(Y8:Y10) and drag that to the right to cell AB11. We need to normalise those indices because the average adds to 3.9366 and not 4.0000 and we do that this way, starting in cell Y12 with this: =Y11/AVERAGE($Y$11:$AB$11), which we then drag right to cell AB12
Back to screenshot 2 to continue: we enter the seasonal indices from cell P8 and down, quarter by quarter, until cell P19.
The deseasonalised values are found by starting in cell Q8 with =M8*P8, that you then drag down to cell !19
The irregular or error values start in cell R8 with =Q8-N8, that, again, you drag down to row 19
The forecast column is populated by implementing the regression function that I have derived in cells Y16 and Y17, screenshot 3:
Slope = Y16 =SLOPE(N8:N19,L8:L19)
Intercept = Y17 =INTERCEPT(N8:N19,L8:L19)
Then, in cell S8 we enter =(L8*Y$16+Y$17)*P8 that we drag down to row 19
The Level column also uses the regression output, starting in cell T8 with =L8*Y$16+Y$17 ... drag down to row 19
The Cyclical index begins in cell U10 with =N10/T10*100 that you drag down to cell U17
Finally, in cell V10 we enter the Seasonality value with this formula =M10-(T10+U10/100+R10) and for the final time, we drag that down to V17
That's a lot to take in but we have all of the elements of the Ratio to Moving Average Method in programmed full now and we can create the various graphs that comprise the decomposition of the data and we can create forecasts.
The Graphs
Although they lack detail, I initially created my graphs by using Sparklines and they convey the overall message pretty well. Download and open my Excel file to see my more formal graphs, such as this one:
The Forecasts
You can see my forecasts in screenshot 2 but download and open my Excel file to see exactly how I did that, if it is not fully clear above.
Conclusions
I have taken you through the ratio to moving average method of data forecasting in a more systematic and structured way than I saw in the demonstration I watched over the weekend. I have used the data set from that presentation but I think there is absolutely no value is sharing the erroneous method illustrated there.
In my Excel file, there is a second worksheet, FS, that illustrates the Forecast Sheet results that I have prepared and that I contrast with my Ratio to Moving Average Method results. I do also include the discussant's final forecast results but offer no further comments.
Download my
Excel File ...
but please note, the Sparklines and other graphs will not show in google sheets ... download the file and open it in Excel
Duncan Williamson
14th June 2021
No comments:
Post a Comment