Enhancing Forecasting Capability of Excel with User Defined Functions

We have developed seven user defined functions (UDF) related to forecasting. The first four functions are for exponential smoothing. These are simple exponential smoothing, Holt’s fit, Winter’s method and Holt-Winter method. The next three functions can be used to calculate forecasting errors. These errors are mean square error (MSE), mean absolute difference (MAD) and mean absolute percentage error (MAPE). These UDFs eliminate the need for intermediate steps in exponential smoothing and error calculations. These functions are as easy to use as other forecast related (statistical) functions, such as “Trend” and “Correl”. Moreover, our functions present users with choices. They can choose to provide (or not to provide) initial values and seasonal indices (where applicable). These characteristics make our functions user-friendly and flexible, and therefore enhance the power and usefulness of excel for forecasting.