Forecast Solutions

 FMCG Forecasting for Consumer Goods

Forecasting for fast moving consumer goods has all the usual challenges of demand forecasting, but is particularly influenced by the high impact of promotions.  Frequent promotional activity complicates the already difficult tasks of historical data cleansing and seasonal analysis, then there is the task of estimating the effect of future promotions and integrating them into the forecast.

As in any company a choice has to be made regarding the time bucket to be used in forecasting.  The dominance of promotional activity in FMCG, together with the likelihood of needing to integrate weekly customer forecasts, tends to tip the balance somewhat in favour of weekly rather than monthly forecasting.  A slight downside to this is that the important matter of seasonal analysis becomes more difficult and may need special attention.

Forecast Solutions can provide forecasting software based on Excel in some instances, including a neat and convenient method for dealing with promotions, or we can help in the selection of specialist software.  Our training programmes include good coverage of all the issues around forecasting for fast moving consumer goods.


retail shopping mall

Forecasting Methods

Short term forecasting for FMCG is most commonly approached using time series forecasting.  This involves analysis of the seasonality and trend shown in historical demand, with the intention of projecting those patterns into the future.  In a business which is heavily inflenced by promotional activity, sometimes with pronounced seasonal patterns as well, it is usually best to deal as well as possible with these essential components and keep the statistical forecasting really simple.

Causal modelling to quantify the effect on sales of causal factors such as price or weather can also be useful, but needs a good amount of historical data for analysis that is not always available.

What the FMCG forecaster often benefits from is a wide range of information including EPOS data and continuous market research information, so a further challenge is to make the best use of the big data that may be available.

Forecast Solutions can help with all of these considerations.  We may be able to provide a forecasting system in Excel, or we can help with a software selection process.

Historical Data Cleansing

Before any form of statistical forecasting for FMCG can be carried out it is important to cleanse the sales history on which the forecast will be based of past promotional activity and other data abnormalities. This is particularly true if there is pronounced seasonality as promotional history can easily confuse the seasonal analysis.

There are a number of ways of dealing with promotions in the sales history, although the forecaster may be somewhat dependent on tools existing in software to hand. Some of the options that can be considered are:

  • adjust the historical data on the basis of what is thought would have occurred had the promotion or other event not taken place

  • flag out individual time periods from the analysis (subject to how missing data is dealt with in your software)

  • if you have specialist forecasting software try using the automatic data cleansing that may be available

Once the sales history has been cleansed it can then be submitted to the  statistical procedures chosen for the preparation of the baseline forecast i.e. the forecast excluding promotions.

Seasonal Analysis

Many FMCG products are affected by seasonal effects caused by a variety of factors including regular annual events such as Xmas, school holidays and seasonal weather patterns.  If forecasting is to be carried out in Excel there are a couple of easy methods that can be used.  It is often useful to calculate seasonal factors at a logical group level ('group seasonal indices), then apply them to the most detailed level needed for forecasting.

If forecasting is carried out at a weekly level of detail, the greater amount of volatility in the historical data often leads to a ragged profile in the set of seasonal indices that are calculated and it is often necessary to smooth out the indices.  This can be done quite easily in Excel, but is not available in many specialist forecasting packages.  With weekly data a 'group seasonal indices' approach is especially useful.

Our training courses cover a number of these topics including some worked examples using Excel.

Estimating the Effect of Promotions

In order to estimate promotional effect, either for data cleansing or for forecast preparation, it is sometimes necessary just to use judgement guided by market intelligence. This may be particularly true for companies where promotions are uncommon or when the particular type of promotion has no precedent.

For many large FMCG companies, however, there is likely to be a good amount of history on various types of promotions.  If that is the case it is often possible to analyse previous promotions, consolidating those of a particular type, in order to arrive at a set of promotional profiles. These can be based on volume or on percentage uplift. Profiles are useful to help with data cleansing and to build in the future effect of promotions into the forecast.

Excel or other software for FMCG forecasting

Excel is an ideal vehicle for small-scale FMCG forecasting systems because of the total flexibility that it offers.  At Forecast Solutions we can build forecasting software in standard Excel or with a VBA front end if preferred.  It is easy to build in a simple promotional planning mechanism into an Excel system. 

Causal models based on causal factors such as pricing or weather can be implemented in Excel as the main forecasting solution or in conjunction with time series methods.  The causal analysis itself is usually carried out in statistical software because the regression tool in Excel is somewhat basic.

Excel becomes less practical where the number of forecast entities is large, where multi-level forecasting at more than two levels is required and if multiple units of measurement need to be managed.  In such cases it may be more sensible to look for a specialist software solution and we are well equipped to help in a software selection process.