I have written before about a busy accountant’s need for objective forecasting, and I think it’s high time to revisit the topic. By “objective forecasting”, I mean something that can be constructed simply, such that if anyone follows the same algorithm, they will obtain the same figures. This is important, as this is where operational and finance staff come together, each with their own skillsets where they will work better together than individually.
As such, it remains a mechanical, objective process. No one becomes invested emotionally or mathematically in their figures — and variations from the initial calculations only need to be explained (ie, incremental, rather than zero-based, forecasting will be adopted).
Consider an example (see the screenshot “Simple Example — Sales”).
Here, I have 12 months of sales, and I wish to predict the following 12 months’ values. Some analysts and forecasters will calculate moving averages, weighted moving averages, and so on, but perhaps a better approximation may be made using linear regression analysis — something supported by Excel.
Linear regression analysis is a means of drawing the “best straight line” through a set of observable data points (see the graph “Linear Regression Analysis”).
This is not subjective. The “best straight line” is usually defined using the least squares method. For a set of data points:
the average (mean) of the x and the y values can be determined as x̄ and ȳ respectively:
The equation of the slope (gradient) is then given by:
The equation of the slope (gradient) is then given by:
Thus, the equation of the straight line would be:
Please don’t start running for cover in response to the mathematical equations above. You don’t need to know — or understand — them. Excel has functions that can calculate them:
● SLOPE(known_y’s, known_x’s) will calculate the slope or gradient.
● INTERCEPT(known_y’s, known_x’s) will calculate the y-intercept, ie, the value of y when x is zero [0].
● TREND(known_y’s, known_x’s, new_x’s, [constant]) will calculate the new y value(s) for given x value(s). The argument constant should be omitted so that it will be calculated according to the best straight line and not forced through the origin (the point (0, 0)).
The important thing is to get the x and y values the correct way round. The x value is known as the independent value (the one you may select), and the y value is known as the dependent value, as it is based upon the value of x chosen.
For instance, you would ask, “What will my sales in December be?” rather than “My sales are $12,497,639 — what month is it?”
Here the independent value (x) would be the month, and the dependent variable (y) would be sales.
Returning to my example, I can use either the formula:
=TREND($E$11:$E$22,$D$11:$D$22,$D23)
(See the screenshot “Using the TREND Function”.)
and copy it down, or else use the dynamic array formula, assuming you have Excel 365:
=TREND($E$11:$E$22,$D$11:$D$22,$D23:$D34)
(See the screenshot “Using the Dynamic Array Formula”.)
Either way, you generate a “swordfish” image. It wouldn’t look good to report these values to senior management, as the numbers look too simplistic.
However, we do now have an implicit growth rate, based upon the historical values. We can use this to reallocate the numbers computed. I will begin by copying and pasting as values the forecast numbers (see the screenshot “Forecast Numbers As Values”).
I will then delete their formulaic counterparts (see the screenshot “Formulaic Counterparts Deletion”).
In this instance, since I have both 12 months of actual data and 12 months of forecast data, I may sum both sets of values, which I will then copy and paste as values, to avoid a potential circularity (see the screenshot “Summing Both Sets of Values”).
I now have the implied growth rate, being F35/E35, which is approximately 1.0492 (see the screenshot “Implied Growth Rate Calculation”).
This value in cell G35 may then be used as the multiplication factor for the forecast values, using it on the actual values from 12 months previously (see the screenshot “Deriving Forecast Values”). Of course, this assumes a 12-month cycle.
This looks much better. Where staff disagree with the numbers, since they have been forecast objectively, they merely have to revise these estimates and explain the reason for the deviation(s). Incremental budgeting in seconds!
If you have actual data that pertains to a longer historical period, you may need to revise the mathematics accordingly and generate a growth rate based upon compound annual growth rates (CAGRs). However, this is not the key point; it is understanding the principle.
You are not going to forecast this way, though. You are simply going to use this as an explanation for a very simple technique, known as exponential triple smoothing (ETS). It may sound 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, similar to the example above.
Allow me to demonstrate how you may forecast in seconds. Let’s consider the following data, available in the example Excel file. (See also the screenshot “Historical Data”.)
Of course, historical data this perfect will never happen in practice, but I am simply trying to demonstrate the idea.
Since the introduction of Excel 2016, there is now a set of new functions such as FORECAST.ETS and other supporting functions for additional statistics that will generate your future values. Your data set does not need to be perfect like the above image, as the functions will accommodate up to 30% of your data missing.
I won’t even bother with 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) — see the screenshot “Using the Forecast Sheet Button”.
All you need to do is specify the final forecast period at the prompt, and that’s it (see the screenshot “Create Forecast Worksheet”).
You don’t necessarily need to expand the Options section (the section immediately after the “Forecast End” date), since most of the time the Timeline and Values ranges generated will appear reasonable. It produces a raw data sheet, together with confidence intervals (to demonstrate potential spread in the forecast error), as shown in the screenshot “Raw Data Sheet”.
With a little finesse, you can create a simple, easy-to-read chart in seconds that fits in with your corporate branding — see an example in the screenshot “Chart Aligned to Corporate Branding”.
You should consider analysing your forecasting as you use this method. Since this approach takes considerably less time than archaic, conventional methods, analysts are more likely to reforecast more regularly. I would recommend just a very simple computation to begin, calculating the standard deviation of the variance sample (using STDEV.S) and checking to see whether the standard deviation becomes smaller over time.
Liam Bastick, FCMA, CGMA, FCA, is director of SumProduct, a global consultancy specialising in Excel training. He is also an Excel MVP (as appointed by Microsoft) and author of Introduction to Financial Modelling and Continuing Financial Modelling. Send ideas for future Excel-related articles to him at liam.bastick@sumproduct.com. To comment on this article or to suggest an idea for another article, contact Oliver Rowe at Oliver.Rowe@aicpa-cima.com.