Company Overview
The client for this business intelligence solution is based in North America, and is primarily engaged in the marketing, bottling, and distribution of a broad portfolio of beverages in the US, Canadian, and European markets. With revenues of over $20 billion, the client operates over 80 production and over 350 distribution facilities globally, a fleet of over 50,000 vehicles, and owns some 2.5 million coolers, beverage dispensers, and vending machines. The client’s portfolio continues to grow and garner market share with a mix of new products and market expansion.
Business Challenge
To manage their extensive distribution staffing needs, the client required a rolling productivity forecast for their distribution centers, which in combination with a corporate sales forecast, would provide input to drive local staffing. The productivity forecast would also be used to identify inefficiencies in current staffing plans being supplied by the client’s various distribution centers. The client required the forecasting solution to be scalable, adjustable and maintainable with the ability to respond to changing business conditions and distribution center additions. Ultimately, any solution implemented would supply rolling forecasts to the client’s labor management application, which uses the forecast to provide a base for distribution center managers to construct their planned staffing requirements and communicate those needs back to management.
Solution Approach
The Northridge Business Intelligence (BI) consulting team recommended Microsoft SQL Server as a platform to meet this forecasting challenge. The client was currently using SQL Server technology, and due to this existing familiarity had the technical expertise necessary to maintain the solution once built. For this particular application, Northridge opted to build a custom forecasting algorithm and generation engine using Transact-SQL. This approach provided the opportunity for the engine to automatically adjust the forecast based on situational differences of each distribution center – namely the quantity and quality of each distribution center’s historical data. This engine could also easily supply its forecast to the client’s application database on a scheduled recurring basis.
Results Delivered
The implemented SQL Server forecasting engine yielded excellent results for the client. The engine delivered a rolling 13-week forecast for over 700 forecasting points in its 330 distribution centers. The accuracy of the forecasts was between 80-95% for 80% of the forecasts, which closely correlated to the percentage of distribution centers with adequate historical data. The forecast was 70%-80% accurate for most of the remaining forecasts, with only a few outlying points that could not be forecasted due to more fundamental and unpredictable changes in business. This engine was more accurate than the client’s existing corporate sales forecast when compared to historical data. The engine now produces these results automatically, supplying its forecast directly to the client’s application database, and alerting the client’s field managers of forecasting scenarios that require their attention, such as sparse historical data.