No Time Series Problems – Period!

Every forecast model needs a time series. But how can you ensure it is sufficiently flexible? By Liam Bastick, Director with SumProduct Pty Ltd.

Query

I want to write a formula that I can use to construct date headings for my forecast models. However, I need the start date to be flexible. Could you give me some pointers?

Advice

Most forecast models project key outputs over multiple periods. Typically, these periods are not headed “Time 1”, “Time 2”, etc. but display end dates to assist end users understand payback periods, seasonality, trends and so on.

An example time series could contain some or all of the following:

Before turning our attention to this month’s question, take a look at the above example. Are all of these rows absolutely necessary?

I would suggest not.

Essentially, three lines are necessarily needed when modelling (the rest may be derived as necessary):

• Start date: This will allow for models where the first period is not a “full” period (often called a ‘stub’ period), e.g. a business may wish to project its profits from now until the end of the calendar year for the first year;
• End date: This will define the end of the period and will often coincide with reporting dates, e.g. end of financial year or quarter ends. By having both the start date and end date defined, a modeller can determine the number of days / weeks / months in the period, which financial year the period pertains too and so forth;
• Counter: Start and end dates are insufficient. Constructing calculations based on consideration of a date is fraught with potential issues in Excel. This is because dates are really serial numbers in Excel which may differ depending upon the underlying operating system (e.g. Day 1 for Microsoft Excel for Windows is 1 January 1900, whilst Day 1 is 1 January 1904 for Microsoft Excel for the Macintosh). Further, if you are building a monthly model you may wish to divide an annual figure evenly instead of based on the number of days. This is also the easiest way to identify the first and last periods in a robust manner.

So, bearing this in mind, how do you build up the necessary formulae for these three line items allowing for the more common eventualities? Well, to begin with, there’s only really one troublesome formula. This is because:

• The Counter is simply the last period’s number plus one. I tend to use the formula =N(Previous_Cell)+1, where the N() function takes the numerical value in the previous cell, and more importantly, text is ignored so that #VALUE! errors will not arise;
• The Start Date is simply the Model Start Date for the first period and the day following the last period’s end date otherwise. This can simply be written as =IF(Counter=1,Model_Start_Date,Previous_Period_End_Date+1).

Therefore, we need only consider the formula for the Period End Date. The attached Excel file provides a simple illustration that I will explain here. Consider the following simple example:

I have selected an arbitrary start date (Model_Start_Date) of 11 December 2013, and assumed that the number of months in a full period (Periodicity) is 3. The third line item is a little more subtle: this specifies which periods are period ends by specifying one month that will be a period end month.

For example, tax may be paid quarterly in the months of January, April, July and October. By entering a Periodicity of 3 and specifying an Example_Reporting_Month of any of 1, 4, 7 or 10, this will provide sufficient information to work out the quarter ends, i.e. 31-Jan, 30-Apr, 31-Jul and 31-Oct. The Reporting_Month_Factor is simply the minimum of these acceptable alternative values and is calculated automatically here. The approach I will use here requires that the periodicity is a divisor of the number of months in a year (Months_in_Year) – which is why my example only allows the Periodicity to be 1, 2, 3, 4, 6 or 12.

In the example above, we are building a quarterly model where December is one of the quarter ends. Therefore, the possible quarter end months are:

This example table allows for up to 12 month ends (i.e. for a monthly model).

So how do we derive the necessary formula? I will give you some insight into my simplistic view of the world. First, I would construct the following table:

This simple table considers all 12 months of the year for the Model_Start_Date (first column). The middle column displays which month would be the first quarter, given the assumption regarding the month of the Model_Start_Date. Therefore, a start date in January, February or March will give rise to a March quarter end, etc.

In the attached Excel file, I use an array formula to calculate this month number dynamically (array formulae were discussed in Array of Light). This is not necessary, and the values could just be typed in – remember, this table is simply a tool to ascertain how to construct the formula required.

The final column is then the difference between the end date month and the Model_Start_Date month. It is slightly more complicated than this as we need to consider what happens if the Model_Start_Date month exceeds the final end date month. For example, in my tax example above, tax arising in November (month 11) is after the final payment period of the year (month 10). This would be paid in month 1 of the following year instead.

The point is, the final column highlights the pattern of how many months after the Model_Start_Date the first reporting period will occur. We can now use two functions in tandem to derive this first period end date:

• EOMONTH(Date,Months) returns the last day of the month so many months from the date specified. For example, =EOMONTH(11-Dec-13,14) would be 28-Feb-15, i.e. the end date 14 months after the end of December 2013. EOMONTH() has been discussed previously (please see Asking for a Date);
• MOD(Number,Divisor) returns the remainder when Number is divided by the Divisor. For example, =MOD(17,6) is 5, since 17/6 = 2 remainder 5. Again, MOD() has been discussed previously (please see A Modicom of MOD).

With trial and error the number of months we need to add on can be calculated as follows:

and therefore if we call this equation our Additive_Factor, then the reporting end date will be:

In the example file, I have checked my workings, viz.

Furthermore, a robust yet flexible time series can be constructed:

Referring to Asking for a Date once more, some readers may not wish to use this formula if the file could be opened in both Excel 2003 / earlier, and Excel 2007 / later versions (this formula may corrupt). In the Excel file, I also provide an alternative formula that does not require the EOMONTH() function.

Word to the Wise…

Even allowing for flexible start dates and “Reporting Month Factors”, the above will not work in all circumstances. Other periodicities may be sought, whilst some businesses require weekly reporting or 5-4-4 week period regimes. Nonetheless, the above approach can be modified and extrapolated to consider such complications.