Spreadsheets for Sales Forecasting & Demand Planning

Sales Forecasting  - The Basic Principles

Learn how to combine quantitative methods (like statistics) with qualitative methods (such as the Delphi Technique) for more accurate sales forecasting and demand planning

Introduction

Forecasting what's going to happen in the future has never been easy, but in today's fast moving and often volatile world, predicting future events is more difficult than ever. The fact that forecasting is difficult and forecasts are often wrong doesn't mean we should abandon forecasting altogether. Anyone involved in decision making, and that includes pretty much everybody, needs to base their decisions on what has happened in the past, what's happening now and what they think will happen in the future.

Our focus here is on sales forecasting, but the basic principles apply to forecasting in general and can be divided into three stages:

• Analyse the past to try and spot trends and patterns in the data.

• Project these trends and patterns in to the future (extrapolative forecasting).

• Modify the projected data based on our own experience and judgement (qualitative forecasting).

The Forecasting Process

Extrapolative Forecasting

Using time-series analysis its possible to extracts trends from your past sales data, breaking it down into four principle components:

The Trend Component

Regardless of other fluctuations, there is generally an overall sales trend. Over a period of time, sales may be increasing, decreasing or remain static. Typically, changes in sales growth rates are caused by new technologies, population dynamics, changes in tastes, changes in the firm's marketing strategies or more or less competition in the market place.

The Trend Component
 

The Cyclical Component

Sales are often effected by swings in general economic activity as consumers have more or less disposable income available. These fluctuations normally follow a wave-like pattern being at a crest when the economy is booming and a trough in times of recession.

The Cyclical Component
 

The Seasonal Component

During the year, whether it’s on an hourly, weekly, monthly or quarterly basis, there is normally a distinguished pattern to sales. The Seasonal Component is generally effected by such things as the weather, holidays, local customs and general consumer behaviour.

The Seasonal Component

Erratic Events

Having extracted the three components above, what's left over is data that cannot be accurately predicted, such as strikes, floods, fads, riots, fires, etc. These events are generally random in nature and are difficult to forecast using statistical methods. However, they can, and should be considered using the qualitative forecasting methods described below.

Qualitative Forecasting

Your forecasts shouldn't rely on statistical methods alone. While they can give you a useful insight into what might happen in the future, there are no guarantees that past trends will continue. New technologies, markets, products, competitors and changes in marketing strategies or in the economic or political environment can all effect future sales. Furthermore, new business will inevitably have insufficient historical data for extrapolative forecasting to be effective.

There are various qualitative forecasting techniques that, when combined with extrapolative forecasting, can improve the accuracy of your sales forecasts. They include:

Visionary Forecasting

This method uses personal insight, judgment and when possible facts about future events. It is characterised by subjective guesswork and imagination. If used alone, this method is generally inaccurate, but if used to adjust forecasts based on statistical methods, it can be relatively effective.

Panel Consensus

This technique is based on the assumption that several minds are better than one. Groups of people who can give sensible estimates of sales, such as sales representatives and brand managers, discuss sales expectations and arrive at some consensus on which to base the forecast.

When using Panel Consensus forecasting, you should bear in mind that social pressure, peer pressure and emotional attitudes displayed in small group behaviour can effect the results of the forecast. Furthermore, research suggests that groups are less risk-averse than their component members.

The Delphi Technique

This approach to forecasting was developed by Olaf Helmer and others at the RAND Corporation in the 1960's. It is similar to Panel Consensus, but rather than meeting together to debate future sales, the experts are kept apart so their judgment isn't influenced by social pressure and the negative aspects of small group behaviour.

The process is reiterative; relying on questionnaires to collect the opinions of the experts, while statistical summaries of each series of questionnaires provides controlled feedback about the opinions of the other panel members. The statistical summaries enable the experts to re-evaluate their opinions in the light of the general consensus, thus gradually narrowing the range of estimates until an acceptable consensus is reached.

Historical Analogy

Similar products and markets often display similar growth patterns or life cycles on which you can base your forecast. The S-shaped product life cycle is a typical example. It is generally divided into four stages:

• Introduction - a period of slow growth while the product is introduced onto the market.

• Growth - sales rapidly increase at an increasing growth rate as the market accepts the product.

• Maturity - sales increase slowly but with a decreasing growth rate. The product has now been accepted by the majority of the people that are likely to buy it.

• Decline - a decline in sales caused by changes in tastes, increased competition or a shift away from your product towards a new or improved product.

A Typical Product Life Cycle

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.