Advanced Modelling in Finance Using Excel and VBA

Appendix A. Other VBA Functions

This appendix provides additional examples of user-defined functions for short-term forecasting, ARIMA modelling, calculating splines and eigenvalues and eigenvectors. There is a brief introduction on each application. The spreadsheets themselves are less important than the associated VBA functions, whose code can be examined in the VBE Code window. The associated workbook is called OTHERFNS.xls.

FORECASTING

The functions associated with the 4Cast sheet are based on time-series analysis typically applied to past monthly sales data for a product or a product group. Time-series forecasting methods assume that existing sales patterns will continue into the future, hence they give better results in the short term than the long. Sales data is 'averaged' in different ways, which ensures that recent data is given more weight in the average than data way back in the past. Short-term forecasts are constructed by projecting most recent averages one period ahead.

Firstly, visual inspection and other techniques suggest the appropriate underlying model for the data generating process. If the data oscillates randomly about an average 'level', the model is assumed to be of the form:

Equation A.1. 

If the data displays trend (an average level that changes systematically over time), the model is assumed to be of the form:

Equation A.2. 

If it oscillates regularly with a fixed ...