Approaches to Sales Forecasting - A Review
By Peter Boulton of Data Perceptions
WHAT MAKES MARKETS DIFFICULT TO FORECAST?
Virtually every company needs to generate forecasts of their short to medium term sales. Being able to forecast demand more accurately has major commercial advantages, whether the forecast is used:
- to plan purchasing, production and inventory,
- as the basis of marketing or sales planning,
- or for financial planning and reporting or budgeting.
Yet within real world markets, many factors conspire to make accurate sales forecasting difficult to achieve.
'Political' influences and the sales forecast
In the first place, sales forecasts are frequently used for all the purposes suggested above. This leads to conflicts between optimism and pessimism and potentially introduces 'political' influences into the process. Examples are the different role of the profit forecast (probably conservative) and the sales plan (probably optimistic), or where marketing expenditure is loosely linked to the sales revenue of products (and therefore leads to defensive forecasting to protect planned marketing spends).
Financial sales forecast versus operational demand forecastThere are also conflicts in terms of which units should be forecasted - orders-based for production forecasting or invoice-based for financial forecasting.
The 'right' level of time granularity
Similarly, forecasts by week by sku (stock keeping unit) at the total customers level for the next 12 weeks may be required by production planning. But, this time horizon is far too short and this level of product and time detail is potentially much too detailed for marketing and sales planning purposes. In addition, it omits any reference to customers detail. Frequently, sales to major customers need to be planned and managed.
The important point is to have a clear vision of who the primary customer or customers of the forecasts are. Select the appropriate level of detail and time horizon accordingly and accept that secondary customers of the forecast will probably have to accept sub-optimal forecasts. In many situations it is helpful for both Marketing and Sales to generate sales forecasts. Sales are often more likely to possess the detailed short term knowledge whilst Marketing need to 'own' the forecasts as a result of their role as brand profit 'custodians', and possibly have a clearer knowledge of longer term influences. It is vital here that each area is clear about the role and purpose of the forecasts they produce, and that forecast publication schedules optimise the currency of the data used as inputs, and given as outputs, by each forecaster.
Forecasting sales in constantly evolving markets
The second major difficulty of forecasting in real world markets is the very nature of these markets. They frequently exhibit some or all of the following characteristics:
- Frequent promotional activity
- High level and variety of competitor activity
- Promotions are seldom at the same time each year
- The size of the distribution 'pipeline' tends to vary
- Growing concentration in sales to biggest customers
- Fluctuating positioning at point of sale - between 'value' (i.e. low prices) and 'added value' (i.e. quality)
- The '80-20' rule. That is, 80% of sales come from the top 20% of customers. The top customers exhibit 'lumpy' demand due to promotions etc.. The smaller customers exhibit 'lumpy' demand due to irregular ordering patterns.
In essence, the dominant characteristic of real world markets is "NEVER THE SAME THING TWICE", especially with the major customers, combined with 'lumpy' or irregular demand from the smaller customers.
This makes it hard for commonly used forecasting approaches such as statistical forecasting to provide acceptable results over a short to medium time horizon. 'Acceptable' means able to predict the size and timing of those short-term, but business critical peaks and troughs in sales that result from the factors listed above.
CHOOSING THE RIGHT FORECASTING METHODOLOGY
All statistical methods either even out the peaks and troughs in sales history to produce trend-based forecasts, or else they look for repeated patterns in the historical peaks and troughs to make future forecasts.
Forecasting sales with statistics
But, if the peaks and troughs in sales are often caused by factors that are genuinely 'random' events, such as promotions or competitor activity, how can statistical methods help you forecast? On the one hand, a smoothed forecast has little value if the primary purpose for forecasting is to predict the short term sales peaks and troughs. On the other hand, how valid is the second approach (looking for repeated patterns) given the random nature of historical peaks and troughs? (Even 'seasonality' can vary with the weather, public holidays, world events etc..)
If not statistics....?
If you cannot use statistics, what can you use? In the majority of situations, informed management judgment (or 'finger to the wind' as cynics might describe it) is actually more likely to produce better results within real-world markets.
The essence of judgmental forecasting is the application of the business manager's knowledge and interpretation of past events and activities, and their effects on sales, to planned future events and activities. The result is a 'judgmental' forecast for the future sales periods.
The key factors to consider are well known:
- Trade promotions
- Launch / relaunch activity
- Promotions / special packs
- Price changes / discounts
- Historical out of stocks
- Distribution changes
- Seasonality (if relevant)
- Competitor activity
- Advertising effect
- Market trends
Although there is never the same thing twice, developing and applying an understanding of how sales respond to different types and combinations of events is the most effective way of generating a forecast. It has spin-off benefits too, because it forces marketing and sales people to think long and hard, and hopefully objectively, about which factors really drive their sales.
'Bottom-up versus top-down?
The method most likely to succeed is forecasting from the 'bottom up', and reviewing from the 'top down'. This means generating the forecasts at the lowest (relevant) level of detail using the process described above : the 'bottom up' method.
One then compares how the resulting forecasted year on year growth rates and Moving Annual Totals compare to expectation, historical or current growth rates and Moving Annual Totals. If the 'bottom up' results are out of line with the 'top down', then the 'bottom up' forecasts need to be revisited to identify the sources of the difference.
This process must continue until the 'top down' and 'bottom up' forecasts are consistent.
CHOOSING THE RIGHT SYSTEM
The forecasting methodology recommended in this article places a lot of emphasis on the knowledge and judgment of the forecaster. That is unavoidable given the nature of the market, but it follows that developing a good forecast can be a labour-intensive process.
Using Excel for forecasting sales
Forecasting software can help here, by providing the forecasters with a productive and flexible environment in which to analyse and manipulate their forecasts. A lot of companies use Excel spreadsheet based systems. Some use an option from their ERP (Enterprise Resource Planning) system.
None of these approaches are ideal.
Spreadsheet based systems are generally difficult to maintain, in terms of adding new products or customers, updating actuals or rolling forward years. They also tend to show the data in fixed views due to the fixed rows and columns structure of spreadsheet programs. Some analytical capability can be introduced by building clever spreadsheet macros, or by users reformatting data in different ways within their spreadsheets, but this approach tends to be clumsy and labour intensive. In addition, aggregation / consolidation of data across products and customers tends to require considerable manual processing.
In addition, spreadsheets are essentially single-user productivity aids, whereas business forecasting is normally a multi-user activity. Delays and inaccuracies get introduced through the need for consolidation of spreadsheets. One change can require the whole, cumbersome round-trip process to be repeated.
Using an ERP module for sales forecasting
Terminal / browser-based systems and ERP options overcome the maintenance problems but tend to be inflexible, lacking in functionality, and do not provide the variety of instant graphical views that a specialised PC based system makes possible. In addition, such systems can sometimes have performance problems - where transaction processing systems and decision support systems operate on the same host, transaction processing systems necessarily get preference in receiving processor time. In addition, it is hard to give these systems the degree of user-friendliness which sales and marketing users generally prefer.
Of the shelf, purpose-built sales forecasting software
Many purpose-built systems for sales forecasting are available, though they don't all cover the same range of features. Here is a checklist of features to look out for:
- Can you customise the system for your market and needs - in terms of facts, periods, product and customer hierarchy levels and properties etc.?
- Does the system give you the ability to input forecasts at different levels of product or customer detail (multi-level forecasting) and have the changes recalculated automatically 'up' and 'down' the product and customer hierarchies, without the need for cumbersome manual consolidations?
- Does the system allow you to capture qualitative information too? What caused the historical peaks and troughs? What was the forecaster's rationale for this forecast?
- Can the system take you beyond volume based forecasting to revenue / margin? Does it allow you to capture overall customer account planning, profitability and budgeting within a single environment? Combining and integrating the processes of forecasting, budgeting, medium term planning and analysis of plan versus budgets / forecasts within a single process / business application creates a single, company-wide vision of the future and sales plan.
- Does the system allow you to store and analyse different sets of forecasts through the year?
- Does the system give you forecasting accuracy analysis, allowing you to quickly identify and learn from your mistakes?
- How flexible is the reporting and analytical engine offered by the system? Can you store and replay favoured views of the data?
- How flexible and helpful are the graphics included?
- Can the system run in a networked environment, support multiple forecasters without the need for complex administrative and consolidation processes, and support remote forecasters?
- How easy to use is the system? Can the target users actually use the system or will they be confused by impenetrable statistical nomenclature? How much on line help is available?
- Does the system have any options which analyse forecasts and warn forecasters of risks identified versus previous sales history?
- Check data interchange with your corporate systems. How easy is it to keep the system up to date with latest sales actuals or to send the forecasts back to the corporate systems?
- If you do not already have an information analysis tool for users to "slice and dice" through sales to pick up trends etc., expect some of this functionality from your forecasting and planning system.
- If trade promotions are significant in your market, how does the sales forecasting solution handle promotions? Will it help you analyse incrementality / identify which types of promotions work best?
- And finally... if you need to generate forecasts quickly using statistics, how easy is the statistical forecasting engine to use for your business forecasters? Does it support automatic forecasting or does the user need specialist statistical forecasting knowledge to use it?
Forecasting in the real-world is a difficult process which does not invariably lend itself to automated statistical approaches. The so called 'finger in the wind' / 'judgemental forecasting' method, if carefully implemented and with appropriate systems support, can yield quality improvements in forecasting results.
You need a good system, forecasters who really understand their markets, and above all, the strongly held determination to put it all into practice.
Peter Boulton was a full-time sales forecaster for over 10 years, before creating Prophecy, Data Perceptions sales forecasting software solution.
This article has summarised some of the philosophies, experiences and considerations that led to the creation of Prophecy.