We are approaching the second half of the year, and before we know it, it will be the time of year to start working on our projections for next year and the company’s annual budget. There are many complex and detailed models that we can utilize to forecast the sales performance of the business for the next period. However, I have recently noticed that almost every time I do work for a client, I end up using the most simple and easy to set up methods.

We discussed in our article on why we need a budget that the primary purpose of a forecast is to provide a guideline for the company. We don’t aim to produce a 100%-correct prediction, as in reality, we can never cover all variables that influence the business.

When working on a budget, we usually have a few key assumptions which we can implement in our forecasts. We rarely need a complex model, and more often, we can rely on the simple forecasting functions in Excel to yield satisfactory results.

Let us explore some of the most common methods to forecast performance, with examples in Excel.

Linear Forecasts

We have the following breakdown of monthly sales for the past two years (2019 and 2020), and we have to prepare a sales budget for the next financial year, 2021.

The first function we can look at is the FORECAST.LINEAR function in Excel. The way it works is it takes all previously known monthly sales (y, the result of the linear function) and the corresponding periods (x) and finds the line that best fits the data points.

By fixing the formula with F4 and dragging it down, we now have our linear forecast of sales for the following twelve months.

If we look a bit more into the numbers, we notice that we have some spikes in July and December of our historical data. This means that our business is seasonal, and the linear forecast might not be the best solution.

We can check this by plotting the original data on a line chart. It is easy to spot the spikes in July and December of both 2019 and 2020.

Now that we are confident there is a strong seasonality to our business, and we can further illustrate the incompatibility of the linear forecast with our model.

By adding a trend line to our chart (right-click on the data series and select Add Trendline), we can take a look at the function that best fits our data points.

Where:

• 9436 is the slope of our function; and
• 279,651 is the intercept.

We can also show the R² value on the chart. It is important to remember the relationship of our linear forecast equation and our data. The closer R² is to one, the better the line fits the data points. With a value of 0.0354, we can be sure this equation will not yield a representative forecast for the sales performance of the company.

Instead of using the FORECAST.LINEAR function, we can also calculate the slope and intercept with the dedicated formulas in Excel (SLOPE, INTERCEPT) and use a y(x) = a.x + b function to calculate our estimates.

By doing so, we will yield the same result as the linear forecast function from earlier. To better illustrate that these forecasted values are the same as the trendline, we can add the Linear Forecast column to our chart.

Moving Averages

Another simple way to forecast sales is to use a moving average. It’s a widely-spread technique to filter out noise in data and to estimate future performance. Due to the seasonality spikes in July and December, we might decide to use a 6-period rolling average. We calculate our forecast for January 2021 as a simple average of the previous six months.

The formula then starts to shift down, to the point where every data point we estimate after the initial five months is calculated based on previous moving averages.

We can plot the historical data and our estimates on a chart, to see how well our forecast represents the historical seasonality. It looks a bit better than the linear forecast, but still might not be good enough for our purposes.

Forecast Sheet

The third option we will look at is easy to use and robust option in Excel, which is unknown to many people. It’s in the Data Tab, under the Forecast section, called Forecast Sheet.

We select the whole data table, and it will automatically figure out the data points and the period we want to estimate. The red line on the preview chart shows us the forecasted values, and it changes as we tune the settings in the Options drop-down below.

Excel has noticed a six-month seasonality within our data points and pre-selected the option for us.

We know that our business has a one-year seasonality, with two spikes, rather than a six-month seasonality. As we adjust it to a twelve-month seasonality, we can see the chart preview resembles our data historical sales performance much more closely.

Manual Seasonality

Let’s look at another example from my recently-released online course on Budget Modelling in Excel. We have the annual sales for the past five years and a breakdown of revenues for the year 2020. The aim is the same as in the previous example, to forecast the monthly sales for the year 2021.

To do so, we will first calculate the average monthly sales for 2020 and then a seasonality multiplier, showing us how each month deviates from the average.

We can then reverse this equation by using these multipliers to calculate the monthly sales for our 2021 forecast. To do so, we need the expected average, which we can derive from the expected total revenue for next year. In the specific case, we know that the sales team expects a 12% increase based on 2020. Keep in mind, we can use a linear forecast here as well, and it will work better, as we don’t have seasonality on an annual basis.

Later on, after we calculated the expected monthly sales, based on the prior year seasonality and the expected sales growth, the sales team prepared their own, much more detailed performance plan, based mostly on confirmed orders and signed contracts.

In this example, our forecast turns out to be pretty close to the detailed sales plan. This aims to illustrate how we often need to estimate performance way before the respective departments have the time to prepare a comprehensive plan. However, by knowing the business and its seasonality, we can quickly arrive at reasonable estimates.

In reality, a fast and rugged forecast will rarely be so close to the detailed budget, and will almost certainly deviate a lot from the real performance.

Conclusion

Financial modeling and analysis often require us to forecast and estimate future performance, be it to prepare a budget, stress-test models, and others. It is essential to remember that most of the time, we can get by with a fast and straightforward forecasting method, and we don’t need to get into too much detail. Once you identify the key assumptions that will drive your model, there’s no need to go down the rabbit hole, trying to cover every single variable that has an impact on your estimates.

Please, show your support by sharing this article with colleagues and friends via the social links to the side.

Also, don’t forget to download the free Excel example below and sign up for our newsletter (we don’t spam, promise!).