## 9. Time Series Analysis

Time series analysis, assumptions, consistency, structure, periodicity, rules and outputs.

### 9-2 Time Series Constants

Every workbook that undertakes time series analysis should contain named range time constants (e.g. months in year, days in week, weeks in year, etc.) rather than embedding these constants in multiple formulas.

### Commentary & Examples

In every time series model, a large proportion of the formulas in the workbook will need to reference the same general constants relating to time, unit measurements, denominations, etc. Examples of these general constants include the number of minutes in an hour, the number of hours in a day, the names of months in a year, etc. They can also include conversion factors such as powers of ten, etc. All such constants are universal and never change.

As a result of the common need to have these constants available in every time series model, it is recommended that as a general rule a summary of these constants be included in every time series model.

Note that the model developer will never need to decide whether to classify these entries as assumptions or non-assumptions – i.e. all these entries will be known and therefore fixed (i.e. an hour will always have 60 minutes and a day will always have 24 hours). Hence, the model developer might therefore prefer to include these entries as lookup data on a lookups sheet, as shown below:

If a model developer decides to use a lookups sheet in this way, this sheet could be re-used in every time series model that the model developer builds to reduce model development time.

### 9-3 Time Series Period Titles

Time series sheet period titles should always include the following data in each time series period:

- Period start date;
- Period end date; and
- Period number (counter).

### Commentary & Examples

As mentioned at the start of this chapter, it is the inclusion of time series sheets in a model that differentiates it as a time series model. The period titles that are included within time series sheets should provide model developers with all the information about each particular time series period that might be required by the calculations within the worksheet below. This information might include:

- The period start date;
- The period end date;
- The period number (counter);
- Days within the period;
- Days within the related financial year; or
- Months within the period.

The level of detailed information included within time series period titles will differ depending on the required complexity of the model calculations. However, at least the first three pieces of information listed above should be included within the period labels on every time series sheet, as demonstrated below:

The period number (counter) is simply a numeric representation of the number that the period represents, where the first period in workbook is number 1, and should be included due to the fact that many formulas in time series models are impacted by the time series period number and therefore need to reference this information.

Time series sheet period titles should appear in a consistent format and location on every time series sheet in the time series model. As such, there should rarely be a need for the model developer to create new period counters or references for the purposes of creating calculations that refer to periods.

### 9-4 Time Series Period End Dates

The period end date label for each period in a time series sheet should always be in view on the screen.

### Commentary & Examples

When using time series sheets, model developers and model users should always be aware of what each time series period represents.

The best way to achieve this is to ensure that the period end date for each period within the workbook is always in view on every time series sheet.

This is important for both the screen viewing of time series models and the analysis of printed output information from time series models, and is done as follows:

**Screen viewing:**Period titles should be kept in view by freezing window panes below them; and**Printing:**Period titles should be kept in view by ensuring that the period titles rows (or in some cases columns) are repeated with each new page in each time series sheet. This functionality is available in the page setup properties of each worksheet and prevents the need to repeatedly enter period titles as the depth of a worksheet results in it printing more than one page.

The following example demonstrates the use of freeze panes to ensure that the time series sheet period end dates are always visible, irrespective of the window scroll position:

### 9-5 Time Series Periodicity Identification

The periodicity of each time series sheet should be clearly identified and always in view on each time series sheet.

### Commentary & Examples

Although the periodicity of each period within a time series model should be ascertainable by viewing time series sheet period end dates, this is such an important piece of information that it should nonetheless be clearly labeled on each worksheet in the workbook, as demonstrated in the monthly time series sheet shown below:

This is particularly important in models containing multiple periodicities, such as annual time series sheets in a monthly time series model, so model users are always aware of the periodicity of the active time series sheet.

In some cases, a model user may know the period end year but may not know the end date of each annual period. For example, a model user looking at an annual time series sheet might know that each period represents one year but still may not know whether each year ends on 30th June or 31st December. In such cases, the time series sheet should display both the periodicity and the period end date, as shown below:

The time series period titles in this sheet also indicate whether each period is an historical or forecast period, thereby ensuring that model users are always aware of the periodicity, end date and time frame of each time series period.

### 9-6 Time Series Number of Periods

A workbook that undertakes time series analysis should always include a cell or cell range that indicates the number of periods in each distinct time series.

### Commentary & Examples

The number of periods in a time series model is important for many reasons. Aside from dictating when calculations within a workbook will cease, it also drives the timing of the inclusion of many common time series model features, including valuation terminal values, contract start and end periods or changes in structure, to name a few.

The number of periods can also be important for many other formulas that can be included in a time series model, so it is therefore important for model users that the number of periods in a workbook be clearly labeled and located within the workbook.

The following demonstrates the inclusion of the number of periods in a monthly time series model, including the number of periods in months, quarters, half-years and years, which are referenced by time series sheets with different periodicities throughout into the model:

In this example, the term of the model is specified in months (the lowest periodicity) and is an assumption cell because the underlying workbook is a modular workbook which facilitates the automatic shortening/extending of time series sheets whenever this term assumption is changed.

### 9-7 Time Series Sheet Consistency

Time series sheets for each distinct time series within a workbook should always:

- Contain the same number of periods; and
- Have the first period starting in the same column (or more rarely, row).

### Commentary & Examples

Implementing formats, styles and worksheet structures consistently within every worksheet is a critical part of creating best practice time series models. Using a consistently structured approach to developing time series models can remove many of the issues that both model developers and model users face in working with models. Moreover, the ongoing application of a consistently structured approach will allow model users to develop a familiarity with the structures utilized in the model and thereby greatly reduce the amount of time required to understand and use it.

When model users work with time series models, they generally need to view several worksheets that contain different types of information analyzed over the modeled time periods. It is important for the model user that the periods that are contained within each worksheet are consistently structured such that the first period is always in the same column on each time series sheet. Conversely, if the period labels go down a column, the first period should always start in the same row.

Of equal importance, each time series sheet containing the same period titles should contain the same number of periods. By having a consistent number of periods starting in a consistent column (or row) on each time series sheet, it will be significantly easier for model users to develop an understanding of the model, and reduce the time and risks associated with building and maintaining the model.

The example below shows an annual time series assumptions sheet with 5 time series periods commencing in column J:

In this model, assumptions have been separated onto separate assumptions and outputs sheets, with the corresponding time series outputs sheet shown below:

Note the consistency in every element of these two time series sheets, including period titles content, frozen panes, the first period column, the number of time series periods and the width of all worksheet columns. The result is instant familiarity when moving between the sheets.

### 9-8 Time Series Data Direction

Where practical, time series period titles should be positioned across rows, not down columns.

## Commentary & Examples

In order to ensure the consistency of time series sheets within a time series workbook, the time series period titles in every time series sheet need to be based on the same time series assumptions. These assumptions will provide all the relevant information required to form the foundation for the time series analysis undertaken by the model, including:

Depending on the complexity of the time series model, it may not be necessary to provide a model periodicity assumption, but this information is always of primary importance to model users and other model developers when they first come in contact with a model and should therefore be clearly communicated nonetheless.

In a time series model, these basic assumptions are referred to as time series assumptions because they are referenced either directly or indirectly by almost every formula in the model. The following example shows how these time series assumptions could be presented:

This type of centralized time series assumptions entry interface should be included and labeled as a dedicated set of time series assumptions in every best practice time series model.