If you are looking to work out how to adjust budgets, better understand inventory requirements, anticipate future sales or expenses, or similar, then forecasting in excel can be a solution. Done correctly, this technique can show you probabilities or future trends based on historical data and given assumptions. It is often done in Microsoft Excel, as it’s a powerful application used for storing, calculating, and visualising data.
In this article, we will briefly look at why use MS Excel for financial forecasting, as well as the advantages it offers. Thereafter, we will go into some detail on the three most common methods used when forecasting in Excel.
As an application, Microsoft Excel is particularly useful for financial forecasting. Many SMEs use it to predict future trends by applying one of three methods to analyse their data. With historical data providing the baseline, companies can produce various financial reports that are crucial for its success in the future.
Note that a reliable baseline is key. What happened yesterday impacts on today, with the same being said for tomorrow. Looking at the past can help you take a step forward and forecast what might happen in the future.
While the data produced doesn’t provide a definitive solution or answer exactly how to make important business decisions, it does show probabilities and possible courses of action. It is up to the person who is crunching the numbers and analysing the data to verify the results and decide on the way forward.
There are a couple of reasons why Excel, in particular, is an especially useful forecasting tool.
Three common methods based on different techniques are used to do forecasting in Excel, namely:
If you don’t have any additional source data aside from your baseline information to work with, then the moving averages method is a smart choice.
Generally speaking, market fluctuations impact your data driving trends up or down. As these upward and downward movements don’t always amount to significant trends, it helps to average the amounts over a period of time to smooth out the data.
If you were to average your data from month to month (or quarter to quarter, or even year to year), the longer-term impacts on the data can be more clearly discerned without the consistent upward and downward movements. Therefore, the longer-term trends influencing your data can be easily read from a forecast generated with moving averages.
As an example, if you were to take the average of your sales over a three month period (say, January, February, March), and then compare them to the following three month periods (February, March, April, and so on), you can get some idea of where the numbers are going.
The advantage of this form of forecasting is that it evens out irregularities that shouldn’t matter for the longer-term trends of the data, thereby eliminating the noise made by micro-deviations.
See an example of forecasting using the Moving Averages method here.
Exponential smoothing, which combines error, trend, and seasonal (or ETS) calculations, is similar to moving averages in terms of how historical data is averaged out to forecast the future. The key difference, however, lies in acknowledging the inaccuracies from your previous forecast and adjusting the data to make for a more correct future prediction.
Smoothing takes a few factors into account, which help to produce a more reliable forecast. The algorithm detects any patterns relating to seasonality or confidence intervals to smooth out the data.
For example, if previous forecasts ended up being too low or high in their predictions, then smoothing will adjust the subsequent forecasts accordingly, whether that’s up or down. Essentially, this method makes your next forecast more reliable by addressing factors that would have made your previous ones more correct.
See an example of forecasting using the ETS method here.
This form of forecasting is slightly different from the previous two. When creating a forecast with linear regression, one variable will predict another and this is how you will produce results to examine.
Stated differently, linear regression can assist in modelling the relationships between both dependent and independent variables. As an example, a change in interest rate (the independent variable) could have a significant effect on your predictions. You would then use the interest rate to forecast future trends in your data (the dependent variable).
As the linear relation is determined by the timeline and value series, this method is not suitable for data that sees seasonality or irregular cycles.
The advantage of using this form of forecasting lies in its inclusion of external factors in predictions. This assists in forecasting the effects of these external factors upon your data, thereby increasing the accuracy of your trend predictions.
See an example of forecasting using the linear regression method here.
Ultimately the decision of which method of forecasting in excel to go ahead with will depend on whether you have a baseline of data to work with and what you are trying to achieve with the reports.
If you need any expert assistance with financial modelling, whether it’s for simple or advanced forecasting, get in touch with us at FD4Cast