Sales Forecasting Model

Research by Brian T. Ratchford

THE ABILITY TO RECONCILE SALES FORECASTS WITH SALES PERFORMANCE IS CRITICAL. WHEN IT COMES TO FORECASTING SALES VOLUME/DEMAND AND MAKING SOUND MARKETING DECISIONS, A MULTI-CHANNEL, MULTI-REGION SALES FORECASTING MODEL AND DECISION SUPPORT SYSTEM GO A LONG WAY TOWARD MEETING THOSE NEEDS AND CHALLENGES.

Recent research by Brian Ratchford, PepsiCo Chair in Consumer Research at the Smith School, suggests that sales volume can be forecast by applying established marketing science methods to solve managerial problems. Consumer packaged good companies like PepsiCo and Kraft Foods are increasingly faced with the complex and difficult task of forecasting sales volume and demand for goods sold through multiple channels such as grocery, drug, mass merchandise, and convenience stores in multiple regions. For this reason, it is important for companies to develop separate sales forecasts by product category, channel, region, and major customer account within each channel.

Forecasting has been less than successful in the past because multiple forecasts generated by different users within companies—such as the sales, finance or brand management departments—each based on different methods, were not reliable enough. Incomplete data, unavailable data and the need to capture the effects of past sales, trends, pricing, and promotional and seasonal variables combined to make multi-channel, multi-region sales forecasting an especially arduous task.

In his paper, "A Multi-Channel, Multi-Region Sales Forecasting Model and Decision Support System for Consumer Packaged Goods," co-authored with Venkatesh Shankar, marketing professor at Texas A&M, and Citigroup Senior Vice President Suresh Divakar, Ratchford discusses the development and implementation of CHAN4CAST, a sales forecasting model and a Web-based decision support system (DSS) for carbonated and non-carbonated soft drinks for a leading consumer packaged goods company. Using a dataset drawn from IRI Infoscan data, the company’s wholesaler shipment data, A.C. Nielsen’s Scantrack data, and Wal-Mart’s Retail Link spanning 149 weeks, the authors developed a forecasting model using the best available econometric procedures. Because they needed to consider a large number of variables and the tight timeline, stepwise selection was employed as a preliminary step to develop the initial models. These preliminary models were further refined and the final model was validated against alternative models, using holdout samples. The procedure includes a method for forecasting future values of variables that help in predicting sales, such as price and display activity.

To develop the DSS, Ratchford first identified the users, what forecasting information they needed to make decisions, and what contents and format of the outputs each user wanted. An information technology consultant helped map the model forecast outputs to the desired outputs and the drill-downs of the users and developed specifications for the Web-based tool. The model integrates all existing forecasting approaches into one system with field input, has a scientific benchmark for determining forecasts, and offers diagnostics when the actual volumes
deviate from the planned volumes, placing accountability on the appropriate managers to meet sales targets.

"The model is being successfully used by a leading consumer marketing company for its major annual forecasts," says Ratchford. "The company estimates that the use of the model and DSS has saved $11 million for an investment cost of less than $1 million."

The model captures the effects of non-traditional variables such as temperature and quality of day effects to improve forecasts and incorporate several intricate adjustments to the forecasts, for example, day-of-week lifts for the cusp weeks, load-ins that occur before special holidays (e.g., Fourth of July) as well as trading-day adjustments that account for differences in sales between weekdays and weekends in a month. Key to the company’s needs, the authors’ model includes an appropriately derived quantitative relationship between weekly retail sales and wholesale shipments. Taken together, all these distinctive features of CHAN4CAST have enabled the company’s top- and mid-level executives in sales, marketing, strategic planning, and finance to develop accurate forecasts of sales volume, plan prices and promotional activities over a long-term horizon, track sales response to marketing actions over time and simulate forecast scenarios based on possible marketing decisions and other variables.

Divakar, Ratchford and Shankar’s work has been accepted for publication in the upcoming issue of Marketing Science.