Microsoft Excel, and similar spreadsheet technologies, often serves as the go-to tool for finance teams and C-suite executives when conducting revenue forecasts. I suppose there are a number of reasons for this, and according to an article published on CFO Magazine’s website, the preference likely stems from the fact the software has been around for so long (since 1985).
Compounded with being nearly 40 years old, Excel also comes pre-installed on many devices and has somewhat forced its position as the default option within the toolkit of finance professionals. Its familiarity has bred prolonged usage for teams who prefer sticking to a well-known tool that delivers "good enough" results, as opposed to learning a new one — even if the latter could offer improved outcomes.
In desperately trying to understand how it measures as a forecasting tool, and whether it warrants its current popularity, I scoured some G2 reviews. The reviews were great, mostly 5-stars, but then I realised they were mainly from reviewers who were incentivised and none of them go into detail as to what exactly makes it so "great".
With my scepticism heightened, I needed to understand exactly what Excel's forecasting capabilities and limitations were. I found that there are four common methods used for spreadsheet-based predictions — and spoiler alert: none of them are "good enough’" when you know what "good" actually means. I’ve covered the methods below and have outlined the strengths and weaknesses of each of them.
I need to caveat this post and say that I'm not an expert in this field, so this is largely based on late night internet research and conversations with people who are actually financial and data science experts.
Linear regression forecasting is a predictive modelling technique that finds the best-fitting straight line through data points. It analyses the relationship between two variables by fitting a linear equation to the observed data.
Exponential smoothing forecasting is a time series forecasting method that involves calculating the weighted average of past observations, with the weights decaying exponentially as the observations get older. This technique gives more importance to recent observations while still factoring in older data points.
Moving average forecasting is a technique that calculates the average of a specific number of the most recent data points to generate a forecast. It's used to smooth out short-term fluctuations and highlight longer-term trends or cycles.
ARIMA is probably the most complex forecasting method that I came across - although this LinkedIn article does a great job of breaking down that complexity. The ARIMA method combines autoregression, differencing, and moving averages to predict future data points in a time series. It can account for trends, seasonality, and errors in the data to provide a comprehensive prediction.
Since cohort-based forecasting is a technique that combines multiple forecasting models (including all of the above) in order to garner more accurate predictions, it’s incredibly difficult to execute using spreadsheet-based software. Yes, Excel can manage cohort-based forecasting — but only if you manually structure your data and can layer different forecasting models in a way that utilises their strengths whilst simultaneously compensates for their weaknesses.
For complex cohort analyses, specialised software offers more sophisticated functionality to handle this type of data more efficiently. For example, such tools are able to provide a daily calculation that takes into account all previous daily calculations, as well as for each segment.
And as is the case with Ramp, these tools can also come with bonus elements such as automation. Excel lacks built-in tools explicitly designed for this, and a similar level of output often necessitates advanced data manipulation techniques and potentially having to use Excel's array formulas or Visual Basic for Applications (VBA) scripting for automation. If you’ve ever gone through Microsoft’s Getting started with VBA in Office guide, you’d know how time-consuming and technical it is to actually implement.
So, while Excel's historical prevalence and user familiarity make it a seemingly convenient choice for revenue forecasting, it's critical to understand its strengths and limitations. Knowing when to use Excel, and when to turn to more specialised tools, can empower your team to make more accurate and strategic forecasts.