Forecasting with Excel

Abstract

Introduction: Time series analysis is used by statisticians to make predictions from time-ordered data. This is crucial for planning for the future. The inclusion of little-known forecasting function in Excel™ has brought this type of analysis within the ability of less mathematically sophisticated individuals, including doctors. There are two main models for time series analysis: ARIMA (Autoregressive Integrated Moving Average) and exponential smoothing. This paper will demonstrate how the ubiquitous Excel facilitates a little-known sophisticated forecasting technique that employs the latter and presents a facilitating spreadsheet.

Methods: Excel's FORECAST.ETS function was invoked with supporting macros.

Results: A bespoke spreadsheet was created that would prompt for data to be pasted in columns A and B, formatted as a valid date in A and data in B. After error trapping and a horizon date, the FORECAST.ETS function calculates forecasts with 95% CI and a line graph. The FORECAST.ETS.CONFINT was also invoked using a macro to obtain a 95, 96, 97, 98 and 99% confidence intervals table.

Discussion: Forecasting is vital in all fields, including the medical field, for innumerable reasons. Statisticians are capable of far more sophisticated time series analyses and techniques and may use multiple techniques that are beyond the competence of ordinary clinicians. However, the sophisticated Excel tool described in this paper allows simple forecasting by anyone with some knowledge of this ubiquitous software. It is hoped that the spreadsheet included with this paper helps to encourage colleagues to engage with this simple-to-use Excel function.

Keywords: Excel; exponential smoothing; forecasting; health Workforce; health services needs and demand; holt-winters test; models; statistical; statistics & numerical data; trends.