The types of forecasting you can do using Excel

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

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.‍

Strengths:‍

• Straightforward and easy to interpret
• Works well for data with a clear upward or downward trend, and when there is a linear relationship between the independent and dependent variables

Weaknesses:

• Assumes a constant rate of change
• Likely ineffective for data with seasonal patterns, non-linear trends, or outliers

Exponential Smoothing (ETS) Forecasting

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.

Strengths:

• Great for handling trends and seasonal patterns
• It gives more weight to recent data points, which can make it more responsive to changes

Weaknesses:

• Assumes that future trend and seasonality will mirror the past
• Requires a stable historical data set—any significant changes in trend or seasonality can reduce the accuracy of predictions

Moving Average Forecasting

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.

Strengths:‍

• Useful for smoothing out noise and short-term fluctuations
• Easy to understand and implement

Weaknesses:

• Doesn’t forecast trends or seasonal patterns
• Assumes the future will average out to the past
• The wrong window size (the number of periods to average) can lead to over-smoothing or under-smoothing

ARIMA (AutoRegressive Integrated Moving Average) Forecasting

‍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.

Strengths:

• Can handle a variety of data patterns
• Robust and flexible, capable of modelling trends, seasonality, and other complex patterns
• Takes into account error, trend, and seasonality in the data to give a holistic prediction

Weaknesses:‍

• Relatively complex and requires a deep understanding of the underlying statistical concepts
• Choosing the order of the AR, I, and MA components can be challenging and typically requires trial and error
• Requires large and stable datasets for effective forecasting

What about cohort-based forecasting?

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 Kohort, 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.

‍