Integrating R and Excel for Automatic Business Forecasting

We present a simple exercise in bridging the gap between statistics and everyday business practice, based on two powerful tools already available in the R system: the forecast package (Hyndman, R.J. (2011). forecast: Forecasting functions for time series. R package version 2.13) for automatic time series forecasting and the RExcel add-in for MS Excel (Baier, T. and E. Neuwirth (2007). Excel :: Com :: R. Computational Statistics 22(1), 91–101.) allowing to embed R functionality into spreadsheets and to interact with their built-in macro language. The application we developed makes forecasting practice accessible to those who are not familiar with statistical programs and, possibly, do not even have a sound statistical background. Many processes inside the firm involve forecasting. Some build on models and relationships between balance sheet items, but sometimes an a-theoretical extrapolation of past tendencies is needed. As few firms can afford to have trained statisticians dedicated to supply-chain forecasting and the like, budgeting and other activities are often based on simple, heuristic extrapolation of past data. It is commonplace, especially in small enterprises, to ”pick last year/month’s value”, either in terms of stocks or of increments, as the best estimate for the coming period. Fully automatic forecasting of time series, based on model fitting and model comparing algorhythms selecting the ’best’ model for the data at hand, provides a statistically well founded solution to the forecasting problem and can be of great use to the firm in obtaining accurate predictions for variables like sales, commodities’ input needs and the like, where forecast errors cost money. Such fully automatic procedures are implemented in a variety of commercial software. We show how an open-source solution is also very easy to set up.