At a glance
In many organisations, forecasting budgets and revenues is a subjective and sometimes adversarial process.
The operations team has a view on its numbers and what is achievable, and resents how the finance team interprets and then presents the numbers.
The tension creates division and defensiveness, and fails to deliver forecasts that can accurately guide the business and deliver the performance measures it needs.
According to Liam Bastick, it doesn’t need to be this way.
Bastick is a Microsoft-accredited Excel MVP (most valuable professional) and a director of consulting and training firm SumProduct, and he believes it is possible to take an objective approach to forecasting using Excel.
“By objective forecasting I mean something that can be constructed simply, so that if anyone follows the same process, they will get the same figures,” says Bastick.
“Everyone at the end of the day is working for the same company, so it’s best to find a simple way where we can put something together that people can agree on, so operations and finance can do their bit, get on with their day jobs and carry on.”
Excel solutions
What is needed is a process, and Bastick says Excel can deliver the solution.
There are various approaches to using data to identify the next number in a series, such as simple averages, moving averages and weighted moving averages.
According to Bastick, each of these has its problems and can produce distorted and contentious results.
If you take an average over a certain period, for example, then you are implicitly giving equal weighting to what happened in the past as to what happened yesterday, and this may not necessarily be true.
More accurate results can be delivered through the more sophisticated method of regression analysis, which plots independent variables on one axis against a dependent variable.
Extrapolating this using trend analysis, with operational managers explaining any deviations – such as sudden supply chain issues or one-off problems in a particular market – can deliver an acceptable outcome, but there is an even faster way using either Office 365 or Excel 2016.
Forecasting in Excel
These programs now have a suite of tools, including FORECAST.ETS as a standard feature, and other supporting functions for additional statistics. More good news is that the functions can accommodate a dataset with up to 30 per cent missing data, and still deliver an outcome.
Using a process known as Exponential Triple Smoothing (ETS), Excel employs an algorithm that uses the weighted mean of past values for forecasting, and also adjusts for seasonal or cyclical variations in data.
All users need to do is to highlight the data and click on the “Forecast Sheet” button on the “Forecast” group of the “Data” tab.
To create a forecast, specify a final forecast period in the prompt and Excel will deliver a raw data sheet, along with associated confidence intervals, that demonstrates the potential spread in the forecasting error.
Underneath all this, of course, is a combination of sophisticated assumptions and calculations that together crunch the numbers and deliver the forecast, but for the user it’s all about inputting data and pressing the button.
How is this objective? Once the data is selected then Excel does the hard yards, instead of this work being done by various financial department models favoured by individuals over other alternatives.
“The beauty of using Excel for this is that if anyone does it, we would get the same answer,” says Bastick.
“No one can have a grievance because all anyone has done is plug into Excel and pressed a button, it’s a collaborative tool that no one has any particular ownership of.”
Data into Excel models
If there is any disagreement in this process, it is about the numbers and the data that are fed into the model, not the calculations.
“So if you go to the operations manager with the forecast and he doesn’t agree because he can see that the number for one month is wrong because there were issues on the wharves in China, then just get the new number and keep tweaking it,” says Bastick.
Ultimately, Bastick’s view is that Excel is a communication tool between accountants and the business.
“I believe that a successful business has four things: luck, communication, strategy, and execution,” he says.
“Excel can really take the pain out of forecasting, and that will take the pain out of a lot of the internal communication.
“And that means that the accountants can focus on the real value-added stuff, operations can get back to work, and everyone can leave work at a decent hour and actually have a life.”
