Using MS SQL Server and R for Forecasting Sales of Consumer Goods

Sales Forecasting for Consumer Goods

Project Background

The manufacturer wanted to forecast sales across it 200+ retailer partners for it 600+ items. The items are clubbed into categories which were in turn clubbed into departments. There were retailers who are regular buyers, as well as irregular buyers and intermittent buyers.

Analytics Objectives

• To segment the retailers based on historical purchase patterns and statistically validate the segmentation
• To develop statistical forecasting models with accuracy more than 80+
• To develop a process for regularly update the forecasts, given the recent data.

Solution

• Our team has created a data model specific to the business process of the retailer and the current forecasting problem in a dimension-fact method; product, retailer and time being the hierarchical dimensions; and actual and forecasted sales are the facts.
• The retailer segmentation followed a frequency as well as value based segmentation schema following certain probability distribution aspects.
• The forecasting process adopted time series based models supplemented by seasonality and other factors like promotions.
• The process was created in R integrated with the DF for each product-retailer combination to update forecast every week.

Technology:

MS SQL Server, R (the algorithm was developed in R)