|
|
 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.
|
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.
|
The Northridge Systems Business Intelligence (BI) consulting team recommended
Microsoft SQL Server 2005 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.
|
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.
|
|
|
Get the Northridge Systems quarterly e-newsletter.
|