How to properly date an Excel model

Most forecast models project key outputs over multiple periods. Typically, these periods are not headed “Time 1”, “Time 2”, etc., like our usual generic examples. Instead, end dates are displayed to help users understand payback periods, seasonality, trends, and so on.

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

The above is an example of time series data in a real-life financial model. My question is this, though: Are all of these rows absolutely necessary?

I would suggest not.

Essentially, three lines are 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), eg, 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, eg, end of financial year or quarter. By having both the start date and end date defined, a modeller can determine the number of days, weeks, or months in the period, which financial year the period pertains to, 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 in Excel are actually serial numbers, which may differ depending upon the underlying operating system (eg, day 1 for Microsoft Excel for Windows is 1 January 1900, whilst day 1 is 1 January 1904 for earlier versions of Microsoft Excel for 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 formulas 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. Consider the following simple example:

I have selected an arbitrary start date (Model_Start_Date) of 17 January 2020 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 March, June, September, and December (say). By entering a Periodicity of three (3) and specifying an Example_Reporting_Month of any of 3, 6, 9, or 12, this will provide sufficient information to work out the quarter ends, ie, 31-Mar, 30-Jun, 30-Sep, and 31-Dec. 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 (ie, 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.

I can use an array formula to calculate this month number dynamically, but this is not necessary. 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-20,14) would be 28-Feb-22, ie, the end date 14 months after the end of December 2020.

• 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.

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

=MOD(Periodicity+Reporting_Month_Factor-MONTH(Model_Start_Date),Periodicity)

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

=EOMONTH(Model_Start_Date,Additive_Factor)

In the example file, I have checked my workings, as shown below.

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

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, “last Thursday of the month”, or 5-4-4-week period regimes. Nonetheless, the above approach can be modified and extrapolated to consider most complications.

— Liam Bastick, FCMA, CGMA, FCA, is director of SumProduct, a global consultancy specialising in Excel training. He is also an Excel MVP (as appointed by Microsoft) and author of Introduction to Financial Modelling. Send ideas for future Excel-related articles to him at liam.bastick@sumproduct.com. To comment on this article or to suggest an idea for another article, contact Jeff Drew, an FM magazine senior editor, at Jeff.Drew@aicpa-cima.com.