Excel is an amazing tool that allows users to organize data in spreadsheets and manipulate it using a large selection of functions. However, many WFM departments also use it as forecasting software for their time series. There are reasons why this is not a wise choice. We will elaborate on these reasons.
Excel is not a database
Using Excel for forecasting indirectly assumes that spreadsheets are used as databases; asking that duty from Excel is one of the most notorious reasons for business inefficiencies. Once your data grows, your spreadsheet grows too and becomes very slow and buggy. In such a situation, retrieving records is not straightforward: you need to write some sort of formula, which is slow. There are better solutions, like connecting to a real database, where you can retrieve records sorted as you want in a few seconds using a simple query.
Data integrity
Another disadvantage of Excel is that information is potentially replicated in multiple files instead of being stored in one place. Forecasters can (normally) access Excel sheets only one person at a time, which involves duplicating files in different locations. Thus, when one record is altered in one place, the files in other locations are unaffected, and things can get messy. Integrity and consistency are golden rules of data management that are hardly complied with when using Excel.
No user control
Besides, Excel does not easily allow standardizing the framework: anyone can modify formulas, move things around, and ultimately organize their spreadsheet as they like. In other words, Excel does not enforce structure, and you have to structure it yourself. That requires software development knowledge that most Excel users do not have.
Mistakes
Moreover, in Excel, it is easy to make mistakes. I witnessed a horror story caused by a small distraction from filling the cells of a spreadsheet. Basically, one of the forecasters copied and pasted values into cells that were supposed to contain formulas. This way, the forecasting framework was broken, and predictions were wrong. The error was only identified after a long time, and months of really wrong predictions had already been delivered. This is not an isolated case; I later discovered that there is extensive scientific literature about errors in Excel. One of the most “optimistic” researches estimated that around 1% of all formulas in spreadsheets contain errors (Powell, Kenneth, & Lawson, 2009).
In summary, inefficiency and wasting time and resources are the leading downsides of keeping data in Excel.
Forecasting methods
One important question remains: what about the quality of the forecasting models?
In Excel, we can design a forecasting algorithm creating a structure of cells linked by formulas. For instance, we can design a framework for the exponential smoothing model where the cells of one column are filled with the training data, and the cells of another column are filled with the exponential smoothing formula. The latter formulas link to the input column, calculate, and produce the output. The smoothing parameter can be written in an isolated cell or hard-coded within the formulas. In this example, the model is simple, and only two columns are needed: one for the actuals and one for the forecast (Figure 1). For more sophisticated models, bigger frameworks are necessary (more columns, intermediate steps for the formulas, etc.).
Figure 1: Exponential smoothing in Excel
Another way is to create some reusable forecasting methods through VBA coding. Basically, we can code and store functions that select some input cells (containing the data), apply a forecasting method, and generate the output.
Therefore, most time series modeling techniques can be written in Excel. Obviously, the computation time is slower than the equivalent in R or Python, but if you have the stomach, you can still do it. Some users have created advanced AI algorithms using VBA code, such as artificial neural networks in Excel. However, the fact remains that a spreadsheet is still subject to many sources of error regardless of whether the forecasting model is good.
In summary, Excel is an easy-to-learn platform for once-off calculations, but for any calculation that needs to be repeated and even done by multiple people, Excel is a bad choice.
Solution
So, what is the ultimate forecasting solution? As mentioned, between the lines, you need solid database technology and a real programming language to write your model. This requires some initial investment in resources because employees need training. However, in the end, it really pays off in terms of better and more reliable forecasts and less work.
Several sophisticated alternatives exist. Like programming your own solution in R, C++, or Python. This, however, requires considerable programming skills and mathematical knowledge to create a basic forecasting model.
We know that not all WFM professionals possess these skills. That is why you need forecasting software that can incorporate this knowledge and give the forecasters time to work on what they are good at performing business analysis. At CCmath, we have an all-in-one solution that incorporates all this hard knowledge in a single software: CCforecast.
If you want to learn more about our solution, please contact us by clicking here.
Giuseppe Catanese is a forecasting specialist and developer at CCmath.
To find out more about CCmath and its products, see CCmath.com.
To learn more about the background and practice of WFM, see WFMacademy.CCmath.com.
The author thanks Ger Koole and Alex Roubos for their feedback.
Bibliography
Powell, S. G., Kenneth, B. R., & Lawson, B. (2009). Errors in operational spreadsheets. Journal of Organizational and End User Computing (JOEUC), 21(3), 24-36.