How to use Excel in seasonal forecasting

Operations managers are often the people with the best understanding of demand for products and services and the cost to deliver those items. Yet, many of them don’t have the skills to deliver a forecast to the level that senior management requires. Finance professionals can offer this analysis, but they often are not close enough to the operations to understand them sufficiently.

Because there is an art, a give-and-take, to budgeting, there is a need for objective forecasting. By this, I mean something that can be constructed simply such that if anyone follows the same process they will get the same figures. This needs to be a mechanical, objective process. This way, analysts may prepare this data in moments without feeling emotionally attached to the output. Furthermore, operational managers can review the trend and state where future numbers are wrong, and all they need to do is explain the variation, ie, undertake incremental budgeting. There is no need for disagreements or confrontation: Both parties may work together as a team.

Now the scene is set for having a simple, mechanical approach for budgeting. Using my sample Excel file (click to download), let’s take a look at ways Excel can do this for you. Imagine I have some historical data:

The data are from September 2012 to July 2017, and I want to extrapolate them until the end of 2020. (I want 2020 foresight!)

There are several functions that can help, with one of the simplest being TREND. TREND(known_y’s,known_x’s,new_x’s,[constant]) assumes that there is a relationship between variables x (independent variable — here, the dates) and y (dependent variable — the sales), through a formula y = βx + c, ie, the equation of a straight line (β is the gradient of the line and c is the y-intercept).

Before you disregard linear regression, bear in mind many nonlinear relationships can become linear ones by taking logarithms of the variables. However, this won’t always work.

Here, time is our independent variable (x), and sales is our dependent variable (y). We only specify the constant if we want to force c into the equation (not common — it will usually be left blank). Similarly, it’s preferable to leave constant blank in the TREND function. For example:

Here, we can extrapolate the data using the TREND function viz.

You may have heard of hockey-stick projections; well, this is the swordfish. You extrapolate linearly, you get a straight line. Now who would like to present that projection to the senior management team?

This isn’t good enough. We need to identify the cyclicality of the data. It appears to go through a cycle once every 12 months. This might not always be the case, but the concept remains the same even if the periodicity is not annual.

I want to calculate a periodic growth rate objectively. There are various ways I can do this. You might argue with me. That’s the problem here — it’s subjective until your organisation defines how it is to be measured. Then, everyone follows that process and it becomes objective.

In my example, I am going to compare the sum of the sales over the 12 months ending 31 July 2017 with the forecast sales as calculated using TREND over the 12 months ending 31 July 2018:

It is this percentage I will use to grow the forecasts. (Note that the sample Excel file allows for different periodicities as long as the cycle remains constant.)

I then grow each period’s value by its corresponding value in the previous period by this percentage (7.82% here). This gives me a much better chart:

With practice, this approach doesn’t take that long to prepare. Numbers may be varied from this forecast with the operational manager having to explain these deviations. It makes life easier all round.

Once the method of assessing inferred growth rates based upon the TREND function has been agreed upon and what normalisations to historical data should be input, the process becomes more straightforward. Of course, this method should be used for all forecast inputs separately and not just on their aggregation; otherwise confusion occurs due to sales mix changes, new products, cut-off periods, etc.

But there is an even faster way — if you happen to have Excel 2016.

Exponential triple smoothing (ETS) sounds like a dairy process, but it actually uses the weighted mean of past values for forecasting. It’s popular in statistics as it adjusts for seasonal variations in data, like in the example above. For those who really need to know, Excel uses a variation of the Holt Winters ETS algorithm.

In Excel 2016, ETS has gone “native”, ie, it is a standard feature. This includes both a set of new functions such as FORECAST.ETS and other supporting functions for additional statistics. Your dataset does not need to be perfect, as the functions will accommodate up to 30% missing data.

Don’t worry about using these functions. Simply highlight the actual data and click on the Forecast Sheet button in the Forecast group of the Data tab of the Ribbon (ALT A + FC):

All you need to do is specify the final forecast period at the prompt and that’s it. It produces a raw data sheet, together with confidence intervals (to demonstrate potential spread in the forecast error), which looks something like this:

Word to the wise

Manual versus automatic?

• The “manual” method, using TREND, assumes some linear relationship (possibly at a derivative level) and requires some initial subjectivity regarding the normalisations of actual data and how to determine what growth rate to use over what duration. Once it has been agreed, it becomes a simple process to understand and maintain.
• The “automatic” method using the Forecast Sheet works it all out at the press of a button, after normalisations to historical data have been made. However, it’s only available in Excel 2016, it’s quite “black box” to many, and you’d be left having to explain to your line manager what exponential triple smoothing is.

The jury remains out. Whatever you decide, for your own sanity, I recommend an objective forecasting approach.

Liam Bastick is director of SumProduct, a global consultancy specialising in Excel training. Send ideas for future Excel-related articles to him at liam.bastick@sumproduct.com.