Forecasting In Excel: 3 Methods Commonly Used

Why Use Excel For Forecasting

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.

Forecasting For SMEs

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.  

Why Use Microsoft Excel For Forecasting

There are a couple of reasons why Excel, in particular, is an especially useful forecasting tool. 

  1. Many companies store or save a copy of their historical financial data in Excel. If that is the case, the baseline data is then readily available and accessible for forecasting purposes. 
  2. Even if your data isn’t kept in Excel, it is still possible to upload files or connect external databases to it. This way it’s possible to utilise the formulas and built-in tools the application has to offer. 
  3. Scanning mountains of data compiled as numbers and words doesn’t always make for the easiest intake and understanding. However, that is where Excel is useful. It can produce engaging visuals in the forms of charts or graphs that make it easier to understand historical data and its consequent effects on your forecast. 
  4. If you know what you are doing, then the tools built into the add-in, Analysis ToolPak, make forecasting in Excel easier. Note that the ToolPak isn’t quite as easy to navigate if your proficiency on the application is limited and does require some expert knowledge to draw reliable results from it.
  5. Once you become more accomplished with forecasting, you can skip add-ins and enter formulas on your own accord. This gives you more control over the report to generate the results you need.

3 Common Methods Used When Forecasting In Excel

Three common methods based on different techniques are used to do forecasting in Excel, namely:

Moving Averages

Forecasting Using Moving Averages

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 (ETS)

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.

Excel Forecast

Linear Regression

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.

Conclusion

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